European population grid

The scatterplot approach

When I saw this grid with a CSV attached (data), I immediately wanted to apply my poor man’s GIS approach: create a scatterplot with latitude values along the y-axis and longitude values in the x-axis. I had never before tested adding 2M data points to a scatterplot in Excel, and that was also something I wanted to try. First, I experimented with the Iberia:

Using a scatterplot to display latitude / longitude.
Using a scatterplot to display latitude / longitude.

This is a 1x1 km grid, but the point is represented only if some population is found there. You get a basic idea of where the population is, but there is no difference between a square with one person and one with 1000 people. You can’t represent this third dimension in a scatterplot (well, you create bins and associate series (x,y) to each bin, but you would need to have dozens of bins to mimic a gradient).

A better approach: pivot table + conditional formatting

A more straightforward way of displaying a grid is using a pivot table. Set column width and row height to a minimal value (like 1), associate latitude with rows and longitude with columns, and get a geographic grid. Then use conditional formatting to generate the map. To avoid outliers’ impact, I never use the minimum and maximum values to create the gradient. In this case, I used percentile 10 for minimum and percentile 90 for maximum. Here is how it looks:

Excel pivot table with conditional formatting
Excel pivot table with conditional formatting.

An interesting side-effect of using a pivot table is that you can filter for a single country or an area, and everything adjusts automatically. Here is how my 1920x1080 screen looks when I choose France:

Applying filters to a conditionally formatted pivot table.
Applying filters to a conditionally formatted pivot table.

Pivot tables: untapped potential for data visualization?

Like many others, I love exploring Excel’s flexibility to create new charts or replicate the ones we see and are jealous of. But this is often done starting with an actual chart. Pivot table and conditional formatting are less explored and deserve a closer inspection. I think I’ll add that to my projects in 2021.