Skip to Content

How do I sort by color in Excel for Mac?

By: Author Color With Leo

Sorting data by color can be a handy way to quickly visualize and organize information in your Excel for Mac spreadsheets. With just a few clicks, you can arrange your data according to the cell or font color applied. Here’s a step-by-step guide to sorting by color in Excel for Mac.

Why Sort by Color in Excel for Mac?

Here are some common reasons you may want to sort data by color in Excel for Mac:

  • Quickly identify values or categories – For example, highlight negative numbers in red.
  • Separate data visually – Color code different data sets on a single spreadsheet.
  • Organize and prioritize tasks or steps – Use color to denote order or importance.
  • Spot trends and patterns – Colors make patterns stand out visually.
  • Track changes or edits – Use highlighting to see what’s been changed.

Overall, sorting by color allows you to arrange your data in a way that’s easier to understand and analyze at a glance.

Sort by Cell Color

To sort data by the background or cell color applied:

  1. Select the cells you want to sort.
  2. Go to the Data tab and click the Sort button.
  3. In the Sort dialog box, click on Cell Color in the first Sort by drop-down menu.
  4. Choose how you want to sort:
    • No Color – Sorts uncolored cells first.
    • Cell Color – Sorts by the cell’s applied color.
    • Custom List… – Choose a custom color order.
  5. Click OK to sort.

Your selected cells will now be sorted based on the background color order you selected. Keep in mind conditional formatting colors will also be included in the sort.

Sort by Font Color

To sort data by the font or text color:

  1. Select the cells you want to sort.
  2. Go to the Data tab and click the Sort button.
  3. In the Sort dialog box, click on Font Color in the first Sort by drop-down menu.
  4. Choose how you want to sort:
    • No Color – Sorts uncolored text first.
    • Font Color – Sorts by the text’s applied color.
    • Custom List… – Choose a custom text color order.
  5. Click OK to sort.

Your selected cells will now be sorted based on the font color order you selected. This includes colors applied through formatting or conditional formatting rules.

Sort Two Columns by Color

You can also sort one column by cell color and another by font color in a multi-level sort. Here’s how:

  1. Select the columns you want to sort.
  2. Go to the Data tab and click the Sort button.
  3. In the first Sort by drop-down menu, choose Cell Color.
  4. In the next Sort On drop-down menu, choose Font Color.
  5. Adjust the color sort orders as needed.
  6. Click OK to apply the multi-level color sort.

This will sort the first column by cell color and then further sort each cell color group by the font color.

Sort by Fill Color

In Excel for Mac, you cannot directly sort by fill color, which is the interior color of a cell. However, there is a workaround:

  1. Select the cells you want to sort.
  2. Copy the cells and use Paste Special to paste only the fill color.
  3. Sort this new column by cell color – this will effectively sort by fill.
  4. Delete the extra column when done.

While not as straightforward, this lets you sort by interior fill color.

Custom Color Sorting

When sorting by either cell or font color, you can choose a custom order rather than the default rainbow order:

  1. In the Sort dialog box, select Custom List…
  2. In the Custom Lists dialog box, click NEW LIST.
  3. Rename the list to your desired name.
  4. Under List entries, enter each color in the order you want to sort.
  5. Click Add after each entry.
  6. Click OK when done to save the list.
  7. Back in the Sort dialog, choose your custom list from the drop-down menu.

This will let you define a custom color priority order when sorting in Excel for Mac.

Sort by Color Using a Formula

You can also sort by color programmatically using a formula that assigns a number value to each color:

  1. Add a helper column and use this formula to generate a color code number:

    =IF(CELLCOLOR=0,1,IF(CELLCOLOR=13551615,2,IF(CELLCOLOR=255,3,IF(CELLCOLOR=8388736,4,IF(CELLCOLOR=65535,5)))))

    This assigns a numeric code from 1 to 5 for common colors.

  2. Sort the helper column numerically to effectively sort by color.
  3. Hide the helper column when done.

This approach lets you sort by color without the dialog box.

Sort by Color Icon

Some specific cell colors have associated sort icons you can click on instead of opening the full Sort dialog box:

  • Orange cells – Click the AZ sort icon to sort orange cells to the top.
  • Yellow cells – Click the ZA sort icon to sort yellow cells to the top.
  • Green cells – Click the Ascending sort icon to sort green cells to the top.
  • Red cells – Click the Descending sort icon to sort red cells to the top.

However, these presets only work for the default theme colors in Excel for Mac.

Sort All Worksheets by Color

When you have multiple worksheets in a workbook, you may want to sort all of them simultaneously by color:

  1. In the first sheet, select the cells you want to sort and open the Sort dialog box.
  2. Check the Match case box.
  3. Enable the Sort left to right option.
  4. Click Options and check Sort all sheets.
  5. Click OK to sort all the selected cells by color across all worksheets.

This can help you quickly arrange multiple sheets consistently.

Sort Objects by Color

In addition to sorting cells, you can also sort drawn objects and shapes by color in Excel for Mac:

  1. Select the objects you want to sort.
  2. Right click and choose Format Object.
  3. Go to the Fill tab and pick the color you want the objects sorted by.
  4. Run the Sort by cell color procedure outlined earlier.

This will arrange the objects according to the applied fill color.

Sort Charts and Sparklines by Color

You can also customize the color order of data points in charts and sparklines in Excel for Mac:

  1. Select the chart or sparkline data.
  2. Open the Sort dialog and choose a color sort order.
  3. Click OK. The chart colors will update to match the sort order.

This provides another way to visually organize your data in Excel charts or sparkline groups.

Tips for Sorting by Color in Excel for Mac

Here are some useful tips when sorting by color in Excel for Mac:

  • To reverse the sort order, choose Z to A instead of A to Z in the Sort dialog.
  • Make sure Conditional Formatting colors are distinct if using them to sort.
  • You can reapply existing colors after a sort using the Format Painter.
  • Use dark, high contrast colors to make sorted data stand out.
  • Add icons or data bars alongside colors for another visual indicator.

Conclusion

Sorting by color can help you better organize, visualize and understand your Excel for Mac data. With just a couple of clicks, you can arrange your spreadsheet information in a colorful order that fits your needs. So don’t be afraid to sort by shades as well as values in Excel for Mac.