In Heat Map Explorer, you can visualize change over time by comparing two periods or a series of periods. We’ll show you how to do both.
Comparing two periods
We’ll use NASDAQ 100 stocks to view the percentage change in share price between the previous close and last trade.
In the spreadsheet below, “Prev Close” and “Last Trade” represent the share price in the last two time periods; “% Change” represents the relative difference between these two time periods.
In Heat Map Explorer, we map “% Change” to a “+/-: Red-Green” color scale which maps decreases in price to red, and increases in price to green.
Now we can see that all the stocks in Semiconductors dropped in price, while the stocks in Software & Programming were mixed.
Comparing a series of periods
We want to visualize the performance of sales reps over a year. We’ve tracked monthly sales for each rep in a spreadsheet.
1. Review the spreadsheet layout
First we need to see how the spreadsheet is laid out. The example below shows how this data often appears: each row is a sales rep, and each column, a time period (in this case, a month).
In Heat Map Explorer, each row in the spreadsheet must represent one time period (1 month). In the edited spreadsheet below, the month, rep, and sales amount for that month appear by row. Note, too, that numbers have been prefixed to each month (e.g., 01-Jan, 02-Feb, …, 12-Dec) to preserve calendar order in Heat Map Explorer.
2. Import into Heat Map Explorer
Launch Heat Map Explorer, and then import the spreadsheet. (For steps, see How to Import Data from Microsoft Excel.)
3. Display data
On the Data tab, check the Size, Color, and Label fields. For this spreadsheet, we select Performance for size (the better the sales performance, the bigger the size) and Variance for color (better than the target is green; worse than the target is red). For the label, we choose Actual to display the dollar value of the actual sales. Since we’ll be grouping by salesperson and month, this information will already be displayed in the group headers and doesn’t need to be used in the label field.
4. Group data
We want to group data first by sales rep, then by month.
5. Change the map type
We’ll choose Column Bar for the map type to display the data in rows and columns, enabling us to easily see changes over time.
The first level of grouping, sales rep in this case, gets displayed as rows. The second level of grouping, months in this case, gets displayed as column. The bar in each month represents the rep’s performance (sales amount), while the color of the bar depends on whether actual sales are greater than (green) or less than (red) targeted sales for the month.
As we can see, Jan’s performance over the 12-month period was consistent. Phil’s performance, on the other hand, decreased steadily, then sharply. The lack of data in November and December probably means he was fired. Sam’s performance is inconsistent, with peaks every few months.
Have you learned how to visualize change in data over time? Let me know if you’ve found this post useful.