Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
The solution for a practice problem relating to retails sales data.
Example Excel Spreadsheet
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Remember with charts, sometimes it comes
down to style and personal preference.
0:00
In this specific problem, I think the
chart you choose to visualize your data,
0:06
depends primarily on the story
you're trying to tell.
0:11
Is your primary goal to show the relative
size of the sales of the different items?
0:15
Or is it to show the components of
total sales, across the retail network?
0:20
Let's start with relative size and
make a line chart.
0:26
So I've created a separate sheet
0:30
that pulls all the different item sales
locations, from the location info tab.
0:33
So here you can see the formula,
referencing LocationInfo!B8.
0:39
The value is 2475, lets just go
over here to the LocationInfo tab,
0:43
and you can see right there,
in cell B8 2,475.
0:49
So we've done that for all the different
items, for all the different months.
0:55
So let's make a line chart.
1:00
I'm going to use my keyboard and
hold down Shift + CTRL.
1:02
Press the down arrow, and the down arrow
again, to select that entire column.
1:07
And then you press,
while I'm still holding shift and control,
1:12
right arrow once and right arrow again.
1:15
And now I've selected all
the data I wanna chart.
1:17
Using another keyboard shortcut ALT+N,
and then N for the for the line charts.
1:21
I want a 2D line chart,
so Enter and there we go.
1:27
I wanna move this chart
to a different sheet.
1:33
Since there are 12 data
series on this chart,
1:40
it's not a bad idea to use line markers.
1:43
So let's just remind ourselves
of how we can do that.
1:45
Format Data Series.
1:51
And I'm going to change the lines here.
1:55
So we added the line markers, and I knew
that it was gonna give us the same markers
2:00
for all the different data series,
and we didn't want that.
2:04
So let's go over here and
format the marker.
2:07
And make it a triangle, and
let's go ahead and make it size ten.
2:13
So you'd wanna do that for
more data series,
2:19
to more clearly distinguish
the different series.
2:22
But we don't need to do
that together right now.
2:25
What you can see from this, very clearly,
The Mike the Frog shirt from location one,
2:29
was consistently one of
the top sellers every month.
2:34
And down here, you see all the pens,
which are consistently low sellers.
2:37
If the goal is to show the component
parts of total sales though,
2:42
then the line chart is no good.
2:46
You'd go for a stacked area chart.
2:48
So let's change this chart
to a stacked area chart.
2:51
So here, we can see all the different
items stacked on top of each other,
3:01
and it shows the total sales for
the entire network.
3:06
Now, some people would just leave it here.
3:11
And that might be okay.
3:15
However, you could take it one step
further by changing some of the color
3:17
formatting.
3:21
Let's do that together right now,
and make all the items for
3:23
store one, different shades of green.
3:26
So here we go.
3:30
I want to format the data series.
3:33
I'm gonna change the fill to that nice
green color that looks so beautiful.
3:38
And I'm gonna make another green.
3:45
Let's do more fill, and
there kind of a more neony-looking green.
3:48
And then one more time.
3:57
How about this really dark green.
3:59
Now, we can see here, there's pen one and
4:02
you can just barely see the grey
right there, for pen one.
4:05
But I can't select it with my mouse.
4:09
So there's a little trick you can do,
which is,
4:12
you can select the data series
before it then press tab.
4:15
Tab again, and
it'll go back to the sticker one.
4:20
But you can tab through different
elements or items, On a chart.
4:23
So yeah, selected that item,
I'm gonna right-click it, and let's do,
4:34
That right there.
4:43
Okay, so
all the items from store one are green.
4:46
Let's make all the items for
store two, red.
4:51
Okay, so here we go.
4:57
How about this red, right there.
5:00
These look very similar to me, so I'm
going to go in here and choose this one.
5:05
I guess that's almost
like the magenta color.
5:14
My art teachers wouldn't be
very happy with me right now.
5:18
Maybe it's Acadian.
5:21
[LAUGH] Okay, anyway, so
different colors of red.
5:23
And, Again, I can't select the pen easily,
5:27
so I'm gonna go back here and
I'm just gonna tab to it.
5:32
And choose one last red fill shade.
5:37
How about this one right here?
5:41
And then for store three, let's make
them all different shades of blue.
5:44
So there's one, Two,
5:50
it looks like the pen for
store three is already blue.
5:54
So that saves us some time.
5:59
And finally, the sticker.
6:01
So just by changing around
some more of the formatting,
6:05
we're communicating more to the viewer.
6:08
They can distinguish
the different contributions from
6:10
all the different stores.
6:14
I want to call one thing
to attention here, though.
6:16
And that is that the formatting you choose
is associated with the series number.
6:20
So what does that mean?
6:26
Well, let's do an example.
6:27
Let's say we wanted to sort the chart, so
6:31
that the largest items were at the bottom
and the smallest items were at the top.
6:33
Well, how do we do that?
6:38
First, let's write a function to sum up
all the sales information over the course
6:43
of the year.
6:47
What did I do wrong there?
6:57
Lets find out.
6:59
That's weird, it looks right to me.
7:01
Let's do it again.
7:06
=sum.
7:10
You know what I was doing?
7:16
I was going all the way to
N3 instead of M3 up here,
7:17
which created a circular reference.
7:22
Okay, so we've got the total sales for
each of the different items.
7:30
And now we're gonna sort everything,
based on these values.
7:33
So select all the information,
and data sort.
7:37
No data headers, Column N.
7:47
And let's do Largest to Smallest.
7:54
Cuz remember, the first data series
is on the bottom of the chart,
7:59
and I want that to be the case,
so the largest is at the bottom.
8:04
Okay so our data series, or data set,
8:09
has been reorganized now to be
most sales to smallest sales.
8:13
Now let's go look at our chart.
8:20
Well, it's still all the green down here,
all red, and then all blue at the top.
8:24
But we did that formatting to
signal different locations.
8:29
So just remember,
the formatting you choose is gonna stick
8:34
with the data series in the order,
not the item.
8:38
So if you reorganize the data,
8:41
it's not necessarily gonna stay
associated like that on the chart.
8:44
Just something to be aware of.
8:49
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up