MOS Excel Expert (Office 2019): A flat margin line, a chart that stops growing and a board that wants 12 months

A flat margin line, a chart that stops growing and a board that wants 12 months

Elena maintains the monthly board pack for a subscription business. Sheet Data holds 36 months of history in columns A:C - Month, Revenue (values between 1.8 M and 4.2 M) and Gross Margin % (values between 22% and 31%). Her combination chart plots Revenue as clustered columns on the primary axis and Gross Margin % as a line on the secondary axis. The margin line hugs the bottom of the plot area and looks almost flat. Data labels are switched on for all 36 columns and now overlap into an unreadable band across the top. Each month she pastes three new rows at the bottom of Data and then manually edits each chart's source range, because the charts still stop at the old last row. The board has asked to see only the most recent 12 months on the chart while the underlying table keeps all 36 months for audit purposes, and she has no dynamic-array functions or Power BI on her Excel 2019 build.

1. The Gross Margin % line looks flat. Explain why the charts shows this and describe the axis settings and supporting formatting you would apply.

Read the worked answer

Cause. The secondary vertical axis is auto-scaled from zero to a rounded maximum, typically 0 to 35%. A series that moves between 22% and 31% therefore occupies only the bottom quarter of the plot height and reads as an almost flat line. The data is right; the scale is the problem, and no formula change is needed.

Fix:

Right-click the margin series > Format Data Series and confirm Secondary Axis is selected.
Select the secondary vertical axis > Format Axis > Bounds, then set Minimum 0.20 and Maximum 0.32. Tighten to 0.22/0.31 for more visible movement, but beware a truncated axis exaggerating change in a board document.
Set Number format to 0.0% and add an axis title "Gross margin %", so readers know which axis applies to which series.
Delete the secondary gridlines: two sets of unaligned gridlines are the classic cause of misreading a combo chart, and the primary gridlines already anchor the columns.

Judgement. Fix the bounds manually rather than trusting auto-scale, because next month's refresh will re-flatten the line. Do not rescale margin into millions so that it shares the primary axis - that preserves the picture but destroys the meaning of the series.

2. New rows pasted at the bottom of Data are ignored by the charts. Compare the ways to make the chart expand automatically, including the pitfalls of each.

Read the worked answer

Cause. A series pointing at Data!$B$2:$B$37 is a fixed address. Excel never inspects what lies below it, so rows 38 to 40 are simply outside the definition, and the only cure is a source definition that grows.

Option 1 - an Excel Table (recommended). Select the range, press Ctrl+T, name it tblBoard, then set each series to structured references, for example =SERIES(,tblBoard[Month],tblBoard[Revenue],1). Rows pasted directly beneath the table's last row are absorbed by the table on paste and the chart follows with no editing. No formulas, nothing volatile, and PivotTables built on the same table refresh too.

Option 2 - a dynamic defined name. Name ChartRev as =OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,1), or the non-volatile =INDEX(Data!$B:$B,2):INDEX(Data!$B:$B,COUNTA(Data!$B:$B)), then type the name into the SERIES formula. Prefer the INDEX form on a workbook recalculated often, since OFFSET is volatile.

Pitfalls for both. A blank separator row between the data and the new rows breaks table expansion and stops COUNTA at the wrong place. A single embedded blank in the key column also truncates the COUNTA version, so check for gaps before trusting it. Verify by pasting three dummy rows and confirming the chart updates without any manual edit.

3. Show only the most recent 12 months on the chart while the sheet keeps all 36 months. Describe the mechanism and the trap that produces a false zero.

Read the worked answer

Approach - a fixed 12-row helper block, because Excel 2019 has no FILTER or spilled ranges.

On a helper sheet, build 12 rows. With n equal to the count of data rows, the first plotted row is n-11. Use =INDEX(Data!B:B,COUNTA(Data!B:B)-11+ROW()-1) filled down for Revenue, and the same pattern for Month and Margin.
For any helper row beyond the data, return NA() rather than "".
Point the chart series at the 12-row helper range. It is a fixed address, so it never needs resizing, yet it always contains the latest 12 months.

The trap. A line chart plots #N/A as a gap, but a zero-length string is plotted as zero, which drags the line to the axis and invents a collapse in the business that never happened. If the helper must show something friendly on the worksheet, keep the display columns separate from the plotted columns.

Alternative. Load the data to a PivotChart and filter Month to "Last 12 Months", or add a timeline. That is less formula work but constrains chart formatting.

Also fix the labels. 36 labels is noise; label only the last 12 points, or drop column labels and let the axis carry them, keeping labels on the margin line.