Recently a user in the Mr. Excel Forum a member wanted to know how they could make an Excel Chart where one Metric was a Horizontal Line and the other metric was a Vertical line and to also plot the intersection. I thought that we might have need of these techniques in our Excel Dashboard Charts for my companies templates. So I wanted to create Sample File, Tutorial and Demonstration on this Dashboard Technique.
So most people might start with a Line Chart to solve this query. But Excel Charts set up a Line Chart as a time series graph so that means that the first point will create a line to the second point to the right of the first point. This is fine for creating a horizontal line chart that would look like this:
However, when you want to create the vertical line. It would look something like these attempts:
Even if we were able to make a Vertical Line in an Excel Line Chart, how would we get it to intersect the Horizontal Line. So now we are getting frustrated. How can we do this for our Excel Dashboard Template Charts and Graphs?
The solution is to use an XY Scatter Chart.
An XY Scatter Chart you say? But how? Well here is how, the XY Scatter also has the option of creating XY Scatter with Lines. That’s how
It may take a little more setup but once you get the hang of it, you will start to use XY Scatter with Lines for lots of your Company’s Executive Excel Dashboards.
We are going to create 2 XY Scatter with Line Series in the same chart. So we need to go back to our early education Graphing principles. These 2 lines will will be drawn in an Excel Chart using 2 points to make the Line.
You can set up your data like this, however, this is mostly so that you know which points are for the X’s and Y’s for your lines. Excel has difficulty looking at multiple XY series in the same chart, so this is really for us.
1.00 1.000 1.25X’s 01.25Y’s 1.001.00
To make this Excel Chart, do the following tutorial:
1) Click on any blank cell in the Excel worksheet not next to our data. We don’t want Excel to make any decisions for us on this one and it will try if you start directly next to data points. If you do, just delete any series that shows up on your Excel Chart:
2) For Excel 2007, go to the Insert Ribbon and Choose Scatter with Lines from the Charts Group:
3) Now with your Chart Selected, choose Select Data from the Data Group in the Design Ribbon for Excel 2007:
4) Click on the Add button in the Legend Entries (Series) area of the Select Data Series Dialog Box:
5) Choose A1 as your Series name, C1:D1 as the X Series Values and C2:D2 as the Y Series Values (This will create the vertical line):
6) Click on the Add button in the Legend Entries (Series) area of the Select Data Series Dialog Box like you did in Step 4 and then Choose A3 as your Series name, C3:D3 as the X Series Values and C4:D4 as the Y Series Values. (This will create the horizontal line):
7) Click on the Add button in the Legend Entries (Series) area of the Select Data Series Dialog Box like you did in Step 4 and then Choose A6 as your Series name, C6 as the X Series Values and C7 as the Y Series Values. (This will create the Intersection Point):
8) Click on Ok and Ok again to save all your changes for the Excel Intersecting Line Chart.
9) Rest on your laurels.
The Excel 2007 Video Tutorial Demonstration and Sample Free Download Files:
Here is a video tutorial showing you exactly how to do the steps listed above.
Try it yourself – Here is a FREE Sample Download Tutorial File that you can use to try and make a 2 Lines Intersect in an Excel Chart:
Don’t forget to leave me a comment and also to sign up for my RSS Feed.