Wardrobe Spreadsheet Analysis: Simple Metrics

Last time we talked about starting to do some simple analysis using tools built in to Google Sheets (and Excel). Now that we're familiar with our friend the pivot table, we can do more. We're not going to go over everything you could possibly do with your wardrobe and a pivot table, instead we're going to go over a few specific things as a jumping off point to get our feet wet.

Things we'll do in this post:

• Sums of each brand
• charts
• Most expensive main piece
• most expensive JSK, OP, Skirt
• Breakdown of types for each brand

Let's get started

Total spent per brand

This is pretty similar to the "Average price per brand" that we did in the last post. What we're going to do now is set it up and add a chart to it so we can visualize the sums.

Here is my result:

Here is my pivot table editor:

• Add one row for "Brand"
• Add one value for "Price", and summarize by SUM
• Add a filter to filter out anything you don't currently own

That's it!

Let's make the chart

• Start by going to the top ribbon.
• Insert->Chart
• Make sure your cursor is focused on the pivot table, the chart will automatically grab that data

Cool it gave us a chart! It automatically selected a pie chart and grabbed the data from the pivot table. Here's what it looks like right off the bat

Notice that "Grand Total" is included? Let's get rid of that

• Look at the Chart Editor on the right hand side
• Look at the Data Range field, and change it to exclude your Grand Total

Much better!

This shows me on which brands I've spent the most, least, etc. If we want to see the data differently, we can try a different chart. Let's do a column chart:

• Go to the Chart type dropdown on the Chart Editor
• Select "Column Chart"
• Everything happens automatically!

Neat

Most Expensive Main Piece by Type

Let's look at the most expensive main piece of each type:

Here's how to get that in the pivot table editor:

• Add a row for "Type"
• Add a value for "Price", summarize by MAX
• Add a filter to filter out stuff that I've sold

Do we want a chart? Why not...

 This chart also required me to adjust my data range to exclude "Grand Total", but there you have it!

Types of main piece for each brand

I wanted to see what kinds of main pieces I buy from each brand. Do I favor one brand for its skirts? It's OPs?

Here is the result from my wardrobe:

And here is the editor with what I did to get this table

• Add two rows. The first row should be "Brand", then add a second row for "Type"
• Add a value for "Type"
• summarize by COUNTA, this means "count anything", which we need since the "Type" column in our sheet is text and not numeric
• Finally, filter out anything that you've sold

And there you go!

Those are three things to do with a pivot table and your wardrobe spreadsheet. Message me on Instagram or leave a comment if there is any other analysis you'd like a tutorial for!