Microsoft Excel is an excellent way to keep track of everything in your gaming collection. If you’re finding it difficult to understand why you would want to put the time into essentially making a list of everything gaming related that you’ve got, I’m not really sure what I could say that would convince you. For me, I needed a way to keep track of what belonged to me, and what belonged to my brother. I wouldn’t say that there’s a certain number of games that you should have before this is a good use of your time, but if you have more than you can list off the top of your head, you could probably find something about this sort of a project that would be valuable to you.

That being said, I am writing this for the benefit of people who can already see some of the merits of having such a record. I intend to demonstrate some of what can be done, and less on how precisely to do it, because I expect that this is not the best medium to make a highly technical discussion of Excel. I’ve got lots of screenshots, and I’m sure that many that read this will be in awe of my mad Paint skills. If you’re already very familiar with Excel, this article won’t have much for you as I’m intending to start from the beginning.


My games database

To have a good file, you’re going to want to do two things – start by making a database of the stuff you own, and then make a place to read important information/statistics from that database. This separated approach makes the whole thing a lot tidier, and also makes it MUCH easier to automate later on, should you decide you want to.

The first thing that you want to do is dedicate a worksheet (tab) as an exclusive database for your games. By this I mean that you want to organize a worksheet into a series of columns and rows, that start with headers at row number 1, column A. If you start with this, you will be able to have other sheets that read information from this database, so having an organized sheet, with nothing else on it is important.
You need to decide for yourself what aspects of your games are important to you, and dedicate a column in your workbook for each of these things. Keep in mind that the more information you have, the more statistical information you’ll be able to pull out of it. In my workbook I’ve kept track of things for each game I own, such as:

Title, Platform (System), Owner, # of copies, Series, Company (that makes the system), Publisher/Developer of the game, genre, sub-genre, year of release, does the game have instructions/box/inserted extras, what version of the game do I have (Greatest Hits, Collector’s Edition...), # of discs, and did I buy it new or used?

I have also included some binary fields, such as mobility (console/handheld). The reason I have these binary fields, which appear completely obvious, and offer no information to someone who already knows that an NES is a console rather than a handheld, is simply that Excel does not know this. If I want to cross-reference this information later, and find out how many Nintendo handheld games I own, this field is remarkably useful, as I will later demonstrate. Certainly you have to make your own judgment call on which of these fields are useful/meaningful to you.

Some other fields that I would love to have would be what I paid for each game, and where I got each game. Maybe you’d like to include a field to remind you of whether or not you’ve played/completed a game, or some other more descriptive measure of progress. It’s very important that you decide what fields you’re interested in BEFORE you start entering every game you own into the file, so put some thought into it.


Freeze panes to make the title of your games, and your field headers always visible while you scroll.

I want to point out two little technical details that will make entering games much easier for you. First is freezing panes, which you can do by selecting a cell (start with B2), and then clicking “Freeze Panes” in the “View” menu if you’ve got Excel 2007 (it’s in the “Window” menu in 2003).


Put an autofilter on your column headings.

The second thing is to select filter from the “Data” menu once you’ve written down all of your field headings (I think it’s autofilter under the “Data” menu in 2003, not sure). Next to each of your headings you’ll get a little arrow that opens a drop menu when you click it. This will let you filter your database, so that you can find only your NES games quickly if you’d like.

One more little trick I’d like to mention is that it’s very helpful to have an extra column as a Sort field. When you finish putting in all your games, you’re going to want to sort them, perhaps by system, and then by title – it’s up to you, but all very easy to do with the “Sort” button on the “Data” menu. But let’s say that like me, you want to have the games come up in a very particular way, and alphabetical just isn’t enough of a criteria to get it the way you want. What if you want to have it organized by company (Nintendo, Sony, Sega, Microsoft), then by handheld/console, then by age of console, THEN by title? You might be way better at Excel than I am, and able to just do it, but the simplest way is to create a column that when sorted alphabetically, will be in this very particular order.

For example, every NES game I have is coded N1 (Nintendo 1). Every SNES game is N2. PS games are S1, Sega Master System games are SG1, XBOX games are X1. This labelling may have to be adjusted if you go and buy an old system, but it’s not a big deal to change later if you need to, and gives you a little more organizational control. If your first sort field is the “Sort” header, then everything will come up the way you want it.

So now you’re ready to enter every game you own, row by row, into this to make a database. Although this is tedious, I actually really enjoyed going back through all my old games, and I think you might find that it’s a little nostalgic. Some of the information is a little tough to remember (like what year the game was released), and although you can sometimes hunt down this information on a cartridge or instruction manual, I find that it’s easier to just look up online (I use gamefaqs).


Now you need a sheet to read your database, and tell you about it.

Now that you’ve got your database up and ready to go, you’re done with that tab until the next time you buy a game and you need to add it. You probably would like to keep track of how many games you own for each system, perhaps who owns how many for each system, and probably some other stuff. I’m not going to go into too much detail, but you should know that every cell can either hold a value, or a formula, and in this case there are a few exceptional formulae that can really help you out. I strongly recommend that you type each of these formulae into the help search field, to find out what they can do:

Counta()
Countif()
Countifs() (2007 only)
Sum()
Sumif()
Sumifs() (2007 only)
Today()

These give you a lot of options to find the numbers of things in your database, based on certain conditions. I’d like to point out that you can use $ signs in your cell addresses (eg. $A$5), to distinguish between absolute and relative cell references (search that in the help), which will help you drag formulae to copy quickly the same formula into multiple cells, and get the effect that you want. This is particularly useful in capturing changing ranges in for example finding the cumulative number of games that you have at a particular year. Also, the * is the ‘wildcard’ character, so if you wanted to find out how many games you have which have a title starting with the letter 't', you would use something like =COUNTIF(GAMES!A:A,"T*").

With the above information, and some incredibly trivial adding of cells, you could completely recreate the screenshot above, and keep track of how many games you have for each system, for each game company, how many handhelds/console games you have, how many games by year, and by alphabet, and where in your room that game is located. In short, you can know even the most useless of information about your collection.

I’d like to point out that you can also create and maintain databases for your hardware:


Surprise! It’s ANOTHER database!

This will allow you to keep track of how many different consoles/formats you have, maybe how many controllers you have, and so on. I have databases for games, hardware, guides and swag, and Funktastic keeps another for soundtracks. Again, the more information you have to look through, the more (meaningful/meaningless) associations/statistics you can keep track of.


Now I mentioned before that you can do some awesome cross-referencing stuff with Excel, so if you’ve stuck around this long, congratulations, you’re about to learn the most powerful, simplest Excel tool there is...


The pivot table.

Pivot tables are incredibly powerful cross-referencing tools, which only take about 30 seconds to set up once you’ve got a database all ready to go. Go to the tab that has your games database on it, go to the insert menu and select pivot table (both in 2007 and 2003). In both cases, a little wizard pops up, and you select the range of your database (in 2007 it will be selected for you), and then it takes you to a screen like above.

Your fields are on the right side of the screen, and have the same headers that you used in your database (yeah, there was a reason I made such a fuss about all that database organization bullshit). The blue outline on the top left is about to get WAY more awesome, as you drag and drop fields in the right menu onto the outline. Let’s say you want to know who owns how many games of each genre for each system...



Drag and drop Platform, and then Genre (or whatever you called them) into the left side of the pivot table.

Then drag Owner into the top row of the pivot table, and Title into the big, rectangular bottom right part. You can now see a count of how many titles you have, divided into the cross-referenced way that you’ve selected. Yeah, that took about 30 seconds.


Now you've got pivot table.

Still not impressed? A count of titles is nice, but you want to know what those titles are right?


Digging a little deeper...

Let’s say I want to know which RPGs on the DS I own. I find the cell on the pivot table, and double click it.


Behold! The true power of Excel!

Instantly, a new tab is created, and it pulls everything matching the criteria I have selected out of the database (it doesn’t literally remove it, just makes a copy of it). Yeah, you can do that in Excel, and it’s totally awesome.


Pivot tables have autofilters built in.

One more tiny little detail is that each of the fields that you drag has its own autofilter built in. It should also be noted that once you make a pivot table, it uses the data range that you have selected, until you right click on the table and tell it to refresh. This means that every time you add a new record to your database, your pivot table will be unable to find it until you refresh it.


So that’s pretty much everything that you can do in Excel with zero experience. I’m going to continue on to let you know of some other really cool things that are possible, but they involve programming in Visual Basic for Applications (VBA), so you need a little dedication if you’d care to learn it, and this seems like a really inappropriate forum for teaching programming.


My custom userform for adding new games to my database

So what is VBA? It’s just another programming language, and this one is specifically used in Microsoft Office, and more generally within Windows. In the context of Excel, VBA allows you to write macros, which can automate routine tasks. Above is a custom userform that I have made, where I can enter my new games information, and this will be copied into the database. It’s really just a nicer user interface than filling it in cell-by-cell.

The VBA editor can be accessed by ALT+F11, or you can turn on the developer menu, and access it there. You can write macros either to be triggered by a button somewhere in your file, a hotkey combination, or from a userform like above.


Some random VBA code.

You code in an object-oriented language that is very similar to every other object-oriented language out there. I assert that it is EXTREMELY easy to use, even if you have no experience, you can figure out what to do just by reading the excellent help file, and maybe visiting some online help forums. If you can do it with a mouse and keyboard, or you can think of a way that it could be done by hand, there is a way to automate it with VBA.


Success! The userform in action.

As I said before, the result of all of your coding work is that you get to use a nice interface within Excel. You use the code to make everything else easier/simpler, and once you’ve got it programmed, you don’t have to touch the VBA again (unless you think of some cool new feature to add).

Part of what makes it so easy is that you can record macros. Let’s say you’d like to find the VBA code to make a cells font bold, size 10. That’s obviously something very easy to do with the mouse and keyboard, so you can simply start recording a macro, change the font in one cell, stop recording, and then view the code that was generated. Although this code by itself might not be very useful, you can usually generalize it and figure it out.


My series report userform in action

One of the features I’ve made lets me search my databases for all items within a particular series, and generate a report of these items. I can select up to 8 different series, and find out which games, special edition games, guides, and SWAG/promos I have related to these series, copied out into a nice little report.


The fruits of my series report macro

It probably took me 10 hours to write the thing (I am by no means the world’s greatest programmer), but it turned out the way I want it, and it really is a satisfying result. I was able to slightly modify a copy to do the same thing based on the publisher/developer field rather than series, in about a half hour.

So that’s basically everything I’ve got to say about using Excel to keep track of your stuff. I truly hope that if you’ve spent the time to read through this that you’ve found something useful in it. I hope that I’ve conveyed just how easy this is to do, and that it’s really just a matter of finding the time to do it. I find looking at other peoples spreadsheets a confusing affair, but if you feel that looking through mine would help you set up your own, I’d be more than happy to send you a copy, just let me know what version of Excel you’ve got. I get a very sick enjoyment out of discussing the technical details of Excel, and while I feel that I’ve shown great restraint in this article, I would definitely welcome any questions or comments or suggestions if you’d care to PM me about it.

Happy spreadsheeting...