INTHEBLACK August 2022 - Magazine - Page 60
WORK SMART
// E XC E L YO U R S E L F
STORY NEALE BLACKWOOD CPA
20
YE ARS
OF E XCEL
YOUR SELF
VERTICAL
LINE IN A LINE
CHART
WHEN CREATING AN EXCEL LINE CHART, YOU MAY WANT TO INSERT A
VERTICAL LINE TO MARK SOMETHING ON THE CHART. THIS MAY BE TO SHOW
THE SPLIT BETWEEN ACTUAL AND FORECAST FIGURES OR TO HIGHLIGHT AN
IMPORTANT DATE THAT AFFECTED THE RESULTS BEING DISPLAYED.
01
T
he techniques used in this example
can be applied to other charting and
reporting requirements.
WORKED EXAMPLE
The chart we will create is shown in Figure 01.
The dotted vertical line automatically moves
to the right whenever new actuals are added to
column B in the data table shown in Figure 02.
Columns B and C are input columns. The
formula in cell D2, which we’ve copied down, is
=IF(B2=0,C2,B2)
This formula displays the amount from
column C if there is a zero (or blank cell) in
column B. If there is an amount in column B,
it will display it in column D. This creates the
revised sales forecast for the year.
60 ITB August 2022
To position the vertical line and make sure
it is long enough, we need to estimate an
amount greater than the maximum amount in
the sales in column D. That’s what the formula
in cell G1 does, and it is
=ROUNDUP(MAX($D$2:$D$13),-4)
The MAX function returns the maximum
value from the Sales in column D. To make
sure we exceed that amount, we’ve used the
ROUNDUP function with a negative number as
the second argument. Using a negative number
as the second argument rounds to the left of
the decimal point. By using -4, we’re rounding
up to the nearest 10,000. In this case that
equals 130,000. Using -3 would round up to the
nearest 1000. Column E is used to create the
dotted line for our chart. As you can see, there
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
are 11 #N/A errors and a single value. These
errors are displayed on purpose because Excel
won’t plot #N/A values on the chart. You may
be surprised to discover that there is an Excel
function to display the #N/A error.
The formula in cell E2, which we’ve copied
down, is
=IF(AND(B2>0,B3=0),$G$1,NA())
This formula reviews the current row in
column B as well as the next row in column B.
If the current row is greater than zero and the
next row is zero, then the maximum value we
calculated in cell G1 is displayed. Otherwise,
the NA function returns the #N/A error. This
results in the maximum value being displayed
on the same row as the last actuals figure.
The vertical line between Actuals and
Forecast in cell E1 is achieved by using what
is called the “pipe” character. This is on the
same key as and you need to hold down the
Shift key to select it.
With this structure, we can create the chart.
CHART INSTRUCTIONS
1. Select the range A1:A13.
2. Hold the Ctrl key down and select the
range D1:E13.
3. Click the Insert ribbon tab. In the Charts
section (middle of tab), select the dropdown for line charts and select the top left
line chart.
4. With the chart still selected, press Ctrl + 1.
Press the 1 on the top left of the keyboard,
not the numeric keypad. This opens the
Chart Format task pane on the right of the
screen. This shortcut works throughout
Excel and opens the format dialog for
whatever is selected.
5. In the top left of the task pane, click the dropdown and choose Series Actuals|Forecast –
bottom of list. See Figure 03.
6. A single data point should appear on the
chart. This is the 130,000 data point in August.
7. Click the Chart Elements green plus sign in
the top right of the chart, tick Data Labels
and Error Bars and untick Gridlines and
Legend. See settings in Figure 04.
8. In the task pane on the right of the screen,
click the top left drop-down as per step 5 and
select the Series Actuals|Forecast Data Labels.