Inserting rows or columns in incremental formulas

Some time back I set up an accounting spreadsheet for my local neighborhood association, and one of the things it did was keep a running balance by adding the credits and subtracting the debits for the current row to the balance on the previous row. Last week the current treasurer contacted me saying that the spreadsheet was no longer working, and asked if I could figure out what was wrong. What happened was a very common problem that crops up when you use incremental formulas, but the fix is also very easy.

First, look at the table below.

Here’s an example of incremental formulas and their results. Going Down Going Up
Formula Result Formula Result
a b c d
1 1 1 =a2+1 5
2 =a1+1 2 =a3+1 4
3 =a2+1 3 =a4+1 3
4 =a3+1 4 =a5+1 2
5 =a4+1 5 1 1

Now, the most common problem happens when you decide to insert a row (or column, if the formula refers to previous columns).

Here’s what everything looks like once you’ve inserted a row. Going Down Going Up
Formula Result Formula Result
a b c d
1 1 1 =a2+1 5
2 =a1+1 2 =a3+1 4
3 =a2+1 3 =a5+1 3
4
5 =a3+1 4 =a6+1 2
6 =a5+1 5 1 1

If you’ll look, you may notice that there are now two rows that need their formulas corrected. Obviously you need to fill in the blank row, but the formula in the row immediately above/below (depending on which direction you’re incrementing) is also messed up. Let’s assume for the moment that you fill in the blank row correctly, but don’t touch the other one. Here’s what would happen.

Here’s the table with the blank row filled in correctly. Going Down Going Up
Formula Result Formula Result
a b c d
1 1 1 =a2+1 5
2 =a1+1 2 =a3+1 4
3 =a2+1 3 =a5+1 3
4 =a3+1 4 =a5+1 3
5 =a3+1 4 =a6+1 2
6 =a5+1 5 1 1

See how you’ve got duplicated values and formulas?  Row 4 is correct, but the formula in a5 is still referring to a3, not a4.  Likewise, c3 refers to c5, not c4.  You may have inserted a new row at row 4, but the formulas are ignoring it so it’s not going to get added in.

Conga line, from Microsoft clip artIt’s kind of like a line of conga dancers.  Each person has their hand on the shoulder of the person in front of them.  Inserting a row is like having a new person duck into the middle of the line. 

Here’s what has to happen for the conga line  (and the spreadsheet) to keep working.

Conga Spreadsheets
Person X enters the line between person A and person B. A row gets inserted
Person X puts their hand on person A’s shoulder. The blank row gets the correct formulas.
Person B moves their hand from person A’s shoulder to person X’s. The other affected row gets the correct formulas.

In other words, when you copy the correct formulas into the blank row, you also copy them into next row, which was also affected.

Now, the other common problem is that you copy the incorrect formulas into the blank row, and the row beyond.

Here’s the table with the blank row and next filled in INcorrectly. Going Down Going Up
Formula Result Formula Result
a b c d
1 1 1 =a2+1 4
2 =a1+1 2 =a3+1 3
3 =a1+1 2 =a5+1 2
4 =a2+1 3 =a6+1 2
5 =a3+1 3 =a7+1 1
6 =a5+1 4 1 1

Essentially what happened there is that the new conga dancer (X) looked at how the person behind them (B) was reaching over X’s shoulder and holding on to the shoulder of the person in front of them (A), and decided to imitate that by reaching over A’s shoulder and grabbing the next one in line. That’s not too likely to spontaneously occur in conga since there’s physical bodies involved, but a spreadsheet has no such built-in guidance.

So how do you know which formula to copy, and which to overwrite?  Well, you can logic it through, knowing that if the formula refers to the cell above then the row above is safe, and the row below needs to be overwritten, but if the formula refers to the cell below then the row below is safe, and it’s the row above that needs to be overwritten.  But sometimes incremental formulas can get complicated and refer to things in both directions, and besides which, any time you have to stop and think about something there’s an increased chance you’ll make a mistake.    Fortunately, there’s a MUCH easier way.

If the row was inserted anywhere other than right below the first row of formulas, just copy the first row and paste it over all the rows.  If it was inserted right after the first row, then just copy the last row instead.  Even if you’ve messed it up earlier, all you have to do is copy a safe row (first or last) and paste it over everything!  It’s probably the easiest fix I ever have to do.