Since starting this blog, I have been more actively building up my game collection. It has been great fun to do overall but something I am continually questioning is the best way to manage my collection of video games.
To date, the best option I have found in terms of flexibility and price is achieved by using Google Sheets or Excel.
I recently revamped how I manage my game collection via Google Sheets, so I thought it might be interesting to share some tips and tricks with you so you can try this out for yourself.
Be sure to stay tuned to the end of this article where you can download the free video game tracker sheet I’ve created.
Do Some Research Before You Start
After doing some research online and via the Instagram community, the general consensus was that using Microsoft Excel or Google Sheets or similar is the best option.
Spreadsheets such as these allow for the most flexibility and often times provides the cheapest option. All you need is a little knowledge of the software and you can get great results.
I have tried a number of apps in addition to Google Sheets over the last number of months but nothing really seemed to keep me interested enough to maintain things.
That was until I stumbled across some amazing examples on Pinterest. The things some people could achieve using this tool were amazing. With my inspiration acquired, I decided to make a new sheet in Google Sheets and try from scratch.
Figure Out What You Want From A Game Collection Tracker
There are a lot of collection tracker sheets available online that you could simply take from and job done.
This has worked for me on and off over the last few months but I wanted to try something different that was my own creation.
I wanted to take inspiration from lots of different sheets to make my own one that I would enjoy using. This would mean that I would continue to make use of this system meaning my collection would stay organised.
With all of this in mind, I jotted down my requirements for a video game collection tracker:
- A totals tab which would display a count of each console and its games
- I wanted to note certain details about each game e.g. Name, cost, where bought, box condition, manual condition, disc condition etc.
- I also wanted to be able to categorize games in terms of their quality e.g. CIB, Want, Boxed, Re-Boxed etc. With this I can also track games I want to buy so win win!
- To include drop downs where possible for example when denoting box condition, disc condition etc. to make life easier when adding new entries
- To track my progress on games e.g Completed, beaten, backlog etc. This is done in conjunction with my Trello board which you can read more about in my blog post Manage your gaming backlog using Trello
- I wanted everything to look nice! Very important criteria for my design brain.
All of this information proved very useful to note down ahead of actually implementing anything. It made me realize that everything I had wanted to do could be achievable using Google Sheets which further motivated me to go with this approach.
Start Building Your Sheet
Feel free to download a copy of the game collection tracker template at the end of this post and follow along with it as you go to see how I do it!
If you’re following along, I’m assuming you are using Google Sheets or Microsoft Excel or some spreadsheet type software. I highly recommend Google Sheets as it’s free and easy to use.
Anyway, the structure of this tracker sheet is essentially a Totals tab and a separate tab for each console.
Out of safety there is no harm having a template tab as a backup. I also ended up needing another tab for validation content but I will talk about that down further.
I started building out my Totals tab with column headings and styling, I wasn’t worried yet about formulas etc. but I had a fair idea of how this would be structured.
I had a main totals section across the top which included total number of games and then each of the categories I wished to track like complete in box, loose.
Additionally I wanted to track my gaming progress as in how many games were in my backlog, completed etc.
I then broke down into each console grouping i.e. Sony, Microsoft, Nintendo.
Finally under each console grouping I listed each console.
Like I said, I wasn’t worried about formulas etc. right now I just wanted to see where everything would go.
Once I was happy with the Totals tab I began to move onto the Template tab which would serve as a base for all the other console tabs.
Similar to the Totals tab, I simply began entering the headings I wanted and ensuring the styling looked good.
Across the top I would have a running total of games under each category.
Then underneath would contain each of the games and their relevant detail.
Add Test Data
With the structure of the content more or less worked out by this point, I added an entry or two to the template sheet as a test.
With some data to now work with I ensured that I could easily filter and sort this data.
To do this I selected the content, including the header and selected Data > Create a Filter. Now I could sort and filter each column as needed.
Formulas are super powerful tools available inside spreadsheet apps that carry out various mathematical tasks based on the data you select. In the case of this game collection tracker sheet, formulas are the thing that makes this process seem like magic.
As you start adding games to your list, you will see the totals numbers update automatically. This means you don’t have to spend time pulling out your calculator every time you add new games to your list!
Taking the time to understand and implement these formulas at this stage will make your life so much easier down the road. (Plus if you download my free video game tracker sheet, all of the formulas are built right in).
There are 2 main formulas that I use in my tracker sheet that form the majority of the numbered data i.e. the number of games in each category. These formulas are SUM and COUNTIF which we’ll look at below.
My next step in filling in my sheet involved filling in the columns across the top which represented totals of each category.
This was achieved using the =SUM() formula in Sheets which is pretty easy to work with. =SUM() simply adds up the values in the columns specified.
As you start typing this formula into Google Sheets or whatever spreadsheet tool you are using, you will see some autocomplete options appear to assist you with filling in the formula. This should make your life a lot easier when filling in these formulas.
The slightly more trickier task to sort out is being able to count all of the entries for each category. A sum wont work here as were not adding up the data, simply counting each entry.
For this I used =COUNTIF(). This allows you to for instance count the number of rows that are marked as CIB for example.
- The first part of the formula is the area you wish to use i.e. the grade column.
- The second part of the formula refers to the cell you are trying to match against, in this case CIB.
This evaluates then to if any of the cells in the selected area evaluate to CIB, then count them.
Simply repeat this formula across each of the cells in the row to get an accurate count of each category.
Add Drop Down Validation
With this content now in place, I then moved onto the validation for each entry i.e. having drop down select menus for box condition, disc condition etc. This would make life easier when adding new games as selecting from a list is much easier than typing out each entry manually.
In order to do this:
- Create a Validation tab and include the drop down selection options you wish to use. For this example let’s use the quality validation: Good, OK, Bad, Missing, N/A.
- Select the content you wish to validate, let’s use the Disc Condition column as an example.
- With the column selected, select Data > Data Validation.
- From the pop-up window, in the Criteria section, select the grid icon to select the data range (the items we defined in the Validation tab)
- Select the Save button.
- Ensure that the drop down menu is displaying correctly.
Add Color Validation
This step could really be left until last, but for me, I wanted a way of denoting the games that had bad quality boxes or missing manuals.
In order to do this:
- Select the relevant data to color format.
- Select Format > Conditional Formatting
- Now you can specify for example “Text Contains” = “Missing” and then in the case that this criteria is met you can control the formatting of the cell. In my case I made the cell turn orange.
- Select the Done button and admire your work!
Create A Tab And Start Entering Live Content
Once you are happy with your Template tab, you can duplicate it and start entering live data. Once you actually go through the process of adding data, you might notice some things you could change or even things you might be missing.
Don’t be afraid to add what you need! Just be sure your Template tab also contains these changes so all is in sync.
Sync Up The Totals Tab
Once you have a few other tabs with some data in them, it’s time to sync up the Totals tab to reflect all of this. This was definitely my favorite part as I liked seeing the numbers fall into place as I added in the formulas. Very sad I know.
Simply connect each cell to the corresponding one in each tab with “=”. For the totals, use the good old =SUM() formula and voila, you have an awesome video game collection tracker sheet!
Hopefully this was something that proved useful to any of you out there who want to have a better way of tracking your game collection.
If you have any tips or advice for me, please do reach out to me on social media, I would love to hear from you!
Now that you’ve got your collection tracked, it’s time to start managing your growing backlog. To make a start at this, read this post: How to Easily Manage your gaming backlog using Trello
Oh and don’t forget to download the template that I created below!