Welcome to the fabled third part of Documentation Tips of Game Designers! The first part was all about theory, the second part was 50% theory and 50% practical tips on Google Docs, this time we are going full practice with Google Sheets.
To be clear, it’s impossible for me to write everything about Spreadsheets right here - this is going to be an introduction that will give the readers some tools that should be enough to kickstart the process of learning how to use them.
About the differences between Google Sheets and Microsoft Excel: they’re mostly the same, Google Sheets has less features than Excel, but it has Plugins to make up for it. As always, I’ll be explaining Google Sheets since it’s going to be what most of the readers will be able to use, but everything can still be applied in any Spreadsheet editor.
A small love letter to spreadsheets
Spreadsheets are feared by many, and rightly so I may add. Creating a Spreadsheet is not easy, and you need to write some code to use them properly, but they are well worth the investment.
Remember how I explained that Documents don’t bring forth the development of your game, but they are an investment to be faster during production? Well, Spreadsheets are an amazing investment.
You can use them for tuning of your game’s Progression, Economy and Stats, to analyze your competitors and gather data from there, to make sure that you are on budget or organize your team’s work. They are a giant and automatic table, and you know how good tables are (if you read my second article, *wink wink*).
The basics of Google Sheets
Let’s start with the basics of the basics. Before even starting your Spreadsheet you need to organize your ideas into rows and columns. Let’s make a small dictionary: elements are the single pieces you are analyzing in your document, like competitor games, your game’s enemies or the character stats; data are the specific stats that each element has, so for example: a competitor game (element) might have these data: sell count, positive reviews counts, metacritic, steam tags, cost and more, if needed.
Ideally, you want to organize elements in rows and their data in columns, and you want to keep the columns on a single screen window, since it’s easier to scroll down than it is to move left and right - keep in mind that Google Sheets doesn’t adapt to screen resolution, so different screen will show a different amount of columns.
Some quick tips to help your document look better:
- You can change your columns and rows sizes, and by clicking on multiple rows / columns you can change all of their sizes to match each other
- You should use the first row of your document as an header and have it in different colors - in Google Sheets, you can also automatically add alternating colors + header by going to Format > Alternating colors
- You can make rows or columns sticky by moving the small gray bar on the top left of the document, so that they will move while scrolling (image below)
- You can open new tabs on the same Spreadsheet by clicking on the small “+” sign at the bottom left of the screen, and you’ll be able to reference the data in that tab as well
- You can add Rows and Columns by right clicking on a Row / Column and going “Add 1 above/left”, but be warned: this will make a completely empty row / column, possibly invalidating some of your formulas.
- You can merge cells, which makes the Document easier to read, but remember that formulas will be confused by this, so make sure you only use it in documents that don’t have many formulas or charts.
Now, let’s explore some more commonly used and powerful features for your Design Documents:
How it works
Highlight the cells you want to add Conditional Formatting to (typically a whole column), right click and select “Conditional Formatting” at the bottom of the menu.
You can decide what formatting (i.e. fill color, font style) the cells in the range will have based on a criteria you choose. By clicking on “Color scale” you can give cells a gradient based on their value.
Custom formula is a particularly good criteria since it allows you to script the criteria yourself; just be warned that there is no autocompile, so you might want to test the formula in a cell and then copy paste it. Also, syntax is a bit weird, so you might need to troubleshoot.
When to use it
You need to see what’s the biggest element in a list? Use a gradient conditional formatting, this way you’ll be able to quickly find what you need.
You want to check if you have a duplicate anywhere in a column? Use a conditional formatting with “custom formula” using this formula right here: =countif(A:A,A1)>1.
Do you have a Traffic Light document with tasks that can be Done, WiP or Cut? Add conditional formatting that automatically colors the cells containing those words in Green, Yellow and Red - even better if you use Data Validation!
How it works
Just like Conditional Formatting, right click on the selected range and click on “Data Validation” at the bottom of the menu. This creates a drop down menu on the cells, containing the data you want to. There are many criteria, but the main ones are:
- List of items, where you choose what data the user can select
- Items from a range, where the data is taken by the selected cell range
- Checkbox, which creates a clickable checkbox in the cell
When to use it
Remember the Traffic Light I talked about with Conditional Formatting? What if Done, WiP and Cut are words chosen from a Data Validation menu? This way, anyone using the document will use the right syntax and the Conditional Formatting will work as intended.
Do you want to have a dynamically changing Data validation that doesn’t require to edit the whole column each time? By selecting “Items from a range” you’ll be able to add the items directly on the document itself, maybe even importing it from different places.
You want to check which and how many games you are analyzing are on Steam? Add a “is on Steam” Column and put a checkbox in there. Pro tip: with the custom formula “=TRUE” in Conditional Formatting, true checkboxes will change color!
Formulas are what make Spreadsheets so amazing. They are definitely not quantum physics, but they do require a bit of a mathematical mindset to properly use them.
To create a Formula you’ll have to type “=” inside any cell, at which point the cell is going to automatically expect a Formula. The “=” sign is basically saying to that cell that it should be containing “this data”, in which “this data” can be the result of various mathematical operations, the result of Google Sheets built-in Functions or even just the value of a different cell.
To quickly show what I mean and get everyone accustomed to this: write something in the cell A1; then, write in the cell B1 “=A1” - the same word should appear in both the cells. Now, by changing the text in A1, B1 will change as well. Let’s move it up an inch: write a number in A1, and a number in B1; now, write in C1 the formula “=A1+B1” - the value in C1 is the sum of those two values.
Tip: by dragging a formula, it will auto compile itself in the other cells - what this means is that if you drag down the previously made formula by selecting the cells A1, B1 and C1, the formula will automatically update (and the numbers should increase). By adding a $ sign in front of the letter, number or both in the formulas, you can prevent this from happening (like "=$A$1+$B$1")!
Here’s a quick rundown of some useful Functions in Google Sheets:
- IF: if statements are a foundation of any scripting language - they basically tell the cell “check if something I told you is true, then write this thing if it is or write this other thing if it’s false”.
- SUM: this allows you to quickly sum the numbers in a cell range
- CONCAT: you use this to concatenate two different “things” in the same cell, for example writing the words contained in two separate cells into one. It’s especially useful if you want to have a word and a numeric value obtained by a formula inside the same cell: to do so, use the formula in the image below.
- SUMPRODUCT: this allows you to sum the numbers of a cell range that have been multiplied by the numbers of another cell range
- MAX / MIN: these give you the maximum or minimum values inside a cell range
- MATCH: this function will search for a value in a specific range of cells and tell you where it is; be careful, because if it doesn’t find it, the result will be the biggest number in the range - by adding a “0” as the last argument of the function, you are going to prevent this, but the function will result in an error instead. To solve this problem, the IFERROR statement will give some help
- IFERROR: basically, what this function does is telling what to show if the cell has an error in it; this is incredibly useful in conjunction with the MATCH function, since you can use them to check whether or not something is in the range with the following formula: =IF(IFERROR(MATCH(A2,B2:B,0),0)>0,1,0)
- COUNTIF: this will count the how many times the specified IF statement is true inside a range, useful to count specific words, values etc.
Final boss: Arrays
I should mention Arrays, since they can be of great help in creating documents that are easy to use. What they do is essentially collect inside a single cell the information that needs to be written down a range of cells. This is incredibly useful for formulas that might break when adding rows or columns to the spreadsheet - the Array will automatically fill and fix the values inside of them. They are a bit hard, so I won’t be explaining them here, but it is definitely something to study for anyone interested.
Secret boss: Queries
And I must mention Queries. QUERY is the most powerful function in Google Sheets: it allows you to use a Structured Query Language to script behaviours in your spreadsheets. This is hard programming, so using them is a big investment in both time and work, but it will give any document an edge. To follow the trend of this article, they are mostly useful in… well, anything really.
Charts: their own thing
To briefly touch charts, they are their own thing really. There is a field of study completely dedicated to how to display data, so trying to cover it here would be useless - it is a topic that really fascinates me though, so one day I might make an article solely dedicated to Charts! You can add them by clicking on Insert>Chart. If you select a range of cells first, Google Sheets will make a chart with those, if not it will try to figure it out by itself.
Well, this was a ride. When I started this series I wasn’t sure it would work out so well, and I didn’t even think someone would find it useful, but I’m glad I was wrong. Like last time, I highly suggest everyone to add me on LinkedIn to ask questions and chat!
Meeting new people was my goal all along, so I look forward to talking with anyone!
Thank you all for your time!