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 continues to evolve over the years, in terms of both volume and value. Please keep reading to learn how to create a comprehensive beauty inventory system.

---

Whether you’re a beauty blogger, YouTuber, Project Panner, or just a major makeup lover, today’s post is for you. By learning how to create a comprehensive beauty inventory system, you’ll be better organized and better equipped to monitor your spending.

Step 1: Create Your Beauty Inventory Skeleton

Before you dive into your inventory system think about the metrics and details you want to record. By taking time to assess what you’d like to get out of your inventory system, you’ll be equipped to determine what details you should track within your Master Product Tracker worksheet.

For example, your beauty inventory will look wildly different than mine if you only want to track the number of products you own within certain categories. Moreover, if you want to track things like Cruelty-Free Status or Vegan Status, your inventory system will naturally need to track more details. Once you have an idea of what you want to track, open a blank Excel or Google Sheets Workbook.

After years of tracking my collection, I know that I want to track my 2018 collection vs my 2019 collection. Additionally, I know that I want to track minute details on my collection – including product details within each beauty category. Therefore I create the below tabs:

• Master Product Tracker
• 2018 Overview
• 2019 Status Overview
• Palettes
• Eyes
• Lips
• Cheek
• Base
• Skin
• Hair
• Single Shadows

Step 2: Create Your Master Product Tracker

Accurately setting up the Master Product Tracker 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. Remember, I like to track as much as I can. Why? Keeping a comprehensive product tracker makes my life as a beauty blogger and Project Panner easier. Here’s what I track:

• Master Category (ex. lips, palettes, base, etc.)
• Category (ex. lip gloss, lipsticks, lip liners, etc.)
• Brand
• Product Name
• Color / Finish
• Availability Status (ex. Limited Edition, Permanent, Discontinued)
• Cruelty-Free Status
• Value (Net Sales Tax)
• Price Paid
• Store Purchased
• Date Acquired
• Date Opened
• Month or Quarter Finished or Decluttered
• Acquisition Method (Gifted, Freebie, Purchase)
• Status (Current, Decluttered, Panned)
• Product URL

As a blogger, including these minute details in my spreadsheet helps me to stay more organized. Additionally, tracking these details makes it easier for me to write posts since I have information about my collection readily available. I don’t need to waste time searching the internet for details I already know.

Next, I add filters to the top row in this spreadsheet. Filters make it easier for me to search for specific products in my collection. To add column filters on a Mac, select the A1 cell. Then select “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:

1. 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.
2. 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.

However you enter your data is up to you. When I put together my very first beauty inventory, I entered items by category. This was easy for me to do given the way that my collection is organized.  From there, 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 a Pivot Table, this is essentially an automation feature that makes it easy for users to ~pivot~ or toggle ways of data tables in Excel.

For nearly every page in the inventory system workbook, you will use a pivot table to create the views you want to see. Here’s how:

1. 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.)
2. 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.
3. 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 – 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**
• Empties

To create these tables, you’ll use more pivot tables. Please see the below for what is needed to build out each table. However, please note that I use a traditional excel formula to track Year-over-Year differences in my collection.

• Acquisition Method
• Collection Volume & Value by Category and Status
• YoY Differences in Value & Volume
• Empties

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. However, I don’t count my single shadows individually in my collection. Instead I track them by the bundle in which they were purchased. Nevertheless, I still want an easy way to keep track of all my single shadows. Therefore I track their names, textures, and values on another tab in my beauty inventory.

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 are quite eye-opening. Tracking what I own and what I buy has made me more hyper aware of my overconsumption habits. Also, it  alerted me to the fact that I need to stop acquiring certain makeup  products. Additionally, this made me more aware of the gaps in my collection.

From a blogger standpoint, this inventory system helps me realize how many products are still unused or in the queue to be reviewed. Also, as mentioned above, a system like this makes it easier to actually write my blog posts.

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.)

Ultimately, there is no right or answer to the question of ‘how to create a comprehensive beauty inventory system?’ At the end of the day, there are many ways to track how we spend our money on beauty products.

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. 🙂

…

Let’s connect on social media: Instagram| Twitter| Pinterest| Bloglovin’ | Influenster

---

Did you enjoy my post on how to create a comprehensive beauty inventory system? 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.

beauty beauty blogger beauty collection beauty inventory cosmetics how to create a beauty inventory how-to makeup makeup minimalism project pan project pan 2019