0

I am using Excel Table which has formulas. I have added 300+ rows and realized some of the formulas are incorrect.

Whenever I add a row, I have to manually make correction to formula in the new row.

How do I change underlying master row that Excel uses to create new row?

| improve this question | | | | |
  • Can you explain a bit more clearly giving examples of the formula(s) that "become wrong" after you add a row ? – danicotra Jun 25 '15 at 19:02
  • 1
    Are you talking about the Table feature where Excel propagates the formula in a column to new rows? If so, do you want the propagation to stop or do you simply want to choose which formula that is propagated to each new row since it sounds like you have a mixture of formulas in that column? – joehanna Jun 25 '15 at 19:35
3

There is no "master row". Copy a cell containing a "good" formula then paste in all other cells for that column.

| improve this answer | | | | |
0

Does your formula reference a static cell?

In the example below the static cell B1 is referenced in all the cells B4 to B9.

Excel spreadsheet example 1

The formula in B4 is "=A4*$B$1", B5 is "=A5*$B$1", and so on.

The $ prefixes on the row and column indexes tell Excel that, when copying the cell (to another row or column), don't change the row/column values.

Without the $ prefixes (B4 would be "=A4*B1") Excel treats the row/column values as "relative to the current cell". When copying, Excel looks at the formula in the source cell - "multiply the cell on the left by one that is 3 rows above relative to the current cell" - and repeats it in the cell below. So if we copied B4 to B5 the cell three rows above B5 would be B2 and we'd end up with the formula "=A5*B2". In the example, B2 is empty and the result would be zero.

The example below shows how just such a copy could appear.

Excel spreadsheet example 1

With the prefixes the formula becomes "multiply the cell on the left by one that is in position B1 no matter where the current cell is" and copying the formula to B5 (and B6, B7, etc) gives us the desired result.

The prefixes are applied by pressing F4 with the appropriate cell selected in the formula. In the example, when setting the formula in cell B4, press F4 when cell B1 is being added to the formula. Then copy the cell to B5, B6, etc.

Note: Repeated pressing of F4 when the cell is selected cycles through the variations of "fixed column and fixed row", "fixed column only", "fixed row only", "neither").

| improve this answer | | | | |
  • No there is no static cell. – ashishn May 13 '16 at 6:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.