I'm working on a spreadsheet that includes a number of different salespeople, and the
amount that they sold each month.
I need a way to visualize these numbers, so I can see how each person's sales are progressing
over time.
I could just put them all together in one big chart.
This certainly looks cool, but it's not exactly easy to read—and it's almost impossible
to pinpoint any one person with all these overlapping lines.
Luckily, there's another option called sparklines that'll let us give each person their own
separate mini-chart—one that fits into a single cell.
In this case, we'll be putting a sparkline on each row, so it'll be right next to the
source data.
To get started, select the data for a single sparkline.
In most cases, this will mean a single row or column.
Then go to the Insert tab… and in the Sparklines group, you'll find commands for the three
different types: Line…
Column… and Win/Loss.
I'm going to choose Line.
Next, we just need to select the cell where we want the sparkline to go (in this case,
right next to our source data)… then click OK.
And there's our first sparkline!
We'll copy it to the rest of the worksheet the same way that we'd copy a formula: by
clicking and dragging the fill handle.
Now all the rows have sparklines, and we can easily to see whether sales are increasing
or decreasing for each employee.
For example, David Carson's sales are trending upward… while Elizabeth Counts' are trending
downward.
Let's make some changes that'll make the sparklines stand out a bit more.
If you want to make them larger, you can just make all of the rows taller… and the sparklines
will stretch to fit.
You can also select any sparkline (it doesn't matter which one)… and the Design tab will
appear on the Ribbon.
Here you'll find a wide range of tools that'll let you customize your sparklines.
In the Show group, for instance, you can choose to highlight certain points to make your sparklines
easier to read.
Probably the most useful ones are High Point… and Low Point.
I also like First Point and Last Point for clearly marking the beginning and the end
of the line.
If we turn on Markers, you can see that it highlights every point, which can make your
sparklines look a bit cluttered.
And I know there are no Negative Points in my data, so I'm going to leave this option
alone.
It may help to choose a different style for your sparkline to give it more contrast.
I like this one because it makes the high point green, and the low point red, which
should really make them stand out.
In the Type group, you can change the type of sparkline that you're using.
In addition to Line, Column is also good choice because it works with most types of data.
Win/Loss, on the other hand, will just tell us whether the values are positive or negative—it
won't show how high or low they are.
I don't have any negative values here, so that's not as useful.
I'm just going to switch back to Column.
There's one more thing you should know about sparklines—specifically, the way they're
displayed.
By default, they're scaled to fit each cell perfectly, so they fill the entire thing.
Notice how the maximum values look the same in these two sparklines, even though we can
tell from the source data that the numbers are very different?
That's the problem with this feature.
It makes it hard to compare one sparkline to another, because they all look so alike—in
some cases, it can even be misleading.
Luckily, there's a way to counter this.
All you have to do is go to the Axis command… and look to the Minimum and Maximum Value
Options.
Right now, they're both set to automatic, and we just need to change them to Same for
All Sparklines.
Now we can see that the maximum in this sparkline is much higher than the the maximum in this
one.
Compared to traditional charts, sparklines offer a very different approach to visualizing
your data.
They make it easy to view and analyze trends on a more individual basis, and can really
help you manage your worksheet.