Wait, I thought you were a game designer, not an accountant!

LOL. I’m not an accountant… but I was a Business major. Getting good at spreadsheets means being able to wrangle the data in your game into a thing you can show someone else, do math to predict how things will turn out, and check for balancing issues before printing up more cards.
Spreadsheets can look really intimidating, especially if you get introduced to some of the more fanciful formulas too early (‘no, literally, I just changed one number and half the spreadsheet broke – GAHHH!). I’m here to tell you that a spreadsheet is the easiest way to track, edit, and balance your game.
(One semi-important note: it doesn’t matter whether you prefer Microsoft Excel, Open Office, Google Sheets or some other spreadsheet solution. These are tools and terms used in all of them. These screenshots were all taken in Google Sheets, which is free, online, backs up automatically, and lets you roll back to a previous version of a file easily. It’s what I do 99% of my spreadsheet stuff in. Start at https://docs.google.com/spreadsheets.)

Terms

Just so we’re all on the same page…

  • Row: stretch west to east. Click the gray box with the number on the left to select the entire row.
  • Column: stretch north to south. Click the gray box with the letter at the top to select the entire column.
  • Cell: the box where row and column meet. It’s often named by the letter for the column and the number for the row (so A1, B5, C4, and so on).
  • Sheet: one ‘page’ of the file. Create different sheets for different types of cards, different versions, etc.
  • Workbook: the generic name for the full file of sheets. ‘Sheet’ and ‘Workbook’ are like ‘Page’ and ‘Book’. In the picture above, the workbook has five sheets along the bottom edge.
  • Function: a distinct calculation a spreadsheet performs. These have names that must be referenced to summon their powers — we’ll get to a few like SUM (adds cells up), COUNTIF (count something if something else is true), and AVERAGE (average specific numbers)
  • Formula: a chance to let the computer do the math for you, based on the content of other cells. A formula always starts with an equals sign (=) to tell the spreadsheet to calculate the result for this cell. A formula can have one or more functions.

One other thing to know is how spreadsheets refer to cells. If you’re just talking about a specific cell, it’s just the letter and the number without a space between them (so A1, B2, and so on). If talking about a range of cells, then you mention two cells, separated by a colon (so A1:C3). Think of these two cells as the upper left and lower right corners of a rectangle.

Finally, I’ve put the names of functions in ALL CAPS so they stand out a bit. That’s not necessary to do, though — spreadsheets recognize them whatever case they’re in.

Basic setup

Everyone’s sheets are going to look different for each game, naturally, but as a basic principle in my spreadsheets:
  • Each row signifies one card or tile
  • Each column signifies one type or thing present on those cards or tile.
  • The top row should be used for the name of the column. It makes it much easier to sort things that way as well.
I’ll have columns for things like:
  • the ID # (an internal tracking number that doesn’t have to be on the card / tile itself)
  • the name of the card
  • the effect or power
  • what type of card it is
  • flavor text
  • any yes/no type questions important to the game
  • any numerical values present in the game (if the thing on the card costs 2 Wood to produce, I’ll have a column for Wood and that card’s entry would be 2)
Believe it or not, this may be all you need to do to keep things organized. If that’s the case, don’t worry about formulas or calculations.
Once the game’s data is all in the spreadsheet, it’s time for the fun to begin.

Real world example time!

Here’s a screenshot of one of my works in progress, Pirate Grannies:
Without even explaining what the game is about, you can probably begin noting the important details:
  • The top row explains what’s in the column — all the better to help sort things.
  • Each row holds info for one card.
  • Each column shows a specific purpose.

There are a few formulas working behind the scenes to ensure things are balanced as I’d like, but this type of organization may be all you need a spreadsheet for.

Moving around

Scrolling around the spreadsheet is obvious enough — what I’m talking about here is to help make light work of the formulas you’ll want to use more than once.
See that little box in the lower-right corner of the cell? If you hover your mouse over that little box, then click and drag it, the spreadsheet will take whatever’s in that cell and copy it into the cells you drag into.
There’s a catch, though — if there’s a number or formula in that cell, the spreadsheet will change the cells referenced according to how it’s dragged. This is a little hard to explain, so play with it and see for yourself.

So, what do you want to do?

See all the cards with similar features grouped together

The sort feature isn’t a function, but it’ll be your best friend once you get to know it.

Let’s say I wanted to see how many times between the Resource and the Where? columns.

One crucial part of the process is super easy to mess up here. You want to select ALL of the columns that make up the data in a row / card. If you don’t, you’ll sort part of the data, but leave other parts fixed in place, and stuff gets jumbled about. Undo is your friend in this case, but it’s a pain in the butt to fix later on after you’ve made changes…

In Sheets, select the columns you want to sort, then click Data > Sort Range:

Check the ‘Data has header row’ to tell the spreadsheet to use that top row instead of ‘Column A’. Don’t be intimidated by that A1 to I1000 — that’s a spreadsheet’s way of saying you chose columns A through I. Again, whatever range you select should all the data for all the cards you want to sort. It’s also fine to copy this into another sheet — sort things one way in one sheet and another way in a second sheet.

Give that ‘Add another sort column’ button a click:

Whatever the first ‘Sort by’ column is, Sheets will continue to the right by default. Setting it up this way will group all the rows with the same resource, then sort by the ‘Where?’…

Bam. Done. I can see I give out Energy at the Cannon and Officer’s quarters twice, and most other places only once.

Total something up

Use the SUM function, and reference the cells or range you want to total up.

For example, =sum(B1:B3) will total up the three numbers in B1, B2, and B3…

Note that in Google Sheets, you should also see the result of your formula just above the equal sign.

You can also add each number and use standard mathematical symbols like +,-,*, or / — for example, =sum(B1+B2+B3)…

This is also the easiest sort of formula to see the magic of spreadsheets — change one of the numbers in column B, and the sum in A2 will automagically update.

I want to count how many of each card I have

Sure, you could count these and then type a number into a cell… but that count goes out of date if you change something.
Let’s look at the screenshot I shared earlier:
Let’s say I wanted to count the number of times I used ‘Energy’ as a resource in column B.
Elsewhere on the sheet, I’d have something like this (it’s a few columns to the right of the screenshot above):
On the left here, I have the five things I’m looking to count. On the right is my goal — how many times I want each resource to be used. This number is just typed in. The ‘count’ column is where the magic happens:
=COUNTIF(B:B,L38) is the formula here. Let’s break this down.
  • =COUNTIF: An equal sign always starts the formula, and ‘COUNTIF’ is the function I want to use. The details that function needs will go inside the following parentheses.
  • (B:B, L38): This function needs two details, separated by a comma: the range of the area we want to count, and what exactly we want to count. In this case, ‘B:B’ is a fancy way of saying ‘all of column B’, while L38 refers to the cell with the thing I want to count. I could say “None” or “Food” or “Energy” instead (be sure to use double quotation marks), but this keeps it more flexible.

A few other fun functions

=AVERAGE does exactly what you think it’ll do. Type it into a cell, then choose the range you want to average.
=AVERAGE.WEIGHTED is another one of those ‘does what you think it’ll do’ sorts of formulas. You’ll select two ranges to make this one work: the values first, then the weights.
=RAND() gives you a random number from 0 to 1 (so for example: 0.2683017912).
If you want to randomly assign a column of abilities you want to randomly assign to cards, create a new column next to it. Type =RAND() in one cell, then drag the formula down as far as needed. Select those two columns, then sort by the random number column. This is a unique sort of formula that will generate new random numbers every time a cell is changed or manipulated.
=ROUND and =ROUNDDOWN both round numbers from other calculations. =ROUND lets you choose how many places you want to round to, while =ROUNDDOWN will always round down. 

There’s plenty more…

Spreadsheets have plenty of power underneath the hood, and you only need a tiny fraction of it to keep things organized. People have made entire games in Excel, done some crazy simulations, scripted stuff in VBA, experimented with PivotTables… After years of game design, I’ve never needed any of those. Play around with the deep end of the pool if you like — if you’ve become comfortable with the handful of functions talked about here, you’re doing great.

Over to you?

—Go on, ask a question =)