Normally, one turns to the line tool in Excel to manually draw vertical lines on a chart. The issue is that these lines aren’t bound to the x-axis, so any change to the chart is usually accompanied by manual line repositioning. Thankfully, there’s a “hack” that can eliminate the hassle of drawing vertical lines while allowing you to instantly change their position with a few mouse clicks.
Step 1: Create an XY (Scatter) chart…
For this example, theclosetentrepreneur.com feed subscribers for the month of May is being used.
Using the Chart Wizard, create an XY (Scatter) chart with time on the x-axis and your numeric data on the y-axis. If you want to follow the example in this tutorial, you can download the Excel spreadsheet *HERE*. Be sure to follow all the Chart Wizard steps and choose the “as object in” option to place the chart in your worksheet (we can change the location of the chart later).
Step 2: Add the vertical line data to your chart…
In a separate area on your worksheet, add the info for the vertical line data. Be sure to follow the same data structure that you used for your original data. For this example, the date when theclosetentrepreneur.com was featured on dumblittleman.com is being used as the vertical line data. It doesn’t matter what you name your y-axis data (you will see why “featured on dumblittleman.com” is being used in a moment) or what value you use as long as it is in the neighborhood of the data that has been plotted.
Once you’ve entered your vertical line data, select it and place your mouse over the select border until you see the “four arrow” cursor appear. At this point, click the selected data and drag it to your chart.
A “Paste Special” dialog box will appear, be sure that the options match those shown in the picture below:
Step 3: “Hacking” the error bars to create a vertical line…
After dropping the vertical line data in your chart, you should see a singe point appear representing your vertical line data. Click on the data point once to select it, then right click on the data point and choose “Format Data Series…”. In the “Format Data Series…” dialog box, click the “Axis” tab and select “Secondary axis”. Then click on the “Y Error Bars” tab and select the “Minus” option under Display. Change the error amount to percentage and enter 100% for the value. Click “OK” and your data point will now appear on a secondary axis with a vertical line underneath it which was created by the “minus” y-error bar. Changing the date and value of the vertical line data will move the line around as you wish, and the vertical line will remain in place when resizing or adding more data to the chart.
Tips on formatting your chart…
You’re basically done at this point and although this technique for adding a vertical line is useful, it’s not easy on the eyes. So to improve the appearance of your chart, you can format the vertical bar by clicking on it once to select it, then right clicking on the bar and selecting “Format Error Bars…” and click the “Patterns” tab to change the look of the error bar as you wish.
In addition, you can hide the secondary axis by clicking on it once to select it, then right clicking the secondary axis and select “Format Axis…”. Choose the “Patterns” tab and you can choose “None” for all the line and tick mark labels to hide the axis on the chart. In addition, you can change the scale of the axis to expand or contract the vertical line as you wish.
And last but not least, you can access the “Format Data Series” dialog box for the single vertical line data point (as was discussed above) and add the y-axis column label to your line by selecting the “Data Labels” tab and checking “Series name” under the Data Labels section. In this example, doing so will add “featured on dumblittleman.com” to our line. With a little additional formatting, you can end up with something like this:
This technique may seem like a lot of work, yet it’s easy to get the hang of once you’ve done it yourself. Plus, you can change the chart any way you like and not have to worry about manually moving vertical drawn lines around to compensate for any changes. Also, the vertical line location is accurately displayed, which isn’t necessarily the case with a manually inserted line. Ultimately, there are numerous ways to insert vertical lines into an Excel chart and hopefully this technique gives you a different perspective on the process. If you have any questions or suggestions, feel free to leave a comment below!
The Closet Entrepreneur