4 Excel Settings to Review Now to be more Productive ⏩

when you install programs on your computer they come with default settings Excel does too now some of these settings might be working against you because you have your way of using Excel there are specifically four settings that you should review now to see if you need to update them let me show you so the first setting you might want to update is this year when I start to write a formula just anything doesn't matter when I press enter I move automatically to the next cell so every time you press enter or you type a number you press enter you move automatically to the next cell now I usually write a lot of formulas and then I want to stay on the same cell not move down so that I can easily copy my formula down now there is a way that you can do this with a shortcut key so instead of pressing enter you can do control enter but I don't always remember to do this I just want enter to stay on the same cell there is a setting for that you go to file options Advanced and right here you can Define what happens when you press enter by default it moves down you could move right up that would be weird but if you want to do a prank on your colleague you could do this or to the left but if you don't want it to move anywhere like in my case you just uncheck this and then you click on OK and now every time you press enter you stay on the same cell and we can just try by just typing any type of formula and pressing enter and we stay right here by the way since we're talking about adjusting settings you should also check your YouTube settings to see if you're subscribed to this channel this way you don't miss out on any other tips that you might find useful in the future the next setting you might want to update is your autocomplete settings if you deal with long text long names or long phrases that keep repeating in different Excel files you might want to do this so let's say I need to write the company name a lot so office plus headquarters and sometimes I make mistakes and I don't even notice it it's going to save me a lot of time to have an autocomplete for this we can easily set this up by going to file options this time we're going to go to proofing right here we can change how Excel corrects and formats the text as we type when you click on autocorrect options you are going to see a big list here and this is going to be dependent on your language the great thing is that you can add your own stuff here let's say every time I type in op I want to see office plus headquarters click on ADD and then okay and okay now every time I type in op and I add a space it's going to put in office plus headquarters and it doesn't matter which workbook I'm in I'm going to open a brand new Excel workbook by pressing Ctrl n and then I type in op space and I get office plus headquarters next I think you might want to review is your custom list you know how every time you type in the months here and then you drag this down you automatically see the next months where is this coming from it's coming from your custom lists but it doesn't have to be restricted to months only you can add any list you want you could have a list of your departments if that's something that you keep using in multiple workbooks to do that go back to file options advanced scroll all the way to the end and you're going to see edit custom lists when you click on this you're going to see some predefined custom lists for you so the ones that I just showed you are right here you have the ability to add your own custom list by just entering here whatever you want and then clicking on ADD or since I have my departments in different cells here I am going to import the list from cells I'm just going to click on this and drag this I'll include the word Department as well because it's easy to remember and then I'm going to click on this import it's going to add it here then click on OK and ok now every time I type in department and then I drag this down I can see my list here and if I continue dragging I just get a repeated list now this isn't just for this workbook it's a global setting that applies to all my workbooks so let's open a blank workbook by pressing Ctrl n and this time I'm just going to start from the first Department it was Athletics let me just make sure I spell it correctly now I'm going to drag this down and I get the other departments what happens if I start from admin no problem just drag this down and you get the next departments and then it starts repeating itself over and over again if you don't want the header Department in there just skip that when you import your list next setting is to Define your preferred pivot table layout now this annoyed me for the longest time because check this out if we go ahead and insert a pivot table let's just insert it on a new sheet we get this standard layout from Excel I always had to go and make adjustments to this I like to have my report layout in tabular form I don't want to see these subtotals I had to go and turn this off I don't want to see that button there so I would go here turn that off as well and I'd also go to pivot table options and I would turn off autofit column widths on update because it annoyed me that every time I would refresh this this would jump and autofit you can avoid these steps all together if you have Office 365 you can define a custom layout for all your pivot tables across all your spreadsheets you go to file Options under data this time you're gonna see make changes to the default layout of pivot tables and you can edit that layout you could do it manually by defining what you want to see for subtotals for your grand totals the report layout whether you want it in compact form tabular or outline form and also your pivot table options that annoying autofit can be turned off globally here now you can do this manually if you want but you don't have to if you've already created a pivot table with the layout that you want all you need to do is import that layout here so you just click on import and the settings are brought over now when I go to pivot table option that tick mark is gone I'm going to click on OK and OK and now that's my default layout across all my workbooks whenever I go and insert a new pivot table let's this time just put it down here I get the layout that I just defined in case you want to update the colors of your pivot table you can do that from design under pivot table Styles you can use another layout you can also customize a layout based on your own brand colors to make this as default right Mouse click and set as default and this will remain the default for this spreadsheet okay so I know I said four settings but while I was recording this video I remembered two more you know how every time you open the Excel application you come to this standard view where you can pick one of your recent workbooks click on blank workbook or click on open here now you might personally find this useful but your working style might be different you might prefer to work via Explorer so you might just open Explorer and open whatever file that you want directly from here and if you open the Excel application you just want to get a blank workbook you don't want to go through that startup page you can adjust that setting by going to Options under General scroll all the way down here for startup options uncheck this box to show the start screen when this application starts and click on ok now every time you click on Excel you get a blank workbook the last setting I want to share with you is brand new so new that you probably don't have it yet if you go to excel options go to Advanced scroll a little bit down there's a new section called automatic data conversion it's currently in beta in office insiders you have the ability to change some of the default data conversion behavior of Excel One annoying one is removing leading zeros when you're entering numbers numbers like product codes phone numbers and so on one other useful one is this one right here when loading a CSV file or similar file notify me of automatic data conversions so if this is checked and it should be checked by default once you get these settings what happens when you open a CSV file is this so over here in file explorer I have the CSV file and we can see in the preview these are the product codes that I have the first two and the last one have zeros in front this one is just a standard number now when I open this with this feature on I automatically get this notification that by default Excel will perform the following data conversions in this file and it's going to remove the leading zeros so this is how it's going to look like by default so by the old default now we get this notification and we can choose not to convert this way the zeros come with and we don't run into any mistakes or errors when we're analyzing our numbers so that's the list of my suggested settings to update in Excel let me know in the comments if there is a specific one that you found useful or maybe some other ones that you'd like to share with us that's it for today thank you for being here thank you for watching and I'm gonna see you in the next video [Music]

Channel: Leila Gharani

Views: 178,756

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, excel options, excel settings, custom lists, autocorrect excel, default pivot table settings, default pivot table layout, global pivottable setting, excel startup options, excel create own lists, productivity tips

Id: jLHqGRpGOPk

Length: 10min 14sec (614 seconds)

Published: Thu Aug 25 2022