[In this reprinted article from Game Developer magazine's 2012 Game Career Guide issue, Ira Fay explains how game designers can use Microsoft Excel for data analysis.]
When people ask me what tools I use as a game designer, they're usually surprised when I tell them how much I use Microsoft Excel (or more recently, Google Docs). Excel can be a powerful data analysis tool for any level of game designer, and it takes some practice to master. Feed playtest results into your spreadsheet and suddenly you have all of the information you could ever want about your game. But it's important to remember that Excel can't work on its own: We need to analyze data thoughtfully. It's also worth remembering that Excel can help with text-based games. Here are some Excel tips and tricks for aspiring game designers with real-life examples taken from games that I've designed.
Microsoft vs. Google
First, let's talk about Microsoft Excel vs. Google Docs spreadsheets. While these two options are generally interchangeable, there are some key differences. Google Docs is free and cross-platform (it runs in your web browser), and it's great if you want multiple people to edit documents simultaneously. Unfortunately, you need to be connected to the Internet to create and save your documents, and it's not easy to track changes. Excel documents can be easily integrated into your version-control system, can be saved and edited without an internet connection, and include advanced features such as countifs() and sumifs(). But Excel isn't free, and multiple people can't work simultaneously on the same document.
In most cases, you can use either one, though it's useful to be aware of the differences so you can choose the tool that's best suited to your needs.
I won't spend time covering the basics of using spreadsheets, which you can learn in a few minutes on your own. I'll let you know how to use these functions: sum(), average(), max(), min(), and len(). Also, you should know how to sort your data ascending and descending and employ freeze rows and columns so you can always see row and column headers while scrolling your document. If any of that feels unfamiliar to you, spend a few minutes playing around or searching the Internet for answers.
Setting up your data
When starting a game project, I make sure to consider what data I'll need to gather. Plan to gather the right data early on and you'll ensure you have the right tools to balance the game later. When I was designing a casual bingo/lottery scratcher card game called Lottso Express (http://pogo.com/games/lottsoexpress), I worked with the game's engineer to output certain metrics into an Excel spreadsheet that was generated every day, filled with data from our beta testers. If you want to play along, you can find the raw Excel files at http://gdmag.com/resources/code.php.
For Lottso Express, I started by looking at the rank points (experience points) a player earned during a game, the game tokens (in-game currency) the player earned, and the jackpot tokens the player earned from a slot machine-like bonus at the end of some games. All these metrics were measured for a single game, which was recorded in the game length column-make sure to note the units! Are we measuring in seconds, minutes, or something else? I asked the engineer, and game length was being measured in seconds, so the entry in D8 (see Figure 1) is actually a single game that took more than five hours (18,965 seconds / 60 seconds in a minute / 60 minutes in an hour =~5.27 hours). Since this is a casual game designed to last 1 minute to 15 minutes, I knew something was wrong. I asked the engineer again, and we were actually measuring time from game start to game over. If players pause the game for a long time, we get very long game lengths, which throw off the metrics we're trying to balance.
Figure 1: Game metrics from a few sessions of Lottso Express
This brings up our first important Excel lesson: Don't blindly trust the data. Data is important, but you must use your powers of analysis and reason to determine the correct course of action. Also, you'll want to make sure you have good communication with the engineering team, since they're the people feeding you the data you need to do your job.