I was puttering around on the internet when I came across Daniel Ferry’s old post on Location mapping in Excel. The idea is that you can use latitudes and longitudes to plot points on a chart. Cool trick.
Then I wondered if you could achieve a similar result with a PivotTable. Well…it turns out you can.
This is a screenshot of a map visualization I pulled together using a normal a PivotTable.
With slicers, this PivotTable becomes a dynamic location intelligence component for your dashboards.
The steps to create this kind of map are pretty straight forward:
1. Get some data with Latitudes and Longitudes.
2. Create two new columns to round the latitudes and longitudes. This essentially reduces the number of unique values so that your PivotTable doesn’t choke.
3. Create a new PivotTable, placing your Rounded latitude field in the Rows area and the Rounded longitude field in the Columns area.
4. Once your PivotTable is created, sort your Rows so that they run from Largest to Smallest.
5. Go into the PivotTable Options and remove all GrandTotals. Also be sure to clear the “Autofit columns… check box.
6. Click inside the PivotTable and select Conditional Formatting. In the Rules Manager dialog box, select the third option under the Apply Rules To section. For Rule Type, choose to Format all cells based on their values. Finally, choose the 3-Color Scale format Style. Adjust the formatting as needed. Your Rule manager should look something like this.
7. At this point, all there is left to do is clean up.
- Change the number formatting of the values so that no values show (select Custom and enter ;;;
in the Type field).
- Hide the Row and Column fields of the PivotTable
- Adjust the sheet row and column widths to get the aspect ratio right.
8. Add a Slicer and make all your nerd friends jealous.
Now obviously, this techniques doesn’t produce a strictly accurate map. I mean, come on, the first step is to round the latitudes and longitudes. That being said, it does generate a directionally correct map profile that looks pretty cool.
You can play with a sample file by downloading it.
You kids have fun.