This is without doubt, one of the most useful videos I’ve ever seen produced by anybody on Excel. You are going to save me a great deal of time. Thank you very much for taking the time to educate me about things in Excel that I had clearly been missing.
Thanks John. Always brilliant videos. The pivot table and backstage have been the most annoying things for me. With the options to decide on the pivot table and to remove the backstage when saving have been the game changer for me.
Thanks, Mohamed! Happy to hear it. I also turn off the backstage. One thing I forgot to note in the video is that if the file is already saved, you still need to use F12 to open the Save As dialog if you want to save the file with a new name or location. I hope that helps. Thanks again and have a nice day! 🙂
As usual John, there is always a better way to do things in Excel. Every video I watch, I can see my productivity increasing. My most common used excel options was the analysis (I just didn't realize I could just click the quick menu button when selecting my data!!! You are a wealth of information. Thank you for sharing your knowledge!!
For leading zeros, if you're not doing any calculations on the cell, then format the cell as text. Zip codes, employee IDs, product numbers, etc. It's good practice to format text for an attribute or identifier that isn't meant to be calculated.
An option I would love to have is the ability to add comments to formulas. Some of our formulas are quite long and it would be nice to document the actions in line rather than with notes. Great video, I appreciate the tips!
These settings were wonderful 😊. Could you please make more videos on settings. It would really be helpful in improving the efficiency of work. Thanks once again 🎉
ACTUALLY Useful 🙏🏽 F.Y.I.: the last setting . . the Automatic Data Conversion option . . I can see it. Seems, it is rolled out for General Public. Thank You!
Thanks Ankur! Yes, looks like it rolled out to the current channel in August. Sorry, I was not aware of that when I recorded the video. But I'm glad you have access to it. 👍
The fact that Microsoft effectively disabled the CTRL+O Keyboard shortcut to open files starting in Office 2013, when literally EVERY OTHER APPLICATION ON THE PLANET USES IT, is almost as dumb as disabling Dark Mode in Power BI Desktop back in 2018 or so. They just added that back in the last update. I know about the Save setting in Options which reenables this, but it's stupid to have that as the default. If I want to go to the Backstage > Open, I'll bloody go there on my own. Good video, Sir.
Thanks, Randy! Yes, our Elevate Excel Training Program covers beginner to advanced training on Excel, data analytics, and data automation. Here is a link to the enrollment page with more info. www.excelcampus.com/elevate I hope that helps. Thanks again and have a nice day! 🙂
I have found it helpful in some of my workbooks to match the gridline colour to the tab colour. Options I would like to have would be to place the tabs at the top instead of along the bottom, and have a fairly thin line stretch across the top of worksheet which is the same colour as the tab. This makes it easier to know which tab is active. That feature existed 30 years ago in Lotus 123. If we can’t have the tabs at the top, couldn’t we at least have the coloured order along the tab edges.
Thank you so much. Also we can control inserting new data into table and table is not extending to include new data. This is not suitable for dynamic data ranges in table but excel team had added this feature to options menu
Options->Advanced->Allow editing directly in cell...uncheck. Moves formula writing up into the formula bar and gives you highlighting of referenced cells. Enables double-clicking a cell to jump to the first reference cell in the formula.
Absolutely Awesome It is impressive !!!!!!! In my Excel sheet, in Data ->, I can't find the Geographics and Stocks. How can I find them and fix the issue?
Awesome! Thank you very much for your videos Jon, they are really helpfull, so many tricks I did not know. Would be great if there is a way (even with VBA or something) to bypass the options menu to get right to a specific option like changing the default grid color right, since there ara a lot, would be really usefull to have some quick direct access as favorite or something.
Thanks so much, Manu! 🙌 That's a great idea to have quick access to favorites. A lot of the settings are available in VBA and you can create macros that toggle the settings quickly. For example, here's the code to turn Table formulas on/off Application.GenerateTableRefs = xlGenerateTableRefStruct Application.GenerateTableRefs = xlGenerateTableRefA1 Here is the VBA for the gridline color ActiveWindow.GridlineColorIndex = 5 'Blue ActiveWindow.GridlineColorIndex = 3 'Red You could create a custom ribbon button or add-in for this. Here's a video on how to add macro buttons to the ribbon. ua-cam.com/video/dmdolFcS-fI/v-deo.html With that said, I think the Excel Options window needs some updates/enhancements. My wish list includes: - A search bar (available on Mac but not Windows). - A list or way to see what settings have been changed from defaults. - Ability to import/export all settings for other computers. - More visibility on application vs workbook level settings. - Ability to pin favorite settings for quick access. I hope that helps. Thanks again and have a nice day! 🙂
Consider Advanced > Formulas > set "Number of calculation threads" to Manual at one less that the number of processors on the computer. I once had a workbook that was really big with a lot of lookups and other formulas. Even with autocalc turned off, when I had to recalc the workbook (F9) it could take a minute or two (or more sadly). Having at least one processor NOT tied up with Excel work would allow me to check my email or preform some other task while the spreadsheet crunched along on the remaining processors.
Great idea, Keith! It would be nice to be able to specify your defaults for the paste options. If you are a Microsoft 365 subscriber, you can use Ctrl+Shift+V to paste values. Well, I'm not sure if that has been rolled out to all channels on Microsoft 365 yet, but I believe most users have it. It works on Windows desktop as well as the web version. I hope that helps. Thanks again and have a nice day! 🙂
I don't particularly like 3 finger shortcuts. Before ctrl shift V came on the scene, I wrote a one line macro that would do this. I stored it in the Excel personal workbook and then assigned it to the fourth position on the Quick Access Toolbar so that I could use it by clicking Alt 4. Why position 4? No reason 😊
Great tip, Martin! I do have a video on adding macro buttons to the QAT for anyone else that is interested in this setup. ua-cam.com/video/dmdolFcS-fI/v-deo.html The only downside to using a macro to paste data is that you can't undo the action. Well, I spent a lot of time creating a workaround solution with the SendKeys method in VBA, but SendKeys is notoriously buggy...
@@ExcelCampus Thanks. Agree that SendKeys can be a bit hit and miss at times. Incidentally it is quite possible to create a custom "undo" function. I remember John Walkenbach went into a lot of detail on this in his VBA Bible. Michael Alexander currently holds the torch in the Excel Bible series, but I'm assuming that Walkenbach's chapter is still in it. (However, I'm so lazy, I never wrote the code into any of my projects. Should revisit it sometime this decade... maybe...! 😊)
Oh amazing! Thanks for letting us know! And my apologies, I thought it was still in beta but looks like it started making it's way to production in August.
How can I Turn off a specific formula in a tablet? In some cases you create a formula, decide to remove it. When you add a new row the deleted formula comes up again in the column in the new row despite deleted in that column for all the other rows. Is it possible to delete the memory of the formula?
@@ExcelCampusNo worries. It would be nice to know the distribution of users across the channels. While the Semi-Annual only got it in Jul-Aug 24, Monthly got it in Nov 23 and Current Channel in Sep 23. Having a similar issue with PivotBy & GroupBy which have been rolled out the Current Channel but not yet available for Monthly and it could be a while for Semi-Annual and yet there is a general perception that everyone already has it. 😥
Everyone, especially people who make videos, should really set that option to do nothing for "After pressing Enter, move selection...". It drives me insane when someone presses Enter to invoke a formula only to quickly press up-arrow to get back to that cell. Reminds me of a trained monkey doing motions by rote. Incidentally, when performing data entry in a column, you can always just type your data and then press down-arrow to go to the next row. So that option is superfluous anyway.
Great suggestion! Personally, I try to keep most of my settings to the defaults. The reason I do this is so the look, feel, and behavior of my Excel is similar to the viewer's. If it differs too much, then the viewer will question that and stop paying attention or fall behind. I've learned the hard way on this... 🙂 With that said, I agree that the move selection setting would save a lot of keystrokes for most users. If you are doing a lot of data entry, then maybe not. One shortcut is to press Ctlr+Enter to keep the selection on the active cell. Sometimes you'll see me do this in videos, and I try to make note of it when I do. I'll try to do that more often in the future. Again, thanks for the feedback. I appreciate you taking the time to share your thoughts. 🙌
This is without doubt, one of the most useful videos I’ve ever seen produced by anybody on Excel. You are going to save me a great deal of time. Thank you very much for taking the time to educate me about things in Excel that I had clearly been missing.
Really helpful tips, especially the pivot table options
Thanks John. Always brilliant videos. The pivot table and backstage have been the most annoying things for me. With the options to decide on the pivot table and to remove the backstage when saving have been the game changer for me.
Thanks, Mohamed! Happy to hear it. I also turn off the backstage. One thing I forgot to note in the video is that if the file is already saved, you still need to use F12 to open the Save As dialog if you want to save the file with a new name or location.
I hope that helps. Thanks again and have a nice day! 🙂
As usual John, there is always a better way to do things in Excel. Every video I watch, I can see my productivity increasing. My most common used excel options was the analysis (I just didn't realize I could just click the quick menu button when selecting my data!!! You are a wealth of information. Thank you for sharing your knowledge!!
Thanks so much, Irma! I'm happy to hear you learned something new! 🙌
Outstanding 🙏 I am your new subscriber ❤
Thanks so much! I appreciate it! 😊
Very useful because these kind of Excel tips are rare but yet helpful and important to know 👌
For leading zeros, if you're not doing any calculations on the cell, then format the cell as text. Zip codes, employee IDs, product numbers, etc. It's good practice to format text for an attribute or identifier that isn't meant to be calculated.
Great tip, Charles! Thanks! 🙌
Exceptionally impressive
An option I would love to have is the ability to add comments to formulas. Some of our formulas are quite long and it would be nice to document the actions in line rather than with notes. Great video, I appreciate the tips!
Type "+N(Enter your Comment here)" after your original formular
@@LarsScheffen Thank you for that tip! I'll put that to good use.
Yes true for that , for long formulas I usually use let function , in let variables can have names (somehow similar to comments )
Or to use lambda
I didn't know the table formula option!! Nice one!!
These settings were wonderful 😊.
Could you please make more videos on settings. It would really be helpful in improving the efficiency of work.
Thanks once again 🎉
I'm happy to hear it was helpful. And yes, I'll be happy to do more videos on settings in the future. Thanks for the suggestion! 🙌
your videos are helpful for me , I learned many things , think you sir
Excellent tips Johh. They are very useful!!! Thank you!!!
I'm happy to hear they're useful. Thanks, Ivan! 🙏
Extremely useful! Thank you, Jon!
Thanks Jo! 🙂
Another great video John, thank you!
Thanks! Appreciate your support, Chris! 🙌
Nice and useful! Thank you for making this video.
Thanks Prasenjit! 🙌
great one 👏👏
Thanks for watching! 🙂
I like the pivot table options - tahnks a lot!
Thanks Lars! 🙂
Great Tips!! Thanks
Very helpful tips, thank you!
Thanks, Elke! 🙂
Thank you Jon for this nice Video. I like the song played at the end of the video
Thanks Nader! We have a few songs we typically use, and we'll keep that one in the rotation. 🙂
ACTUALLY Useful 🙏🏽
F.Y.I.: the last setting . . the Automatic Data Conversion option . . I can see it. Seems, it is rolled out for General Public.
Thank You!
Thanks Ankur! Yes, looks like it rolled out to the current channel in August. Sorry, I was not aware of that when I recorded the video. But I'm glad you have access to it. 👍
Awesome! Thank you!
More than wonderful 👍
The fact that Microsoft effectively disabled the CTRL+O Keyboard shortcut to open files starting in Office 2013, when literally EVERY OTHER APPLICATION ON THE PLANET USES IT, is almost as dumb as disabling Dark Mode in Power BI Desktop back in 2018 or so. They just added that back in the last update.
I know about the Save setting in Options which reenables this, but it's stupid to have that as the default. If I want to go to the Backstage > Open, I'll bloody go there on my own. Good video, Sir.
Thanks John. Helpful video.
Thank You, John.
Thanks, Rajesh! 🙌
As usual John, this was well presented. You are a wealth of information. Is there an Excel course that you teach us from beginner to advanced?
Thanks, Randy! Yes, our Elevate Excel Training Program covers beginner to advanced training on Excel, data analytics, and data automation. Here is a link to the enrollment page with more info.
www.excelcampus.com/elevate
I hope that helps. Thanks again and have a nice day! 🙂
the brilliant one is the tables formula. excellent one. was annoyed with that table formula earlier. thankyou
I'm happy to hear it, Babji. It's good to know that the setting is application level, meaning it does not travel with the workbook.
I have found it helpful in some of my workbooks to match the gridline colour to the tab colour.
Options I would like to have would be to place the tabs at the top instead of along the bottom, and have a fairly thin line stretch across the top of worksheet which is the same colour as the tab. This makes it easier to know which tab is active. That feature existed 30 years ago in Lotus 123. If we can’t have the tabs at the top, couldn’t we at least have the coloured order along the tab edges.
Wow did not realize can get rid of the backstage window, great tips!
That's one of my favorites. Thanks Tiong! 🙌
Great video.. Thank you.
Thanks! 🙂
This is a very useful video
Thanks! 🙏
Thank you so much. Also we can control inserting new data into table and table is not extending to include new data. This is not suitable for dynamic data ranges in table but excel team had added this feature to options menu
Great tip! Thanks Emre! 🙌
Great. Thank you.
Thanks Prabhuling!
Very usefull, thanks.
Thanks🙏
Options->Advanced->Allow editing directly in cell...uncheck. Moves formula writing up into the formula bar and gives you highlighting of referenced cells. Enables double-clicking a cell to jump to the first reference cell in the formula.
Thanks for sharing! 🙌
Excellent video- I wouldn’t encourage anyone to move away from structured references though! 😅
Also I kind of like the backstage
how possible,, youtube knows i want to know about excel today and suggest this video ,, anyway im happy i found your channel
pivot table default layout is great, i use it, also i use autosave option like 2 min
Great tip on Autosave! Thanks Chahine! 🙂
Pivot table defaults are the best
Absolutely Awesome It is impressive !!!!!!!
In my Excel sheet, in Data ->, I can't find the Geographics and Stocks. How can I find them and fix the issue?
Awesome! Thank you very much for your videos Jon, they are really helpfull, so many tricks I did not know. Would be great if there is a way (even with VBA or something) to bypass the options menu to get right to a specific option like changing the default grid color right, since there ara a lot, would be really usefull to have some quick direct access as favorite or something.
Thanks so much, Manu! 🙌
That's a great idea to have quick access to favorites. A lot of the settings are available in VBA and you can create macros that toggle the settings quickly. For example, here's the code to turn Table formulas on/off
Application.GenerateTableRefs = xlGenerateTableRefStruct
Application.GenerateTableRefs = xlGenerateTableRefA1
Here is the VBA for the gridline color
ActiveWindow.GridlineColorIndex = 5 'Blue
ActiveWindow.GridlineColorIndex = 3 'Red
You could create a custom ribbon button or add-in for this. Here's a video on how to add macro buttons to the ribbon.
ua-cam.com/video/dmdolFcS-fI/v-deo.html
With that said, I think the Excel Options window needs some updates/enhancements.
My wish list includes:
- A search bar (available on Mac but not Windows).
- A list or way to see what settings have been changed from defaults.
- Ability to import/export all settings for other computers.
- More visibility on application vs workbook level settings.
- Ability to pin favorite settings for quick access.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Amazing as always!, Thank you so much, you even open my eyes with that list, you are right it needs an update. Have a great day.
Is it possible to change the cell border colour?
Uncheck the "Modify directly in to the cell": helps me understand the formulas better, if referencing is from different sheets
Are you aware of the Trace Precedents in the Formula Auditing section of the Formula tab?
@@WheelDan sure, but the issue above feature helps is when the referencing is from some other sheet . makes easier to understand a formula.
Consider Advanced > Formulas > set "Number of calculation threads" to Manual at one less that the number of processors on the computer. I once had a workbook that was really big with a lot of lookups and other formulas. Even with autocalc turned off, when I had to recalc the workbook (F9) it could take a minute or two (or more sadly). Having at least one processor NOT tied up with Excel work would allow me to check my email or preform some other task while the spreadsheet crunched along on the remaining processors.
Great tip! Thanks for sharing, Stephen! 🙌
I pray for the day ‘paste as values’ can be made the default!
Great idea, Keith! It would be nice to be able to specify your defaults for the paste options. If you are a Microsoft 365 subscriber, you can use Ctrl+Shift+V to paste values. Well, I'm not sure if that has been rolled out to all channels on Microsoft 365 yet, but I believe most users have it. It works on Windows desktop as well as the web version.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Ctrl+Shift+V, why didn't I think of that? I knew it work in other apps. Thank you!
I don't particularly like 3 finger shortcuts. Before ctrl shift V came on the scene, I wrote a one line macro that would do this. I stored it in the Excel personal workbook and then assigned it to the fourth position on the Quick Access Toolbar so that I could use it by clicking Alt 4. Why position 4? No reason 😊
Great tip, Martin! I do have a video on adding macro buttons to the QAT for anyone else that is interested in this setup. ua-cam.com/video/dmdolFcS-fI/v-deo.html
The only downside to using a macro to paste data is that you can't undo the action. Well, I spent a lot of time creating a workaround solution with the SendKeys method in VBA, but SendKeys is notoriously buggy...
@@ExcelCampus Thanks. Agree that SendKeys can be a bit hit and miss at times.
Incidentally it is quite possible to create a custom "undo" function. I remember John Walkenbach went into a lot of detail on this in his VBA Bible. Michael Alexander currently holds the torch in the Excel Bible series, but I'm assuming that Walkenbach's chapter is still in it. (However, I'm so lazy, I never wrote the code into any of my projects. Should revisit it sometime this decade... maybe...! 😊)
I am on the Current Channel and I have the "Remove leading zeros" option.
Oh amazing! Thanks for letting us know! And my apologies, I thought it was still in beta but looks like it started making it's way to production in August.
How can I Turn off a specific formula in a tablet? In some cases you create a formula, decide to remove it. When you add a new row the deleted formula comes up again in the column in the new row despite deleted in that column for all the other rows. Is it possible to delete the memory of the formula?
FYI The Automatic Data Conversion bonus topic to "Remove leading zeros and convert to a number" is now live in the 365 Excel version.
Automatic Data Conversion was rolled out in Version 2309, I am pretty sure everyone has that by now.
Thanks Alex! Yeah sorry about that. I didn't realize it was rolled out in August. That's great news for everyone.
@@ExcelCampusNo worries. It would be nice to know the distribution of users across the channels. While the Semi-Annual only got it in Jul-Aug 24, Monthly got it in Nov 23 and Current Channel in Sep 23. Having a similar issue with PivotBy & GroupBy which have been rolled out the Current Channel but not yet available for Monthly and it could be a while for Semi-Annual and yet there is a general perception that everyone already has it. 😥
How do I update my profile picture in Excel/Word 365?I tried and it shows my old picture in Excel/Word but my new picture in Teams.
link for Download the excel file shows 404 error: Page not found
It should be fixed now. Thanks for letting us know!! 🙂
Everyone, especially people who make videos, should really set that option to do nothing for "After pressing Enter, move selection...". It drives me insane when someone presses Enter to invoke a formula only to quickly press up-arrow to get back to that cell. Reminds me of a trained monkey doing motions by rote. Incidentally, when performing data entry in a column, you can always just type your data and then press down-arrow to go to the next row. So that option is superfluous anyway.
Great suggestion! Personally, I try to keep most of my settings to the defaults. The reason I do this is so the look, feel, and behavior of my Excel is similar to the viewer's. If it differs too much, then the viewer will question that and stop paying attention or fall behind. I've learned the hard way on this... 🙂
With that said, I agree that the move selection setting would save a lot of keystrokes for most users. If you are doing a lot of data entry, then maybe not. One shortcut is to press Ctlr+Enter to keep the selection on the active cell. Sometimes you'll see me do this in videos, and I try to make note of it when I do. I'll try to do that more often in the future.
Again, thanks for the feedback. I appreciate you taking the time to share your thoughts. 🙌
nothing groudbreaking this time
Sorry to hear that and thanks for the feedback. 🙂