The heatmap is the most information-dense way to present a table. The pattern is: rows for one dimension (cohort, person, cost line), columns for another (month, day, scenario), values for the metric. The colour scale encodes the value; the table structure encodes the pattern. The four patterns below are the ones the senior ships most often. The four tips are the ones that turn a heatmap from a default into a senior-built visual.
The four patterns
Cohort retention
Rows = cohort month, columns = months since, values = retention %. 2-colour scale: white (low) to dark blue (high).
The senior ships this on every SaaS engagement. The diagonal pattern is the cohort story; the dark stripes are the healthy cohorts.
Schedule density
Rows = person, columns = day, values = hours scheduled. 2-colour scale: white (0 hours) to dark red (12 hours).
The senior-built resource schedule. The reader sees over-allocation at a glance. The dark red cells are the overloaded resources; the white cells are the under-utilised.
Variance heatmap
Rows = cost line, columns = month, values = variance %. 3-colour scale: red (over), white (on target), green (under).
The senior-built variance dashboard. The reader sees the over-spend and the under-spend at a glance. The white cells are on target; the green cells are wins; the red cells are problems.
Customer activity
Rows = customer, columns = month, values = revenue. 2-colour scale: white (0) to dark green (high).
The senior-built customer activity heatmap. The reader sees the active customers and the dormant customers at a glance. The pattern is the foundation of any account-based sales motion.
The four usage tips
Use muted colours, not bright ones
The senior uses a muted palette: white to dark blue for positive, white to dark red for negative, white to dark green for positive, white to dark amber for warning. The default Excel colours (bright red, bright green) are too loud; the muted palette matches the dashboard theme and is easier on the eyes.
Use 3-colour scales for data with a midpoint
For any data that has a meaningful midpoint (variance, growth %, score), use a 3-colour scale with white in the middle. The midpoint is the neutral; the extremes are the action. The pattern is the foundation of any variance dashboard.
Pair the heatmap with a data bar for magnitude
The senior pairs a heatmap with a data bar when both distribution and magnitude matter. The colour shows the position in the range; the bar shows the absolute size. The combination is the most information-dense way to present a table.
Test the heatmap for colour-blind readers
About 8% of men and 0.5% of women are colour-blind. The senior tests every heatmap by viewing it in greyscale. If the pattern is still visible, the heatmap is accessible. The fix: pair the colour with a number, an icon, or a data bar - never rely on colour alone.
From heatmap to dashboard
The heatmap is one element of a senior-built dashboard. The full pattern: a KPI strip, a chart strip, a heatmap in the middle, and a Slicer/Timeline at the top. The heatmap is the detail view; the KPI strip is the summary; the Slicer is the filter. The senior ships this in two to three weeks. The reader opens the dashboard, slices by region and date, and sees the pattern in the heatmap. The pattern drives the conversation in the next meeting.
Step-by-step: building a cohort retention heatmap
Six steps. (1) Lay out the cohort data: rows are cohort months, columns are months since cohort (M0, M1, M2, ...), values are retention %. (2) Select the value range. (3) Home → Conditional Formatting → Color Scales → More Rules → 2-Color Scale. (4) Set Min to "Lowest Value" with white fill. (5) Set Max to "Highest Value" with a dark blue fill. (6) Add a header row above the heatmap that names each column. The pattern is the standard for every SaaS cohort analysis and ships in under an hour from raw data.
Common pitfalls
Five pitfalls to avoid. (1) Too many colours - three is the senior's limit. Beyond three, the chart is unreadable. (2) Bright default colours - the default Excel red and green are too loud; use muted red (#fca5a5) and muted green (#86efac). (3) Colour-only encoding - colour-blind readers can't distinguish the values. Pair colour with a number, icon, or data bar. (4) Wrong midpoint - a 3-colour scale with midpoint at zero or 50% is meaningless; pick the natural midpoint of your data (variance zero, target value, expected baseline). (5) Mixing scales across rows - pick one scale and apply it consistently. The reader learns to read the chart in 30 seconds.
Conditional formatting vs custom formatting
Conditional formatting is dynamic - the colour changes when the value changes. Custom formatting is static - the colour is set once. The senior uses conditional formatting for any value that changes with data refresh (revenue, variance, score). The senior uses custom formatting only for fixed labels (red for "Critical", green for "Approved"). The confusion between the two is the most common Excel formatting mistake - a custom-formatted red cell stays red even when the value drops to zero, which misleads the reader.
Performance and limits
Conditional formatting on a 1K-row range renders in under a second. On 100K rows, expect 3-5 seconds to apply the rule. On 1M+ rows, conditional formatting slows the workbook to a crawl - switch to Power Pivot (xVelocity engine) or split the data into multiple sheets. The senior's rule: if the dataset exceeds 100K rows, store in Power Pivot and apply conditional formatting on the pivot output, not the source.
How this fits the senior-built dashboard
The heatmap is the centrepiece of every operations dashboard the senior ships - resource schedule, vendor spend, customer activity, project hours. The pattern is documented on designing a KPI dashboard and is the standard ship pattern for every operations dashboard we build in our dashboard development engagement. The heatmap is one tile; the dashboard has 5-7 tiles total. The reader slices by date and region and watches the heatmap pattern change in real time.
