Solution: Sales Details8:51 with Michael Watson
The solution for a practice problem relating to retails sales data.
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 up