Custom Sort Order in Google Sheets: Step-by-Step Guide

A custom sort order lets you arrange Google Sheets rows in a sequence you choose, such as High, Medium, Low, instead of alphabetically. You can do this with a temporary helper column, or create a reusable custom list through Sheets’ sort settings when that option is available in your interface.

What You’ll Need

A Google Sheets spreadsheet with the values you want to sort.
Permission to edit the sheet. If other people are working in it, consider using a filter view so your sort does not change their view.
A clear list of the desired order, such as Not started, In progress, Complete.

Step-by-Step

Identify the rows and sort key. Decide which column contains the categories that determine the order. Check that each row’s other data should move along with that category. For example, when sorting task status, the task name and due date must remain attached to the status in the same row.
Make sure the data has headers and no gaps. Use one header row, followed by a continuous block of records. Avoid blank rows inside the data range. If the data has multiple columns, select the whole table rather than just the category column.
Choose a method. For a one-time or highly specific sort, use a helper column. This is the most dependable approach because you define the exact ranking yourself. For a reusable list, check whether your Sheets interface offers a custom-list option in its advanced sort settings; availability and labels can differ by account and interface.
Add a helper column. Insert a blank column beside the data and give it a header such as Sort rank. For example, if statuses are in column C and your desired sequence is High, Medium, Low, enter this formula in the first data row: =SWITCH(C2,"High",1,"Medium",2,"Low",3,99). Replace C2 and the labels with your actual cell and category names. The number 99 puts any unlisted value after the listed categories.
Fill the formula down. Select the cell containing the formula and use its fill handle to copy it through every data row. Check a few results: High should show 1, Medium 2 and Low 3. If the formula gives an error, verify that the quotes and commas are in place and that your spreadsheet’s locale uses commas as separators. Some locales require semicolons.
Select the complete data range. Include every column in the table, including the helper column, and exclude unrelated notes or other tables. Do not select only the category or rank column: sorting a single column can detach values from their corresponding records.
Open the sort controls. Choose Data > Sort range > Advanced range sorting options. If your selection includes a header, check Data has header row. In the sort-by control, choose Sort rank, then select A → Z for ascending order. The numbered ranks will determine the sequence.
Confirm and inspect the result. Click Sort, then check several rows to confirm that the desired categories appear in order and the associated information stayed together. If anything looks wrong, undo immediately with Edit > Undo or Ctrl+Z on Windows/ChromeOS, or Command+Z on Mac.
Remove or retain the helper column. Keep it if you will sort the data again, and hide it if you do not want it displayed. If you delete it, do so only after the sort is complete; the row order remains, but you will need to recreate the ranking to repeat the sort.

Common Mistakes

Sorting only one column: This can mismatch names, dates and other row details. Always sort the full table range.
Forgetting the header setting: If Sheets treats the header as a record, it may move into the sorted data. Mark the header row in the advanced options.
Using text labels without a rank: A normal A-to-Z sort puts categories in alphabetical order, not necessarily your preferred order. Give each category a numeric rank.
Leaving out unexpected values: A misspelling or new category might not match the formula’s listed options. The example assigns unmatched values rank 99; replace this fallback with a suitable number if needed, and inspect those rows.
Including unrelated cells: A range that omits some table columns can separate details from their records. Select the whole contiguous data set.
Sorting shared data without warning: A regular sort changes the sheet’s row order for other editors. Use a filter view when you need a personal view instead.

Tips

Write the intended order down before sorting. This prevents accidental gaps or reversed rankings.
Use simple ascending numbers such as 1, 2, 3. They are easy to review and expand later.
For categories in a fixed range, a lookup table plus a lookup formula can be easier to maintain than editing a long formula. Keep the lookup table somewhere clearly labeled.
When multiple rows share a category, you can add another sort level, such as Sort rank first and Due date second, to group categories and order items within each group.
Use Data > Create a filter or a filter view when you want users to sort or filter interactively without permanently changing the main view. A filter view can be especially useful in a shared sheet.

Troubleshooting

The order is still alphabetical: Confirm that you sorted by the numeric helper column, not the original category column. Make sure the ranks are numbers rather than text; remove any leading apostrophes or spaces that cause values to be treated as text.

The rows no longer match: Undo immediately. Select the full table, including all fields that belong to each record, and sort again. If the sheet has separate sections, sort each section as its own range.

The header moved: Undo, reopen the advanced range sorting options, and enable the header-row checkbox. Check that the selected range begins with the actual header row.

The formula returns an error or unexpected rank: Check that the cell reference points to the right category column and that the text matches exactly, including spaces. Use TRIM in a cleanup column if values contain extra spaces, or correct inconsistent labels before sorting.

You cannot find a custom-list setting: Use the helper-column method. It works by sorting numeric ranks and does not depend on a particular custom-list feature being present.

Sorting changes what collaborators see: Undo if necessary, then create a filter view from the Data menu and sort within that view. The exact menu label can vary, but look for filter views in the Data menu.

Frequently Asked Questions

Can Google Sheets sort by a custom order without a helper column?
Some interfaces provide custom-list controls in advanced sorting, but the helper-column method is a reliable way to specify any order. It also makes the ranking visible and easy to check.

Can I sort by more than one custom rule?
Yes. Add separate helper columns for separate priorities, or use multiple sort levels. Set the most important criterion first, followed by the next one.

Will sorting change the original data?
A regular sort rearranges rows in the sheet. To preserve the original order, duplicate the sheet first or use a filter view for a temporary arrangement.

How do I put unknown categories last?
Give unmatched values a fallback rank higher than every listed category, such as 99 in the example. Check that no intended category is missing from the formula.

Can I reuse the same order later?
Yes. Keep the helper column and sort the full table by it again. You can also maintain a small lookup table of category names and ranks for easier updates.

Why does my formula use semicolons instead of commas?
Google Sheets uses separators based on spreadsheet locale settings. If commas produce a parse error, replace the formula’s argument commas with semicolons.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →