This article shows how to prepare your Microsoft Excel data for importing into Heat Map Explorer.
Heat Map Explorer expects Excel data in a tabular format (i.e., column headings in the first row, followed by rows of data). If your spreadsheet contains multiple heading rows, blank rows/columns, footers, or invalid/undefined values, you need to adjust your data before importing.
Be sure headings are in the first row, with one heading per column.
In the example below, the original headings span two rows and two columns. The adjusted headings are in one row, and each column has a heading. The unnecessary row was deleted.
If your spreadsheet contains footers (e.g., date, page numbers) after the data rows, delete the footers.
Delete blank rows and columns.
To analyze data using Heat Map Explorer, make sure all numeric cells either contain numbers or are blank (representing a null value). If Heat Map Explorer encounters text or a non-numeric value in a cell, it treats all values in the column as text.
To replace multiple text values with blank (null) values, select the column and use the Find and Replace feature, leaving the value of the “Replace with” field blank.
Similarly, Heat Map Explorer treats Excel errors (e.g., #VALUE!, #DIV/0!) in a calculated field–and therefore the entire column–as text. The #VALUE! error occurs if text is used in a formula, while #DIV/0! occurs if the denominator is zero. You can inspect all values in a column to ensure there are no errors by using the autofilter feature.
Correct any errors before importing into Heat Map Explorer. A simple fix is to take your formula and use the ISERR() function to test for errors:
IF(ISERR([original formula]), "", [original formula])
[original formula] represents the formula originally in the column.
Although you can do it in Heat Map Explorer, it’s easy to format currency, percentage, and other numeric columns before importing. You can select multiple columns to format at the same time.
Heat Map Explorer groups data based on repeating values in a column, so make sure repeating values are consistent.
In the example below, “and” and “&” were used interchangeably in the Category column.
To make the text values consistent, select the Category column, then replace ” and ” with ” & ” (in the Find and Replace window, use a space before and after ” and ” and ” & ” to avoid replacing words that contain the letters “and”).
To perform better analysis in Heat Map Explorer, add calculated fields before importing.
In example below, the column K (Lag) shows the percentage difference between scheduled and actual progress. Notice that the formula in the column K takes into account null (blank) values in column G so there are no undefined values in column K.
Now You’re Ready To Import
Now that you’ve prepared your data for Heat Map Explorer, learn how to import your data with a 5 minute video tutorial.