In the second session on heatmap analysis, we look at one way to create a heatmap.  This method, using a facility in Excel called a 'data table', is not the slickest - but it is powerful, and certainly gets the job done.

We start this session with a minor file re-structure.  This ensures the role of each sheet is clear - and we know, in the long run, that helps keep us sane as Excel programmers!  With that done, we are ready to begin the analysis.

Like many of the more powerful facilities in Excel (FREQUENCY being another,) some preparation is required before implementation.  A data table in particular requires a model in order to work.  This is because the data table would be better described as an 'output table' - it actually displays multiple outputs from a model.

With the model checked and validated, we setup the odds brackets in a row and a column which gives us the basic data table structure.  Only then can we actually create the data table through Excel's 'What-If Analysis' area.  As always, take your time, and don't be discouraged if it doesn't work first time - it probably won't.

Once the table is in place we can harness conditional formatting to create the 'heatmap' formatting overlay.  and there it is - a thing of beauty and a powerful addition to your analytical toolkit.

TOOLS AND TECHNIQUES

- Data Table
- SUMIFS
- Conditional Formatting