Sign in or start a free trial to avail of this feature.
5. Line Charts for Large Datasets
The line chart is the best way of displaying large datasets on a PowerPoint slide. Here I show you how to plot daily oil prices over a 3 year period.
- Line charts should be used when you need to plot large amounts of data
- For datasets with over 15 rows of data, consider using a line chart over bar charts
- Most of the work in creating line charts comes when formatting axes
To change x-axis and y-axis settings
1 Select the desired axis
2 Right-click on desired axis
3 Select Format Axis
4 Perform changes to settings
When we need to plot large amounts of data, bar charts can become difficult to read. When I have a data set of, say, over 15 data points, I'll normally use a line chart to plot this information. The perfect case for using line charts is when you're plotting a company's share price, or a commodity price, over time. Here you may have hundreds of data points, and a line chart is one of your best options. Let's say, for example, I'd like to chart the price of oil over the last 3 years. I'll start by inserting a chart, so I'll go to Insert, Chart, and select the line chart.
Then press OK to generate the line chart on our screen.
As you can see, Excel creates 3 lines, each with 4 data points, just to get us started. This chart will have only 1 line, so I'll move the blue region to only include Series 1.
The X axis dates will replace the 4 categories, and then the oil price information will be under the Series 1 column. Off-camera, I'll now paste in the data from my oil price.
Now when I minimize the Excel sheet, you can see our oil price appear as a line chart on our PowerPoint slide. Unlike with bar charts, we won't be adding data labels to our line chart, because there's simply too many data points to make these labels readable. Instead, we'll keep the Y axis in place, and read the oil price from the horizontal grid lines. Again unlike with bar charts, the axis on the line charts requires some formatting to make the chart more readable. Let's start with the Y axis.
So that the values consume most of the chart area, I'd like the Y axis to start at just below the lowest oil price value, which is around 75. To do this, I'll select the Y axis, right click, and select Format Axis.
I'll then go to the minimum axis options, and write in 70, which is my desired value.
When I close this dialog box, you can see that our values now take up our full chart area, which is exactly what we want. Now let's take a look at the X axis. I'll start by changing the chart area by making it wider, and I'll remove the legend because we have only one line in this chart, so a legend isn't needed. The first change I'd like to make to the X axis is to change the format of the labels. Currently, we're showing the day, the month, and the year. I'd like to just show the month and the year, so I'll select the X axis, right click, and format axis.
I'll select the number option, which allows us to change the date format, and then the format code. I'll write 3 M's, space, YY.
This format will show Jan 12, for the 3rd of January 2012, which is a much neater date format. I'll click Add, and then Close.
And as you can see, our labels have changed, but unfortunately they're overlapping. Let's make this more readable by changing the size of the label font. So I'll go to Home, and I'll select 14.
This is certainly an improvement, but I'd still like the month and year to appear on the same line. To do this, instead of showing every second month on the X axis, I'll show every third month. So again I'll go to the X axis, I'm going to select it, I'll right click, and format axis.
I'll then go to Major unit, change it manually, and enter 3 instead of 2.
I'll then close.
And you can see that our X axis labels now show every third month instead of every second month. And it's much easier to read. The last change I'll make will be to select the chart, and move the chart area slightly in from the right, so that the January 14 label can be easily read. Off-camera, I'll make some small editing changes, giving this slide a title, giving the chart a title, and cleaning up some of our font sizes to make them consistent. As this example has probably shown you, the line chart does require a lot more formatting than the bar chart. I'd encourage you to experiment with the various axis options available for the line chart, so you understand all of the ways in which these charts can be formatted. In this example, I used this simple line chart to show our oil price fluctuation. There are other line chart options available as well. If I go back to the Insert Chart options, you can see that we have the stacked line chart, and the one hundred percent stacked line chart. These are similar to the stacked column charts we've used in previous lessons. If you want to use a stacked chart for large data sets, I don't recommend using this option, because I think the audience doesn't intuitively grasp a stacked line chart. Instead, I'd advocate using an area chart, which I'll show you in the next lesson.