How to Add a Moving "Today" Line to an Excel Chart (Budget vs Actual)

Step-by-step

A line chart comparing a project’s planned budget with actual spending, plus a red vertical line that marks the latest month with actual data and moves forward by itself as you enter new figures.

1. Lay out the data

The main table has three columns: month (A), planned budget (B) and actual spending (C). Future months are blank in the actual column. Next to it, a small helper table with two X cells and two Y cells (E5:F6) will draw the line.

2. Insert a line chart

Click any cell in the table and go to Insert > Line > the first 2-D line. Excel picks up the whole table and draws planned and actual lines. Make the chart bigger.

3. Find the latest month with data

A vertical line is just two points with the same X value and different Y values. In E5, find the most recent month where actual spending isn’t blank:

=MAXIFS($A$4:$A$27,$C$4:$C$27,"<>")

“<>” means “not empty”. In E6, repeat it with =E5.

4. Set the line’s height

In F5 type 0 so the line starts at the bottom. In F6, make it reach the highest value in either column:

=MAX(B4:C27)

5. Add the helper points to the chart

Copy E5:F6, click the chart and paste. It will look wrong at first. Right-click > Change Chart Type > Combo: keep planned and actual as Line, set the new series to Scatter with Straight Lines, and untick Secondary Axis for it. Now there’s a vertical line at the right month.

6. Format the lines

Make actual spending a solid black line and the planned budget a grey square-dot line. Make the vertical line red, long-dash, 3 pt. Retitle the chart, for example Project budget: planned vs actual.

7. Label it “Today”

Right-click the vertical line > Add Data Labels. Format the top label to show the Series Name instead of the value, and delete the bottom label. In Select Data, edit the series and rename it Today.

8. Watch it move, and polish

Enter next month’s actual spending and the red line jumps forward automatically. Optionally set the vertical axis bounds to zoom in on the useful range, and set the chart area to no fill and no border.