dark        
possumwrangler blog header photo
possumwrangler's c-blog
Posts 0Blogs 3Following 0Followers 10


 
 

LONG BLOG

Keeping Track of Your Games. With Excel!

   0


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...
Login to vote this up!


 
 

  0 COMMENTS

Please login (or) make a quick account (free)
to view and post comments.



 Login with Twitter

 Login with Dtoid

Three day old threads are only visible to verified humans - this helps our small community management team stay on top of spam

Sorry for the extra step!

 

About possumwranglerone of us since 9:08 PM on 02.13.2009

Greetings from Calgary, Alberta, Canada!

I'm new to Destructoid, but I've been playing games for the last twenty years and collecting them for the last five or so. I have a sort of love/hate relationship with xbox - I hate the hardware, but love xbox live. I'm a veteran of many online/offline battles including:

N64
Goldeneye
Perfect Dark (PERFECT File!)

PS2
SOCOM
SOCOM 2

XBOX
Return to Castle Wolfenstein
Counterstrike

X360
CoD2
CoD4

I'm currently pretty engaged in CoD4 (about 50 days played), so if you're interested in some Hardcore S&D, let me know. My favourite games are FPSs, but I've certainly got an open mind, and I collect games from every genre. My favourite series are pretty mainstream - CoD, MGS, GTA, Zelda, Final Fantasy.

Recent Games Played:
CoD4
FFCC: Echoes of Time
Dokapon Kingdom
Mario Power Tennis

I've passively visited Destructoid for the last year or so, and I think it's about time I got more involved, so I'm going to start with an introductory blog post, and be more active by participating in discussions on gaming on this site.
Xbox LIVE:possumwrangLER


 

Around the Community

Featured

Posts

Blogs

Support