I was asked recently if it is possible to make this graph in Excel. It’s just a simple line chart with the data labels placed on the point instead of above or below, as is the usual case. I generally like this approach—you keep the same encoding method for the line and adding the labels in this way make for a nice design touch.
It’s also quite easy to make this in Excel, so I thought a quick tutorial was in order.
There are only six steps to it, so here we go:
- Make a standard Marked Line Chart.
- Delete the horizontal gridlines and the y-axis (simply select each and hit the delete key). I’ve also removed the x-axis tick marks; you can do so by selecting using the options in the horizontal axis format menu (accessible through the Ribbon, right-clicking, or using the CTRL+1/CMD+1 keyboard shortcut).
- Add data labels by right-clicking on the line and selecting “Add data labels” from the menu.
- Using the “Format data labels” menu (accessible by right-clicking on the labels themselves), place them at the Center of each point.
- Now we need to change the style and size of the markers. Use the Format menu (select the line and use that CTRL+1/CMD+1 keyboard shortcut) to change the marker type to the circle and increase the size so it neatly surrounds each label.
- You want the Marker Fill to be white (not empty because you will then see the line behind it) and the Marker Line to match the color of your line chart.
- The last touch is to fill in the final point of the chart. This isn’t always necessary, but it can be used to highlight certain points. To do so, select only that point (you will probably have to select the line and then click again to select just that point) and then change the Marker Fill on that point to match the Marker Line and the line itself.
That’s it folks. It’s a pretty straightforward. All you have to do is make a Marked Line Chart, add the labels, and modify the circle markers around them.
November 2, 2015
Leave a comment