How to Sort Excel Rows by Cell Background Color

You may have an Excel sheet where you used cell colors to categorize data. Sorting this data by those colors can be difficult because Excel’s standard sort does not see them. The color is a visual format, not a data value that sort commands recognize. This article explains how to add a sortable column based on color and then organize your rows.

Understanding Excel’s Color Sorting Options

Excel treats cell background color as part of the cell’s format, not its content. Therefore, you cannot sort by color using the standard A-Z or Z-A buttons. To sort by color, you must use features that can interpret the format. The primary methods are the Filter and Sort dialog, which include a ‘Sort by Cell Color’ option, and creating a helper column with a function. Both methods require your data to be in a proper table or list format with headers.

The Filter method is quick for simple lists. The Custom Sort dialog offers more control for complex sorts with multiple colors. The helper column method is the most powerful, as it translates colors into numbers you can sort, filter, and use in formulas permanently. Choose the method based on whether you need a one-time sort or a reusable solution.

Steps to Sort Using Filter or Custom Sort

For a quick sort when you have already applied cell colors, use the filter or sort menu. This method sorts the entire row based on the color in one column.

1. Select your data range
   Click any cell inside your data table. Ensure your data has column headers.
2. Open the Sort dialog
   Go to the Data tab on the ribbon. Click the ‘Sort’ button to open the Sort dialog box.
3. Set the first sort level
   Under ‘Column’, choose the column that contains the background colors. For ‘Sort On’, select ‘Cell Color’.
4. Choose the color and order
   In the ‘Order’ section, click the dropdown to select the first color you want on top. Choose ‘On Top’ for the sort position.
5. Add additional color levels
   Click ‘Add Level’ to sort by a second or third color. Repeat the process, selecting the same column but a different color for each new level.
6. Apply the sort
   Click OK. Excel will rearrange all rows, grouping them by your specified color order.

Using AutoFilter to Sort by Color

1. Apply a filter
   Select your data and go to Data > Filter, or press Ctrl+Shift+L. Dropdown arrows will appear in your headers.
2. Filter by a color
   Click the dropdown arrow for the column with colors. Hover over ‘Filter by Color’ and select one color to filter the view to only those cells.
3. Sort the filtered view
   With the filter applied, right-click a cell in that column. Go to Sort > Put Selected Cell Color On Top. This sorts the visible (filtered) rows.

Creating a Sortable Helper Column

For a permanent, formula-based solution, add a new column that assigns a number to each color. You can then sort by this number column. This requires using the GET.CELL function, which is part of Excel’s legacy macro language. You must define a name for it.

1. Add a new column
   Insert a column next to your data. Give it a header like ‘ColorCode’.
2. Define a named formula
   Go to Formulas > Name Manager. Click New. In the ‘Name’ field, type ‘CellColor’. In the ‘Refers to’ field, enter =GET.CELL(63,INDIRECT(“rc[-1]”,FALSE)). Click OK, then Close.
3. Enter the formula in the helper column
   In the first cell of your new column (e.g., B2 if colors are in A2), type the formula =CellColor. Press Enter.
4. Fill down the formula
   Double-click the fill handle (the small square at the cell’s bottom-right corner) to copy the formula down the entire column.
5. Sort by the new numbers
   Each cell’s background color now has a numeric code in the helper column. Select your entire data range and sort on the helper column using Data > Sort A-Z.

Common Mistakes and Limitations

Sort Does Not Move Entire Rows

If only a single column gets sorted, you did not select the entire data range. Always select all columns you want to keep together or click one cell within a formatted table before opening the Sort dialog. Excel will usually detect the contiguous range.

GET.CELL Formula Returns an Error

The GET.CELL function only works in Excel for Windows. It will not function in Excel for the web or Mac without specific compatibility settings. Also, ensure the named formula’s reference uses INDIRECT correctly to point to the cell with the color, typically one column to the left (rc[-1]).

Newly Colored Cells Are Not Sorted

If you add more colored data later, the sort order will not automatically update. You must reapply the sort. For the helper column method, you must copy the formula down to the new rows for the color codes to appear.

Sort Method Comparison

Item	Custom Sort Dialog	Helper Column with GET.CELL
Best For	One-time sorting of a finished list	Permanent, reusable color coding
Sorts by Multiple Colors	Yes, with full control over order	Yes, after assigning numeric codes
Works with New Colors Automatically	No, requires manual re-sort	No, formula must be extended
Excel Platform Support	All desktop and web versions	Windows Excel only
Complexity	Low, uses built-in menus	Medium, requires named formula setup

You can now organize your visually formatted data by using the Sort dialog or creating a helper column. For most tasks, the Custom Sort option under the Data tab is the fastest path. If you manage color-coded lists frequently, set up the GET.CELL helper column once to save time. Remember that after sorting, you can use the F4 key to repeat your last sort action on different data sets quickly.