Excel Keeps Inserting Old Formula - How to Fix It! - Episode 2640

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 16, 2024.
Microsoft Excel Tutorial: Excel Keeps Inserting Old Formula - How to Fix It!

Every time that Tori adds a new row to her Excel table, four columns auto-fill formulas from years ago.

Oh my gosh. Ever had a crazy frustrating problem with Excel? In this video, we tackle Tori's Excel nightmare head-on. Tori's adding new rows to her table, only to find four columns haunted by formulas from three years ago! What gives?

Hey there! If you're facing Excel woes like Tori, hit that Like button below to help more folks find this solution-packed video – thanks for the support!

Picture this: Tori's table looks fine until she adds a new row – then, bam! Ancient formulas appear like unwelcome ghosts. What's the deal? I was stumped, but thanks to Tori's screenshot and some help from the community, we cracked the case!

Join me as we dive into Tori's Excel labyrinth. Those pesky formulas, like ghosts of spreadsheets past, haunt every new row. But fear not! With the right steps, we banish them for good.

Ingeborg from New Zealand came through with the solution: copy, paste, delete, and paste again – formulas exorcised, new rows free from ancient spells!

Ever wonder how this spooky scenario came to be? I've got a theory. Maybe it wasn't Tori's doing at all. Join me for a theory session on how Excel hauntings can sneak into your workbook, and how to prevent them.

Big shoutout to Tori for sharing her Excel nightmare, and to you for tuning in! Stay tuned for more Excel adventures – subscribe, like, and ring that bell below! Got questions or thoughts? Drop them in the comments – let's crack these Excel mysteries together!

Join me next time for another net cast from MrExcel, where we navigate Excel's twists and turns with ease. Don't let old formulas haunt your spreadsheets – banish them with us!

To download the workbook from today: Excel Keeps Inserting Formula From Three Years Ago - Episode 2640 Sample Files - MrExcel Publishing

Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel

Table of Contents:
(00:00) Excel old formulas appearing in new rows of Excel table
(00:42) Demo of old formula appearing in new row
(01:11) Where is Excel storing the formula?
(01:25) Steps to solve the problem
(03:00) Clicking Like helps the algorithm
(03:09) How did the problem happen in the first place?
maxresdefault.jpg


Transcript of the video:
Oh my gosh. A crazy frustrating problem today. Tori says that every time she adds a new row to her table, she's getting four columns of formulas that are three years old and completely inappropriate.
What's going on?
Hey, just down below the video, if you click Like, that will sure that YouTube shows this video to more people - thanks.
So we have a table here, a Control T table, and Tori sent in this problem.
She says that she has this table and every time that she adds a new row, four columns, get formulas that are from years ago. What the heck is going on?
I said, oh, Tori, this is a great question. I have no idea.
Can you send me a screenshot or something so I could see what's happening, right?
So the data looks kind of like this.
Here we are in the last row, last column, and we just simply press Tab. This creates a new row.
Check this out, this 702 formula.
These are three hard coded numbers that go all the way back to the very first row on the table.
Somehow this table thinks that that formula belongs in every single cell.
So Tori constantly is deleting the formula in all four columns every time she adds a new record. It's just absolutely insane.
And so where, I wondered, is Excel storing that formula?
And I went through all of these icons here on the Table Design tab.
I just couldn't find it. And then went to the MVPs.
I said, this is so weird. What's the story?
I have to thank Ingeborg down in New Zealand who gave me these steps.
These steps to solve it.
So I'm going to take all of the formulas, which are presumably still good, in column G. I'm going to copy them, control C.
Then come out here and paste as formulas. So Paste, Paste Special, Formulas.
Then come here to column G.
And the only way to exorcise that formula, to get it out.
Is simply to select everything and press the Delete key.
So now that seems to clear the formula from the cells.
And then come over here to this temporary spot where we put the formulas CTRL C. Then come back and Alt E, S, F for formulas.
You can use whichever method you want to paste special formulas.
And now our formulas are back.
The big difference now is that we should be able to add new rows.
So right here from the very last row, last column, press the Tab key.
And finally, the formula from three years ago is no longer automatically propagating into that new row.
I can see how frustrating this would be for Tori. How did it get this way?
Well check out the outtake - I have a theory on how it got that way.
Maybe Tori didn't set this up.
A coworker set it up, and every time that Tori creates a new row, she has to go through and clear out those four formulas, which just make no sense.
Well, hey, I want to thank Tori for sending a question in and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below. So here's what I imagine happened years ago.
They had this nice little table. They had to log these special charges.
Added a new column.
And that first order happened to have a special charge assigned to it. So they went in and did the 123 plus 234 plus 345.
When they pressed Enter, it copied down. They are like, what the hell is this?
Then select the data, press the delete key.
I think that will get us into - there you are - that's how it happened.
So this formula extends and this formula, which is only supposed to be for that one cell, also extends.
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top