To get a quick handle on your Excel worksheet data, you can change the color of your cells according to their numerical values. Doing so will amp up your organization and filtering capabilities—not to mention let your data speak volumes at one, quick glance.
Present Your Data in Flying Colors
To color-code your cells according to their contents, follow these easy steps, which work for Excel 2016, 2013, and 2010:
- Select the entire block of data you want to format.
- Select the Home tab on the main ribbon, and then, in the Styles group, click on Conditional Formatting.
- Choose Color Scales from the resulting menu, and then mouse over the options to see a preview.
- Click on the option you prefer.
Result: Excel automatically applies a color gradient based on the range of numerical data in the cell.
Example: If the data in your selected cells ranges from 1.29 to 275, and you choose the Red – Yellow – Green color scale, small numbers (e.g., 1.99) will be green, mid-range numbers (e.g., 12.49) will be yellow, and large numbers (e.g., 255) will be red. The smaller the number, the darker the shade of green.
Benefit: This striking graphic contrast allows you to quickly pick out the largest and smallest numbers in your selection, as well as the middle values.
But there are even more benefits to color-coding your worksheets!
Filter Based on Fill Color
Most Excel users are comfortable filtering data, and we typically think of filtering based on the cells’ values. But now that you know how to color-code by cell value—whether numerical or otherwise—you can put those colors to filtering work!
For instance, maybe you’ve color-coded your contacts list by applying a yellow fill color for people with unverified information and a red fill for people with missing information. If so, you can bring all the yellow or red cells into view with a simple filtering command.
Example: Let’s display the red cells in our workbook:
- With your workbook open, right-click on a red cell within your data.
- Choose Filter | Filter By Selected Cell’s Color from the resulting shortcut menu.
Result: Excel displays only your red cells.
With these two color-based techniques, your workbooks—and your data analysis—will never be boring again!
For more ideas on how to make Excel’s untapped features work for you, check out Inside Microsoft Excel-PLUS. Every monthly issue is delivered straight to your inbox and packed with easy-to-follow instructions to help you learn new techniques on the fly. Whether you want to pick up specific data analysis tricks or seek general guidance on simplifying complex and repetitive tasks, you’ll find what you need in the pages of Inside Microsoft Excel-PLUS.
