How to 'Heat Map' Columns of Numbers in Excel

Spreadsheets and graphs on a desk
xfgiro/istock

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:

  1. Click any single cell within the list.
  2. Select Excel's Data menu.
  3. Click the Sort Z-A command to sort the numbers from highest to lowest.
  4. Select the numbers you wish to format.
  5. Choose Conditional Formatting from Excel's Home menu.
  6. Choose Color Scales from within the Conditional Formatting menu.
  7. 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.
  8. 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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

About David Ringstrom, CPA

David Ringstrom

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.

Replies

Please login or register to join the discussion.

avatar
Oct 21st 2017 23:02

Really appreciate this post. I spend a lot of time in Excel although less these days as we move to and more cloud-based environment I still feel the most at home in Excel. Thanks again.

Thanks (1)