Thanks Mr Excel!!! I have been wondering for years how to do exorcism in Excel and know I know lol
@jegane
7 ай бұрын
Can we have duelin' excel time video on Lambda? 😊
@Kailuaboy59
8 ай бұрын
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!
@excel-in-g
8 ай бұрын
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.
@canirmalchoudhary8173
7 ай бұрын
Nice analysis to debug the problem.
@nadermounir8228
8 ай бұрын
Thank u Mr excel for this nice video
@marcus3457
8 ай бұрын
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!
@RandyMcNeil2
8 ай бұрын
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
@johnborg5419
8 ай бұрын
Thanks Mr excel!!! :)
@dafeac
7 ай бұрын
it also happens for formating I get old fill colors when adding new rows
@MrXL
7 ай бұрын
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.
@sledgehammer-productions
8 ай бұрын
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
8 ай бұрын
If you get the data in via power query, couldn't you just perform the formulas in PQ before saving to table format?
@sledgehammer-productions
8 ай бұрын
@@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.
@ricos1497
8 ай бұрын
@@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.
@sledgehammer-productions
8 ай бұрын
@@ricos1497 fine if that works for you, I'm not built that way 🙂
@abigailmcleod3206
8 ай бұрын
This has been happening to me in a spreadsheet that I created in another version of excel.
@rohanb6822
8 ай бұрын
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💯
@rohanb6822
8 ай бұрын
And sir give this video in short video ok
@rustypope8753
7 ай бұрын
'promosm'
@ricos1497
8 ай бұрын
Just delete the table and use named ranges #excelTablesAreTheEnemy
@MrXL
8 ай бұрын
You know I am smiling.
@ricos1497
8 ай бұрын
@@MrXL it's the children of the future that I worry about. Imagine being brought into the world, only knowing Excel tables?
@MrXL
8 ай бұрын
@@ricos1497 I’ve met the children. They are, unfortunately, using G**gle Sh**ts.
@ricos1497
8 ай бұрын
@@MrXL the inhumanity.
@excel-in-g
8 ай бұрын
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.
Пікірлер: 29