Hello again and welcome back to our course on Excel 2016 Advanced.
In this and the next section we’re going to look at a couple of types of chart that
are very often used in financial applications.
Having said that, these two types of chart are very different indeed.
And in fact the one we’re going to look at in this section, the Sparkline.
Where it was introduced quite recently.
It came in in Excel 2010.
And Sparklines are unlike any of the other types of Excel Chart.
Amongst the main differences are that Sparklines are not actually charts in the same way that
things like Area Charts and Column Charts and so on are.
These are tiny little charts and each chart fits into a worksheet cell.
So it doesn’t sort of hover over a sheet.
It fits into a cell.
Now in fitting into a cell a Sparkline can have other cell content in the cell as well.
But the key thing about Sparklines is that they are simple, visual indicators.
Now we’re looking at a page here from Google Finance.
Obviously this will be out of date by the time that you come to look at it but you’ve
got some tiny little charts there.
And very often in sources of financial information these tiny little charts are used to show
the overall trend of something or perhaps to compare performance of one stock to another
stock, that kind of thing.
So let’s take a look at how we can use Sparklines in Excel 2016.
So in order to demonstrate Sparklines I’m going to go back to the Team Sales 2015 data,
the commission sheet we have here.
And we’re going to draw Sparklines to show how each member of team A’s commission has
progressed over the course of 2015.
I’m going to put these Sparklines in column B. so let me just open up a spare column.
Let’s make that slightly wider.
And then on the Insert tab to the right of the Charts area there’s actually a Sparklines
group.
There are three types of Sparklines, Lines, Columns and Win/Loss Sparklines.
Let’s start with the Line.
Now that brings up a Create Sparklines dialog.
And we basically have two selections to make.
The first selection is for the data that we want to represent in the Sparklines.
Now in this case that’s going to be the data from C4 to Q8.
And where do we want the Sparklines to be placed?
We want them to go in B4 to B8.
Click on OK and there are our Sparklines.
If you look at Andy Carothers Sparkline that gives you pretty much the same message you
get if you work methodically through the twelve months of commission that he’s had and that
is that overall his performance during the year deteriorated.
It was a little bit up and down but the overall tendency is downwards.
In fact the team in general aren’t doing so well.
Maybe Jeff Hays not quite so bad but overall there isn’t an improvement showing.
Overall things are at best staying pretty much how they were.
So that’s an example of the sort of clear visual representation you can get by using
Sparklines.
For team B I’m going to use one of the other types of Sparkline.
I’m going to use a Column Sparkline.
You may have noticed just now that when I inserted the Sparkline Excel assumes that
the range that I had selected was the destinations.
So let just select the destination for the team B Sparklines here.
This time I’m going to click on Column.
Notice that the location of Sparklines is correctly set by Excel.
So I just need to choose the data which is that, click on OK and now I have a Column
style of Sparkline for each member of team B.
Now there is a third style of Sparkline, the Win/Loss Sparkline, but we’re going to cover
that as part of Exercise 09.
Now whilst it’s not mandatory for Sparklines to be in groups such as these they very often
are.
And if you imagine a situation where you had inserted say 20 or 30 Sparklines, if you wanted
to change the formatting, so for instance you can choose a Sparkline Style it’d be
very tedious to have to go through and do that to 20 or 30 separate Sparklines.
So if I try to select a Sparkline like Andy’s Sparkline when I select it I automatically
get selected the other Sparklines in its group.
And by default the group is the group that was created when it was inserted.
It is possible to ungroup Sparklines.
And when you’ve got one or more Sparklines selected you have the Sparkline Tools Design
tab and at the right hand end there is a Group group.
So you can ungroup Sparklines.
So I could ungroup Andy’s Sparklines there.
That removes Andy from the group that he was in.
Let me just put him back in again.
If I want to ungroup all of the members of the group, select the whole group, click on
Ungroup, and now each of those is a separate Sparkline and I can format each separately.
So for instance, I could choose a completely different style for that individual Sparkline.
Let’s look at one or two other features on the Design tab.
There is a Show group.
And in the Show group you can select certain entities to show on your Sparkline.
So for instance, you can check markers.
And on the Line Style of Sparklines you can see those little markers.
You can also click, for example, a high point to show the high point on each of those Sparklines.
If instead of the lines I selected the Column Sparklines down here and selected high point
what happens is that the highest columns get a different color.
They’re that sort of deep red color.
Now let me switch the high points off here and concentrate for a moment on the team B
scores.
What I want to do is to go back to the Group group and click on Axis.
And just explain that at the moment the heights of those columns are set for each individual
member of the team.
So for instance, Scott MacDonald’s are scaled according to Scott’s overall performance.
Laura Steele’s according to her overall performance and so on.
Those column heights are not in some way related to the performance of the whole group.
Now if you look at the dropdown on the Axis button that I pressed there you notice that
in the middle section Vertical Axis Minimum Value Options Automatic for Each Sparkline.
And then in the lower group Vertical Axis Maximum Value Options Automatic for Each Sparkline.
That’s what setting the Sparkline so that each minimum or maximum block size is according
to the individual member of the team.
If I wanted to set them to be the same for all Sparklines those Sparklines would also
indicate to me not only how each individual team member’s performance has changed over
the year but how they compare to each other.
So let me change both of those to Same for All Sparklines.
Now what we can see, for example, is that Laura Owen’s blocks generally speaking are
much higher than say Eric Parkinson’s are.
And Laura Steele towards the bottom there, particularly in the earlier parts of the year,
her performance was significantly worse than say Laura Owen’s.
So with this we can now visualize now only the performance over the year but the relative
performance within the members of the team.
And just a couple of final points on Sparklines.
One of them is on the left of the Sparkline Tools Design tab you have an Edit Data button.
That brings up the Edit Sparklines dialog so that you could change the data range or
the location range for the Sparklines.
You also have at the bottom half of that button options to edit group location and data or
a single Sparkline data.
And at the right hand end in the Group group you’ve got a clear option there.
Clear selected Sparklines or Clear selected Sparkline Groups.
And one final point here is that a Sparkline can refer to data in a column.
The examples here of course refer to data in rows but data in columns is fine with Sparklines
as well.
That’s it on Sparklines.
I’ll see you in the next section.