Sparklines

Quick reference

• 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.