Clustered columns chart in Excel

[country themed introduction music]

(male narrator)
Hello in this screencast we'll take

a detail look at clustered
columns type of graph.

Let's say you have this data set here
Widgets Inc. and you have a bunch of data

about sales of the various different
regions and across different years

and I also have done subtotals
here by region and by year,

and let's say we want to create a simple
clustered column graph for this data.

First you select the data set, insert a
column I just choose a plain 2-D column,

and that's your simple graph right
there but you can see that it's got some

unnecessary data that probably
does not belong in this graph

for example it has totals by year
and it also has totals by region.

So how do we remove this data?

Okay so let's first, select this graph
just make sure that it's highlighted.

When you do then you will see a
design tab here with all this information

now click on select data.

When you click this button you
will see a pop up box appear here

and what you can do here is if you
see a column here that doesn't belong

for example, totals by year, you
can just select it and click remove.

Now then totals by year goes away and you
just have the North, South, East, and West

represented in your graph and you can
see that your graph is slightly changed

to reflect that.

Supposing you want to
remove totals by region.

What you can do is first switch row and
column and now you have totals by region

as one of the rows and you can
just click remove to take that out

and now you just have the different years

and if you want to can get back to your
original configuration so that you have...

let's say each column be a
year and within each column

the height represents the total sales
and within each year you have

four different sub columns,
each for a different region.

And if you want you can interchange this
so that you have instead of 2009-2010.

You have North, South,
East, and West appear here

so this will be North,
South, East, and West

and instead of the years appearing here
you can have each of these columns

be a year.

To do that again select
the graph, make that active,

click on switch row and column so as soon
as you do it your rows and columns

are interchanged.

So that's a quick and simple way to create
a simple bar chart with clustered columns.

What if you wanted to have
a slightly different style?

Instead of clustered columns maybe you
want to have all the columns stacked up

one on top of another.

It's simple to do that.

Just click on change chart type
and then you will be presented

with all the different options and you can
just select a stacked column, click OK,

and that will result in this
kind of a stacked column.

And now you can actually see
what's the total sales in each year.

So you can see that or in this
case in each zone you can see that

East has the highest sales and
within each zone you have the sales

broken up by the year.

Or you can switch rows and columns and
now you have the total sales by each year

and for each column you have it
broken down by sales by region.

So that's how you can
change the chart type on-the-fly.

Another neat thing you can
do is to create Sparklines.

Ok so let me just
actually delete this for now.

And what you can do is create Sparklines

I will show you what I mean by that.

So let's just create a new column called
trends and let's say you want to create

a trend for each of these columns.

Okay, so one way to do that is to click
on the cell H3 here and go on to insert

and in this Sparklines lines
box here just click on line

and it asks for a data range.

So you can just click on this
data range right here, click OK,

and location range would be this one right
here, and you click OK, and you will see

a line here that represents a
trend for all these four data points.

Now if you are obviously you don't
want to just draw a line, a graph,

for just this one row you want
to do it for the entire region

and to do that what you do is,

let me just edit this here, you can edit
the data and instead of saying data range

is just C3 through F3 I can make
the data range be this entire region

and instead of saying the location is
just this, H3, you can see the location

is all four cells here and you click OK
and now you have four different lines.

Each of these lines represents the
trend of sales in this region right here.

So that's a Sparkline.

You can make the Sparkline line a
little bit more informative by changing

its format a little bit.

So you can go to design, just
select the Sparklines, all of them,

and then click design and you can say
you want to highlight the high point.

If you do that whichever is the highest
point in this graph will be highlighted

for somebody that's looking at it,
it'll be easy to see in which year

the highest sales happen and you can
change the style of your Sparklines

to something more appealing, say
something like this for example.

If you want you can also change, you know,
put a color to the markers if you do this

then you'll have markers for each
of the different points and so on.

So these are all some neat ways
in which you can display data.

So that's all for now I hope you
enjoyed this thanks for watching.

[country themes music]