The formula looks to be part of the table definition. this is stored in the XML parts somewhere. One of the following should work if my assumption is right. Either convert the table to a range then convert it back to a table, or go in the setting and stop the table formula to populate down automatically. Save the file. This updates the XML. Then put the settings back. I need to test it. Maybe later when feeling less ill.
Please give me total new excel sheet tricks in episode-2641 give a excel sheet tricks more new another differents types🎉so congratulations to your new check l give you some more than like this chennel on now💯
I have found that when a formula or formatting is applied to the ENTIRE column, it seems to update the defaults for the column. It's relatively easy to update the defaults when all rows in the column have the same formula/formatting (just copy the desired formula to the entire column in one operation), but when they are different, your workaround of temporarily copying the column outside the table, deleting the entire column, and then restoring will effectively delete the default formula. Hope this helps!
I get he same issue. I have a table I redo every year. At the end of the year I delete all the rows and remove the data except lookup formulas I want to keep. I specifically use the table because it automatically copies down my lookup formulas. I also key numbers as a formula in some of the cells (23 + 34 + 45), and that formula seems to carry down as well. I just assumed this was a feature that didn't work as planned. I will have to try this on my table next time I open it. Thanks!
Excellent workaround, The trick is finding out it is happening. Some of my tables are several hundred thousand lines ling and 50 plus columns wide. My data is imported via CSV file and I sometimes find an extra comma in the initial data(users add a comma in a text field) that causes a shift in the imported data. some of my data cleaning tools can cause the Table formulas to break in almost exactly the same way. This method may be just the answer to how to fix them without having to do it by eyeball. Thanks MeExcel
I have the opposite problem, every month I get in 15 minute data via Power Query, so in November I have 2.880 rows, in December 2.976 rows. In the worksheet there are some added columns with formula. When going from November to December I'm always missing the formula in the 'extra' rows. The sheet does it consistently so I just made it part of the manual "check that the formula are copied down to the last row". But it's annoying and unexpected, this 'use formula in the whole column' feature is what I like about tables.
@@ricos1497 I could, but I want the people that have to work with it to see what happens "easily" - following the formula is already a stretch, but having them get into the PQ editor is a bridge too far. Then I would be the only one able to make changes, and unfortunately those changes have proven to be frequent. Even I prefer the worksheet to do quick calculations (that turn out the be lasting) opposed to diving into the PQ editor.
Tables are meant to store structured data. This is an example where that rule was broken. For structured data, tables are great. Think Power Query, Power pivot.
Thanks Mr Excel!!! I have been wondering for years how to do exorcism in Excel and know I know lol
Can we have duelin' excel time video on Lambda? 😊
The formula looks to be part of the table definition. this is stored in the XML parts somewhere.
One of the following should work if my assumption is right. Either convert the table to a range then convert it back to a table, or go in the setting and stop the table formula to populate down automatically. Save the file. This updates the XML. Then put the settings back.
I need to test it. Maybe later when feeling less ill.
it also happens for formating I get old fill colors when adding new rows
I heard something about old colors too. No wonder the Excel team tried to abandon Tables in 2006 but it was too hard to remove it.
Please give me total new excel sheet tricks in episode-2641 give a excel sheet tricks more new another differents types🎉so congratulations to your new check l give you some more than like this chennel on now💯
I have found that when a formula or formatting is applied to the ENTIRE column, it seems to update the defaults for the column. It's relatively easy to update the defaults when all rows in the column have the same formula/formatting (just copy the desired formula to the entire column in one operation), but when they are different, your workaround of temporarily copying the column outside the table, deleting the entire column, and then restoring will effectively delete the default formula. Hope this helps!
This has been happening to me in a spreadsheet that I created in another version of excel.
And sir give this video in short video ok
Thanks Mr excel!!! :)
'promosm'
I get he same issue. I have a table I redo every year. At the end of the year I delete all the rows and remove the data except lookup formulas I want to keep. I specifically use the table because it automatically copies down my lookup formulas. I also key numbers as a formula in some of the cells (23 + 34 + 45), and that formula seems to carry down as well. I just assumed this was a feature that didn't work as planned.
I will have to try this on my table next time I open it. Thanks!
Excellent workaround, The trick is finding out it is happening. Some of my tables are several hundred thousand lines ling and 50 plus columns wide. My data is imported via CSV file and I sometimes find an extra comma in the initial data(users add a comma in a text field) that causes a shift in the imported data. some of my data cleaning tools can cause the Table formulas to break in almost exactly the same way. This method may be just the answer to how to fix them without having to do it by eyeball. Thanks MeExcel
Nice analysis to debug the problem.
I have the opposite problem, every month I get in 15 minute data via Power Query, so in November I have 2.880 rows, in December 2.976 rows. In the worksheet there are some added columns with formula. When going from November to December I'm always missing the formula in the 'extra' rows. The sheet does it consistently so I just made it part of the manual "check that the formula are copied down to the last row". But it's annoying and unexpected, this 'use formula in the whole column' feature is what I like about tables.
If you get the data in via power query, couldn't you just perform the formulas in PQ before saving to table format?
@@ricos1497 I could, but I want the people that have to work with it to see what happens "easily" - following the formula is already a stretch, but having them get into the PQ editor is a bridge too far. Then I would be the only one able to make changes, and unfortunately those changes have proven to be frequent. Even I prefer the worksheet to do quick calculations (that turn out the be lasting) opposed to diving into the PQ editor.
@@sledgehammer-productions always be the one that knows how to make the changes, then they have to keep you forever. Hide all the passwords too.
@@ricos1497 fine if that works for you, I'm not built that way 🙂
Thank u Mr excel for this nice video
Just delete the table and use named ranges #excelTablesAreTheEnemy
You know I am smiling.
@@MrXL it's the children of the future that I worry about. Imagine being brought into the world, only knowing Excel tables?
@@ricos1497 I’ve met the children. They are, unfortunately, using G**gle Sh**ts.
@@MrXL the inhumanity.
Tables are meant to store structured data. This is an example where that rule was broken.
For structured data, tables are great. Think Power Query, Power pivot.