K2 Tech Update | Tech Tip
Sorting Data By Cell Color In Excel
The improvements to conditional formatting beginning in Excel 2007 are potentially among the most beneficial of all of the enhancements to Excel for accountants and other business professionals. Now, with just a few clicks of the mouse, users can quickly apply colors to large sets of data to assist in analyzing that data. However, most Excel 2007 and Excel 2010 users still do not realize that once those colors have been applied, they can sort the data based on cell color.
To sort data based on color (whether applied by conditional formatting or manually), begin by clicking in the data range and choosing Sort from the Data tab of the Ribbon to open the Sort dialog box, as pictured in Figure 1.
Figure 1 - Opening the Sort Dialog Box in Excel
In the Sort dialog box, select from the Column drop-down list the column on which the sort should be performed. From the Sort On drop-down list, select Cell Color. Lastly, select the Order in which the sort should be performed. Repeat this process for each color you want to include in the sort. To sort the data shown so that the cells with blue coloring appear at the top of the list and the cells with red coloring appear at the bottom, arrange the options in the Sort dialog box to match those shown in Figure 2.
Figure 2 - Completed Sort Dialog Box
Finally, click OK to complete the sort. Upon doing so, the data pictured in Figure 1 arranges as shown in Figure 3 .
Figure 3 - Completed Sort of Data by Color
As you can see, sorting by cell color in Excel 2007 and Excel 2010 is really quite easy and can make data analysis much more efficient.
For a video presentation of this tip, visit www.tinyurl.com/k2tips57.