When doing data analysis, frequently you’ll use conditional formatting to highlight certain cells. But then you’ll probably find you still need to group those similar cells afterward. That’s where sorting by color comes in. Here’s how to sort by color in Excel.
You can sort by color in Excel using either the filtering function, or the Sort button on the Data tab of the ribbon. Sorting with the filtering function is faster and easier, but using the sort button lets you set up complex criteria, including multiple levels. This is useful if you want to sort on both cell color and font color.
Sorting Excel data by color for data analysis
In a previous job, one of the things I would frequently do was load two vulnerability scans in Excel, extract key data from them, then compare the two so I could find the differences. This let me know what our teams fixed after the previous scan, and what new vulnerabilities had emerged since the previous scan.
This is useful for more than security data, though. I could use the same method to compare my blog stats for this month with this year. It could be useful to know if there are posts that received significant traffic over the course of the past year that haven’t gotten any traffic this month. If a once-significant post has fallen out of favor, it’s good to know that, so I can figure out whether the post needs to be updated.
All of this implies that there’s an automated way to color cells. If you’re coloring cells manually, there’s probably a much easier way to do it.
Highlight duplicate values
The easiest way to do this is to copy the two data sets into different columns on the same sheet. Then, to find things that are in one data set but not the other, select the columns for each data set, then highlight duplicates. To do this, click the Home tab on the ribbon, then click Conditional Formatting and select Highlight Cells Rules, then click Duplicate Values.
More complex analysis
For more complex analysis, you might want to apply other types conditional formatting. For example, if my data sets have statistics next to the title, I might use conditional formatting based on the values in those cells. I used to do this with trend data, highlighting a move in the positive direction green, and moves in the wrong direction red. Then I could sort by color to group the good news together and the bad news together if I wanted.
Sort by color in Excel using filtering
The first step to sort by color in Excel is to turn on filtering. Click on your top row, then click the Data tab on the ribbon. Next, click Filter. Then, click the down arrow on the column heading you want to sort. From the dropdown menu, select By Color. Here you can pick to sort by font color or background color.
Pick the color you want at the top of the list, or if you have more than two colors, click Custom Sort to let you choose what order the colors should be.
Excel will then dutifully sort the data by color for you.
Sort by color in Excel using the Sort button
An alternative way to sort by color is to use the sort button. The sort button isn’t quite a quick to use, but it’s more powerful because it lets you set multiple criteria.
On the ribbon, click Data, then Sort. The Sort dialog box appears. You’ll see a level to sort by. In the third column from the right, you’ll see a label named Sort On. Change that option from Values to either Cell Color or Font Color, depending on which you want. You can also click the + button in the lower left to add more criteria, such as factoring in both cell color and font color.
David Farquhar is a computer security professional, entrepreneur, and author. He started his career as a part-time computer technician in 1994, worked his way up to system administrator by 1997, and has specialized in vulnerability management since 2013. He invests in real estate on the side and his hobbies include O gauge trains, baseball cards, and retro computers and video games. A University of Missouri graduate, he holds CISSP and Security+ certifications. He lives in St. Louis with his family.