How to Sort Data in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on August 12, 2023

This tutorial demonstrates how to sort data in Excel and Google Sheets.

how to sort intro

 

Sort a Single Column of Text

  1. Select any cell in the column of text you want to sort.

how to sort list

  1. In the Ribbon, go to Data > Sort & Filter, and then click the A→Z sort button.

how to sort ribbon

The column is sorted into alphabetical order, as shown below.

how to sort list sorted

Sort Multiple Columns

  1. Make sure your cursor is position within the data table that you want to sort, and then in the Ribbon, go to Home > Editing > Sort & Filter > Custom Sort.

how to sort custom sort ribbon

  1. Choose a Column to Sort by, what to Sort On, and the Order you want to sort. Then click Add Level.

how to sort multiple add row

  1. In the Then by drop down (below Sort by), choose a new column to sort. Continue to add each column you need.

how to sort add level

  1. Click OK to sort.

how to sort custom sorted

Tip: See How to Sort Multiple Columns for more information.

Sort by Custom List

It’s easy to sort alphabetically in Excel, but you can also sort by a more complex custom list.

  1. Select the data you want to sort, and then in the Ribbon, go to Home > Editing > Sort & Filter > Custom Sort.
  2. Choose the Column to Sort by and what to Sort On. Then in the Order drop down, choose Custom List…

how to sort custom list

  1. Select the custom list you want to sort by. If you don’t see the list you need, see How to Sort With a Custom List in Excel to learn how to create a new list.

how to sort dialog lists

  1. Click OK and then OK again to sort the data.

how to sort sorted custom list

Sort Numbers

You can sort numbers from smallest to largest, or largest to smallest.

  1. Select any cell in the column of numbers you want to sort.
  2. In the Ribbon, go to Home > Editing > Sort & Filter > Sort Smallest to Largest.

how to sort smallest to largest

The numbers are then sorted.

how to sort numbers sorted

Sort and Expand Selection

When your data is within a range of other data columns, Excel detects that you might need to expand the selection to sort out. If you do not do this, you end up sorting just the individual column and you may end up invalidating the data.

  1. Select the column to sort in your data table.

how to sort select single column

  1. In the Ribbon, go to Data > Sort & Filter, and then click the A→Z sort button.

how to sort select single column ribbon

  1. Choose Expand the selection, and then click Sort. This sorts all columns while keeping the rows together.

how to sort select single column expand

Sort Dates

  1. Select any cell in a column of dates.

how to sort dates list

  1. In the Ribbon, go to Home > Editing > Sort & Filter > Sort Oldest to Newest.

how to sort select dates ribbon

  1. The rows are sorted with the row with the oldest date now being at the top of the data range.

how to sort dates sorted

Sort by Color

  1. Select any cell in the table you want to sort.

how to sort color list

  1. In the Ribbon, go to Home > Editing > Sort & Filter > Custom Sort.

how to sort custom sort ribbon

  1. Choose any column to sort by, and then in the Sort On drop down, choose Cell Color.

how to sort color sort on

  1. In the Order drop down, choose the color you want to display at the top of the sheet.

how to sort color order

  1. In the rightmost drop down, choose whether to sort the color at the beginning or end of the dataset. For this example, choose On Top, so orange rows are moved to the beginning.

how to sort color sort on order

  1. Then click Add Level.

how to sort color add level

  1. Use the same Column and leave Sort On as Cell Color. For Order, choose the color you want to display next and leave the last drop down as On Top. You can continue to add as many levels as you need.

how to sort add levels

  1. Click OK to sort.

how to sort color sorted

Horizonal Sort

You can also sort horizontally in Excel. If your field headings are set up as rows rather than columns, you don’t have to transpose the table to sort it.

  1. Select the range to sort (here, C2:I7). Note that Column B (with the field headings) is excluded from the selection.

how to sort-horizontal 1

  1. In the Ribbon, go to Home > Sort & Filter > Custom Sort.

how to sort-horizontal 2

  1. Click Options.

how to sort-horizontal 3

  1. Choose Sort left to right, and click OK.

how to sort-horizontal 4

  1. Choose a row to sort (e.g., Row 2 to sort by Salesperson). Leave the Sort On as Cell Values and the Order as A to Z, and then click OK.

how to sort-horizontal 5

The data is then sorted from left to right based on the row chosen in Step 4.

how to sort horizontal 6

Sort Filtered Data

If the data has a filter applied to it, each column has sort options available via filter buttons.

  1. First, add a filter if there isn’t one already. In the Ribbon, go to Data > Sort & Filter > Filter.

how to sort add filter

  1. Notice a small arrow (filter button) at the top of each column of data. Click the arrow in the column of data you want to filter, and then tick the value to filter by (e.g., Salesperson Brown).

how to sort filter

  1. Select a different column to sort (e.g., Wheat) and this time, Sort Largest to Smallest.

how to sort filtered

This sorts the filtered data.

how to sort filter sorted

  1. Select a cell back in the column that you filtered on and click Clear Filter From… in the filter drop down.

how to sort filter clear

Notice that only the data that was visible during the filter was included in the sort.

how to sort filter sorted visible

Sort Table

Filtering as described above is also available when your data is formatted as a table.

how to sort filter table

Click the filter arrow for the column you want to sort and choose:

  • A→Z or Z→A for text columns;
  • Oldest to Newest or Newest to Oldest for date columns; or
  • Smallest to Largest or Largest to Smallest for number columns.

how to sort filter table sort

Sort Formulas

If you have a worksheet that constantly changes, you might need to re-sort the worksheet again and again with each change, since sorting is not dynamic. One way around this is to use a formula to automatically sort the data for you.

Consider the data below.

how to sort city list

You could easily sort this data with Excel’s Sort feature, but preserve the data order in Column B with these steps:

  1. Insert a column to show how the data should be sorted. In this “helper” column, use the COUNTIF Function.

how to sort city list countif

  1. Copy the formula down to the other rows.

how to sort city list countif copy down

  1. Then, use the next column for a formula containing the containing the INDEX, MATCH, and ROWS Functions to put the first column into alphabetical order.
    =INDEX($B$2:$B$12,MATCH(ROWS($C$3:C3),$C$2:$C$12,0))

how to sort city list index match

  1. Copy the index–match formula down to the rest of the rows.

how to sort city list index match sorted

  1. If you change one of the list items (e.g., Cities), then the sorted column automatically changes as well.

how to sort city list amend list

The SORT Function

The SORT Function enables you to sort values in a range or an array.

how to sort city sort function

Tip: You could use the SORT BY Function to achieve a similar result.

Sort and Ignore Blanks

If you sort data that has blank rows in it, Excel pushes the blank rows to the bottom of the data. If you do not want the blank rows to be moved, you must hide the blank rows before you sort.

  1. Highlight the data and then, in the Ribbon, go to Editing > Find & Select > Go To Special.

how to sort go to special

  1. Choose Blanks, and then click OK.

how to sort blanks

  1. Now in the Ribbon, go to Cells > Forms > Hide & Unhide > Hide Rows.

how to sort hide rows

  1. Then, select the column to sort and in the Ribbon, go to Home > Editing > Sort & Filter > Sort Smallest to Largest.
  2. The data is then sorted in ascending order for the Corn column, but the hidden rows remain in place.

how to sort hidden sort

Sort Without Duplicates

If you have a list of numbers that you want to sort into ascending or descending order, but the list has a some duplicates in it, you can use formulas to sort the numbers while excluding the duplicates.

  1. First, add a helper column containing the COUNTIF Function in the formula.
    =COUNTIF(B4:B$13,"="&B3)

how to sort duplicate list

  1. Copy the COUNTIF formula down to the remaining rows.

how to sort duplicate copy down

  1. All unique numbers have a zero in their helper column, while duplicates have a positive or nonzero number.
  2. Write a simple IF statement to extract all the unique numbers, and then copy this formula down to the remaining rows.
    =IF(C3=0,B3,"")

how to sort duplicate extract

  1. Finally, create another helper column to sort values from Column D without any blanks. Another formula can be used to achieve this using the LARGE and ROW Functions.
    =LARGE($D$2:$D$13,ROW(D2)-ROW(D$2)+1)

how to sort duplicate sorted

Issues That May Affect Sorting

For data to sort correctly, it needs to first be set up correctly. A few things to consider before sorting:

  • Make sure there are no leading spaces in your data.
  • Make sure that the column that you are sorting on contains the same type of data and not a mixture of text and numbers.
  • It is useful to have a column heading for each column of data when sorting, then make sure that when you use Custom Sort, My data has headers is ticked.
  • It is preferable to not have any blank rows or columns within your data.
  • If your data contains formulas referencing the sheet name, you might need to remove the sheet name from the formula, if the reference is on the same sheet.

AutoMacro | Excel Automation Made Easy | Free Trial

Sort With VBA

You can also use VBA code to sort. Here’s an example of a simple macro to sort by the first column on a range of data:

Sub SortMe()
  Worksheets("Sheet1").Sort.SortFields.Clear
  Range("B3:G100").Sort Key1:=Range("B3"), Header:=xlYes
End Sub

Sort in Google Sheets

Google Sheets can’t automatically identify a header row in a dataset. To identify a header row, first freeze the row you want to use as the header.

Sort Sheet

  1. Click in the row you want to freeze and then in the Menu, go to View > Freeze > 2 rows (or n rows, depending on your layout).

how to sort gs freeze

  1. Then select any cell in the column you want to sort. In the Menu, go to Data > Sort sheet > Sort sheet by column…

how to sort gs sort column

  1. You can sort text, numbers, and dates by selecting the relevant column and going to Sort sheet.

how to sort gs sort numbers

Sort by Range

To sort by more than one column, you need to sort the data range.

  1. Select the sort range, and then in the Menu, go to Data > Sort range > Advanced range sorting options.

how to sort gs range range

  1. Check Data has header row, and then select the column to sort by. Choose A→Z to sort alphabetically or Z→A to sort in reverse order.

how to sort gs range header row

  1. Click Add another sort column.

how to sort gs range add row

  1. Select the next column you wish to sort by, and once again, choose A→Z or Z→A.

how to sort gs range add column

  1. Continue to add as many columns as you need to your sort range. If you add one by mistake, click the Delete button to remove it from the sort.

how to sort gs range delete column

  1. Click Sort to sort the data.

how to sort gs columns sorted

AutoMacro | Excel Automation Made Easy | Free Trial

Sort Filtered Data

Sorting filtered data works the same in Google Sheets as it does in Excel.

  1. First, add a filter. Click within your data and then, in the Menu, go to Data > Create a filter.

how to sort gs create filter

  1. Then, filter on a selected column, for example, filter the Region column by the value Chicago.

how to sort gs filter

  1. Sort A→Z on the Wheat column.
    (Google Sheets doesn’t differentiate between numbers and text for sort order options. Here, A→Z means lowest to highest.)

how to sort gs filter sort

  1. Remove the filter from Region; click the filter button, then Select all, then OK.

how to sort gs filter select filter

Note that the Wheat column in other regions (e.g., New York, Boston) is not sorted, but the Chicago Wheat numbers are sorted (in cells visible with the filter on).

Sort by Color

You can sort by color in Google Sheets, but you need to add a filter first.

  1. Click within your data, and then in the Menu, go to Data > Create a filter.

how to sort gs create filter color

  1. Then, click one of the column’s filter button. Go to Sort by color > Fill color, and then choose the color (e.g., light green 2) you want at the top of the table.

howtosort gs filter color

  1. To sort by a second color, select a different column and repeat Step 2 for the second color (e.g., light red 2).

howtosort gs create filter color second

Rows with the second sort color (red) from Step 3 are sorted to the top of the table, and rows with the first sort color (green) from Step 2 are directly below the red rows.

howtosort gs color sorted

Sort and Ignore Blanks

To ignore blank rows in Google Sheets, first add a helper column to filter out the blank rows.

  1. In the column to the right of your data, enter a formula to count the number of populated cells in the selected row and copy the formula down to the rest of the rows in your data.
    =COUNT(B3:G3)

how to sort gs filter blank count

  1. Then add a filter to your data, and filter the “blank” column to only show the rows where the cells are populated.

how to sort gs filter blank select

  1. Now in a different column, choose Sort A→Z, and then click OK.

how to sort gs filter blank sort

Only visible data gets sorted.

how to sort gs filter blank

  1. Remove the filter on the Blank column. Like in Excel, the blank (filtered out) rows haven’t moved.

how to sort gs filter blank remain

Sort With Formulas

As you do in Excel, you can also use the COUNTIF, INDEX, MATCH, and ROWS Functions in formulas to sort in Google Sheets.

how to sort gs formula index match

Or, use the SORT Function to sort in a column to the right of your original data.

how to sort gs formula SORT

You can sort duplicates using Formulas in the same way that you sort duplicates using formulas in Excel.

how to sort gs duplicates

More Sort Tutorials

Read more of our tutorials on sorting in Excel and Google Sheets.

Sort Data
Advanced Sorting
Keep Rows Together During Sort
Put Things in Alphabetical Order
Random Sort
Reverse the Order of Data
Sort a Column Alphabetically
Sort and Ignore Blanks
Sort by Color (Highlighted Cells)
Sort by Last Name
Sort By Month
Sort By Number
Sort Dates in Chronological Order
Sort IP Addresses
Sort Multiple Columns
Sort Multiple Rows Horizontally
Sort Subtotals
Sort With a Custom List
Sort Without Duplicates
Undo a Sort
Calculationyes
Keep Variable Cell Constant
Lock / Freeze Cell in Formula
Copy & Pasteyes
Copy Every Other Row
Databaseyes
Clean Up Data
Create a Searchable Database
Drop-Down Listyes
Alphabetize a Drop Down-List
Duplicatesyes
Show Only Duplicates
Filtersyes
Apply Multiple Filters
Clear All Filters
Copy Filtered (Visible) Data
Filter by Color
Filter Merged Cells
Filter Rows
Hide Blank Rows
Hide Rows Based on Cell Value
Remove Filters
Show or Hide AutoFilter Arrows
Show Rows With Specific Text
Use Custom AutoFilters
Find & Selectyes
Select Every Other Row
Select Multiple Cells
Forecastyes
Quick Analysis Tool
Format Cellsyes
Make All Rows and Columns the Same Height and Width
Formulasyes
Custom Sort List with Formula
Sort Multiple Columns with Formula
Sort with SMALL & LARGE Functions
Functionsyes
SORT Function Examples
Insert & Deleteyes
Delete Blank Rows
Delete Every Other Column
Delete Every Other Row
Insert Blank Row After Every Other Row
Objectsyes
Make a Combo Box
Outlineyes
Expand or Collapse Rows or Columns
Text to Columnsyes
Split a Cell into Two or More Columns
VBAyes
VBA Sort Array
Sorting Data in Excel VBA

AI Formula Generator

Try for Free

See all How-To Articles