Adding a Dotted Line for the Budget in Excel

• 2 minute read

In the image below we have actuals listed in column B and the budget in column E.

We will create a formula in cell C2 that we can copy down to set up the data ready to chart.

The formula for cell C2 is.

=IF(AND(B2<>"",B3=""),B2,IF(B2<>"",NA(),E2))

This will show the #N/A error if it is a normal actuals month and the actual figure if it is the last actuals month. If there are no actuals, then the budget value is displayed.

Excel does not plot the #N/A error.

We can now create the chart.

Selecting the range A1:C13.

Click the Insert ribbon tab and click the Recommended Charts icon.

Choose the second chart shown and click OK – see below.

You could leave the chart as is with the budget shown with a different colour.

The image below has the budget changed to a dotted line of the same colour. The Chart title is linked to cell A1 via the Formula Bar.

The format options for the budget series to make the changes are shown below.