How can I propagate changes to formulas in Excel automatically?

November 29, 2012 10:53 AM

How can I propagate changes to formulas in Excel automatically?

In my job there are lots of Excel spreadsheets that I use to analyze various data sets. Often all of the thought goes into just making a single row that analyzes a single transaction; I then just copy everything downwards into a bunch more rows to repeat the calculation.

The problem with this is that then I realize I wanted to do a different calculation. So I update the first row with the new formula, and forget to copy it downwards, and the results are wrong.

The obvious answer, I suppose, is don't use Excel. Some of the analysis I'm doing I do using R or Python, and in those settings what I'd do is just change a single line of code that happens to be embedded in a loop. But I like using Excel for "rapid prototyping" of analyses, when I have no idea what I'm trying to do and so it's helpful to see the numbers changing in front of my eyes.

Is there some way to tell Excel "if I change the formulas in the first row, change all the other rows accordingly?" I can't be the only one who has this problem.

posted by madcaptenor to computers & internet (8 answers total)

You click the little black arrow in the bottom-right of the cell you changed, drag it across every row, and then double click its new position after releasing to copy the formula down into every cell. Excel will make the adjustments automatically
posted by MangyCarface at 10:58 AM on November 29, 2012

You would have to write a macro (VBA code) to do that...and I'd have to let someone else give you the specifics on the code. Or you might be able to google it because it does seem like a basic If/then/else function. But otherwise, the quickest way is just to drag down and update your other cells.
posted by Eicats at 10:59 AM on November 29, 2012

Select the cells you want to update, type the new formula, hit Ctrl-Enter.
posted by kindall at 11:00 AM on November 29, 2012 [3 favorites]

MangyCarface: point-and-click won't really work here, these files often contain thousands of rows.

Eicats: I was hoping to not go down the VBA rabbit hole, but if I have to, I'll do it.

kindall: I tried out your solution. It seems to work! (The dragging issue isn't a problem - Ctrl-Shift-Down Arrow is my friend here.)
posted by madcaptenor at 11:06 AM on November 29, 2012

Whoops, I meant columns. Just double click the black arrow if it's a single column you're concerned about
posted by MangyCarface at 11:11 AM on November 29, 2012 [1 favorite]

Hey, that works! Thanks.
posted by madcaptenor at 11:12 AM on November 29, 2012

Warning about double clicking - if you have blank cells in the column or even maybe in adjacent columns (assuming you're doing everything "vertically"), it could stop prematurely. So make sure to check where the paste ends and that it's on the last row, every time you do it.
posted by Yowser at 11:19 AM on November 29, 2012

Format the data in a table (Excel 2007+), and the column will automatically update with the formula. Also, if you're doing anything complex, you really should be using tables already: especially because of named columns.
posted by ambrosen at 11:57 AM on November 29, 2012 [4 favorites]