Training

How To Prepare Excel Data for Heat Map Explorer

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.

Column headings

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.

Before editing headings

Before adjusting headings

After editing headings

After adjusting headings

Footers

If your spreadsheet contains footers (e.g., date, page numbers) after the data rows, delete the footers.

Delete all footers

Delete all footers

Blank rows/columns

Delete blank rows and columns.

Before deleting blank row and column

Before deleting row 2 and column A

After deleting blank row and column

After deleting the blank row and column

Numeric data

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.

Text in numeric column

Text in numeric column

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.

Replace text with blank value

Replace text with blank value

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.

Cell G8 contains a space (text) instead of null (blank)

Cell G8 contains a space (text) instead of null (blank)

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])

Where [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.

Format Budget, Scheduled Cost, and Actual Cost columns

Format Budget, Scheduled Cost, and Actual Cost columns

Text data

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.

Before editing text

Before editing text

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”).

Use Find and Replace to make text consistent

Use Find and Replace to make text consistent

After editing text

After editing text

Calculated fields

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.

Calculated field in column K

Calculated field 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.