Day 21: Advanced Charts & Visualisation

In Excel 2016+, Insert → Chart → Waterfall. The chart shows cumulative effect of sequential values — used for profit & loss analysis (start with revenue, subtract each cost category, end with net profit) or variance analysis (start with baseline, add/subtract variances, end with actual). Each intermediate bar is floating — only the start and end bars touch the axis. Right-click a bar → "Set as Total" to make it a full bar (for the starting and ending values).

Click the chart title → type "=" in the formula bar → click the cell containing the title text → press Enter. The chart title now links to that cell and updates whenever the cell value changes. This is essential for dashboard charts connected to slicers — when a user filters by "North Region", the chart title should update to "North Region — Monthly Revenue" automatically. The cell can contain a formula like ="Monthly Revenue — "&SlicerSelection.

A target line shows the goal value as a horizontal line across a bar or column chart, making it easy to see which bars meet the target. Implementation: add a "Target" column to your data with the same target value in every row → add it to the chart as a second data series → change its chart type (right-click the new series → Change Series Chart Type → Line). Format the line as dashed, orange or red. Add a data label on the last point showing "Target: ₹50L".

A heatmap is a table of values where each cell is coloured based on its value — green for high, red for low (or any colour scale). In Excel, create a heatmap using conditional formatting: select the value range → Conditional Formatting → Colour Scales → choose a 3-colour scale (red-yellow-green or similar). For a professional heatmap: remove gridlines, use a white or light grey background, bold the header row and column, and format the values to show cleanly (round numbers, add commas). Common use: sales by product × month matrix.

A bullet chart (developed by Stephen Few) is a variation of a bar chart designed for dashboards — it shows an actual value as a bar, a target as a marker line, and performance zones (poor/satisfactory/good) as background bands. Excel does not have a native bullet chart type — build it with a stacked bar for the background bands, a bar on secondary axis for the actual value, and error bars or a separate series for the target marker. Bullet charts pack a KPI, target, and context into a small space — ideal for dashboards with many metrics.