Locked lesson.
About this lesson
In this lesson, we will look at sparklines: what they are and how to create and modify them.
Exercise files
Download this lesson’s related exercise files.
Sparklines.xlsx111.2 KB Sparklines - Completed.xlsx
113.9 KB
Quick reference
Sparklines
Creating Sparkline charts in Excel.
When to use
Use to create tiny in-cell charts that give an idea of trends in the data, allowing you to identify potential issues.
Instructions
Creating a Sparkline
- Lay out your data in either a horizontal or vertical layout
- Select the data you want to chart
- Go to Insert --> Sparklines and choose Line, Column or Win/Loss
- Choose a location for your Sparkline
Copying Sparklines
- Sparklines can be copied via copy and paste or by dragging with the fill handle
Modifying Sparklines
- Done via the Sparkline Tools tab (which shows up when you select a cell with a Sparkline)
- Sparkline types can be changed from one style to another
- Highlight different data points by selecting the High, Low (or other) checkboxes
- Modify color combinations by selecting different styles from the Styles gallery
- Take more granular control by setting Sparkline and Marker colors manually
Hints & tips
- You cannot base Sparklines on data that lives in another worksheet
- To allow hiding of your source data, select the Sparkline --> Edit Data --> Hidden and Empty Cells --> Show data in hidden rows and columns
- Remember that a Sparkline is a micro chart only used to expose issues in a data trend
- 00:04 In this module we're gonna look at sparklines, which are a cool little
- 00:08 micro-charting feature that actually builds a chart directly in a cell.
- 00:13 So here's what we're gonna do first.
- 00:14 I'm just gonna scroll this data down just a little bit in size, so
- 00:18 that we can take a look at the data table that I actually have in place here.
- 00:22 And what I'd like to do is I'd like to go and
- 00:25 add a trend to my data to see what this actually looks like.
- 00:29 I've got lots of data on a monthly basis but
- 00:30 it would be cool to see something a little bit more visual about this.
- 00:33 So here's how we start, we're gonna go and
- 00:35 select the data that we actually want to chart.
- 00:39 And we're gonna go to insert and you'll notice that three-quarters of the way
- 00:42 across the tab we have line, column, and win/loss sparklines.
- 00:45 Let's start with a line.
- 00:48 And it says all right,
- 00:49 you found your data, where's the location you'd like to place it?
- 00:52 I'm gonna place it right over here in R24 and we're gonna say OK.
- 00:58 And it gives us this cool little line.
- 00:59 Now again, remember this is a dashboarding technique.
- 01:02 This is all about exposing problems,
- 01:05 not necessarily being able to read things in a great and wonderful manner here.
- 01:09 So the key here is that we're looking for dips in our trends.
- 01:12 And we say hey, look, it's really dipped down in the last couple of years.
- 01:15 Is that right, is that what we'd expect?
- 01:17 And if it's not, then we'd go and chase it.
- 01:20 Now I will admit though, I'm not really a big fan of the line sparklines.
- 01:24 I way prefer a column sparkline.
- 01:27 So if I go and change this now to column, you'll notice it changes it so
- 01:31 I can sorta see the shape of my data a little bit easier, in my opinion.
- 01:35 Some other things that I can do to this, I can highlight, say, the high point.
- 01:40 Now this highlights it in red, which is not my favorite.
- 01:42 But I also get an incredible styles gallery here with preformatted options,
- 01:48 one of which is grey bars with a green highlight.
- 01:52 And I like that one, that one actually looks pretty good.
- 01:55 When I'm happy with the sparkline, I can actually extend it and make one for
- 01:58 each of these rows simply by going and grabbing it as if it were a formula.
- 02:02 Left-click and drag and it will actually bring my sparklines all the way down.
- 02:06 You'll notice the shape of the data is different depending on the types of
- 02:09 sparklines that we have.
- 02:10 And it's all proportional to the data in that row, so that's kinda cool.
- 02:15 Now what happens with sparklines that are based against positive and negative data?
- 02:21 Let's go over here and let's create a new one.
- 02:23 In this case, back to insert, and we'll go to column.
- 02:27 And it asks us where do we want to put it?
- 02:29 And I'll just go put it here in column O.
- 02:32 And you'll notice that we get something with an axis around in the middle.
- 02:35 I could go and highlight the high point, let's say that we highlight it in red.
- 02:39 I could highlight negative points, it also highlights those in red.
- 02:43 Or maybe I'll change the negative to just the low point, that's a little bit better.
- 02:47 And again I can change the gallery so that I could go with grey bars
- 02:50 with the green high point and a red low point, and it looks like that.
- 02:54 And if I go and drag it up, we can see that we have some different trends because
- 02:59 all the stuff above it is completely positive.
- 03:02 Although you'll notice that it's highlighting the low point which is very,
- 03:06 very small in red as well.
- 03:07 So this is some of the differences you have.
- 03:09 Now we do have the ability every time we click on a sparkline,
- 03:13 it brings up our sparkline tools design tab.
- 03:15 We can change the sparkline colors,
- 03:17 we can change the colors of each of the markers and what not.
- 03:19 We can even change a few little things about the axis and what not.
- 03:23 I'm not gonna get into that too much cuz there's really a very limited amount of
- 03:26 things we can change.
- 03:28 I'm more interested in trying to figure out how do I get my trend to
- 03:32 show up on my dashboard page in column L?
- 03:35 So I'm gonna go and insert column L right here, go back to the source data.
- 03:40 And I'm gonna grab my sparklines, go over to the dashboard, and paste.
- 03:47 Now this is problematic, you'll notice we do get the sparkline tools.
- 03:51 The challenge is,
- 03:52 when we go to edit data, it's picking up a bunch of referential errors.
- 03:56 And unfortunately I will not be able to link these back here because, well,
- 04:01 the sparklines just don't work.
- 04:02 So we'll say Ctrl+Z,
- 04:04 this doesn't work at all because it has to pull from data on the same worksheet.
- 04:08 So let me show you how to get around this particular problem and
- 04:12 build a sparkline trend on this particular worksheet.
- 04:16 So what I'm gonna do is I'm just gonna narrow this guy down a little bit and
- 04:21 then I need to go and insert 12 columns.
- 04:24 Insert 12 columns, I'll just shrink these guys down a little bit as well.
- 04:29 I'm gonna select the area that I know I need for January to December.
- 04:35 And from here we'll say =.
- 04:39 We'll go click on January, hold down Ctrl+Enter.
- 04:42 Because this is a relative formula,
- 04:44 it brings across all the data that I'm looking for.
- 04:46 What I can do now is just quickly go back, grab my column headers, format paint this,
- 04:52 back to the dashboard, and make sure that I have all the columns.
- 04:56 There's January through December, that's great.
- 04:59 And at this point I will now go and insert my column sparkline.
- 05:04 Where's my data?
- 05:05 It's in this particular area.
- 05:07 Where is it gonna place it?
- 05:08 In X6, we'll say OK.
- 05:12 I can now drag the sparkline down and then I can go and
- 05:17 set my data the way I like it with my high point highlighted.
- 05:22 And that's cool, and now I'll just go and hide these individual columns.
- 05:26 Hide, no, my sparkline went away.
- 05:30 This is kind of an interesting little trick, and
- 05:31 this happens sometimes with charts, too.
- 05:34 We can go to edit data and we can show our hidden and empty cells, please show those.
- 05:41 And now, even though those columns are hidden, my trend is now here and
- 05:44 it's linked back to the original dashboard source data.
- 05:48 So this is the way that we actually go about bringing these things in to work on
- 05:52 our dashboard page.
- 05:53 By linking the source data into the original columns or
- 05:56 into the columns we need to show our sparkline and then hide them off.
Lesson notes are only available for subscribers.