Solution: Total Sales4:30 with Michael Watson
A solution for the first practice problem for Data Visualization in Excel, and the delivery of the second practice problem.
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