Excel and Google Docs Spreadsheet Tips for Game Designers

As a new game designer, I’m finding that there isn’t much information around game design or game balancing. Seems like most of us are starting off with paper & pen, whipping up a quick prototype, and then immediately playtesting.

I started Monster Zoo off the same way. However after a few rounds of playtesting, I realized I need something that I could use to see how the game looked from an overview. Enter Excel/Google Docs.

Here are a few of the tricks I’m using to make my life easier as a game designer.

Card Files

First off I start with a basic spreadsheet of all the cards in the game (card file):

card file spreadsheet

The card file contains everything about the game at the component level

  • All the cards
  • Types of cards
  • Costs
  • How many copies in the deck
  • How many copies in a player’s deck
  • Card strategy

The card file makes it easy for you to make changes to the game and gives you quick sense of how your game looks. But what if you need more detail around specific card types? Maybe you are interested in knowing if a certain card type is out of balance?

For example, in Monster Zoo, there are three types of monsters: Ooglies, Booglies, and Zooglies. How can I tell if there are too many Ooglies? Or if the cost of Ooglies are too high compared to Zooglies?

Pivot Tables

This is where Pivot Tables can help. Pivot Tables provide you with a way to summarize your data, so you can easily understand what is going on. It’s great for getting an idea of how your initial game balance looks.

Let’s get back to answering a few of my questions about Monster Zoo. How can I tell if the three monster types are balanced from a cost perspective?

I can create a Pivot Table with the following settings:

  • Rows: Monster Type
  • Values: Cost (Average)

Monster Types Cost Average

This shows me that all three monster types are relatively balanced – the cost average ranges from 3.9 to 4.2. Pretty close.

Now what if I want to know how the different card strategies are distributed throughout the game? Monster Zoo generally has three basic strategies: card advantage (you draw more cards than other players), point advantage (cards help you gain more points), or resource advantage (you are able to catch more Monsters than other players). In general, I want these all to be relatively balanced, but I’d like to have less card advantage in the game because that tends to have an effect where players keep drawing cards but nothing new is happening.

So let’s take a look.

I create a Pivot Table with these settings:

  • Rows: Monster Type
  • Columns: Card Strategy
  • Values: COUNTA

Monster Type Card Strategy

I can see that the game is more heavily skewed towards resource advantage and points. Which for now is good.

Calculating Draw Probabilities

One of the bigger balancing issues I had with Monster Zoo is trying to figure out the right number of each card type in the central deck. Too many of one type of card and the game didn’t see enough variety. But too little of one type and it possibly could bog down.

This is where the function HYPGEOMDIST can help. HYPGEOMDIST represents the hypergeometric distribution  This function helps you understand the probability of a certain card being drawn from a deck.

For example, I used HYPGEOMDIST to calculate the probabilities of each card type showing up in the first 5 and 10 cards drawn.

card distribution draw

This shows me that it is almost guaranteed to have a Monster card show up on the first turn draw from the central deck. There’s only a 40% chance that a Visitor card will show up on the first turn, but by the time 10 cards are drawn, that chance goes up to 65%.

The function is: H (n) = C (X, n) * C (Y – X, Z – n) / C (Y, Z)

  • C stands for Combination
  • X stands for the number of a certain card that you have in the deck.
  • Y is the number of cards in the deck.
  • Z is the number of cards you are drawing.
  • N is the number you are checking for

Both Google Docs and Excel have a shortcut function called HYPGEOMDIST to make the calculation easier.

Modeling Resource Gain

Another thing spreadsheets are good for are modeling game progression. For example in Monster Zoo as you gain cards and shuffle them back in your deck, you are changing the distribution of resources in your deck. As the game designer, it’s good to understand how the amount of resources are changing because this effects what your players can afford each turn.

Here’s an example model of 20 turns of Monster Zoo:

monster zoo resource model

This shows me that over time, most players should be able to afford more costly Monsters. If I end up modifying the average +Food on a Monster card, you can see how that affects a player’s buying power in later turns.

monster cost

I hope I’ve helped show off some of the interesting game design tasks a spreadsheet can help you with. Looking forward to hearing some of your tricks and tips.

11 thoughts on “Excel and Google Docs Spreadsheet Tips for Game Designers

  1. Paul Owen

    I love the application of combinatorics and numerical analysis to gain insight into your game design dynamics.

    I need to do the same thing with my current work-in-progress, “East India Company.” EIC is my prototype 18th-century mercantile trading game. There are two questions that came out of my latest round of playtesting –
    (1) whether the “dividend track” that allows players to exchange money for bonus points is never cost-effective and
    (2) whether there is a degenerate strategy in always buying spice in China as soon as it becomes available.

    I think I need to hit the spreadsheets hard and figure out if there are a couple of “saddle points” that I need to perturb here.

    Thanks for the inspiration.

  2. Nathanuil

    I like it, a very nice tool for people who are trying to get things done in an efficient manner. The only thing I would possibly add is a short tutorial on making the pivot tables. When working with students I find that they struggle with inputting the data from their larger tables into the functions.

  3. Michael N. Post author

    Cool theme.

    Do you already have the money/spice cost/points mapped out? Maybe graphing them out would give you good sense if something was too out of balance?

  4. Phil @ Cray Cray Games

    This is a great article and I’m thinking of posting a follow-up.
    I use Google spreadsheets to do what you say here and I also use it to export the CSV data for InDesign so that the editing and rendering of the cards themselves is effortless.
    This way you can work, as in our case, with multiple designers and have them tweak the files in GoogleDocs/Drive (your central location) and then export when you’re ready for a printing.

    We even manage the placement of art within the spreadsheets as well.

    Read more about this at http://www.craycraygames.com!

    Love that other get mathy! :-)

  5. Pingback: Excel and Google Docs Spreadsheet Tips for Game Designers #2 | Ruby Cow Games

  6. Pingback: Making games to communicate research findings – Frederick van Amstel

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>