Did you know that you don’t have to put up with many of the default settings you get with Excel if they don’t fit with the way you work. Here are 8 of my favourites. All these options are available from the File menu and options.
1. Changing the default save location
When you save it always defaults to saving in documents but how many of us save our files here? By changing this location to something more appropriate will save a lot of time given the number of times we save throughout the day. It is available from the save option on the left-hand side of the options. Just change the default local file location to something you prefer. This can be changed in a similar way in the other Microsoft products.
2. Automatic saving
For years there has been an autorecover option that has prevented too much work being lost if you forget to save and the computer crashes or the battery power goes. In Office 365 there is also the option of autosave one drive or SharePoint files by default. This check box option is situated right at the top of the save options and by default is switched on. However, if you are used to playing around with your spreadsheet and then closing without saving if it goes wrong you will find that the file has already saved with your errors included. By switching this off you can work with it as before. This option also exists in Word and PowerPoint so you may choose to turn the option off here too. This can be temporarily switched on and off from the slider at the top left corner of the screen but this is not a permanent change.
3. Getting rid of extra decimal places permanently
It’s straightforward to change how many decimal places are viewed using the formatting options but if you want them properly rounded then you either need to use rounding functions or the default option of set precision as displayed which we will look at here. To use this feature format the numbers as you need, tick the set precision as displayed option from the advanced option on the left and the calculating this workbook section. The numbers will now be stored as they were viewed. This option is workbook specific. To ensure you don’t accidentally lose more decimal places by clicking on a formatting option that reduces them I suggest switching off the set precision as displayed option at this point.
4. Setting calculations to manual
If you are working with large spreadsheets with lots of complicated formulas, you may find that it takes a while for the calculations to update. When lots of changes are being made this can become a slow tedious process. If, instead, the calculations are changed from automatic update to manual then they will not change until the Refresh icon on the formulas tab is clicked. There is, however, the choice to automatically recalculate before save. If this is selected, then the saving could take longer than usual but it does guarantee that periodically the calculations are being updated. This is found in the Formulas section within Options.
5. Error checking options
Excel is set up to make us aware of issues with our data that might cause us problems but some of these options are not relevant because of what we are doing with the package. By switching some of these off it means the relevant information stands out. Otherwise, it can become a “can’t see the wood for the trees” scenario. These options are found at the bottom of the formals section.
6. Changing default formatting
If you always need your font type to be different to the norm or need to keep changing the size then these can be changed to your requirement from General options then going to the when creating new workbooks section. The default view and number of sheet tabs can also be set.
7. Stopping flash fill from automatically completing columns of data
Flash fill has been an option in Microsoft Excel since 2013. It looks for patterns in your data and once the first entry has been completed and the next started it will suggest what the rest of the column should be if it sees a potential pattern. Sometimes this is not what you want and it can become annoying. If this happens just switch it off!! It resides in the editing options section from the advanced options. Flash fill can still be used by using the flash fill icon on the data tab once the first entry has been completed if you want to temporarily override this.
8. Spelling options
We always assume that if we have spell checked our document that there will be no more spelling errors. Did you know that words that are fully entered in upper case or that have numbers in them are not checked. It makes sense as they could be abbreviations or codes of some sort. But what if we need them checked? From proofing options, and when correcting spelling in Microsoft programs, just untick the relevant boxes if you need to.