1 Comment

  • […] black numerals and the white background. Removing the background fills also allows me to overlay a heat map later on if I […]

  • Leave a Reply to Ann K. Emery | Transforming a Table: Four Charts and Four Different Stories Cancel reply

    Your email address will not be published. Required fields are marked *

    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 variables, choose a sequential color scale (a one-color scale, e.g., green faded into white).

    For diverging variables, choose a diverging color scale (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).

    Here’s the before version, in which the cells don’t have any outlines. The greens blur into each other.

    Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Here’s the after version, in which I’ve added white outlines to each cell.

    I just highlighted all the cells, went to the tiny border icon on the Home tab, and added white outlines.Heat Table Tutorial in Microsoft Excel by Ann K. Emery

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

    I always add an intentional order to my spreadsheet.

    When I initially downloaded this dataset from a website, Column A was ordered alphabetically.

    I decided to order the dataset from greatest to least instead of alphabetically.

    You can also order your dataset from least to greatest. It just depends on which pattern you want to emphasize.

    Use the Sort feature. Or use Filters (Home –> Sort & Filter –> Filter).

    If you don’t use spreadsheets on a regular basis, then you can follow my tutorial on sorting and filtering your spreadsheets.

    Here’s the before version, in which the spreadsheet is ordered alphabetically.
    Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Here’s the after version, in which the spreadsheet is ordered from greatest to least based on the numbers in Column B.
    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

    Half the time, I’m creating heat tables for my own purposes. I want an at-a-glance view of my dataset. I want to see whether any numbers are really big.. or really small… or whether I spot any errors right away. Heat tables are excellent exploratory data visualization techniques.

    The other half of the time, I’m creating heat tables for someone else. I might send them the Excel file. Or, I might send them a PDF’d handout. Or, I might bring a printout from the spreadsheet to a meeting and pass out the handouts to everyone at attendance so that we can discuss the patterns shown in the table.

    Data People like opening up Excel. But Regular People like opening up PDFs. Excel files can be daunting to someone who’s not using that file on a regular basis.

    So, I get the data out of spreadsheets and into real-world conversations by PDF’ing my heat table.

    If you’re preparing to PDF your heat table so that it can be shared with others, I recommend that you:

    • Add a title.
    • Repeat the column headers at the top of each page.
    • Add a footer with the date and page numbers.
    • 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.

    Here’s what the print preview looks like in Excel:

    Heat Table Tutorial in Microsoft Excel by Ann K. Emery
    And here’s what the two-page PDF would look like:
    Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Bonus! Customize the Color Palette

    No more default Microsoft colors!

    Use your audience’s custom colors. It only takes a few minutes to update your visualization’s colors, and it makes your visualization look more professional.

    You can find custom color codes in an existing style guide. Or, you can identify color codes with an eyedropper tool or by using good ol’ Microsoft Paint.

    Then, 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.

    Just click on More Colors and type in the RBG codes.Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Here’s what your heat table would look like in purple.
    Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Or, in yellow.

    Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Or, in blue.

    Heat Table Tutorial in Microsoft Excel by Ann K. Emery

    Download the Spreadsheet

    This two-sheet spreadsheet contains the “before” and “after” dataset used in this blog post. The “before” dataset is just a regular ol’ dataset–some rows and columns without any color formatting. The “after” version has a heat table, is sorted, has a title, and is ready to print or PDF.


    Download the Spreadsheet

    Join the Conversation

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

    Sponsored Content

    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.

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    1 Comment

  • […] black numerals and the white background. Removing the background fills also allows me to overlay a heat map later on if I […]

  • Leave a Reply to Ann K. Emery | Transforming a Table: Four Charts and Four Different Stories Cancel reply

    Your email address will not be published. Required fields are marked *

    You Might Like

    Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

    Enroll

    Watch Out for Mars! 6 Data Cleaning Steps to Save You Millions

    You’ll learn how to: (1) check for duplicates; (2) check for survey changes; (3) check for outliers in survey length; (4) use COUNTA and COUNTBLANK; (5) recode variables with IF; and (6) combine datasets with VLOOKUP.

    More »

    Need practical how-to tips? In this course, you’ll learn how to make great graphs inside software you already have. Includes beginner, intermediate, and advanced tutorials for making charts from start to finish.

    Enroll

    Subscribe

    Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.