Fun with Closet Inventory: A Stylish Excel Dashboard Tutorial

This Excel inventory dashboard can track any type of inventory. We’re gonna do something fun today and do closet inventory. So let’s go up to insert pivot table. This is how we get started. We wanna first start with category in count. We wanna know how many we have in each type or category of clothing. And so we’ve inserted that. And now let’s go to change our style. Let’s make this like a gray and black type of color. We’re gonna go with a pink, gray and black theme, but you can pick anything you want. Let’s type in a logical title for ours today, it’s closet inventory. And then I’m going to do a merchant center because there is no filtering or anything that would interfere with that. And let’s do bold and change the heading color and the font color. Really make it stand out.

Next, I want to insert shapes. So I’m going to go insert shape and I’m drawing in the rounded rectangle. We’re gonna make it somewhat in the shape of a square and then just resize to how you need. And then we can make this a light pink.

Again, we’re going with a pink, gray, black theme. This can be any color theme you want. And I’m in a control C and control V. This four times so that it copies and pastes it. My sizing estimate was a little bit off, so I need to move these into place so that they are spaced fairly evenly. And it’s pretty easy to do just by clicking on them and dragging.

Now, because this is going to be a dashboard, I’m removing the grid line. So I’m just unchecking that. And then let’s go to insert icons. Now you can type in anything if it’s business related, whatever it is. But for us, again, we’re doing a fun topic today. So let’s just do different styles of folding. So I’m typing each one in and checking it. So it’s kind of like banking these or keeping these set aside. And then once you’re finally done, you can insert all at once. Now before I click off of these, I’m going to drag the little arrow and resize them to a more appropriate smaller size. And then let’s move them into place. So we’ve got our shirt, our top, we’ve got our bottoms, we’ve got our handbag for the accessory, and then we’ve got our shoe to represent shoes. So I’m selecting all of these by pressing shift and clicking on each one because I want to make changes to all of them at the same time. So I’m removing the outline or the border. So now there’s no border. So next, let’s go to insert shape again. And we’re gonna choose a line. And let’s draw a line in there as a divider between the top and bottom. So we’re gonna copy paste this once we’ve got that size that we’re happy with. And one more time, you can control Control V. And that’s looking good.

Now we’ve noticed that the pants are a little bit big, so let’s resize that down further so that these lines are fairly equal going across. Now we need to insert a shape yet again. So let’s go up to insert shape and grab that rounded rectangle again and draw it into place. Now we wanna link the count over to the left, but we can’t do that right from a pivot table. So I’m gonna do is go equals and I’m gonna click on each number so that the number is actually in a cell. So now that’s done, we can format this by right clicking on it. And then let’s turn it to a light gray. And then if you click on the shape there, you can change this to a bevel. So it looks more like a button, which I think is kind of neat.

And then again, let’s control C, control v, cuz we’re gonna copy and paste those four times and we wanna move those in place so that they’re pretty lined up. Now I can go into that top formula bar and I can link each appropriate cell. So make sure you grab the right one. And let’s hit equals and grab each one of those cells. And here’s the last one. Looking good.

Now again, press shift and click on each one of them to change all of the formats at the same time. So let’s make it bold. Let’s make it a larger font. We really want these to stand out. And we’ll center. Yep, those are centered. So looks good. So I want a divider here. You can leave this. You can do it whatever you prefer, but I’m just gonna fill that black and then make it thinner so it’s like a border of sorts.

And now down here I’m gonna copy and paste that pivot table. And then let’s go to show field list. And let’s change the data here. So now we wanna see the type of clothing by value. So we wanna put our sum of price into that lower right corner. And let’s change the format on this to currency since it’s dollars hit. Okay, and much better.

So let’s add yet another divider and let’s copy and paste the exact same one we just put in. And then we’re gonna highlight this pivot table and we’re gonna copy and paste again.

And now we’re gonna change this to be the seasons. So we want to see season and let’s put season in the values because we want to see the total we have of each type of clothing that’s labeled as I’m cold or warm.

So now on this middle section, I inserted a slicer at the top. And the reason for this is I want to be able to control our chart with this. So I’m choosing the type of clothing and I’m just going to cover right over the pivot table. And let’s change the style of this to more of a gray just to go with our color theme. That’s looking good.

You do need to be able to click on the pivot table to insert a pivot chart. So we’ll just move that real quick. So when we go up to the top and we click on pivot chart, now we can choose any type of chart, but let’s just go with a bar chart. That’ll work for this. And we’ve got some formatting to do here. So if we resize this to fit exactly how we need it, move it in place. Let’s click on the title, delete it, and let’s right click on these little headers and say hide. And then let’s remove the legend. We don’t really need that. And then let’s change the style of this as well. So we’ll go with that dark gray background and then right click and click on the bars so we can change the color. So click on the paint can and let’s change this to like a very bright pink. And then you can go through here and make any changes you want. Let’s change the gap with down so that the bars are a little bit heavier. Alright, this is really coming together.

Now in the bottom portion, let’s say we want to add a gray background. You could leave it white if you like, or if you want to select all of these cells by pressing shift or control, however you want to do it in dragging, you can select all of them at once and you could go with a light gray and then let’s add that order at the bottom. And then if you highlight all of these cells by clicking control shift down arrow, you can hide all of those empty cells. So you’re not seeing all of that.

Now let’s go up to the top and we want to pivot chart again. And let’s insert a 3D pie chart. And again, delete the title. Let’s right click and hide those headers. And let’s size this down so it fits a little bit better. And we’ll move it into place. So we still need to resize a little bit so it fits.

And now let’s change the colors of these. So we’re gonna click on, double click on each of the pie slices here, and then change the fill color. So we’ll just go with shades of pink just to keep with our theme.

And then all you’re doing is clicking on each one and changing the fill color. And then if you click on the backgrounds, you can change those to maybe something like a dark gray. Let’s click on the outside background, change that as well.

And now let’s close out of here. So if your headings in your pivot table don’t make sense, double click on it and you can change the headings here. So let’s change the name here and then let’s resize this. It moved a little bit on us when we resized or when we renamed the header. And now let’s test this out. So let’s go back over to our data tab. And let’s say you suddenly gained a lot of inventory in a particular area. You go back to the dashboard here and click on Data, Refresh all, and your tap buttons are updating perfectly. And then if you go over here and test out your slicer. Perfect.