Dashboard chart: expanding source, gaps, secondary axis, template
A chart on the Dashboard sheet plots monthly revenue for four product lines from a range on Data!A1:E13 (Month in A, products in B:E). The range is a plain range, not a table. Each month a new row is inserted above row 13, and the chart often misses the newest month until the range is manually extended. Product C's values are in thousands while the other products are in units, so its line flattens the others. Blank cells for unlaunched products currently plot as zero, creating misleading drops. You need to add a secondary axis for Product C, show gaps for blanks, make the chart capture new months automatically, and produce a chart template for three other dashboards with the same formatting. The dashboard is printed monthly for executives.
1. How do you make the chart capture new months automatically without editing the source range each month?
Read the worked answer
Convert Data!A1:E13 to an Excel table (Ctrl+T) and give it a name such as RevenueTbl. Then set the chart source to the table name rather than a fixed range. When a new month is inserted inside the table, the table expands and the chart's series automatically include the new row after the workbook recalculates. This is more reliable than manually editing the range each month and avoids volatile dynamic named ranges built with OFFSET. If the new row is inserted above row 13 but outside the table, it will not expand; insert it inside the table, for example by selecting the last data row and using Insert Table Rows Above. Check that the chart's horizontal axis uses the Month column and that all four product series still point to the correct columns. Format the table without banded rows if the chart labels look cluttered.
2. How do you show blank cells as gaps rather than zeros, and what should you check about formulas that return empty text?
Read the worked answer
Select the chart and use Chart Design > Select Data > Hidden and Empty Cells, then choose Gaps for empty cells. This stops blanks from being plotted as zero. The important distinction is that a formula returning "" is not an empty cell; Excel plots it as zero on a line chart. If the blank is produced by a formula, change it to return NA() for the chart series, then hide the #N/A with conditional formatting or a custom number format, or keep a separate chart range. Verify by checking the months for unlaunched products: the line should break, not drop to the axis. For a column chart, gaps show as missing columns, which is usually the desired message. Do not use zero if the business meaning is 'no data yet,' because zero implies a real measured value of zero.
3. How do you add a secondary axis for Product C and reuse the formatting across the other dashboards?
Read the worked answer
Select the Product C series, open Format Data Series, and choose Secondary Axis; then change the chart type to Combo and set Product C to a line or column on the secondary axis. Add axis titles that state the units, for example 'Units' on the primary axis and 'Thousands' on the secondary, otherwise the chart invites false comparisons. Be cautious: a secondary axis can make a small series look dramatic, so keep the axis scales honest and label them. To reuse formatting, right-click the chart and choose Save as Template, name it, and apply it to the other dashboards via Insert > Charts > Templates. A template stores chart type, colors, labels, and axis settings but not the data, so each dashboard still needs its own series selected. Check that the secondary axis setting survives when you apply the template.