By Michael G. Barford, Keeper of the Eternal Chime
I’ve been working on a character keeper for Offworlders to demonstrate using Google Sheets for online character keepers. In the Advanced post, we ended up with something like this: https://goo.gl/fhNiZW. If you haven’t been following along, you may want to start at the Introduction post.
As you can see, this sheet is looking pretty barren and drab. Grayness abounds. Nothing is visually distinct. Functionally, all the mechanisms are in place for this car to drive, but it’s time we slapped on a fresh coat of paint and some upholstery.
Initially, I’m mostly concerned with how we can leverage formatting to aid in the use of this character keeper. You can see how I’ve already taken advantage of borders and background fill to organize information and emphasize headings. One glaring thing that this sheet is missing is color. Color is an easy way to visually distinguish our character sheet columns from one another.
The easiest thing to do is color your heading cells a different color for each sheet. Using the “light 3” or “light 2” colors provided in the Fill color tool works well enough. The nice thing about coloring each heading: if you scroll down away from your character name, you can still remember which column belongs to them based on the colors. You can also use colored borders to achieve the same goal with a bit more subtlety. Here’s what my sheet looked like when I added some colors to the headings:
I’ve been working on a character keeper for Offworlders to demonstrate using Google Sheets for online character keepers. In the Advanced post, we ended up with something like this: https://goo.gl/fhNiZW. If you haven’t been following along, you may want to start at the Introduction post.
As you can see, this sheet is looking pretty barren and drab. Grayness abounds. Nothing is visually distinct. Functionally, all the mechanisms are in place for this car to drive, but it’s time we slapped on a fresh coat of paint and some upholstery.
Initially, I’m mostly concerned with how we can leverage formatting to aid in the use of this character keeper. You can see how I’ve already taken advantage of borders and background fill to organize information and emphasize headings. One glaring thing that this sheet is missing is color. Color is an easy way to visually distinguish our character sheet columns from one another.
The easiest thing to do is color your heading cells a different color for each sheet. Using the “light 3” or “light 2” colors provided in the Fill color tool works well enough. The nice thing about coloring each heading: if you scroll down away from your character name, you can still remember which column belongs to them based on the colors. You can also use colored borders to achieve the same goal with a bit more subtlety. Here’s what my sheet looked like when I added some colors to the headings:
Next, we can look to formatting the text of different cells. Bolding your headings or increasing their font size helps them to stand out amidst a sea of character info as landmarks to navigate to when you’re looking for something in particular. To make these changes across your sheet quickly, you can highlight whole rows by clicking their number on the left, and adjust the format from there. You may also choose to change the alignment of your headings. I prefer to keep things left-aligned in general, but in my Attributes grid I will center those elements:
When you’ve updated your sheet with color, you can then use gray as a tool for a different purpose than emphasis. I’m going to color my Ability description cells with a light gray to communicate that these cells do not need input from the player, and to break up the lines of the list a bit (quick tip: holding the Ctrl/Fn key while clicking allows you to highlight multiple individual cells at once):
A couple of additional changes I’ve made here are worth noticing. First, I italicized the Ability names to highlight them. This creates a hierarchy of headings: the Ability category is bolded, the names of each Ability are italicized, and the descriptions use plain text. If I had bolded the Ability names, they would have blended in with the other heading categories and weakened that structure. Italics allow me to emphasize the Ability names from the descriptions without undermining that hierarchy. Additionally, I have formatted these cells to be top-aligned and to have wrapped text. I recommend formatting almost all of your cells this way, so that all of the information can be clearly seen while keeping the same structure for each sheet. To highlight all of the cells in your sheet, you can click the empty square in the top left where the axes of your rows and columns meet.
In the last article, I mentioned that the checkboxes we added to the Advancements section can be used for conditional formatting. When I click the checkbox next to an Advancement option, I want both of those cells to turn gray and to strikethrough the description text, in addition to having the box checked. To do this, I go to the taskbar and click Format>Conditional formatting. A new rule set-up will pop-up on the side of the screen. I’m going to select the ranges for each of these character sheets individually, so my first section will be B33:E38. For the Formatting style, I will set the background to “gray” and apply strikethrough to the text. Under rules, I will choose Custom formula, and the formula I will use is: =$B33=TRUE
In this formula, the $ symbol in front of the column reference will apply the rules to all columns within that row. Likewise a $ in front of the row reference would apply the rules to all of the rows within that column, and both would have it affect the whole range. When a box is checked, the value is set to “TRUE” - this formula will read that and apply the formatting rules to those cells. These rules are automatically applied for each row in the range this way, so you can select the whole Advancement section to apply them and it will work as intended. It would be nice to cover multiple ranges with the same rule, but unfortunately that $ symbol would apply the rules across each range. That is, if Character 1 checks a box, that will grey out the same Advancement option for all of the other characters’. Therefore we will need to select each character’s Advancement section range individually and adjust the formula accordingly.
In this formula, the $ symbol in front of the column reference will apply the rules to all columns within that row. Likewise a $ in front of the row reference would apply the rules to all of the rows within that column, and both would have it affect the whole range. When a box is checked, the value is set to “TRUE” - this formula will read that and apply the formatting rules to those cells. These rules are automatically applied for each row in the range this way, so you can select the whole Advancement section to apply them and it will work as intended. It would be nice to cover multiple ranges with the same rule, but unfortunately that $ symbol would apply the rules across each range. That is, if Character 1 checks a box, that will grey out the same Advancement option for all of the other characters’. Therefore we will need to select each character’s Advancement section range individually and adjust the formula accordingly.
We can use a similar technique to override the formatting in the Abilities section if a relevant box is not checked. Way back in the Basics post I left room for five Abilities for each character, even though they only start with two. We can use conditional formatting to gray-out those extra Abilities until they are unlocked with an Advancement. Now, since these rules will only affect one column at a time, we can be a little bit more liberal with the range selection. However, one checkbox will be affecting two rows, so we’ll still need multiple rules to cover each ability for each character. It will end up looking something like this:
Notice how multiple ranges are separated by commas: B20:E21, G20:J21, etc. The custom formula is referencing the first “Get a new class ability” checkbox but automatically applies it as intended for each section. We do put a $ symbol before the row reference so that both the Ability name and description get grayed out. This formula looks to see if the box is unchecked (aka displays FALSE) to apply the rules, so until the checkbox is marked, the cells will be grayed out. You’ll have to duplicate these rules for the other two extra Ability slots; make sure to update the formula to reference the relevant checkboxes. To speed this process up with each new rule, try using the four-square range selection icon and Add another range to select the ranges with your cursor instead of typing them out individually.
I want to try using conditional formatting for one more thing. I also left room for a third Skill on each character sheet, even though most characters will only have two. That’s because the Geek has an ability called Polymath that grants them an extra Skill. I’ll make a new rule, select each of those Skill slots, and use this custom formula: =COUNTIF(B16:B25,"Polymath")=0
This formula looks in the selected range for the number of instances of the word “Polymath”. If there are zero instances of it (like if that character hasn’t acquired the Polymath ability), this formula will be TRUE, and the rules will be applied. I’ll set the formatting style to gray, and presto!
I want to try using conditional formatting for one more thing. I also left room for a third Skill on each character sheet, even though most characters will only have two. That’s because the Geek has an ability called Polymath that grants them an extra Skill. I’ll make a new rule, select each of those Skill slots, and use this custom formula: =COUNTIF(B16:B25,"Polymath")=0
This formula looks in the selected range for the number of instances of the word “Polymath”. If there are zero instances of it (like if that character hasn’t acquired the Polymath ability), this formula will be TRUE, and the rules will be applied. I’ll set the formatting style to gray, and presto!
With the style I’ve used for the sheet so far, I think I’ll make the margins a dark color to make the sheets stand apart even more. I went with “dark gray 3” instead of full black:
The last bit of functional formatting I’ll do is mostly a nitpick: I like to delete the extraneous ~900 rows that sit at the bottom of a sheet. It doesn’t really do anything practical but adds a bit of finality to the sheet. You can always add more later if you need them.
There’s a couple more features I’d like to draw your attention to. The first is the Freeze rows option. You can freeze a number of top rows on your sheet by clicking View>Freeze on the taskbar. Try it out for yourself and see if you like it! You can unfreeze those rows without messing with their content. Next is the Hide feature. We talked about how you can hide whole sheets, like your Data sheet, to keep them tucked away but still functional. You can do the same thing with individual rows and columns. This is helpful if you want to hide character sheets. For instance, you have a PC who will not be present for a session in the middle of your campaign/series, or you only have three PCs to start but may need more later. Just highlight those columns, right-click and choose hide. There will be little black arrows near the column headings you can click to reveal them again. That’s another benefit to the IMAGE function: inserting images directly doesn’t allow you to hide them within that space.
And with that, I will call this keeper complete! It accomplishes everything I’ve set out to do:
But the fun doesn’t stop there! We’ve really only begun to scratch the surface of what you can do with online character keepers. This sheet is perfectly functional and not-hideous, but it’s also kind of generic. You don’t have to be a professional graphic designer to put your own visual spin on your keeper. Try using a custom color palette or matching the theme of your design to the game. I’ll show you a couple of quick examples.
I’d like to share a few more awesome examples of character keepers I’ve come across in the Gauntlet. These keepers have been designed from the ground up by their games’ designers. If you want to play around with them and take a peek under the hood, just click File>Make a copy.
Look how @puizlaulo uses multiple columns and merged cells to create grids to his liking for the layout of these keepers. Also check out his use of images and symbols to communicate meaning, aesthetic, and theme:
Be prepared to be blown away by the level of detail in @pawper’s keepers. His Mass Effect sheet is nothing short of a masterpiece, but even the simpler sheet for Colony Farout has some clever techniques: see how he created an embossed effect with colored top and bottom borders? Genius!
@nahualrpg does some smart things with this (beta) keeper for Nahual: https://goo.gl/E8RqTa
There’s some automation with data validation, VLOOKUP, checkboxes, and conditional formatting. Notably, each playbook has been given their own column in the keeper, instead of just each character. This allows room for those moves that don’t fit the mold, like the Perro’s Pack, while still maintaining a consistent parallel appearance to each section. If you want to make a more exhaustive automated sheet for a game with more unique playbooks like Masks or The Veil, this might be a good route to take. Also check out the sheet for the Changarro as a good example of how to use Sheets to track other aspects of your game.
Check out the hidden Data tab on Dylan’s World of Dungeons keeper to see how he made the character creation instructions work. Super thoughtful idea and a very clever execution: https://goo.gl/4tMXkX
I’d also like to draw your attention to this game info page that @tgurantz made: https://goo.gl/z6JTmP
Did you know that if you right-click on the sheet name at the bottom you can copy it whole-cloth to another spreadsheet by clicking Copy to...? If you find this info page to be useful, it’s super easy to add it to your existing keepers.
Do you have any more questions about online character keepers? Are you struggling with a particular bit of a keeper you’re working on? Are you proud of one you’ve designed or want to share one that you think is particularly cool? Let me know in the comments below or hit me up on Twitter @michaelgbarford!
There’s a couple more features I’d like to draw your attention to. The first is the Freeze rows option. You can freeze a number of top rows on your sheet by clicking View>Freeze on the taskbar. Try it out for yourself and see if you like it! You can unfreeze those rows without messing with their content. Next is the Hide feature. We talked about how you can hide whole sheets, like your Data sheet, to keep them tucked away but still functional. You can do the same thing with individual rows and columns. This is helpful if you want to hide character sheets. For instance, you have a PC who will not be present for a session in the middle of your campaign/series, or you only have three PCs to start but may need more later. Just highlight those columns, right-click and choose hide. There will be little black arrows near the column headings you can click to reveal them again. That’s another benefit to the IMAGE function: inserting images directly doesn’t allow you to hide them within that space.
And with that, I will call this keeper complete! It accomplishes everything I’ve set out to do:
- It collects all the information that the players and GM would want to see in one screen.
- It streamlines the character creation process by automating choices and input.
- It uses formatting to organize this information in an effective manner.
- It leverages the technological capabilities of Google Sheets.
But the fun doesn’t stop there! We’ve really only begun to scratch the surface of what you can do with online character keepers. This sheet is perfectly functional and not-hideous, but it’s also kind of generic. You don’t have to be a professional graphic designer to put your own visual spin on your keeper. Try using a custom color palette or matching the theme of your design to the game. I’ll show you a couple of quick examples.
- Here’s a variation on this sheet inspired by Material Design principles: https://goo.gl/WKU3TK
- And here’s one made to look like a retro spaceship terminal: https://goo.gl/BWFpqy
I’d like to share a few more awesome examples of character keepers I’ve come across in the Gauntlet. These keepers have been designed from the ground up by their games’ designers. If you want to play around with them and take a peek under the hood, just click File>Make a copy.
Look how @puizlaulo uses multiple columns and merged cells to create grids to his liking for the layout of these keepers. Also check out his use of images and symbols to communicate meaning, aesthetic, and theme:
- Digimon RPG: https://goo.gl/EY4qic
- Melody of a Never-Ending Summer: https://goo.gl/vRGtFQ
- Hope and Despair: https://goo.gl/shFLxi
Be prepared to be blown away by the level of detail in @pawper’s keepers. His Mass Effect sheet is nothing short of a masterpiece, but even the simpler sheet for Colony Farout has some clever techniques: see how he created an embossed effect with colored top and bottom borders? Genius!
- Mass Effect SR-X: https://goo.gl/Pbvjbp
- Colony Farout: https://goo.gl/oJQVSm
- Alone to the Wilds: https://goo.gl/DGXo9K
@nahualrpg does some smart things with this (beta) keeper for Nahual: https://goo.gl/E8RqTa
There’s some automation with data validation, VLOOKUP, checkboxes, and conditional formatting. Notably, each playbook has been given their own column in the keeper, instead of just each character. This allows room for those moves that don’t fit the mold, like the Perro’s Pack, while still maintaining a consistent parallel appearance to each section. If you want to make a more exhaustive automated sheet for a game with more unique playbooks like Masks or The Veil, this might be a good route to take. Also check out the sheet for the Changarro as a good example of how to use Sheets to track other aspects of your game.
Check out the hidden Data tab on Dylan’s World of Dungeons keeper to see how he made the character creation instructions work. Super thoughtful idea and a very clever execution: https://goo.gl/4tMXkX
I’d also like to draw your attention to this game info page that @tgurantz made: https://goo.gl/z6JTmP
Did you know that if you right-click on the sheet name at the bottom you can copy it whole-cloth to another spreadsheet by clicking Copy to...? If you find this info page to be useful, it’s super easy to add it to your existing keepers.
Do you have any more questions about online character keepers? Are you struggling with a particular bit of a keeper you’re working on? Are you proud of one you’ve designed or want to share one that you think is particularly cool? Let me know in the comments below or hit me up on Twitter @michaelgbarford!
0 Comments
RSS Feed