In this lesson, you will learn how to sort data in Excel. Sorting data can help you make your spreadsheets more organized and easier to read. It can also help you identify patterns and trends in your data.
Table of Contents
Sorting in Excel
To sort data in Excel, you first need to select the data you want to sort. In the example below, we want to sort the data in the range A1:C11.
Once you have selected the data, you can sort it by clicking on the Sort button in the Data tab.
In the Sort dialog box, you can choose the column you want to sort by, and the order you want to sort it in. We want to sort the data by the Sales column in descending order.
You can also add another sort level by clicking on the Add Level button. This will allow you to sort the data by another column, such as the Region column.
Now your data is sorted, making it easier to analyze or present.
Download sample spreadsheet for free.
How to sort by more than three columns?
It is easy to sort more than three columns.
Click on the first data cell.
Click Data and then press Sort.
Sort by “Year of Employment”.
Repeat, but in the sort criteria, select the manager (or the most important column) and then click Add Level to select the second most important column.
Note that you need to repeat this step until you have sorted the columns from most important to least important. If the cells in the column change positions for the sort to take effect, Excel ignores them.
Sorting by color
You can also sort the colors. You may need this feature if you like to colorize your spreadsheets.
To sort the colors in the table, first prepare a colored data table like the one in the picture below.
Click Data and then click Sort.
Select a column and then select a color to sort by. Finally, select the order.
Note that after completing this step, you need to click the Add Level button, sort by the same column, and select the color you want second. Continue this process until you have added all the colors in your data.
How to Sort by Row?
Did you know that you can also sort by rows? This trick can be useful for large spreadsheets with multiple columns.
First, prepare a data table.
Go to the ribbon on the Data tab. Click the Sort button.
Click the Options button.
Click Sort left to right.
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.
Sort the word list by the number of characters
Excel can sort data in a variety of ways. You can also sort data by the number of characters.
In column B, type the formula =LEN(A2).
Drag the formula down to the rest of the cells in column B.
Now you can see the character count of the word from column A in column B.
Then select the entire table. On the ribbon, go to the Data tab. In the Sort dialog box, check the My data has headers box and select Number of characters from the Sort by drop-down list. Select Smallest to largest from the Order drop-down list.
Excel sorts words from the shortest to the longest.
How to Sort Out Blank Rows?
Sorting out blank rows depends on how many there are and whether they should be there or not. First, look at these lines that have both filled and blank cells:
Select all data.
Press CTRL + G and click Special
Select Blanks
On the Home tab, select a color from the Cell Styles menu.
Select all data again.
Click Data (1) and then Sort (2).
Select a category to Sort by, select a cell color in Sort on, No cell color in the order, On top.
Select all colored lines, right-click the selected lines and select Delete.
How to sort whole worksheet by one column?
Whether you have a simple dataset or a large datasheet, sorting is a simple and effective way to organize and analyze your data in Excel. You can sort data by a variety of options, including the built-in filtering feature and custom sorts.
Regardless of the number of columns, rows, and cells in a worksheet, you can sort the entire worksheet by one column in seconds. Let’s see how you can sort whole worksheet by one column in Excel.
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.
Simply select the column you want to sort by. In the drop-down menu, you will see the available sorting options, which depend 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.
