Excel Settings That ACTUALLY Make a Difference

[@barttrudeau9237]

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!

[@LarsScheffen]

Type "+N(Enter your Comment here)" after your original formular

[@mjbah]

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.

[@ExcelCampus]

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! 🙂

[@bigbyrd057]

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!!

[@ExcelCampus]

Thanks so much, Irma! I'm happy to hear you learned something new! 🙌

[@charlesmarshall4182]

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.

[@ExcelCampus]

Great tip, Charles! Thanks! 🙌

[@babjiparamathma1890]

the brilliant one is the tables formula. excellent one. was annoyed with that table formula earlier. thankyou

[@ankursharma6157]

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!

[@LarsScheffen]

I like the pivot table options - tahnks a lot!

[@PrasenjitSarkarSingapore]

Nice and useful! Thank you for making this video.

[@IvanCortinas_ES]

Excellent tips Johh. They are very useful!!! Thank you!!!

[@ExcelCampus]

I'm happy to hear they're useful. Thanks, Ivan! 🙏

[@teoxengineer]

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

[@chrism9037]

Another great video John, thank you!

[@ExcelCampus]

Thanks! Appreciate your support, Chris! 🙌

[@HSALDAIN]

Great Tips!! Thanks

[@rajeshmajumdar4999]

Thank You, John.

[@ExcelCampus]

Thanks, Rajesh! 🙌

[@ElkeSpraul-Hanson]

Very helpful tips, thank you!

[@ExcelCampus]

Thanks, Elke! 🙂

[@randyhaimila6476]

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?

[@ExcelCampus]

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! 🙂

[@thedon5470]

Pivot table defaults are the best

[@josealvesferreira1683]

Very usefull, thanks.

[@TinfoilHat007]

Nice to see a mac shortcut included, just wish there were more. Excel on mac does not have many of the features in the windows version, even though it is the same price. Wish you would do a few videos using mac and not just windows.

[@ExcelCampus]

Thanks for the suggestion! The Mac version still lags behind the Windows version, but Microsoft has made a lot of updates over the last few years to get it closer. I did a video on a new Mac update a few weeks ago (kzbin.info/www/bejne/hpexd4BtZbWaoac) and might do more Mac videos in the future. Thanks again! 🙂

[@dav4238]

Great video.. Thank you.

[@ExcelCampus]

Thanks! 🙂

[@alexb9312]

Automatic Data Conversion was rolled out in Version 2309, I am pretty sure everyone has that by now.

[@prabhulingpadadayyachaukim6434]

Great. Thank you.

[@ExcelCampus]

Thanks Prabhuling!

[@vrjaiswal009]

Uncheck the "Modify directly in to the cell": helps me understand the formulas better, if referencing is from different sheets

[@WheelDan]

Are you aware of the Trace Precedents in the Formula Auditing section of the Formula tab?

[@vrjaiswal009]

@@WheelDan sure, but the issue above feature helps is when the referencing is from some other sheet . makes easier to understand a formula.

[@keithdaborn5161]

I pray for the day ‘paste as values’ can be made the default!

[@ExcelCampus]

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! 🙂

[@WheelDan]

@@ExcelCampus Ctrl+Shift+V, why didn't I think of that? I knew it work in other apps. Thank you!

[@AniManuSCh]

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.

[@ExcelCampus]

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. kzbin.info/www/bejne/mp7HoJ98mLhgnKs 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! 🙂

[@AniManuSCh]

@@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.

[@WheelDan]

I am on the Current Channel and I have the "Remove leading zeros" option.

[@ExcelCampus]

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.

[@akalarun]

link for Download the excel file shows 404 error: Page not found

[@ExcelCampus]

It should be fixed now. Thanks for letting us know!! 🙂

[@drsteele4749]

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.

[@shaktishukla7558]

nothing groudbreaking this time

[@ExcelCampus]

Sorry to hear that and thanks for the feedback. 🙂

[@barttrudeau9237]

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!