Find solution
How to update an open Excel in C# using OLEDB?

How can I propagate changes to formulas in Excel automatically?

  • 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.

  • Answer:

    Select the cells you want to update, type the new formula, hit Ctrl-Enter.

madcaptenor at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

Format the data in a table (Excel 2007+), and the column will http://office.microsoft.com/en-gb/excel-help/create-edit-or-remove-a-calculated-column-in-an-excel-table-HA010013766.aspx. Also, if you're doing anything complex, you really should be using tables already: especially because of http://office.microsoft.com/en-gb/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx.

ambrosen

Whoops, I meant columns. Just double click the black arrow if it's a single column you're concerned about

MangyCarface

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

MangyCarface

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.

Eicats

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.)

madcaptenor

Hey, that works! Thanks.

madcaptenor

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.

Yowser

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.