How To Sort In Excel

You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

One Column

To sort on one column, execute the following steps.

Result:

Multiple Columns

To sort on multiple columns, execute the following steps.

The Sort dialog box appears.

2. Select Last Name from the ‘Sort by’ drop-down list.

4. Select Sales from the ‘Then by’ drop-down list.

Result. Records are sorted by Last Name first and Sales second.

Revert To Original Sort Order In Excel

Bottom line: Learn how to retain and revert to the original sort order of a range or table in Excel.

Skill level: Beginner

Video Tutorial

Often times we receive a data set that does not have a column of sequential numbers or dates. If we sort one or more columns in the range, it can be difficult or impossible to revert back to the original sort order.

One way to solve this problem is to add a column of sequential numbers to the data set. I typically refer to this as an index column.

After sorting the data you can then go back and sort the index column to restore the original sort order.

Setting up the Index Column

It’s important to setup the index column BEFORE you apply any sorting to the data.

Here are the steps to create the index column:

Type a 1 in a blank column to the right of the data range/table.

Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…

IMPORTANT: If you are NOT using an Excel Table then you will need to reapply the filters to include this new column. This must be done BEFORE you sort the other columns so that the index column is included in the sort.

If you are using an Excel Table then you do not have to worry about step 4. The new column will automatically be included in the table as long as you create it directly to the right of the last column. See the video above for more details.

Checkout my video on a Beginner’s Guide to Excel Tables to learn more about the benefits of using this awesome feature of Excel.

Here is another method for using the fill handle.

Checkout my article on Fill Handle Hacks to learn more about this technique.

Other Techniques for Retaining Sort Order?

My friend Robbie asked me this question about retaining sort order the other day, which sparked the idea for this post, and this index column method is the one I’ve always used.

I’ve also mentioned it before in my article on how to prevent Excel from freezing when deleting rows.

However, I’m curious to know if you have other techniques or methods to solve this problem. Is there a different or better way?

How To Sort Data By Color In Excel?

How to sort data by color in excel?

When you using a worksheet, sometimes you may fill the rows or cells with various colors to make the worksheet much readable. And sometimes you want to sort the cells by color in Excel. In this case, you can use the sort function to sort the data by color quickly as follows:

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier…

Read More… Free Download…

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse Anything:

Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.

More than 20 text features:

Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.

Merge Tools

: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.

Split Tools

: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.

Paste Skipping

Hidden/Filtered Rows; Count And Sum

by Background Color

; Send Personalized Emails to Multiple Recipients in Bulk.

More than 300 powerful features;

Works with Office 2007-2024 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Read More… Free Download…

1. Select the range that you want to sort the data by color.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse:

Quickly insert

complex formulas, charts

 and anything that you have used before;

Encrypt Cells

with password;

Create Mailing List

and send emails…

Super Formula Bar

(easily edit multiple lines of text and formula);

Reading Layout

(easily read and edit large numbers of cells);

Paste to Filtered Range

Merge Cells/Rows/Columns

without losing Data; Split Cells Content;

Combine Duplicate Rows/Columns

… Prevent Duplicate Cells;

Compare Ranges

Select Duplicate or Unique

Rows;

Select Blank Rows

(all cells are empty);

Super Find and Fuzzy Find

in Many Workbooks; Random Select…

Exact Copy

Multiple Cells without changing formula reference;

Auto Create References

to Multiple Sheets;

Insert Bullets

, Check Boxes and more…

Extract Text

, Add Text, Remove by Position,

Remove Space

; Create and Print Paging Subtotals;

Convert Between Cells Content and Comments

Super Filter

(save and apply filter schemes to other sheets);

Advanced Sort

by month/week/day, frequency and more;

Special Filter

by bold, italic…

Combine Workbooks and WorkSheets

; Merge Tables based on key columns;

Split Data into Multiple Sheets

;

Batch Convert xls, xlsx and PDF

More than 300 powerful features

. Supports Office/Excel 2007-2024 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More… Free Download… Purchase… 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint

, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

Increases your productivity by

Read More… Free Download… Purchase… 

How To Sort By Color In Excel * Productivity Portfolio

Why Use Color

Perhaps, at one-time, color sorting and filtering wasn’t available, but you can do it now. The tutorial below can be used for font colors or cell color. For these instructions, I’ll be using Microsoft Office 365. However, the steps can be used in older Excel versions.

It’s probably my laziness, but I find it useful to change a cell’s background color. I might use this when I’m prioritizing or grouping a list. I need to highlight a cell and choose a background color.

In my screen snap above, I’ve applied the cell background color to Column C. However, some people might prefer using color across a row. In the second screenshot below, I used conditional formatting. Regardless of whether you’re using columns or rows, the process is the same. It also works with Excel tables.

How to Sort by Color in Excel

Tick the My data has headers checkbox in the top-right if your worksheet uses them.

Select your first fill color and keep the Order value as On Top.

Add in your other color levels. If you prefer, you can also use the On Bottom option.

You should now see your sorted list by color. You’re not limited to just 3 colors, but you can keep adding if they’re on your sheet. You also don’t need to enter the last color as Excel will put it on the bottom. In my case, that was ” No Cell Color.”

Select the column you wish to filter that has color.

Select your cell color from the side menu.

The same process works if you wish to sort by font color.

How to Filter by Color in Excel

Excel allows you to sort and filter by color

The color can be either the cell background color or font color

You can place colors on top or bottom

The system knows which colors you’ve used

No cell color is for normal cells

You can add multiple color sorts

Sort rules are processed in order

Some people prefer to filter instead of sort. This is great if you want to focus in on certain items. Even better, the procedure is shorter.

In my example, I was using a background color. However, when I added colored text, Excel adjusted the options. On the side menu for Filter by Color, you would also see Filter by Font Color.

Quick Takeaways

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2024-04-17

Sorting And Filtering Data With Excel

As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

Sorting Data

Go down to the Sort option – when hovering over Sort the sub-menu will appear

Select Expand the selection

The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

Filtering Data

The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

Go to the Data tab on Excel ribbon

Select the Filter tool

Select Eastern Company from the dropdown menu

Select Dylan Rogers from the Salesperson dropdown menu

Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

The Sort & Filter Tool

In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:

As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!

Use Learn Excel Now to help with all your Excel questions and training needs.  We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.