With Excel Charts, it is very easy to create a Vertical Column in your Line Chart and make it interactive with a Scroll Bar!

The reason why I do this, is to use the vertical column to highlight a specific point in my Excel chart whilst I am presenting the data to my stakeholders.

Mmmm Steak 🙂

In this example, I show you how easy it is to insert an interactive Vertical Column in your chart using Excel & sprinkled with a little magic!

This is our data:

vertical-line-01

 

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Add a new column Vertical Line, and place in the first value as 100.

vertical-line-02

 

STEP 2: Select the entire table, go to Insert > Line Charts > Line with Markers

vertical-line-03

 

STEP 3: Select the chart and go to Design > Change Chart Type > Combo > Custom Combination

vertical-line-04

 

STEP 4: For the Vertical Line series, change the Chart Type to Clustered Column and check Secondary Axis.  Press OK.

(This will transform the Vertical Line series into a vertical column in your chart.

Make sure the SALES PERIOD and SALES are Line with Markers Chart Types).

vertical-line-05

 

STEP 5: Double click your Secondary Axis to view the Format Axis Panel.

Set Maximum to 100, to ensure our Vertical Line extends all the way to the top since we placed in the value of 100.

vertical-line-06

Click on Labels and change the Label Position to None.

vertical-line-07

 

STEP 6: Just to clean up our chart, notice there is a Sales Period Series.

Right click on it and click Delete.  We do not need this.

vertical-line-08

STEP 7: Double click on the vertical bar, so that we can make it slimmer.

vertical-line-09

In the Format Data Point pane, Set Gap Width to 500%. 

vertical-line-10

 

STEP 8: Now it is time to try out our interactive chart!

Drag and drop the Vertical Line value of 100 into another Sales Period and see the column chart move!

vertical-line-11

 

STEP 9: There is a cooler trick and we can make the Vertical Bar become Dynamic! For this we need the Developer Tab.

If you do not have Developer Tab enabled yet, it is very easy to enable this first.  Go to File > Options

vertical-line-19

Go to Customize Ribbon > Main Tabs > Developer.  Check this and click OK.

vertical-line-12

 

STEP 10: Go to Developer > Insert > Form Controls > Scroll Bar. Drag this horizontally on top of the graph.

vertical-line-13

 

STEP 11: Right click on the scroll bar and select Format Control.

vertical-line-14

STEP 12: Set the Maximum Value to 12. This will depict our sales periods from 1 to 12.

For the Cell link, place in $D$5, this will show the value of the Scroll bar in this cell (i.e. which sales period it’s pointing to).  Press OK.

vertical-line-15

 

STEP 12: In the Vertical Line column, place in this formula: =IF(A5=$D$5,100,””)

What this will do, is simply check if the Sales Period matches the value of the Scroll bar ($D$5).

If YES, then set the value to 100.

Setting the value to 100, will then make our vertical line show up in that Sales Period!

Now the pieces are almost all in place!

vertical-line-16

 

STEP 13: Double click the lower right corner of the formula cell, which will copy the same formula down the entire column.

vertical-line-17

 

Your interactive chart is now complete!!!

Try playing around with the scroll bar and see the Vertical Line move with it! Cool hey?

vertical-line-18

vertical-line

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

XY Scatter Chart: Related Variables When you want to find out whether or not 2 variables are related or correlated, then an XY Scatter Chart should be used to plot the graph. Related variables will show up on the graph as dots that are almost in a straight line.  Examples of variables that have relationships cou...
Bubble Chart: 3 Variables On A Chart A Bubble Chart is an extension of the XY Scatter Chart.  It adds a 3rd variable to each point in the XY Scatter Chart. For example, if you have a Scatter Chart that shows the relationship between the age of a house and its proximity to the city and want to add the value of...
In-Cell Bar Charts with the REPT Function When you are creating an Excel Dashboard and are limited by space and do not want to insert a chart, you can easily create an in-cell bar chart using the RPT (repeat) function. The RPT function uses the vertical bar character | as the first argument: text and references the va...
Process Map Using Excel If you want to show a process in your workplace then this can be designed using Excel´s Smart Art. There are heaps of Process Maps in Excel that shows progress from left to right, top to bottom, one way and circular.  Here is how you can insert a left to right Process Map usin...