Share this content

How to 'Heat Map' Columns of Numbers in Excel

Oct 20th 2017
Spreadsheets and graphs on a desk

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.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.