Dragging is a Drag in Spreadsheets (not on RuPaul’s Drag Race)

I’ve done it. You’ve done it. We’ve all done it. We all do it.

We copy & paste, or we drag down, our formulas in spreadsheets so that the formulas can calculate for all of our rows of data.

And we’ve all been brought down in some way when new data comes into our spreadsheet and we realize that we forgot to drag our formulas down to the new rows.

But this is quickly becoming a thing of the past in Spreadsheets. Yes, Excel and Google Sheets do in fact come out with new formulas and features on a semi-regular basis, and one of the biggest shake-ups of the last few months and years is the introduction of functions that can work over ARRAYS.

An array is basically just a collection of values. Cell A1 in a spreadsheet is a single cell - Cells A1 through A10 is an array. You need your formulas to calculate for your entire array - and that array is often growing as new data comes in.

Knowing how to copy or drag your formulas down is a great first step. Knowing that you can avoid doing that is even better.

Spreadsheets now offer a dizzying number of ways to write a single formula and have it calculate for all of the rows in your data - even when new ones come in, without you having to do anything at all to ensure your new data will get the formulas as well.

The simplest of these is the ArrayFormula(). Type it into Cell A1 and it can calculate all the way down - as far as you want it to - without additional effort on your part.

I’ve got two videos for you that are both introductions to ArrayFormula() and examples of how to use it.

Goodbye, uncalculated rows! Hello, Array Formulas!