Good tip John. A lot of people are not even aware of those types of mistakes. I had to help a colleague who made a similar mistake however in her case she mistakenly referenced the whole rows ( basically the whole sheet was selected instead of just her data)while creating her table and that prevented her from creating her pivot table. I love excel table.
I have found this channel really helpful. The contents are rich enough besides mentioning the pros and cons of some of the functions, making it really interesting and constructive Thanks, Jon.
Thanks Trina! I'm happy to hear it. We just created a playlist with our Excel Table tutorials. The videos will contain a lot of useful tips and techniques if you are new to Tables. Here is a link. ruclips.net/p/PLKbOx3gUV_E8EyPF_KckH1Mdld-oAZoDf I hope that helps. Thanks again and have a nice day! 🙂
Hi Jon. Thanks for the tips on this issue. I have not fallen into the trap (defining the table to include the entire column), but I have seen worksheets from others set up this way. Good to know why it occurs and how to correct for it. Thanks for sharing :)) Thumbs up!!
Very useful video, I went through a lot of information uninstalling countless Windows updates and finally solved the problem, thank you very much for the video!
Hi Valens, As Philip mentioned, you can use the Convert to Range button to remove the Table and keep the data. That button also keeps the Table style/formatting. If you want to remove the formatting as well, you will want to first change the style to None before pressing Convert to Range. I hope that helps. Thanks again and have a nice day! 🙂
Hello Jon, I have a dashboard which has Y and X axis values linked to a dynamic named range. I use a scrollbar to scroll in the dashboard from left to right, and when I click the scrollbar to scroll the columns in the chart take time to load. Any ideas how i can fix that?
Thanks for the video Jon. I'm actually trying to find a solution for a problem I have with some of my tables. I have tables that take a very long time to expand when I paste in the data (formulas are not intensive and data sets are not overly large). However if I past the same data in the first row after the table it expands and calcs immediately, no delay at all but then the top row is without data (this is what I have been doing, then deleting out the top row). Do you have any insight why excel might be struggling to expand the table and requiring a work around to get better performance.
I haven’t ever made that mistake because the way he created the table, is something I don’t do lol I always use ctl+T and that usually detects the range for the most part. But good to know I guess. 👍🏽
Thanks Jaime! For others reading, Ctrl+T is the shortcut to insert a table. If you have the columns selected before pressing Ctrl+T, Excel will still put the whole column references in the Create Table window. Unfortunately, it doesn't detect the used range if you have entire columns selected. At least not yet. Hopefully, this feature will be added in the future. 🤞 Thanks again and have a nice day! 🙂
Dear Jon. Thanks for these GR8 tip. I have one question. When creating a Table, either through "Format as Table" or "Insert Table", the Create Table Dialogue Box presents an absolute reference for the Table, but it still expands automatically when adding rows to it. I know Tables in Excel are designed to expand automatically, so what is the added value of the absolute reference here? (A range name (for the same range as the Table range) would not expand automatically.)
This is a great and thoughtful tip, John; I have experienced the small scroll bar in the past and had no idea what caused it. I thought it was a ghost in the machine. 😉 By extension, I’m guessing that lazily copying and pasting whole columns, rows, or sheets multiple times within a VBA macro instead of manipulating manageable ranges for copy & paste could cause a performance slowdown as well?
Thank you Jon! I always appreciate your videos. Very clear, concise but generous with information/ tips at the same time. I looked at your Videos file for any tips on how to save a workbook/ excel file or even Word document in a shared drive, where multiple users can use and save at the same time (collaborate). I am not looking for the one which the file is saved in a Cloud, but in a shared drive. I did not find it in your teaching Videos collection. Would you consider making a video of it? @ExcelCampus-Jon
I have an issue when using fill color (while I am selecting the color), the excel just lagging and froze for 10-20seconds. the excel will run normally after that, but once I just click on "fill color", the excel will freeze. please help. It happens to only this excel file.
Good tip John. A lot of people are not even aware of those types of mistakes. I had to help a colleague who made a similar mistake however in her case she mistakenly referenced the whole rows ( basically the whole sheet was selected instead of just her data)while creating her table and that prevented her from creating her pivot table. I love excel table.
Thanks, MrTechnology! Great point about whole-row references. I hadn't thought about that case.
Thanks soooo much! I've been looking for this fix for 6 months, since my laptop updated to Win11!
I have found this channel really helpful.
The contents are rich enough besides mentioning the pros and cons of some of the functions, making it really interesting and constructive
Thanks, Jon.
Thanks for the feedback @mohammadrezasohrabi4979 😀
OMGosh! YOU ARE THE BEST!! You are the only person who addresses this issue that I could find over the last hour.
Glad I could help. 😀
different class, best excel channel on you tube
Thanks John, I also use ctrl-* to check if there are no cells nearby...
THANK YOU!!!! I got the answer in the first 40 seconds of the video.
Nicely done, I had this exact issue with performance and now it is resolved. Thank you!
Great to hear that! 😀
amazing all my colleagues faced this issue you are sheer genius..work productivity increased by 100%🖖
It's good to know that your productivity increased by 100%! 😊
Good tip.... This could easily be missed by even seasoned users. Thx for sharing
Thanks much for the "heads up". Since I'm relatively new to Tables, this is really good to know!
Thanks Trina! I'm happy to hear it. We just created a playlist with our Excel Table tutorials. The videos will contain a lot of useful tips and techniques if you are new to Tables. Here is a link. ruclips.net/p/PLKbOx3gUV_E8EyPF_KckH1Mdld-oAZoDf
I hope that helps. Thanks again and have a nice day! 🙂
I was just learning about tables last weekend, so this is relavent to my work and useful as always. Thanks for sharing.
Thanks Randy! I'm happy to hear you are learning about and using Tables. They are a fantastic feature of Excel.
Hi Jon. Thanks for the tips on this issue. I have not fallen into the trap (defining the table to include the entire column), but I have seen worksheets from others set up this way. Good to know why it occurs and how to correct for it. Thanks for sharing :)) Thumbs up!!
Thank you so much! You saved my day, because before i didn't find out why excel got so slow on Zooming and scrolling
Very useful video, I went through a lot of information uninstalling countless Windows updates and finally solved the problem, thank you very much for the video!
Glad it helped! 😀
Thank you I have done this and it took me awhile to figure it out. I love the scroll bar as a warning.
Thanks John, you always shows great tips.
Really Great Tips,I Think Most Of Us Have Made The Same Mistake By Unknowingly Using Whole Column References...Thank You Jon :)
Thanks Darryl! Happy to hear I'm not alone. 😉
Thanks Jon - useful information as always.
Thank John! those are really useful tips.
Thank you Jon as usual very good tip!!
Hello I was watching your video and was wondering if I can keep adding information to the same spreadsheet
Thanks! This was helpful and fixed my issue
Great to hear, thanks for watching!
This is very helpful. Thank you for your work.
Glad it was helpful! 😀
Really great tips, thanks again 👍,
I have a question , after creating a table in excel how can i get back to the original dataset?
Thanks
right click in table, convert to range.
dedign tools, table, convert to range
Hi Valens,
As Philip mentioned, you can use the Convert to Range button to remove the Table and keep the data. That button also keeps the Table style/formatting. If you want to remove the formatting as well, you will want to first change the style to None before pressing Convert to Range.
I hope that helps. Thanks again and have a nice day! 🙂
Excellent tip, thank you for sharing sir
Thanks Simoiya! 🙂
Great tip 👍
Very useful video, keep it up!
Thanks a lot! 😀
what version of excel sre you using i dont have that resize table on my version in tables
THANKS, this helpt me a lot!!!
You're welcome, Eduard! 😀
Thanks for the tip. I use Ctrl + t to create a table which is quicker than wandering about menus 👍
Hello Jon, I have a dashboard which has Y and X axis values linked to a dynamic named range. I use a scrollbar to scroll in the dashboard from left to right, and when I click the scrollbar to scroll the columns in the chart take time to load. Any ideas how i can fix that?
very helpful!
Thank you!
Thanks for the video Jon. I'm actually trying to find a solution for a problem I have with some of my tables. I have tables that take a very long time to expand when I paste in the data (formulas are not intensive and data sets are not overly large). However if I past the same data in the first row after the table it expands and calcs immediately, no delay at all but then the top row is without data (this is what I have been doing, then deleting out the top row). Do you have any insight why excel might be struggling to expand the table and requiring a work around to get better performance.
Great tip, thanks alot!!
Thanks Fredrick! 🙂
I haven’t ever made that mistake because the way he created the table, is something I don’t do lol I always use ctl+T and that usually detects the range for the most part. But good to know I guess. 👍🏽
Thanks Jaime! For others reading, Ctrl+T is the shortcut to insert a table. If you have the columns selected before pressing Ctrl+T, Excel will still put the whole column references in the Create Table window. Unfortunately, it doesn't detect the used range if you have entire columns selected. At least not yet. Hopefully, this feature will be added in the future. 🤞
Thanks again and have a nice day! 🙂
Dear Jon.
Thanks for these GR8 tip. I have one question. When creating a Table, either through "Format as Table" or "Insert Table", the Create Table Dialogue Box presents an absolute reference for the Table, but it still expands automatically when adding rows to it. I know Tables in Excel are designed to expand automatically, so what is the added value of the absolute reference here? (A range name (for the same range as the Table range) would not expand automatically.)
marvelous, You are the best
Thank you, Yyusof! 😀
This does not work with the latest version of Excel :(
A quick way to check all existing tables is to use the Name Manager and check the "Refers to" column🙂
Really useful. Do you offer some free/paid vba courses ?
Dear, Excil 2013 Editing the record too slow in my 32bit 2gb laptop window7 ... what can i do...?
This is a great and thoughtful tip, John; I have experienced the small scroll bar in the past and had no idea what caused it. I thought it was a ghost in the machine. 😉
By extension, I’m guessing that lazily copying and pasting whole columns, rows, or sheets multiple times within a VBA macro instead of manipulating manageable ranges for copy & paste could cause a performance slowdown as well?
Thank you Jon! I always appreciate your videos. Very clear, concise but generous with information/ tips at the same time. I looked at your Videos file for any tips on how to save a workbook/ excel file or even Word document in a shared drive, where multiple users can use and save at the same time (collaborate). I am not looking for the one which the file is saved in a Cloud, but in a shared drive. I did not find it in your teaching Videos collection. Would you consider making a video of it? @ExcelCampus-Jon
Thanks! 👌
How do you use this tip on a table with a dynamic row count?
Great; thanks
I have an issue when using fill color (while I am selecting the color), the excel just lagging and froze for 10-20seconds. the excel will run normally after that, but once I just click on "fill color", the excel will freeze. please help.
It happens to only this excel file.
Sir, how to skip already protect excel workbook and next file protect with password by excel vba
What are the benifits of formatting data into a table?
Thank you Jon. Great tutorial 👌