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.
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.
Select sort Sales by value from Largest to Smallest. Don't forget to check the My data has headers box.
You can also add another sort level. Click Add Level and sort the Region alphabetically from A to Z.
Sorting complete. Good job!
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:
1. Click on the first data cell (manager).
2. Click Data (1) and then press Sort (2).
3. Select "Year of Employment" to sort by, then press OK.
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.
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:
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.
1. Click Data (1) and then click Sort (2).
2. Select a column (1), then select a color to sort by (2), then order (3).
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.
Go to the ribbon on the Data tab. Click the Sort button.
The sort dialog will appear. Click the Options button.
Click 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.
Here you can see how Excel sorted the data. The entire row 7 is sorted in descending order.
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.
In column B, type the formula that corresponds to the value in column A. In B2, the formula would be: =LEN(A2)
Drag the formula down.
Now you can see the character count of the word from column A in column B.
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.
Excel sorts words from the shortest to the longest.
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:
Select all data.
Press CTRL + G and click Special
Choose Blanks
Press Home (1), select a color from Cell Styles (2) menu.
Note: Give the empty cells a unique color. It is recommended to use only one color in the actual data.
Select all data again.
Click Data (1) and then Sort (2).
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.
Select all colored lines, right-click the selected lines (2) and select Delete (3).
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.
After selecting the column headings, select Data> Filter - this will add drop-down icons to the column headings.
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?