Tip:
Highlight text to annotate it
X
I'm working on a spreadsheet that contains a
number of different salespeople
and the amount that they sold each month.
And I need a way of visualizing these numbers so I
can easily see
how each salesperson is doing month to month.
I could just put them all into one big chart.
But while this may look cool, it's not exactly easy to read,
and it would be almost impossible to pinpoint a specific salesperson on this chart.
Luckily, in Excel 2010, there's another option called sparklines that will allow us to give
each
salesperson a separate, miniature chart that fits
into a single cell.
In this case, we'll be putting a sparkline on each
row, so it will be right next to its source data.
To get started, select the data for a single sparkline.
In most cases that will mean a single row or
column.
And then on the Insert tab, there is a Sparklines group that has commands
for the three different types: Line, Column, and Win/Loss.
I'll select Line.
And we just need to select the location for the
sparkline,
which will be the cell right next to our selection.
And there's our first sparkline.
And we'll copy this the same way that we copy a
formula: by dragging the fill handle.
Now, all of these rows have sparklines, and it's
easy to see whether
sales are increasing or decreasing for each employee.
For example, David Carlson's sales are trending upward,
but Elizabeth Ferguson's sales are trending downward.
Let's make some changes to the sparklines to make
them stand out a little better.
If you want to make them larger, you can just make
all of the rows taller,
and the sparklines will stretch to fit them.
Then, select any sparkline - it doesn't matter which one - and the Design tab will appear
with a
number of different sparkline tools that you can
use to modify them,
and if you ever need to delete them, you can just use the Clear command.
In the Show group, there are some options that let
you highlight certain points.
Probably the most useful ones are High Point and
Low Point,
and I also like First Point and Last point.
Now I know that there are no Negative Points in
this spreadsheet, so I won't worry about those.
And Markers will highlight every single point, which may make it look a little too cluttered.
It may be helpful to choose a Style that has a
little more contrast.
I like this one because it makes the high point
and low point green and red,
which really makes them stand out.
In the Type group, you can change the type of
sparkline that you're using.
Column is usually a good choice, and it will work
with most types of data.
Win/Loss will basically just show whether the
values are positive or negative,
but it won't actually show how low or high the values are.
Since I don't have any negative values here, that's not as useful, so I'll just use Column.
There's one important thing that you need to know
about how Excel displays sparklines.
By default, each sparkline is sort of stretched to
fit its own cell.
So the maximum value will go up to the top of the
cell, and the minimum will be at the very bottom.
The problem with this is it doesn't show you how
high or low the values are
compared to the other sparklines.
For example, all of these maximum values look the
same,
even though the values may be very different, and this can be misleading.
To fix this, go to the Axis command, and you can
see that the
Minimum and Maximum options are set to Automatic.
You'll need to change both of these to Same for
All Sparklines.
And 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 data,
and depending on what type of project you're working on, you may prefer one or the other.
And sometimes, to get the most complete picture, you may even choose to use both.