The Christian Rationalist

At a retreat I attended last month, one of the speakers illustrated his point that people have different skills by explaining that some people have a fascination with spreadsheet manipulation. The way he said it, he sounded baffled, but when I heard it, I knew he was describing people like me.

My preferred system is Google Sheets for its easy collaboration and anywhere-access. Last spring, in “Like Magic”: Five Google Spreadsheet Hacks to Save You Time and Money, I shared some of its features that I’ve learned about over the years which expand the scope and power of what you can do with spreadsheets. In this post, I’d like to address the complimentary question of efficiency: How do you build and write spreadsheet functionality quickly? After all, we’re using spreadsheets in the first place in part in order to speed things up relative to doing them by hand. But if designing the spreadsheet takes a long time, we’re at best cutting into those future time savings and at worst losing time in the whole endeavor.

So here are some tips for speeding up your own spreadsheet capabilities.

1) Fill Down and Fill Right (Ctrl-D, Ctrl-R)

This was one of the earliest shortcuts I learned. Let’s say you’re collecting column sums for some reason, and you’ve already written your formula in cell A1: =SUM(A2:A). You’d like to the same sort of thing in cells B1, C1, and D1 as well. Rather than copying A1 and pasting it in B1:D1, you can save a step by highlighting A1:D1 and filling right (Ctrl-R). The corresponding vertical command is filling down (Ctrl-D).

Just like with copy-paste, the formulas will naturally adjust based on their position — B1 will now be SUM(B2:B) — unless they’ve been preceded by a $. Beyond saving the step on copy + paste, filling down and right lets you keep something else in your clipboard for future pasting. Where possible, keeping corresponding formulas in corresponding positions is also generally good spreadsheet design practice. They’re simple, but I’ve probably saved the most time with Fill Down and Fill Right taken collectively.

2) Use multiple tabs with small spreadsheets in each

We’ve all seen, and some of us have built, those gigantic patchwork spreadsheets with different regions dedicated to different tables of data or calculations. Sometimes the regions expand or contract, and you have to shuffle things around to get them all to fit again. Maybe you write a formula based on one table, like SUM(A6:A23), but then later on, that table now goes to row 24, so your sum now doesn’t include everything. Then you’re left with a pernicious error that you likely won’t even notice without inspecting the formula directly or manually adding up those cells and noticing that the value displayed is not their sum.

I used to make spreadsheets like that; I think I got the habit from years of using Excel. Nowadays I try to give every rectangular table of data its own sheet (which I’ll call “tabs” here to not get them confused with the entire spreadsheet). Need a two-column lookup table? Don’t use columns A and B in your main tab; just make another tab with only those two columns.

Why? Well, formatting is one concern: The more tables that share a column or row, the more likely their sizes are to be mismatched. It’s also much easier to avoid the pernicious errors I mentioned by making your sum go to the end of the row or column with formulas like SUM(A2:A). On that note, I’d also recommend just deleting any unused rows or columns. Not only will this look cleaner, it’ll help you not accidentally forget that a function is scanning an entire column of mostly blank rows.

3) “Paste Values Only” (Ctrl-Shift-V)

This is perhaps the shortcut I’ve found uses for more frequently elsewhere, but it’s often enough the case that I want to copy some values but not the formulas that produced those values. Maybe I want to capture the values as they now are, not updating them with any further changes, or maybe I just don’t want them to have the same formatting as the cells I’m taking them from. Both of these are common use cases for “Paste Values Only,” which can be accomplished with “Ctrl-Shift-V.”

This can perhaps be most useful when dealing with randomness. Google Sheets in particular keeps refreshing any random numbers in your cell (RAND, RANDBETWEEN, etc.) every time you make an edit. This can be at the very least annoying, and so if I’m not going to use additional randomness, I might copy the random cells and then just paste their values in the same spot, effectively freezing the randomness. With this solution, there isn’t an immediate way to “unfreeze” the randomness, so if you’ll need more of it, it’s better to paste your copy of the random numbers to a different column, row, or tab, and reference the fixed cell. That way, if you need more randomness, you can always copy the original source again.

4) Find and Replace (Ctrl-H)

Your spreadsheet is pretty mature now; there are plenty of references all over the place, but now you need to change something. In particular, let’s say that you want to change all references to one tab to point to the same spots on another tab. How do you do it?

For this task, find and replace is your best friend. The first thing you need to do is to check that the name of the tab you’re replacing is something unique that isn’t going to show up in your formulas — basically, just make sure it isn’t titled something like “A”. Then go to Find and Replace (Ctrl-H), put the names of your two sheets in, and make sure to check “Also search within formulas”.

5) Begin building your spreadsheet a few cells inwards

When you start making your spreadsheet, you might not quite be aware of how many columns or rows of “header material” you’ll need. It can be tempting to just start at cell A1 and fill in what you know, inserting extra rows above or columns to the left when necessary. But it’ll save you a little bit of time to just start a couple rows and columns in, say, in cell C3 or so, and delete the extra rows once you’ve figured out what format you want.

To be honest, this is more useful when you’re starting out. With more practice, you’ll be better able to foresee what sort of header rows you want, which will make this less important.

6) Copy+Paste+Delete, rather than Cut+Paste

It’s important to keep in mind how Google Sheets handles formula references when you’re moving cells around. If you’re trying to move some data from cells that some formulas reference, try to avoid using Cut and Paste. When you do that, the references will follow the cells to wherever you put them, and usually when you’re moving things you don’t want that to happen.

Fortunately, you can just use the slightly longer Copy-Paste-Delete to accomplish the same goal, without affecting any references. You can also combine this with Paste only values if you don’t want to affect any formatting, either. And if you’re just moving it adjacent and formatting is the same, you can also just use Fill Down / Fill Right + Delete to the same effect.

8) Use JOIN and CONCATENATE to build compatibility with other systems

On occasion, when I want to use a more complicated function to compute something, I’ll code something up in Mathematica or R. Unfortunately, Mathematica doesn’t take copy-pasted spreadsheet data directly, and for a long time I resorted to manually entering lists. More recently, I realized that I could use the spreadsheet functions JOIN and CONCATENATE to transform the data into a format compatible with Mathematica, so now I just have to copy a single cell with all of my data and paste it directly into my Mathematica functions.

This approach also works if you need to draft many copies of a standardized message with slight adjustments. If you don’t already have a compatible messaging software to use for this, you can instead draft the variants directly in your spreadsheet.

9) Become comfortable with storing intermediate values

When I’m coding up some complicated function in a spreadsheet, I’m often tempted to try to write it all into one big function, using a series of IF loops and even copy-pasting individual parts of the code within the cell. For some applications, this is absolutely necessary, but it’s not really using spreadsheets at their finest to pretend like it’s a regular programming language. Instead, it’s often much faster to just store whatever intermediate values you need somewhere else in the same sheet or another sheet. Besides being easier to code up, doing so makes error handling typically much nicer since you can see which step in your computation broke down.

10) Let Google complete your formulas

This is more of a hack than a best practice, but you actually don’t need to fill in the closing quotations or parentheses at the ends of your formulas — Google will close those for you. This is most useful for me when I’m writing a detailed string of IF chains (that doesn’t fit nicely into a SWITCH framework) or if I’m copying text into a CONCATENATE.

What do you wish you could do with Google Sheets but can’t? I’d be happy to take a look at it for you, within reason. If I get enough requests, I might write another blog post about it.