Creating a makeup 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
First, before you dive into your inventory system think about the metrics and details you want to track. This will help you determine the columns that you add to your Master Product Tracker (more details on this below) and what you’re hoping to get out of your system. For example, if your only goal in creating an inventory system is to keep track of what you own from each brand then you may decide to omit details such as monetary value or Cruelty-Free Status.
Once you have a vague idea of what you want to track, it is time to start a blank Excel (or Google Sheets) Workbook. Personally, I know that I want to track my 2018 collection, my 2019 collection, my 2019 collection overview, and how my collection looks by category. Knowing this, I create the below tabs:
- Master Product Tracker
- 2018 Overview
- 2019 Status Overview
- Single Shadows
Step 2: Create Your Master Product Tab
Accurately setting up the Master Product Tab is essential to creating a helpful Beauty Inventory System that will be easy to maintain because this is the tab that will be updated as items enter and leave your collection.
Before I start cataloging data on my collection, I create my column headers. Personally, I like to track as much as I can in this tab because it makes my life as a beauty blogger and Project Panner easier. Also, although certain columns may seem strange at first, they serve an essential purpose to help me sort my data with ease. 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)
- Product Name
- Color / Finish
- Availability Status (ex. Limited Edition, Permanent, Discontinued)
- Cruelty-Free Status
- Value (Net Sales Tax)
- Store Purchased
- Acquire Date
- Open Date
- Exit Date
- Acquisition Method (Gifted, Freebie, Purchase)
- Status (Current, Decluttered, Panned)
As you can probably tell, by including so much detail in my spreadsheet, this helps me to (1) stay organized and (2) write my blog posts with greater ease. Since everything from the price to the Cruelty-Free status and the URL are included in this sheet, I don’t need to waste time searching the internet for the same details over and over again.
Once you are satisfied with your column 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 do this on a Mac, select the A1 cell then “Command, Shift, Right Arrow.” This will select all your columns on the sheet. Then, 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 both tedious and arduous. Be patient with yourself and 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.
How you enter your data is up to you, so chose whatever method makes the most sense and causes the least amount of stress! Personally, I entered items by category (since that is how my collection is physically organized in my room), but then I sorted my spreadsheet (using my filters) alphabetically by brand.
Step 4: Use Pivot Tables to Create Your Category Tables
Here’s where things get fun (and potentially confusing). If you’re unfamiliar with the term, a Pivot Table is essentially a table automation feature that makes it easy for users to ~pivot~ or toggle between various 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:
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 (i.e. what percent of my collection did I purchase my self vs. what was gifted, etc.)
- Collection Volume and Value by category/subcategory and status
- Year-Over-Year differences in collection size and value**
To create these tables (if they’re applicable to your needs), you’ll create 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.
- Acquisition Method
- 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, detailed here, may work for individuals who aren’t familiar with Excel, I found myself having to make a ton of manual updates… so I got 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. (This will make your life easier later on because you won’t need to reselect your Pivot Table data when updating your tables.)
- 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
Again, depending on what you want to track, you may want to add additional tabs. For example, I have a burgeoning single shadow collection… but I don’t track my single shadows individually in my collection… instead I track them by the bundle. However, I still want an easy-to-reach place where I can 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 Collection Workbook to create charts that make it even easier to visualize… I won’t go into this here.
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. 🙂
Do you currently use an inventory-system to track the items you own a lot of (books, shoes, beauty ,etc)? If so, what are your tips for staying organized? Please let me know in the comments below.