Creating a Heatmap using XLOOKUP and Conditional Formatting

Raw data remains untapped treasures until turned into insightful information, rather insightful visualization. In this movie I present tabulated data on COVID cases per month per geographical region (for the year 2021). The table itself does not give insight into how COVID cases are distributed in the World. Nor does it show how COVID cases are linked- maybe correlated- to months of the year.

That said, the same data can be converted into a heatmap matrix of regions vs. months with conditionally formatted COVID cases (in millions) from lowest to highest. Although simple, the heatmap visualization gives a quick insight and a story to tell about the pandemic during 2021 around the globe.

Being fond of the XLOOKUP function, which I believe is a breakthrough introduction in Office 365 and Excel 2019, I use it to transform the table into a matrix. Then, I apply the Color Scales conditional formatting to create this visualization.

As a hint, I use here XLOOKUP with a slightly different syntax. In this scenario, XLOOKUP leverages the use of ampersand (&) to concatenate lookup values and lookup arrays directly in the formula. A kind of finding the value at the intersection of two lookup arrays. See the formula below.

=XLOOKUP(value1&value2,array1&array2,returned results)

As usual, I start by naming the ranges used in my formulas for better legibility and error-traceability.

NB: Data source: covid.ourworldindata.org

Comments

No comments yet. Why don’t you start the discussion?

Leave a ReplyCancel reply