2. Arrange Chart Data Correctly

Overview

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.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Arranging a Stacked Column Chart (00:03)

    In the previous lesson, we created a chart showing revenue by region and year. For a time based chart, the years should obviously be in sequential order. For the stacked regions, it’s best practice to put the largest region on the bottom and the smallest on top.

    We do this by sorting the data that the chart is based on. We open the Sort dialog box with Alt, A, S, S and sort the revenue from largest to smallest by one of the years. The chart then updates automatically, making it easier to read.

  2. Arranging a Chart Without a Time Element (01:09)

    With a chart that doesn’t have time on the horizontal axis, we need to decide on the best way to sort it. Here, we create a column chart with sales people on the horizontal axis and revenue on the vertical axis. 

    To sort this chart, we sort the sales people from highest revenue to lowest revenue. This sorts the columns in the chart from largest to smallest. We can easily see which sales person has the highest and lowest revenue, and how the other sales people in between compare.

    This principle of sorting data from largest to smallest can be applied to any column chart, bar chart or pie chart that does not contain a time element.

Transcript

In the previous lesson, we learned how to create a simple chart in Excel. In this lesson, we'll learn how to arrange the data on a chart correctly.

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 the screen, it's obvious that the year should be in sequential order. What might not be obvious, however, is the order of our four regions. As the general rule, it's easier to read a stacked bar chart if the largest bar is on the bottom and the smallest is at the top.

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

To do this, we'll simply select the data and then bring up the sort dialog box with the shortcut Alt+A to open the data tab and S+S for sort.

We'll sort by the revenue in one of the years. So we'll select, say, 2003 and order from largest to smallest.

We'll then press OK.

As we can see, we now have the largest bar at the bottom and it makes our chart a 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, we have a list of salespeople and the revenue that they generated in the previous year. We'd like to create a chart that has the salespeople on the horizontal axis and the revenue on the vertical axis. So we'll select the data, press Alt+N and to bring up the insert tab, and then C for a column chart.

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

Even when we do this, it's still isn't very easy to analyze this data.

Let's improve this chart by ordering our salespeople from largest to smallest.

So going back to the data, select it, press Alt+A to bring up the data tab, S+S for the sort button, and sort by revenue.

Again, we'll sort from largest to smallest and press OK.

This makes it much easier to analyze the data. We know that Hall is the largest, Simpson is the smallest, and all the other salespeople are in-between.

When you're plotting a chart like this, where time is not on the horizontal axis, I would always encourage you to order your data from the largest to smallest, as you can see it clearly makes a big difference when trying to interpret the chart.

This process of ordering data from largest to smallest also works in pie charts, horizontal bar charts, and custom column charts. It's an easy win, very quick to accomplish and serves to make your charts much more readable.

Charts and Dashboards
Charts in Depth

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial