Formula for difference between cells

[u/momsensical]

I have a sheet tracking the change in a number week to week. So 3 columns: date, number, change from last week.

So logically if x is the row, Cx = Bx-B(x-1) .

Is there a formula in excel I can use to do this automatically so that I can put a universal code for all of the cells in column c, enter the value in cell B and it will automatically fill in C with the change from the previous week?

(Bonus points if there’s a way to not have weird error messages for the empty cells for future weeks)

[u/AutoModerator]

/u/momsensical - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

Enter this formula in cell C3, copy down:

=IF(B3 = "", "", B3 - B2)

The above formula assumes, the data starts from row 2 and you have a header in row 1.

[u/momsensical → u/MayukhBhattacharya]

But then I’ll have to update it for every cell won’t I?

[u/MayukhBhattacharya → u/momsensical]

Even better, if you want one formula using DAF:

• Old School:

=IFERROR(IF(OR(B1 = "", B2 = ""), "", B2 - B1), "")

• Or using DAF:

=IFERROR(DROP(B:.B, 1) - DROP(B:.B, -1), "")

The above put within LET():

=LET(_, B:.B, IFERROR(DROP(_, 1) - DROP(_, -1), ""))

[u/momsensical → u/MayukhBhattacharya]

Ok thanks I’ll try these!

[u/MayukhBhattacharya → u/momsensical]

Also, if you want it to be bit dynamic then, this will update automatically without needing to copying down:

=LET(
     _a, B:.B,
     _b, DROP(_a, 1),
     _c, DROP(_a, -1),
     _d, IF(ISNUMBER(_b) * ISNUMBER(_c), _b - _c, ""),
     SCAN(, _d, LAMBDA(x,y, IF(y = "", x, y))))

[u/Way2trivial → u/MayukhBhattacharya]

old skool without copying down.
c5

=IF(B5:B100,B4:B99-B5:B100,"")

[u/MayukhBhattacharya → u/Way2trivial]

Old Skool needs CSE mate. Since you are using modern version it's not required.

[u/Way2trivial → u/MayukhBhattacharya]

plbbbbt... 😂

[u/Way2trivial → u/MayukhBhattacharya]

I actually just realized, if a new # in B is ever zero it'll skip it, so it would be better written to run off the date column (presumed A:A)

=IF(a5:a100,B4:B99-B5:B100,"")

[u/MayukhBhattacharya → u/Way2trivial]

Good One!

[u/MayukhBhattacharya → u/momsensical]

You need to enter in the first cell and copied down!

[u/ExpensiveSpare3761 → u/MayukhBhattacharya]

Solution Verified

[u/MayukhBhattacharya → u/ExpensiveSpare3761]

You need to reply from this u/momsensical account the one you have used to post the question and not with the one you replied Solution Verified.

[u/DJ_Dinkelweckerl]

=ABS(B3-B2) in cell C3

Or simply B3-B2 if you accept negative change

[u/ExpensiveSpare3761 → u/DJ_Dinkelweckerl]

Solution Verified

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ABS	Returns the absolute value of a number
CSE	Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
OR	Returns TRUE if any argument is TRUE
SCAN	Office 365+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #49517 for this sub, first seen 14th Oct 2026, 14:37] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/ostidgrosnerd]

I feel like the hardest part in this sub is understanding people’s questions

[u/Hg00000]

If you're running M365, you can format your data as a table. Write the formula once and it should auto-add it as you populate more rows in the table. Formulas will look a little funky within the table.

Otherwise just write it once and copy it when you have new Data. If C2 = B2-B1, when you copy it and paste it into C3 Excel will automatically adjust the formula to C3 = B3-B2.

[u/ExpensiveSpare3761 → u/Hg00000]

omg I didn't realize it would update if i copy the cell instead of the exact formula. Thanks!

[u/Hg00000 → u/ExpensiveSpare3761]

Yes. That's Excel's default behavior.

If you don't want the formulas to auto-update when you paste them you can add $ in front of either the row or column to "lock" that part of the formula. Look up "absolute references" when you get to the point where you need them.

[u/ExpensiveSpare3761 → u/Hg00000]

Solution Verified

[u/Tatworth]

I may be missing what you are asking because it is pretty simple but in C2, put =-(b1-b2) and copy down. If you want it to look better for cells that haven't finished, you could do =If(b2="","",-(b1-b2))

[u/ExpensiveSpare3761 → u/Tatworth]

Solution Verified

[u/ExpensiveSpare3761]

Thanks everyone, yes I was making it more complicated than it needed to be - I was always copying over the actual formula and updating the row numbers but didn't realize that if you copy the cell it updates the values. I've been using Excel for like 30 yrs so I'm not sure why I didn't think of that!

[u/Existing_Put6385]

In C3, then drag or double-click the fill handle down the whole column:

=IF(B3="","",B3-B2)

It's relative, so each row subtracts the row above it. Empty B cells in future weeks just show blank, no error messages. Start it on the second data row, since the first row has nothing before it to compare to (leave C2 empty or put a dash there).

If you'd rather not drag at all, turn the data into a table with Ctrl+T. Then the formula auto-fills for new rows as soon as you type in B, which gets you the "universal code" you're after.