How to Sort in Excel

In this lesson you will learn to sort data in Excel. We have many examples of sorting for you to make your spreadsheets better and more useful. Remember that sorting date is not only for better visibility.

It gives also more opportunities and helps you to get much more. You will get to know it from below Excel article.

Sorting in Excel

Data is needed to learn how to sort data. Please refer to the table below.

Excel sorting table

You need sorting because it doesn't look professional. Sort your sales in descending order.

Sorting begins marking the data you want to sort. In this case, select the range of cells A1: C11. Then go to the Data tab on the ribbon and hit the Sort button. A dialog box will appear.

Excel Sort dialog box

Select sort Sales by value from Largest to Smallest. Don't forget to check the My data has headers box.

Excel Sorting

You can also add another sort level. Click Add Level and sort the Region alphabetically from A to Z.

Excel Sort second level

Sorting complete. Good job!

Excel sorting done

Download sample spreadsheet for free.

How to sort by more than three columns?

You can easily sort more than three columns. First, you need data that looks like this:

Excel sorting data table

1. Click on the first data cell (manager).

Excel sorting click first cell

2. Click Data (1) and then press Sort (2).

Excel sorting ribbon data sort

3. Select "Year of Employment" to sort by, then press OK.

Excel sorting by Year of Employment

4. Repeat, but in the sort criteria, select the manager (or the most important column) (1) and then click Add Level (2) to select the second most important column. Press OK again.

Excel sorting add level

Note: Repeat this step until you have sorted the columns from most important to least important. Note that if the cells in the column change positions for the sort to take effect, Excel ignores them.

After following the steps above, the sort would now look like this:

Excel sorting data sorted

Sorting by color

You can also sort the colors. You may need this, especially if you like to colorize your spreadsheets like I do.

To sort the colors in the table, first prepare a colored data table like the one in the picture below.

Excel sorting by data table color

1. Click Data (1) and then click Sort (2).

Excel sorting ribbon sort

2. Select a column (1), then select a color to sort by (2), then order (3).

Excel sort by color

Note: After completing this step, hit Add Level, sort by same in the column, and select the color you want second. Continue until you've added all the colors in your data, then hit OK.

How to Sort by Row?

Who said you only need to sort columns? Learn to sort by rows. This trick can be useful for large spreadsheets with many columns.

First, prepare a data table. Here is an example.

Excel sorting data table to sort by row

Go to the ribbon on the Data tab. Click the Sort button.

Excel sorting ribbon sort button

The sort dialog will appear. Click the Options button.

Excel Sort dialog window

Click Sort left to right.

Sort Options sort left to right

Now decide what you want to sort. I sorted row 7 (which is the sum in my table) in order from largest to smallest.

Excel sorting Way how to sort

Here you can see how Excel sorted the data. The entire row 7 is sorted in descending order.

Data sorted by row

ere's how to sort by rows in Excel.

Sort the word list by the number of characters

Excel allows you to sort data not only alphabetically, but also based on cell color or font color. You probably didn't know, but you can also sort by number of characters. To do this, you will need some data. Let the figure below be an example.


Sort Character Count Table

In column B, type the formula that corresponds to the value in column A. In B2, the formula would be: =LEN(A2)


Sort Character Count Formula

Drag the formula down.

Sort Character Count Drag

Now you can see the character count of the word from column A in column B.

Sort Character Count Value

Then select the entire table and go to Ribbon > Data > Sort. Check my data has headers and sort by number of characters from smallest to largest.

Sort Character Count Sort

Excel sorts words from the shortest to the longest.

Sort Character Count

How to Sort Out Blank Rows?

Sorting blank lines boils down to one thing, how many there are and whether they should be there or not. But first, look at these lines with both filled and blank lines:

Excel sorting and sort out data table

Select all data.

Excel sorting mark whole table

Press CTRL + G and click Special

Excel sorting special

Choose Blanks

Excel sorting blanks

Press Home (1), select a color from Cell Styles (2) menu.

Excel sorting cell styles

Note: Give the empty cells a unique color. It is recommended to use only one color in the actual data.

Select all data again.

Excel sorting mark data again

Click Data (1) and then Sort (2).

Excel sorting click data sort

Select a category to Sort by (1), select a cell color in Sort on (2), No cell color in the order (3), On top (4), then press OK.

Excel sorting no cell color

Select all colored lines, right-click the selected lines (2) and select Delete (3).

Excel sorting delete blanks

How to sort whole worksheet by one column?

Whether you have a simple dataset or a bulk datasheet, sorting is a simple and effective Excel feature that allows you to quickly organize and analyze your data. You can sort by a variety of options, some of which are built-in to the filtering feature and others you can create yourself.

Regardless of the number of columns, rows, and cells on a worksheet, you can sort an entire worksheet by one column in seconds. Let's see how you can sort whole worksheet by one column in Excel.

Data set selection

To sort your data, you need to organize it into rows and columns. You will also need dedicated column headers as this will make it easier to sort your data. Select the row that contains the column headings or drag and select all the column headings to start sorting.

Excel sort headers

After selecting the column headings, select Data> Filter - this will add drop-down icons to the column headings.

Excel sort drop down icons

Just select the column you want to sort by. In the drop-down menu, you will see multiple options depending on the data in the selected column. For columns that contain numbers, you can choose to sort from largest to smallest, smallest to greatest, or you can set a custom sort.

By selecting one of the sort options, you will sort the entire sheet based on that one column. All data in the other columns will also be sorted.

If you also select a text column, Excel will allow you to sort A to Z, Z to A, or according to a custom sort option you enter. Sorting an entire worksheet by one column is a simple and effective way to organize your data.

Further reading: 
How to use Filters?