It can often be difficult to get a measure of numbers in a spreadsheet, but Excel offers many data visualization techniques.
In this article I'll describe how you can apply heat map formatting to a column of numbers. We'll accomplish this with the Conditional Formatting feature available on Excel's Home menu.
Figure 1 shows the steps involved:
- Click any single cell within the list.
- Select Excel's Data menu.
- Click the Sort Z-A command to sort the numbers from highest to lowest.
- Select the numbers you wish to format.
- Choose Conditional Formatting from Excel's Home menu.
- Choose Color Scales from within the Conditional Formatting menu.
- Choose a color scheme. Excel offers a live preview as you hover over each scheme, so you'll be able to see the impact before you commit to it.
- The numbers are now formatted as a heat map.
Figure 1: The Color Scales Conditional Formatting feature makes heat maps easy to create.
As you can see, the Color Scales Conditional Format is easy to apply, but unfortunately is limited to cells that contain numbers. You cannot use Color Scales to format an entire row. You can manually craft conditional formatting rules that will format entire rows.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.