How To Make A Heat-Map In Excel

"Hot Hot Hot" by Nina Matthews Photography is licensed under CC BY 2.0

Quick mental exercise: When I say “Microsoft Excel,” what’s the first thing you think of? Wait, don’t tell me—I can probably guess:

• Excel? That shit is my jam!
• Ain’t no party like an Excel party, ‘cause an Excel party don’t stop!
• Poppin’ data into cells—like a blizzard / when we analyze-data, we do it right, gettin’ slizzerd….

No? Just me…?

Whatever, look: If you’re the average person, yes—Excel is probably boring. But it can do some awesome stuff and make you feel really smart.

One of the things you can do really easily is create a heat-map, using a tool called Conditional Formatting. I’ll show you how to do it here, and it shouldn’t take you more than about five minutes.

What you’ll need:

• Some data (pull maybe the last six months of your Facebook posts, for this example).
• Excel (ideally for Mac, because that’s what these screenshots are from).

STEP 1 – Organize Your Data

For this example, I’ll be using a heat-map to decipher what the best times-of-day are to post to a Facebook page. The first thing we’ll need to do is clean up the raw data Facebook gives us, so:

•  Delete the top row of descriptive tags, and;
• Add a new column next to the “Posted” column, and call it “Day of Week.”

STEP 2 – Add In Days Of The Week

In the first “Day of Week” cell, type this formula:

=TEXT(d2, “ddddddd”)

Hit Enter, and you’ll see a day of the week appear—that day should correspond to the date in the “Posted” column*. Now click and hold on the little blue square that appears in the bottom-right of that selected cell. Drag it all the way to the bottom of your data set, and you’ll now have days of the week associated with all your posts*.

*Those are bonus Cool Things That Excel Does.

STEP 3 – Make A PivotTable

Yet another Cool Thing Excel Does! #Learning, amirite???

This one’s easy: Click anywhere in your spreadsheet, click Data at the top of the screen and click PivotTable. Click OK. Done.

STEP 4 – Organize Your PivotTable

A PivotTable basically groups data so you can start to make sense of it. Click anywhere in the PivotTable, and you’ll see the builder show up. In the builder, drag “Posted” into the row field, and “Day of Week” into the column field. Then go find “Engaged Users” and drag that into the data field.

Next, move your cursor over to the PivotTable itself. Click anywhere in the “Posted” rows, right-click, hover over Group and Outline, and click Group. Tell it you want to group by Hours.

At this point, you should be looking at something like this:

OPTIONAL: Average out your Engaged Users data.

At this point, the data in the middle of your chart will be totals. That’s useful, but it’s also not necessarily representative of how all your data performed. If, for instance, you posted 100 times on Wednesdays at noon, and you only posted twice on Tuesdays at 3 p.m., the totals will give you the impression that Wednesdays at noon are a better time to post.

Averages can help that, but not fix it entirely. You’ll still need to pay attention to sample size—how many times you posted during each time. The way to get that data is to remove Engaged Users (just drag it out of the Values field in the builder) and replace it with Posted. When you drop Posted into Values, you should see that it’s showing you a count of Posted, as opposed to a sum or average. That count shows you how many times you posted during the times listed.

STEP 5 – Heat It Up

This is the easiest part.

Highlight all the data in the middle of the table you’ve created. Do not highlight the times of day or days of the week. Now go up to the ribbon at the top of Excel, and click on the Home tab. Click Conditional Formatting and hover over Color Scales.

From here, you can pick any color scheme you want, and even customize one. I like the one that goes from red (on top) to blue (on the bottom) because that’s your standard hot-to-cold heat map. Here’s what your table will look like once you’ve selected an option:

Now you can visually scan your chart and get an idea of when your best times to post are. Remember, this is only to give you an idea—you need a lot of data before you can draw any definitive conclusions about what it’s telling you.

If you want to string together spreadsheets (i.e. download multiple Facebook spreadsheets over several years) to build up your data, that’s an option. Just remember that all kinds of things can bias the results you see:

• Post types
•  Length of posts
• Content
• Context
• Quality

Play around with all the variables and analyze your results to make sure you’re getting a full picture. In other words, keep that crazy-ass Excel party going for as long as possible.