How to track your game collection for free
Since starting this blog, I have been more actively building up my collection of video games. It has been great fun to do overall but something I am continually questioning is the best way to manage my game collection.
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 if you wanted to try this out for yourself. Included at the bottom of this post is a template that I created in Google Sheets but has been exported to .xlsx format. Feel free to download it and follow along with this post to see how I created it.
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
There are a lot of collection tracker sheets available online that you could simply take from, job done. I wanted to take inspiration from lots of different sheets to make my own one that I would enjoy using.
With this in mind I jotted down my requirements:
- 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!
- I wanted to include drop downs where possible for example when denoting box condition, disc condition etc. to make life easier when adding new entries
- I wanted to track my progress on games e.g Completed, beaten, backlog etc.
- I wanted everything to look nice! Very important criteria..
This proved very useful to note down ahead of actually implementing anything as it made me realize that everything I had wanted to do could be achievable using Google Sheets.
Start building your sheet
Feel free to take a copy of the template at the end of this post and follow along with it as you go to see how I do it!
The structure 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.
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.
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.
Next, I began 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 such, you should use =SUM() for number data only.
The slightly more trickier one to sort out is being able to could 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.
The formula can be seen in the screenshot below for reference.
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.
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 “=
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!
Oh and don’t forget to download the template that I created by clicking the Download Template button below!