Locked lesson.
About this lesson
When you need to show values of a different scale, there is no better way than to add an additional axis.
Exercise files
Download this lesson’s related exercise files.
Plotting data using the secondary axis.xlsx58.4 KB Plotting data using the secondary axis - Completed.xlsx
62.9 KB
Quick reference
Plotting Data Using The Secondary Axis
Adding a secondary axis to plot values of different scales.
When to use
Use when you have values of very different scales so that they can be compared in a single chart.
Instructions
Reminder of Charting Goals
- Every chart should tell a story, quickly and effectively
- Extra chart elements create noise, and get in the way of the story
- It is a best practice to remove as much excess ink (noise) as you can
Creating the Combination Chart
- Select your data
- Create your multi series chart as desired
Adding the secondary axis
- Right click the series you want plotted on the secondary axis
- Choose Format Data Series --> Series Options --> Plot Series On --> Secondary Axis
Adding context
- Expand the chart so that all rows are readable
- To link a title to a cell, select the chart title, press the equals key, click on a cell and press Enter
- Other modifications can be made as with any normal chart
Hints & tips
- Remove items and elements that you don’t need by selecting them and pressing the Delete key
- CAUTION: Removing either the primary or secondary axis for the chart will cause all data to be plotted on the primary axis
- Change font sizes by selecting an element and formatting it from the Home tab
- When removing gridlines, make sure you are selecting the gridlines and not the entire chart
Login to download
- 00:04 For the next chart on our dashboard we want to fill the area that actually
- 00:08 provides a chart for revenue versus customer visits.
- 00:12 The challenge that we have here is that the data that we're gonna use for
- 00:16 these things is a different scale and that becomes a little bit of a problem.
- 00:21 So, let's grab the data that we want to chart.
- 00:25 And I'm gonna make another combination chart out of this, but
- 00:27 we're gonna start with going to a 2D line chart.
- 00:31 And at this point, I'm going to change the Store Entries into a column chart.
- 00:38 So, when we do that, Store Entries will become a column.
- 00:41 And we'll say, Okay.
- 00:43 As you can see,
- 00:44 the challenge here is there's a real big difference between these two scales.
- 00:49 Revenue is in the thousands and that's good, which is where we want it, but
- 00:54 the customer visits is sometimes under 100, so, these don't really plot all that
- 00:58 well if you're trying to actually figure out if the trend is related.
- 01:02 So, what we're gonna do is we're gonna modify this to use the secondary access.
- 01:09 So, we'll format data series and you'll notice that as soon as we do that,
- 01:13 it comes in and says, would you like to plot this on the primary axis, meaning,
- 01:16 would you like to use the same values as you used for your revenue or
- 01:19 would you like to flip this to the secondary access?
- 01:23 When we choose to do that it will actually flip it out right away
- 01:27 to use a different scale and that scale shows up on the right hand side.
- 01:31 Now, one thing to be cautious of, if you look at this and go, I don't need that,
- 01:35 Delete, it goes back to the primary access.
- 01:39 So, I'm just gonna press control Zed to undo that.
- 01:42 This is something that gets forced on you when you're working with these things,
- 01:46 you can't have two different values,
- 01:48 different number scales that don't have an axis to actually display that case.
- 01:52 And it's kind of important, you need to have that kind of information there.
- 01:57 Now, this is cool and everything else, but
- 01:59 we can do some things to make it look a little bit better.
- 02:01 Number one, we should give it some context, so, we'll go and
- 02:03 give it a title, Store Visits vs Revenue.
- 02:06 And I'm gonna go and soften this one a little, I don't know,
- 02:09 that I really want this in this dark orange.
- 02:11 So, I'm gonna grab this particular chart and again, I can come over to the fill
- 02:14 area here and I could change the fill here or I could right-click on this one and
- 02:18 say, let's make this fill into something that looks like,
- 02:22 that's a little too dark, how about something that looks about there, perfect.
- 02:26 So, a nice gray background that's not really obnoxious.
- 02:30 Now we can go and we can, well,
- 02:32 we don't want to format a data point, we got to watch these kind of things up here.
- 02:36 So, we'll go and click here and right click and say, Format Data Series, again.
- 02:41 You'll notice that it's all ready come up.
- 02:43 I can drop my gap width down.
- 02:45 I'm gonna bring it down to about, I don't know, 45%,
- 02:48 something like that, that gives me some room.
- 02:51 So, that I can right click and drop in some data labels.
- 02:56 In fact, I even wanna have a little bit less gap with, I think,
- 03:00 I'm gonna drop this down to about 20%.
- 03:03 There we are and now, I can grab my data labels and
- 03:07 I'm going to push these down to the base.
- 03:11 So, there we are, all my data labels are now sitting nice down low.
- 03:16 I just want one more thing for this chart, I think, and
- 03:18 that's to add some markers to this particular series.
- 03:21 So, we come over and take a look and I don't see my option here.
- 03:26 Always tries to catch me up by this, by hiding this one.
- 03:29 Go to Marker, Marker Options, gonna use Built In and
- 03:33 I'm gonna go with a circle this time.
- 03:35 But this time, I'm thinking, you know, it'll be more fun to do this,
- 03:38 let's change the fill, to no fill.
- 03:40 And now, when I go and click away,
- 03:43 you can see that my markers are empty little circles, which is kind of neat.
- 03:47 So, that's no fill.
- 03:49 If I actually wanted to change them up a little bit more,
- 03:52 I could even come back and say, you know what, maybe,
- 03:55 my marker is gonna use a solid fill and that solid fill is going to be White.
- 04:03 And that makes them pop even a little bit more.
- 04:06 So, that's a pretty nice looking chart.
- 04:08 It is kind of a bummer that we can't delete the secondary axis values,
- 04:11 particularly, since we have data labels already in place for these, but
- 04:15 you know what, at the end of the day it'll work.
- 04:18 That's one of them key magic things about working with the secondary axis is we
- 04:22 can plot different values on different scales.
- 04:26 Engineers actually make use of this kind of technique all the time, but for
- 04:30 financial information, most of the time, we're in the same scale, but in this case,
- 04:33 it's definitely different.
- 04:35 So, let's go grab this, we'll go Ctrl-X,
- 04:37 we'll take this guy over to our dashboard and we'll paste in place here.
- 04:42 Again, hold down the Alt key to resize this and
- 04:44 make sure that everything is lined up exactly where we want with the cells.
- 04:49 And we've now got a nice little chart there to add to our dashboard that's
- 04:52 making things start to flash out really well, with a lot of the key stats we need.
Lesson notes are only available for subscribers.