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
A solution for the first practice problem for Data Visualization in Excel, and the delivery of the second practice problem.
Example Excel Spreadsheet
There are many different ways you could
visualize this data for your boss.
0:00
Some people might create a new
line in their spread sheet and
0:05
sum up the grand total revenue for
the retailer work.
0:08
Let's do that together, I'm gonna
scroll down below and do a Grand total.
0:12
And then, here in cell B75,
I'm going to enter equals,
0:20
take the revenue for
the third location, and
0:27
add in the revenue for
the second location.
0:32
And then, finally the revenue for
the first location.
0:36
So we see here that total
revenue in January was $7,005.
0:47
Let's carry this out through December.
0:52
What I'm gonna do is I'm gonna go
up one row, I'm gonna hit Control,
0:56
go over to December here, and add a 1.
0:59
Then, I'm going to hit Control and
go back, Control Copy.
1:03
And then, using the keyboard shortcut,
holding down Control + Shift, and
1:10
then I'm gonna press the right arrow.
1:13
I've selected all those cells
I wanna copy this formula to.
1:17
I wanna press Command + V,
and there we we go.
1:19
So we wanna graph this, so
I'm gonna again select all these cells and
1:24
using the keyboard shortcut Alt + N.
1:30
And let's do 2-D column chart,
And there we are.
1:33
So this is indeed a chart
showing total sales.
1:43
Now, I wanna make sure that it's
crystal clear to our boss that the x
1:48
axis labels are the months.
1:53
So instead of having numbers, I'm gonna
go ahead and get those labels in there.
1:55
And find our chart again, here we are.
2:03
So that's one way to do it,
this could be a line
2:11
chart, Or it could be an area chart.
2:15
I think it'd actually be more
useful if we showed a stack area
2:23
chart that includes the total sales for
each location.
2:28
So let's go ahead and do that now.
2:32
What I'm gonna do is I'm
gonna make a new sheet.
2:35
I'm gonna call this Location 1,
Location 2, and Location 3.
2:38
>> Then I wanna go back over here
To our locationInfo Sheet and
2:46
make references to this spreadsheet,
2:53
And finally Location 3 is right there.
3:01
And then I'm going to copy these
formulas and carry them all the way out.
3:07
So, Okay,
3:17
and all those hashes right
there are actually numbers.
3:26
It's just they're too big
to display in the cells.
3:31
So what we need to do is we need to,
I'm going to select all these columns.
3:35
And I'm gonna double click up top
to the right to open them up.
3:41
And let me get rid of
these decimal places.
3:46
Now I'm going to create
a stacked area chart.
3:52
Alt+N and again and
here's the stacked area chart.
3:57
And you can see that you're answering
the question about total sales for
4:04
the retail network because the stack
total shows you what those numbers are.
4:08
But you're communicating some more
information by showing the breakout of
4:13
the contributions from
each different location.
4:17
Some of you might be wondering,
well why not break it down even further?
4:20
Why not show each individual
item that we sold?
4:23
Well, check out the next video.
4:27
You need to sign up for Treehouse in order to download course files.
Sign up