In a past article, I posted 2 different ways to make a chart that appeared in the New York Times. Here is the chart and you can see the post with this link:
However, I didn’t provide the files or a detailed tutorial on either procedure on how I made these charts. A follower of the web page (Thanks to Nick for your comment and following of my blog) wanted to see the spreadsheet on how I created these charts. So the next 3 postings will be on how to make each chart and will also include video tutorial and free sample file download on these Excel charting techniques.
The first tutorial will be a detailed step by step of the chart/graph that I created. Also, I have updated the chart to now include 2012. That way we can compare and see if “Tiger is Back”. You decide!
1) Setup the Data
2) Create an Excel Line Chart from the data
3) Replace the Markers with Custom Markers
4) Format Line Data to recreate grid lines
5) Add Category labels for the Years Vertical Axis
1) Setup the Data (Download the file at the bottom of this posting so you can follow this tutorial)
I have a set up the data in 3 rows for each year. You will notice that 1996 has 3 rows of data with the year set in each row. The first row of data is for the wins. The second row is for the Top 10 finishes. The 3rd row is for the times that Tiger entered an even but did not finish in the top 10. This format is repeated for each year from 1996 to 2012.
Below that data, I have many other rows that we will user for the faux gridlines. Each faux gridline is set up at the 1/2 year. For instance, the faux gridline between 1996 and 1997 is set at 1996.5 all the way across where I want the line to appear.
Also, after a few gap/break columns, I have added similar rows for data for each of the 4 masters golf events (The Masters, US Open, British Open and the PGA Championship).
2) Create the Line Chart
Now select the data from C1:AF70 and then create a Line Chart with markers.
Now we need to add one more series that we will user for a faux Vertical Year axis.
Copy the data from B72:B88 as seen here (list of Years from 1996 to 2012:
then Select your chart
3) Fix the Primary Vertical Axis
This step will get the chart more to what we want to see. Right now, the vertical axis is too broad. Now fix the Vertical axis to the appropriate range. Click on the Primary Vertical Axis (Left Vertical Axis) and press CTRL+1 to bring up the Format Axis dialog box. Then set the axis options as follows:
Minimum = 1996 (the first year of the chart)
Maximum = 2012 (the last year of the chart)
Major unit = 1
4) Chart Clean Up
Lets now do 2 things that will make the chart look better as we proceed with the final changes.
a) Delete the Legend – Select the legend and hit the delete key.
b) Delete the Major Horizontal Gridlines – Select the major horizontal gridlines and press the delete key.
5) Add the Custom Markers and Make Line Color = None
If you do not know how to create and add custom markers to your Excel chart, you should check out this posting:
a) Create 3 different markers
i) Black circle to represent the wins
ii) Grey circle to represent the top 10 finishes
iii) Grey ring to represent the tournament entries that were outside the top 10 finishes
You can create these shapes from the Insert Ribbon by selecting the Shapes button from the Illustrations group. I suggest creating a oval shape filled with black and a black line. Then copy the circle and change the fill and line to grey. Then copy the grey circle and change the fill to No Fill.
b) Copy the Black circle, select your chart and find any data series that represent a win. Then press CTRL+V to paste the custom marker.
c) Press CTRL+1 and Change the Line Color Option to No Line
Your chart is really taking shape now.
Looking really good now and you should be able to see where we are going from here.
6) Change Gridline Series Lines Color to Solid and a color of Black and Marker Options to None
On all the horizontal lines that you still see that are running between the Circles should now be changed as follows.
a) Select a line and press CTRL+1 to bring up the Format Data Series… dialog box.
7) Change the Categories for the Horizontal Axis and Formatting
a) Okay, now lets modify the range for the horizontal axis so that the categories depict what we are looking to show. To do this, select the Design menu and then press the Select Data button from the Data group.
Then you want to select the Axis Label Range C1:AF1
b) Now that we have the categories set for the horizontal axis, lets adjust the formatting of the axis by removing the line color and tick marks. To do this, click on the horizontal axis at the top and press CTRL+1 to bring up the Format Axis dialog box. Then change the Major tick mark type to None
8) Remove Vertical Axis and Create Faux Vertical Axis with XY Scatter Series
These are the last few steps. This is necessary to create the vertical axis since we are not able to get the “Year” horizontal category to float over the years on the left.
A) Select the one last series that you created in Step 2. You can see it running from the bottom left corner to the top right area. You can also select it from the Layout Ribbon and then choose Chart Elements (series 71) picklist.
b) This isn’t necessary, so select the same series again from the chart as you did before (if you forgot or can’t find it, then refer to step A above). Then lets change this series back to the Primary Axis:
d) Delete the vertical axis
e) Modify XY Scatter Series (Years for vertical axis)
This is the final step! Click on the chart and then choose Select Data button from the Design Ribbon:
Based on the chart, it looks like TIGER IS BACK to me. 3 wins and over 11 top tens and one top ten in a major. He is back in form with his performance in the mid 2000s. Hopefully he will continue this next year and maybe even win a major. What do you think?
Free Sample Template Download File
Let me know if you can use some of these techniques in your next Company Executive Dashboard using Excel. Also, consider subscribing to the newsletter so you get the next post delivered directly to your inbox.