Love managing my beer collection in Excel

Anyone else use Excel to capture the details of their past and present beer collection? Anyone else love doing so? If you're an Excel lover, maybe you share the same sentiment; thought I'd start a thread about it. Please only post here if you actually document your beer collection in some way, as this may not be applicable to many who pick up a fresh 4-6pk and that is all that is in their fridge.

Maybe I'm an organization freak and I feel like I'm a little crazy talking about this, but I have spoken to a few other people who keep their entire collection, past and present, in an Excel file. For me, it has been the best way to manage and sort on a large beer collection. I feel that it best captures all of the attributes and customizations that I really need in keeping track of and listing details of every single beer that I own.

Some of the data or actions that I find particularly useful are:
1) Knowing how many of each beer vintage I have left

2) Sorting and filtering on important attributes, such as:

• Region - to better look-up trade-able beer
• ABV - to decide how much alcohol I'm in the mood for
• Size - do I want a 12, 16, or 22oz today?
  
• Style - to filter on what style I'm in the mood for
  
• Marking potentially trade-able beers vs will only consume or share with friends
• Price - what I paid and how much something is worth (for trade and personal reference)
• Purchase date, bottled date
• Calculated recommended Ready/Drinking date
• Drank/consumed date - so that I know when I drank something for review purposes
• BA, Untappd, and personal ratings! - very important for choosing the right can or bottle
• Estimated calories - for deciding which beers I can handle calorie-wise that day
• Comments, for reference purposes (who gave it to me, where I picked it up, thoughts on the beer, etc.)

3) From all of this data, in another sheet, creating pivot charts and tables or look-ups of how many beers are ready to drink, how much I paid for everything, how much everything is worth, and how many beers I have at any given time (is my collection going up or down)

4) Documenting completed trades for reference purposes

Just to be clear, I don't do this solely for organization purposes; I actually love creating and updating Excel spreadsheets in my job and at home, and especially when I bring home some new beer. I also love looking up a beer in my list to decide on what the "right" beer is for the night.

I'm sure there are more of you, so I suppose this thread could go a few different ways, and could just be a general discussion. Some potential topics include -

• What details do you like to document in you Excel sheet?
• If you don't use Excel, or just BA or Untappd to maintain your collection, what do you use to maintain a large collection?
• Any rants, raves, or questions/tips about putting your beers in Excel spreadsheets

I'm looking forward to the discussion!

 

At one point I had 450 beers... I know... crazy, BUT,

• I was doing many aging experiments across styles (stouts, barleywines, wee-heavies, etc.)
• I made a good amount of homebrew (each 5 gallon batch produces about 60 bottles, and I still had two batches on hand - a hefeweizen and an imperial stout)
• Some I just had to stock up on, given the opportunity (Lagunitas High West-ified, Bottle Logic pickups, Founders IS, my favorite non-BA stout was out of production for a year or more, sales)
• I recently left California, move to Illinois, so I stocked up on some favorites before I left

I moved a beer collection of about 300-350 beers across the country in a shipping container, in my car on the drive, and in a couple suitcases by plane before I moved (and brought cats).

I am down to about 250 beers now, which is still very high and not quite where I'd like it to be, but when you're into aging, 250 when spread across multiple years of consumption is not that many, especially if you enjoy beer multiple times a week, you do shares, and you sometimes session.

 

At some point, I'd like to build some nice, sturdy shelves, similar to those in craft beer stores, to act like I am shopping within my own collection and for presentation purposes. For now, I have everything in apple boxes, cardboard cases, the fridge, and near my desk.

Do you usually drink off of best by date, your own set date, or just whenever you feel like it? Just curious. I set my own date, but I could see best by date being handy in my spreadsheet to definitely drink it by a certain date.

 

Thank you for sharing your columns! That is helpful to me and part of what I was looking forward to in the discussion. I also have an "Availability" type of column where I distinguish between, "can somebody else pick this up right now", "will it ever be brewed again?", etc.

Here are my columns:

Sheet 1 - Summary

• Total # of beers ready to consume (per region (CA vs IL)
• Total # of beers (CA vs IL)
• Total Drank in #
• Total Drank in $
• By Date, how many beers I had at that moment. Example 1/1/2018 - 1 million beers, 7/5/2019 - 400, to give me an idea of whether my collection is waxing or waning

Sheet 2 - My Beers (columns, all data with filters and sorting)

• Location - where does the beer reside? I was living in CA and people were picking up stuff for me in the Midwest for me to pick up later. Now, everything is in the same location, but before, this was important to me)
• Avlb/Region - Limited vs Unlimited availability and then which region or everywhere
• Ready (calculated) - indicator, calculated based on Ready date, just a flag to say, this beer is ready to go into my mouth
• Brewery, Name, Vintage - example - "Goose Island Bourbon County Brand Barleywine 2017"
• # - a count of how many beers regardless of vintage, so if I have 6 Bourbon County Barleywines across different years, they are aggregated here
• BA - Barrel aged? - Y or N
• Trade? - Would I trade it, Y or N
• Get rid of? - Do I care much about this beer anymore? Give away or maybe when non-beer aficionados visit and I don't want to open a $25 bomber for them. More for waning or drinking a second, cheaper beer in the session.
• Oz - How many oz?
• ABV - What is the ABV?
• Price - What did I pay for it?
• Value - What is it worth in a trade?
• Aquired - Date I bought it
• Bottled - The bottled or canned date
• Ready (calculated) - Based on Bottled date + Desired Age, what is the specific date it's ready, sometimes good for determining a particular occasion (birthday, holidays)
• Desired Age - How old do I want it to be when I drink it? 0.5, 1, 2, 5, 10yrs old?
• Current Age (calculated) - How old is it now?
• Remaining (calculated) - How many years left to drink?
• $/oz (calculated) - used to care about this, but no longer - Price divided by oz, helps me find my most expensive beers regardless of size. A $25 12oz costs more than a $30 bomber, imo. One is technically greater than the other, but at half the size, it costs more, imo.
• BA - BeerAdvocate rating
• UT - Untappd rating
• Rating - My rating
• Recommended age - no longer used, but based on research online, what are some good years for the beer or when is it best consumed by?
• Comments - Notes, where I got it from (trade vs somebody picked up for me, which bottle shop, etc.)
• Age at consumption (calculated) - To back-track what year the beer was good to drink at, so, say I liked one that was 2yrs old vs 4yrs old, now I know looking at previously consumed beers, I'll drink any new ones I buy at 2yrs and definitely before 4yrs
• Serving - Temp best served at, to decide between something I may want that is fine to drink at room temp (some I do!)
• Est Calories (calculated) - Estimated calories based on ABV and size, to make sure that I'm not consuming too many calories that day, or maybe I want to share one with friends that is 8 million calories
• AU (calculated) - Alcohol units - how many will I be taking in one sitting?
• Style - not sure why I have this on the end, but I usually know what the beers are anyways. It's more to say, if I'm meeting up with this one friend who likes barleywines more, let's only look at my barleywines to bring over)

Sheet 3 - Potential trades and wishlist

Sheet 4 - Completed trades

Sheet 5 - No longer used, annual releases I looked forward to, now placed in Google Calendar as an actual reminder

Glad we could share! Hopefully this gives you guys some ideas for your spreadsheet or helps you to generate ideas here for all of our spreadsheets

 

I would really love to move to Google Sheets, but I have had multiple issues with it, unfortunately. It's not as intuitive, in my opinion, and hard to type out data with my fingers on the phone vs on my PC and I've had issues copying over my formulas, inserting, filtering, and it lost a lot of the formatting, so for now, I've kept a Google sheet as a snapshot of my collection for when I'm out, although it's always outdated, and then my Excel sheet at home for all of my updates.

Maybe I can find a way to auto-upload every so often or just remember to do it. Or, maybe I just need to try harder and completely move to Google Sheets and accept the differences, just like any new product upgrade.

 

Glad you like the convo! Check out my recent post with all of my columns in case any of them help you, and let me know if you want any tips on calculations

 

I do find some analysis on consumed beers useful, but it has bogged down my spreadsheet, and really, those beers should no longer matter because they are not a part of my collection, so interesting idea there.

I mostly just document ratings to quickly look up, "okay, I'm going to a share, what is above a 4.4 that these guys would enjoy." Then, maybe sort on style, whether it's ready, etc. Helps me super-hone in on "this is the beer" vs looking through 10 boxes and wondering, what the heck am I going to bring, and, is it even at it's prime yet or is it still hot and not fully developed?

Alternatively, "I just want a beer, but nothing special". Maybe filter out anything above a 4.3 and then maybe bottle size to get something that will do the job but not be dipping into something I'd regret waiting on or not sharing.