Creating a beauty inventory system is a must for anyone with a large beauty collection OR for anyone looking to reduce their collection or spend less. I’ve been using a comprehensive Beauty Inventory system to track how my collection has grown over the years, in terms of both volume and value.
Please keep reading to learn how to create a Beauty Inventory System.
Step 1: Create Your Beauty Inventory Skeleton
Before you dive into your inventory system think about the metrics and details you want to record. This will help you determine the columns that you add to your Master Product Trackerand what you’re hoping to get out of your system.
If your only goal in creating an inventory system is to keep track of the number of products you own, you may track product name and product count. If you are trying to transition to Cruelty Free, you may want to include a brand’s Cruelty-Free status.
Once you have a vague idea of what you want to track, start a blank Excel Workbook. Personally, I know that I want to track my 2018 collection, my 2019 collection, and how my 2019 collection looks by category. I create the below tabs:
Master Product Tracker
2019 Status Overview
Create tabs that work for you!
Step 2: Create Your Master Product Tab
Accurately setting up the Master Product Tab is essential to creating a helpful Beauty Inventory System. This is the tab that will be updated as items enter and leave your collection.
First, I create my column headers. I like to track as much as I can. Keeping a comprehensive product tracker makes my life as a beauty blogger and Project Panner easier. Here’s what I track:
Master Category (these are the categories listed above)
Category (these are sub-categories… ex. Eyeliner is a sub-category of Eyes)
Color / Finish
Availability Status (ex. Limited Edition, Permanent, Discontinued)
Value (Net Sales Tax)
Acquisition Method (Gifted, Freebie, Purchase)
Status (Current, Decluttered, Panned)
Track details that matter to you on the Master Tab.
By including so much detail in my spreadsheet, this helps me to (1) stay organized and (2) write my blog posts with greater ease. Everything from the price to Cruelty-Free status to the URL are included in this sheet. I don’t need to waste time searching the internet for the same details when I’m blogging.
Once you are satisfied with your headers, it is time to add filters to the top row. Filters make it easy for you to search for certain products, brands, etc. in the master tracker.
To add column filters on a Mac, select the A1 cell then “Command, Shift, Right Arrow.” This will select all your columns on the sheet. On the Excel Ribbon, select the Filter Icon.
Now it is time to move onto data entry.
Step 3: Input Your Data
If you have a large beauty collection, entering the data into your Master Product Tracker tab will be tedious. Be patient. Split this task across multiple days if you have to!
When it comes to data entry, there are two strategies I recommend:
Organize your collection by category end enter all the items in one category at the same time. For example, gather all your lipsticks and enter them at the same time.
Gather your products by brand and enter everything into the spreadsheet by brand. For example, gather all of your Anastasia Beverly Hills products together and enter them at the same time.
Step 4: Use Pivot Tables to Create Your Category Tables
Here’s where things get fun (and potentially confusing). If you’re unfamiliar with a Pivot Table, this is essentially a table automation feature that makes it easy for users to ~pivot~ or toggle ways of organizing data in a table.
For nearly every tab in the inventory system, you will use a pivot table to create the tables you want to see. Here’s how:
On the Master Product Tracker Tab, toggle to the A1 cell again. On a Mac, select “Command, Shift, Down Arrow, Right Arrow) to select all the data on this tab. Note: if you leave any columns or rows empty, you may need to hit either the Down Arrow or the Right Arrow multiple times.
Toggle to the “Data” tab in Excel locate the Pivot Table Icon. Select “Create Manual Pivot.” From here a dialogue box will pop up. This will ask you to confirm the data selected. (Hint: you shouldn’t have to update this when creating your tables). The dialogue box will ask you where you want to create the pivot table. Make sure “Existing Worksheet” is selected and select the tab you want to work on first.
Now you can start to build the pivot table, using the pivot table builder dialogue box. First, filter the table by master category and by status. Personally, I only like to track what is current in my collection on the category breakdown tabs. Sort your build out your table as follows:
After you select your data, this is what your new page will look like
As you start to build out your table, you’ll notice that the data starts to populate automatically! We’ll follow the same steps to create the pivot tables, by category for each tab.
Step 5: Use Pivot Tables to Create Your 2019 Status Overview
I use the status overview tab to track a few specific metrics, including:
Acquisition method – the percent of my collection I purchased myself vs. what was gifted
Collection Volume and Value by category/subcategory and status
Year-Over-Year Change – differences in collection size and value**
To create these tables, you’ll use more pivot tables. Please see the below for what is needed to build out each table.
Note: I track YoY collection differences using a regular formula, not a pivot table.
Collection Volume & Value by Category and Status
YoY Differences in Value & Volume
Step 6: Updating & Maintaining Your Tables
For two years, my original inventory system did not include pivot tables or a Master Tracker Tab. While this system may work for individuals who aren’t familiar with Excel, I found myself having to make a ton of manual updates. The manual updates made me lazy in keeping my spreadsheet up-to-date.
When you add new products to the Master Product Tracker Tab, you must update and refresh the data in your pivot tables. To do this:
First, insert the new product information into your Master Product Tracker. To make your life easier, always insert it somewhere within the data you’re already tracking.
Second, click into one of your pivot tables – it doesn’t matter which one. On the Pivot Table Tab in the Ribbon, select “Refresh” and then “Refresh All”. Voila. Now all the data across all your spreadsheets have been updated to reflect any changes in status or any new additions.
BONUS: Optional Tabs in the Beauty Inventory
Depending on what you want to track, you may want to add additional tabs to your beauty inventory.
For example, I have a large single shadow collection, but I don’t count my single shadows individually in my collection. Instead I track them by the bundle in which they were purchased. However, I still want an easy way to keep track of all my single shadows: their names, textures, and values. I track this on an independent tab in my spreadsheet.
Also, if you’re a visual person, you can easily use your Beauty Inventory Workbook to create charts that make it even easier to visualize.
Why Do Beauty Inventory Systems Help?
Not only are Beauty Inventory Systems helpful from a purely organizational standpoint, but they’re quite eye-opening. Tracking what I own and what I buy has made me more hyper aware of my overconsumption habits and alerted me to the fact that I need to stop acquiring certain color cosmetics products. Also, this made me more aware of the gaps in my collection.
From a blogger standpoint, this inventory system will help you realize how many products are still unused and in the queue to be reviewed. Also, as mentioned above, a system like this makes it easier to actually write your blog posts because it will house details you’d need to write a post.
Even if you aren’t a beauty blogger, an inventory system can be helpful. Rather than following this method to track beauty items, you can use it to track whatever items you collect or tend to frequently buy (books, shoes, clothing, etc.)
Want to Use This Spread Sheet But Think You’ll Have Trouble Setting It Up? Please send me a DM on Twitter and I’ll be happy to send you a blank copy of my spreadsheet. 🙂