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 three years.
Well I’ll start by inserting a chart.
So I’ll go to 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 three lines each with four data points just to get us started.
This chart will have only one line, so I’ll move the blue region to only include Series 1.
The X axis dates will replace the four categories and then the oil price information will be under the Series 1 column.
Off camera, I’ll now paste in the data for 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 are 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 axes on the line charts require 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 in the Format Code I’ll write three m’s, space, yy.
This format will show a Jan 12 for the third 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 and when it’s selected, I’ll right click and Format Axis.
I’ll then go to Major Unit, can 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 is 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 the slide a title, giving the chart a title, 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 the 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 100% 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.