Working within one’s limitations

I find spreadsheets to be quite powerful and certainly very useful tools, and I use them quite a bit both at work and at home.

Most of the time I work with either Excel or the LibreOffice equivalent (Calc) but sometimes it’s quite handy to use the online (or, these days I suppose you might say “cloud-based”) Google Drive (formerly Google Docs) spreadsheet.

This has some benefits, mainly the fact that it is accessible from any computer with a working internet connection and gives you access to documents without needing to copy them manually from one place to another and have the appropriate software (other than a web browser!)  installed on all machines, but also has some fairly major limitations compared to more traditional and full-featured spreadsheets.  Possibly not the biggest, but one that I run into very often, is the lack of a “fill series” function (or at least one that I can find).

The ability to automatically fill data across a range of columns or rows is a very handy time- and labour-saving device.  Most often, copying the contents of a cell into adjacent cells below or to the right of a given cell is what’s needed and most spreadsheets not only provide commands for doing these but usually also give keyboard shortcuts (Ctrl-D and Ctrl-R respectively), both of which I use on a regular basis when I’m working with spreadsheets.  (Actually, as I recall, the default key binding for LibreOffice these days asigns Ctrl-R to “align right” but I have mine customised to use it for “fill right” instead, as I tend to make more regular use of that command).

There are various other fill commands, including “fill up” and “fill left”, most of which I rarely ever use, but one that is often quite handy, albeit not as often as “fill down” or “fill right”, is “fill series”. This can be used to automatically generate various sequences of numbers.  The ones I use most often are “linear” (used for an arithmetic progression – I usually just use it with an increment of 1 to generate a set of consecutive numbers, e.g. 1, 2, 3, 4, 5… ) and “date” (which, as the name suggests can auto-generate a sequence of dates).

Unfortunately, Google Drive’s spreadsheet seems to lack a “fill series” command.  It does, however have “fill down” and “fill right” (albeit only accessible via keyboard shortcuts – they don’t show up in the Edit menu, where most spreadsheets (or at least LibreOffice Calc and, as I recall, older versions of Excel) seem to keep a submenu of fill commands), as well as cut and paste commands (including several “paste special” options), so it’s not too difficult to come up with a workaround that, while marginally less streamlined than a proper “fill series” command is still far preferable to manually typing a whole series of figures.

The trick is to enter your initial value in one cell, then set up a formula in the next cell (down or across) to generate the next value in the series, use the available fill commands to copy the formula into the rest of the cells you want filled with the series (it will automatically adjust the cell references, so the value for the third cell will be generated from the value in the second cell – the one just calculated from the initial value – and so forth) and finally select the whole range of cells, cut and paste as values (one of the “paste special” options) to ensure that you don’t later get any weird effects from copying or re-ordering cells.

As an example, if you wanted a simple list of numbers 1 to 10 in the first column of a fresh new spreadsheet, you’d start by putting the value “1” (without quotes) in the first cell (A1), then move down to A2 and enter the formula “=A1 + 1”, which would calculate and insert the value “2” into that cell (while retaining the formula iteself in the background.  Next you’d select the range of cells stretching down from A2 to A10 and hit Ctrl-D to fill down.  It would copy the same formula into each cell, adjusting the references as it went (so A3 would contain the formula “=A2 + 1”, etc.) and you’d end up with the numbers 1 to 10 as desired.  If at this point, however, you decided that you wanted the numbers to run down from 10 to 1 and therefore told the spreadsheet to sort the values in reverse order you’d end up with some strange results because it would move the formulas around and get its knickers in a twist (not wishing to get too technical!).  To get round the problem, you should, before attempting to sort the data, select the complete range of cells, cut them (with Ctrl-X) and then choose “paste special” (either from the Edit menu or the right-click context menu) and select “Paste values only” from the submenu.  After this, if you examine the contents of the cells you’ll find (as you might expect) that the formulas have been replaced by plain old numbers and you can now safely sort or move them to your heart’s content.

Of course, if you only want numbers 1 to 10 it’s not that hard to put them in by hand, but if you want to go up to 100 or 1000 (or several million) it would get increasingly tedious, so it’s handy to be able to automate the process.

I suppose to be fair to Google, it’s (presumably) not setting out to be a fully-featured heavyweight spreadsheet and at least it gives you enough tools to be able to recreate the “fill series” functionality with the exercise of a little ingenuity.  However, it does have a “fill range” command (accessed via “Ctrl-Enter”) which I’ve not seen in other spreadsheets; this acts essentially like a combination of “fill down” and “fill right” by copying whatever’s in the top left cell of a selected rectangular range into all the other cells in the range (updating formula references as appropriate).  While this is quite nice, it’s much more obvious and quick to replicate this functionality using Ctrl-D and Ctrl-R, so I can’t help feeling that it would be more useful if the Google spreadsheet could implement “fill series” functionality and assign that to the “Ctrl-Enter” hotkey instead (on a positive note, that idea has enabled me to find a good keyboard mapping for  “fill series” in LibreOffice, which conveniently didn’t seem to be using “Ctrl-Enter” for anything).