Sorting data is a great feature available in Google Sheets, which helps users organize the data properly.
You can sort data for any value, such as alphabetic, numeric, text, and dates. The good thing is that different values are sorted in the same way. You can also sort the data in ascending and descending order based on the cell color and font color applied to the cells in the data set.
This tutorial will show you how to sort data based on a single column and multiple columns in different ways.
Sort Data using the Filters in Google Sheets
Sorting data using filters is the quickest way to sort it.
- Click anywhere in the data set, go to the “Data” tab, and click on “Create a filter” to apply the filters to the data set.
- Now, click on the filter icon of the column header based on whose values you want to sort the data, and then click on “Sort A-Z” or “Sort Z-A” to sort in ascending or descending order.
Sort Selected Data Range by Single Column in Google Sheets
- First, select the data range you want to sort and then go to the “Data” tab.
- From here, hover the cursor on the “Sort range” option and then click on “Advanced range sorting options” to open the “Sort range” dialog box.
- In the “Sort range” dialog box, first check on “Data has header row” and then choose (A-Z) to sort the data in ascending order or (Z-A) for descending order.
- After that, select the column header name within the “Sort by” field based on which you want to sort the data range and then click on the “Sort” button.
- The data range will be sorted based on column A values in the ascending order we selected in the above step.
Sort the Entire Data Set by a Single Column in Google Sheets
If you want to sort the entire sheet data instead of the selected range, you have first to freeze the header row of the data set; otherwise, the header column will also get sorted.
- First, to freeze the header row, go to the “View” tab and then hover the cursor on the “Freeze” option and then choose the “1 row” option, and it will freeze the first row (header row) of the spreadsheet.
- Afterward, go to any column cell based on which you want to sort the data and then go to the “Data” tab.
- From here, hover the cursor on the “Sort sheet” option and then click on “Sort sheet by Column B (A-Z) if you want to sort in ascending order or “Sort sheet by Column B (Z-A) for descending order”
- At this moment, the entire sheet will be sorted by column B values in ascending order because we were in the cell within column B while initiating the sorting steps and chose the (A-Z) sorting option.
Note: Freezing the header row is a mandate for the above-mentioned process of sorting data otherwise the header will also get sorted. Secondly, this process of sorting data will get applied to the entire spreadsheet not only to the selected data range even if you select the data range before sorting it.
Sort Data by Multiple Columns in Google Sheets
Just like a single column, you can sort data based on multiple columns.
- First, select the data range you want to sort and then go to the “Data” tab.
- From here, hover the cursor on the “Sort range” option and then on “Advanced range sorting options” to open the “Sort range” dialog box.
- In the “Sort range” dialog box, first tick mark on “Data has header row” and then choose (A-Z) to sort the data in ascending order or (Z-A) for descending order.
- After that, click on the drop-down arrow within the “Sort by” field and choose the header name, based on which you want data to be sorted first.
- Now, click “Add another sort column” to choose the second header, which will sort the data again after the first chosen header.
- Here, choose the “second header” as we have chosen “Expertise” in the below image, and then click the “Sort” button.
In the below image, you can see the data first get sorted by the column with header “Name” values and then by column with header “Expertise” values.
Sort Data by Number and Dates in Google Sheets
In Google Sheets, no different steps or options exist to sort the numeric, or you want to sort by dates. The sort function works similarly with numbers, dates, and alphabet values. As explained above, the steps of sorting them are the same as sorting the alphabetic values.
If you have data as numbers in the column you want to sort, it will sort that column values from highest to lowest if you choose (Z-A) and lowest to highest if you choose (A-Z).
As you all know, spreadsheets recognize dates as numbers, so when you apply the sorting on the column having dates, Google Sheets recognizes them as numeric values and sorts the values from latest to old if you choose (Z-A) and older to latest if you choose (A-Z).
Sort Data by Cell and Font Color
In Google Sheets, you can also sort the data based on colors (cell and font). Assume you have data as below with cell color or font color applied in it, and to sort that based on color, follow the below steps:
- First, go to the “Data” tab and click on “Create a filter” to apply the filters on the data set.
- After that, click on any of the column filters and then hover the cursor on “Sort by colour” and then “Fill colour” and then choose the color by which you want to filter the data set.
- When you choose the cell color in the above step (light orange), the cells with that chosen color will come on the top of the data set.
- To sort the data more with other cell colors as well, follow the same steps above again. This time, choose the other color (light blue), and your data will sort accordingly (the latest selected color will come on top).
- To sort the data further based on font color, after the “Sort by colour” option, hover the cursor on “Text colour” and then choose the font color. This time, the cells with the selected font color will come to the top.
Sort Data by Single Column using Sort Function
It also has a function called the “SORT”, by which you can copy and sort the data simultaneously at a new place within the same sheet and in the new spreadsheet without changing the real data.
This would be dynamic, meaning whenever you change anything in the real data, the sorting there will get updated accordingly.
=SORT(A2:D11,1,"TRUE")
=SORT(A2:D11,A2:A11,"TRUE")- First, copy the data headers and paste them in the new place where you want to have a sorted copy.
- After that, write the “SORT” formula in the first cell below the first column header of the data set as we wrote in cell F2.
- In the “SORT” formula, first, specify the data range that needs to be sorted (A2:D11), and after that write the column number or select that column range on behalf of which data needs to be sorted (1 means column A).
- At the end of the formula, write “TRUE” if you want data to get sorted in ascending order and “FALSE” in descending order. Close the parentheses and hit “Enter”.
- The moment, you hit “Enter”, the data will get copied and sorted there based on column A as we have captured “1” in the formula.
Sort Data by Multiple Columns using Sort Function
The formula to sort data by a single column and multiple columns is the same, the only difference is just for multiple columns sorting, you need to specify that additional column references additionally within the formula.
=SORT(A2:D11,1,"TRUE",2,“FALSE”)
=SORT(A2:D11,A2:A11,"TRUE",B2:B11,“FALSE”)In the image below, you can see that while using the same “SORT” formula we used in the above step to sort by a single column, this time we have just added “2” as the additional column to get sorted and “FALSE” as the sorting criteria for that, so that data will first get sorted by column 1 in ascending order and then by column 2 in descending order.
You can further expand the formula if you have more columns to add in sorting the data.
Tips for Sorting in Google Sheets
- Make sure your data does not have merged cells in the range you’re applying the filter to, as this can cause issues with sorting. Also, make sure to delete blank rows from the data.
- Consider using “Freeze” rows or columns (View > Freeze) for headers or important columns/rows that you want to keep visible when scrolling.