2. Arrange Chart Data Correctly

Subtitles Enabled
Replay Lesson

Next lesson: Formatting the Vertical Axis

Watch next lesson


Charts can be made much more readable by arranging the chart data correctly. In this lesson, I'll show you a few tips on how to do this for various charts.

Lesson Notes

Arrange chart data correctly

- Arranging chart data correctly is crucial when building charts
- It can dramatically improve readability for your audience and improve their understanding of the chart

How to arrange data

- For time-series data, always show time in chronological order on the horizontal axis
- For stacked column charts, stack the largest bars at the bottom and smallest at the top
- For column charts and bar charts, sort individual columns from largest to smallest unless time-based
- For pie-charts, sort data from largest to smallest in a clockwise direction

Keyboard shortcuts

SHIFT + : Select next cell
CTRL + SHIFT + : Select all cells within data region
ALT + A , S, S: Open Sort dialog box for selected data
ALT + N , C: Create column chart


To make charts more readable, it's important to arrange the data correctly beforehand. When you have time-based charts, such as the chart shown on screen, it's obvious that the years should be in sequential order.

What might not be obvious however is the order of our four regions. As a general rule, it's easier to read a Stacked bar Chart if the largest bar is at the bottom and the smallest at the top.

In this case, we would need to switch the position of North America and Europe.

To do this, we'll simply select the data.

And then I'll bring up the Sort Dialogue box with the shortcut Alt + A to open the data tab and S S for sort.

I'll sort by the revenue in one of the years.

So I'll select say 2003 and I'll order from largest to smallest.

I'll then press OK, and as you can see, we now have the largest bar at the bottom.

And it makes our chart that bit easier to read.

Now, what happens if we have data that doesn't have time on the horizontal axis? Such as this example.

Here I have a list of sales people and the revenue that they generated in the previous year.

I'd like to create a chart that has the Sales people on the horizontal axis and the Revenue on the vertical axis.

So I'll select my data, Alt + N, to bring up the Insert tab and then C for a column chart.

I'll make this chart bigger so that it's easier to read.

But even when I do this, the data isn't particularly easy to read.

To make it easier to read, we'll simply order our Sales people from largest to smallest.

So going back to my data, I'll select it, Alt + A to bring up the data tab and S S for the Sort button, and I'll sort by Revenue.

And again, I'll go from largest to smallest.

Then I'll press OK, and this now makes our data set much easier to read.

We know that Hall is the largest, Simonson is the smallest and the position of all the other sales people in between.

When you're plotting a chart like this where time is not the horizontal axis, I would always encourage you to order your data from largest to smallest.

As you can see it can make a big difference when trying to interpret the chart.

This process of ordering data from largest to smallest also works for Pie Charts, Horizontal Bar Charts, and Custom Column Charts.

It's an easy one. Very quick to accomplish and serves to make your charts much more readable.