Overview
This workbook creates a column chart with percentage change indicators between consecutive years. An "invisible" base series, positive/negative variance series, and error bars work together to show both the absolute values and the year-over-year changes in a single, clean chart. Built with 3 embedded chart objects and VBA macros.
Download the Excel file (.xlsm)
How It Works
- Data Columns: Year, Order (the main value), Invisible (next year's value for bridge positioning), Variance, Var+ (positive changes), Var- (negative changes), and Variance %.
- Invisible Series: A transparent stacked column uses
OFFSETto reference the next year's value, creating the "floating" base for the variance arrow. - Colour-Coded Variance: Positive changes (Var+) and negative changes (Var-) are split into separate series so they can be coloured green and red respectively.
- Percentage Labels: Each variance arrow is labelled with the year-over-year percentage change, calculated as
Variance / Previous Year.
Sample Data (2020–2028)
The chart uses randomised values generated by RANDBETWEEN(1000, 10000) rounded to the nearest 10. Press F9 to regenerate the data and watch the chart update instantly.
When to Use This
This chart type is a standard in financial reporting — showing revenue, headcount, or any KPI over time with clear indicators of growth or decline between periods. It's especially effective in board presentations where stakeholders need to see both the trend and the magnitude of change at a glance.