Don’t Miss Out on an Often Untapped Resource
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. Very useful to organize your excel data.
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!
By Julie D
(This post first appeared in a ProfEd blog)
Snap tip: 2 more ways to visually make your data interesting… read here
