I have 2 columns in an Excel spreadsheet, A and B. Column A will have a number in it, that is either plus or minus, like so:
Column A:
7
-18
34
2
-1
-22
14
And I want column B to track the “running total” of the numbers in column A, ie:
7
-11
16
18
17
-5
9
I’d like column B to automatically update when I enter a new number in to column A, but Excel doesn’t seem to be smart enough to realise the pattern running down column B:
=(A1)
=(B1 + A2)
=(B2 + A3)
=(B3 + A4)
I have to tediously keep entering this formula in to column B, incrementing the numbers by 1 each time I go. Is there an easier way?
Go to the cell that has =(B1 + A2) in it, and select that cell. Then press CTRL C (copy), and then;
Select all the cells beneath that one, and press CTRL V (paste). That will fill all the selected cells with the correct formula, automatically updating each to reflect that it is in a lower row.
BTW - Excel has a great help function. I use it a lot, and it usually tells me what is needed. If that fails, this Board is the next step.
If you copy and paste a cell in column B to the one below it, it will automatically update the referenced cells by 1.
Ot extend the cell in Column B to the one below by clicking on the lower right corner and dragging it down. This is quicker than Copy-Paste. Anyone know what this drag-copy feature of Excel is called?
On preview I see I have mostly been beaten to the punch.
I get different numbers than you using your formulas. My Column B numbers are:
7
-11
23
25
24
2
16
Anyway, the formula updates itself just fine simply copying the formula at the bottom down.
If you want it to do it automatically as you go along get a few cells started manually. Then highlight (click+drag) the two columns you have done so far and “Format as Table”.
Once done anytime you add a number to the bottom of column “A” it’ll automatically fill in column “B”.
You shouln’t have to keep re-typing the formula – if you drag down the formula in bold, it should automatically change.
I thought Excel worked that way by default. However, there is a tab in the Tool / Options window that you might want to check your settings on. Go to Tool / Options, and look at the Edit tab. I’ve got everything checked except “Fixed Decimal” and “Provide Feedback with Animation”. If you have anything else unchecked, you may not be able to drag formulas around the way you were trying to.
Anyway, to add to what others have said, click on the second cell in Column B. See the tiny box on the outline of the cell on the bottom right corner? Hover your mouse over it until the big white cross of a cursor turns into a thin black cross of a cursor. Now click and drag that down to the bottom of your column. Voila!
Yes, sorry, my “sample” numbers from column B were wrong. I created this example to highlight the problem I’m having, and I ran those numbers in my head. Apologies if this confused my question.
If you want to get a little more fancy and not deal with the table I mentioned above try this:
The problem with copying your formula down the whole column of “B” is you get a column filled with useless numbers till something is input next to the “B” cell in column “A”.
Use this formula instead in cell “B2”:
=IF(A2="","",(B1+A2))
Basically that says: “IF A2 is blank THEN fill in with nothing ELSE do B1+A2”
As soon as you input something into A the cell next to it in B notes it is NOT empty and does the calculation.
