How to Make a Heat Table in Microsoft Excel

Apr 14th, 2015 / Data Visualization / , , , , , ,
You can set up rules in your spreadsheet that automatically change the color of certain cells based on their values. I regularly use heat tables to scan my dataset for patterns. You can follow my step-by-step tutorial to make heat tables for your data.

Just because I adore graphs doesn’t mean that I shun tables. I insert the occasional table inside the body of my report. Every report’s appendices are full of tables, tables, and more tables.

When creating traditional tables, I follow the Dark Horse Analytics guide. When I’m presenting tables in a presentation, webinar, or full-color report, I create heat tables.

In heat tables, higher numbers are darker and lower numbers are lighter. Viewers find patterns in the data much faster than if they were reading line by line of text from the table.
Here’s how to make heat tables in good ol’ Microsoft Excel.

Step 1. Highlight the Cells That You Want to Color-Code

Ignore the categories on the left. Ignore the table headers along the top. Just select the actual numbers or percentages in the body of the table.

Heat Table Tutorial in Microsoft Excel by Ann K. Emery

Step 2. Click on the Conditional Formatting Icon

It’s hiding in plain sight on the Home tab.
Heat Table Tutorial in Microsoft Excel by Ann K. Emery

Step 3. Select One of the Color Scales

For sequential patterns, use a one-color scale (e.g., green faded into white). For diverging patterns, use a two-color scale (e.g., red versus blue).
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Congratulations, you’re finished! Start exploring your data. Which patterns stand out now?
Heat Table Tutorial in Microsoft Excel by Ann K. Emery

Bonus! Add White Borders to Differentiate Cells from One Another

White borders help with color printing and with black-and-white printing (so the cells don’t bleed into each other).
Heat Table Tutorial in Microsoft Excel by Ann K. EmeryHeat Table Tutorial in Microsoft Excel by Ann K. Emery

Bonus! Sort the Data from Greatest to Least Instead of Alphabetically

Or, from least to greatest, depending on which pattern you want to emphasize. Use the Sort feature or use Filters (Home –> Sort & Filter –> Filter).
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Heat Table Tutorial in Microsoft Excel by Ann K. Emery

Bonus! Adjust the Print Settings so the Heat Table can be Printed or PDF’d

Add a title; repeat the table headings at the top of each page; add a footer with the date and/or page numbers; and adjust the font type and font size. Once you learn to adjust print settings you’ll save hours of time and every printed or PDF’d spreadsheet will look more professional.
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Heat Table Tutorial in Microsoft Excel by Ann K. Emery

Bonus! Customize the Color Palette

Highlight the cells again and return to Home –> Conditional Formatting –> Color Scales –> More Rules.
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Select new colors to correspond with the lowest and highest values in your table. You might even match the RGB codes to your organization’s logo.
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Heat Table Tutorial in Microsoft Excel by Ann K. Emery
Heat Table Tutorial in Microsoft Excel by Ann K. Emery

Have you tried this tutorial? Comment below and link to a screenshot of your own heat table!

Pro Tip: Did you know now you can access all your essential business applications, data and documents with Office 365 on your hosted virtual desktop from CloudDesktopOnline powered by commendable live tech-support from Apps4Rent.com.

Related Courses

Most “professional” reports are too long, dense, and jargony. Transform your reports with these practical tips. You’ll never look at reports the same way again.
FREE

Enroll Now

Data visualization best practices, practical how-tos, tutorials in multiple software platforms, and guest experts. Designed with busy number-crunchers in mind.
$599

Enroll Now

SPONSORED

#f-post-el-35{display:none !important}

#f-post-el-35{display:none !important}
#f-post-el-35{display:none !important}

Enroll Now