Set & Forget: Take This 5-Step Excel Option Healthcheck

If you’re reading this, there’s a good chance you spend a lot of time on Microsoft Excel.

Throughout a typical day, you are repeatedly creating new Excel files yourself, or opening up files that colleagues, friends or family members send through to you.

And you have probably picked up a few personal habits along the way.

Some habits are good, but they can also be time-consuming.  For example, if you need to format and present documents in a certain way, you might find yourself always updating the fonts to whatever your marketing team requires.

You may also be clicking through the menus in the Excel Ribbon at the top of the screen, always selecting the same three or four options because a lot of your work is similar or repetitive.

Or, if you’re like me, you might find yourself accidentally selecting the F1 key and opening up the Help menu, when all you wanted to do is edit a cell (F2) or hit the Esc key.

(Hint: this healthcheck will show you how to stop that happening, but it’s a little bit technical to implement!)

There are plenty of Excel options that can streamline your Excel experience; and to help you I’ve compiled this quick healthcheck so you can set it up right, forget about it, and get back to being efficient.

Before we get started…

All of the Microsoft Excel options in this post can be found by clicking on the File button on the top left, then selecting the Options button:

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

 

Step 1. Set Up Default Font Style and Size, and Number of Sheets

This may come as a huge surprise for many Excel users – you don’t have to use Calibri as your default font.

You don’t even need to have three sheets included in every new Excel file you create, either!

Many businesses have style guidelines that require a certain font or size in any email or written communication, and you may want to adopt the same rules in any spreadsheets that are sent to your customers.

In Excel 2007, these options are in the Popular tab in the Excel Options menu, but in 2010 it’s called the General tab:

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

If you use macros a lot, you’ll want to enable the Developer tab for easy access to macro-related options.  In Excel 2007 the option is right there on this page, however if you’re in Excel 2010 you will need to go to the Customise Ribbon page, and tick the Developer menu on the right side.

Step 2. Set up Formula options

If you’re using Excel for analysis of large data sheets (eg: more than around 100,000 cells) then any formula updates will likely take a long time.  If you change a formula or cell value, Excel will recalculate all formulas which can take a long time, especially if you have a few PivotTables or complex formulas which involve a lot of cells.

Depending on the size of the data, these formula recalculations can take up to a minute or more, which is painful if you’re trying to make lots of small changes to the file!

If this is an issue to you, on the top left of this page of options, set Workbook Calculation to Manual.  You’ll need to press F9 every time you want to force an update of all formulas, meaning that you can focus on entering formulas or making any other changes, and leave the updating until the end.

Let’s go to the Formulas page in the Excel options to take a closer look at some of these options:

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

Other options you should look at on this page are:

Working with formulas > Formula AutoComplete

This should be ticked – it will do things such as closing brackets at the end of formulas if you hit the Enter key too early.

Working with formulas > Use GetPivotData functions for PivotTable references

If you use PivotTables a lot and make formulas that refer to static cells in a PivotTable, then consider disabling this option.  To show what this option does, let’s see what happens when you select a cell in a PivotTable while entering a formula:

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

And if it’s disabled:

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

You can see the difference in the formula that Excel automatically creates at the bottom of each screenshot.

Step 3. Fine-tune Your AutoCorrect Options

Let’s move on to the Proofing sheet.  Select the AutoCorrect Options… to open up a new dialog box.

In the first tab, my suggestion is to leave everything enabled but you may want to disable some of these based on your personal style.

Looking at the second tab (AutoFormat As You Type), you may want to consider disabling the “Internet and network paths with hyperlinks” if you don’t want Excel to create hyperlinks for you.  The other options relate to Excel Tables – leave them enabled because they’re just too helpful!

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

Step 4. Fix Your Default Save Location

If you don’t like where Excel saves new files by default, you can choose a different folder.  Everything else can be left as default on this sheet.

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

Step 5. Customise the Quick Access Toolbar

If you aren’t using the Quick Access Toolbar, you are spending too much time navigating through Excel menus.

The list of icons at the very top of the Excel window can be customised to your liking, so you can select your most commonly used commands in a single click!

Even better, these icons are automatically given keyboard shortcuts based on their position in the toolbar.  The first icon can be accessed with Alt+1, the second icon with Alt+2, etc.

In Excel 2007, the QAT is customised in the Customize sheet.  In 2010 it’s more appropriately called the Quick Access Toolbar sheet.

Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com

Simply select the command/option you want to have as a shortcut, and click Add to move it into the QAT.  If you don’t see the option in the list, the dropdown menu on the top left can help you work through each of the main menu categories.

Here are some suggestions for what should go in the Quick Access Toolbar:

  • Filter Data / Clear Filter – if you’re constantly filtering and unfiltering data ranges within Excel, you’ll definitely want easy access to these options. Clear Filter is especially helpful if you tend to filter on multiple columns at the same time, and need to make sure that you’ve unfiltered the data completely.  If the Clear Filter icon is greyed out, it means that the data set isn’t filtered at all, which is really helpful.
  • Custom Sort – similar to filtering, including the Sort option in the toolbar is a bit of a no-brainer.
  • Freeze Panes – I’m a massive supporter of freezing the top few rows or columns, so I can easily understand what I’m looking at.
  • Insert PivotTable – PivotTables are great for quick & easy data analysis, and having this icon (or an Alt + number shortcut) is crucial if you’re constantly creating them to complete your work.
  • Align Text Left / Right / Center – I’m including these here because as far as I know, there’s no simple shortcut to left-align, right-align or center your text (unlike in Microsoft Word). I suggest adding them to the QAT in the same order that they appear in the menu (Left, then Center, then Right) so that the Alt + number shortcuts are in that order too.

Customising the Quick Access Toolbar is just one of the tips in the FREE eBook “10 Simple Microsoft Excel Tips to Save You Time”.  Get the free eBook now!

(Optional) Disable the F1 Key To Stop Opening the Help Menu (Slightly Advanced)

You really should be using F2 to edit cells whenever possible.

However, if you do, you might find yourself accidentally hitting the F1 key occasionally, which opens up the Help menu.

Most people never need to use the Help menu in Microsoft Excel, so disabling the F1 key entirely (only within Excel!) can avoid any unwanted interruptions from getting your work done.  This trick will take a little time to set up, but I’ve found it well worth it in the long run.

This trick works by storing a small piece of VBA code in a workbook called the Personal Macro Workbook, which is opened automatically every time you start Excel, but is normally hidden from view.  The PMW stores any macros or VBA code that you want to apply to every Excel file that you use, and for this trick, we’ll enter some basic code that tells Excel what to do when the F1 key is pressed – which is nothing!

If You Have Stored Global Macros in Excel Before, Skip This Section

Excel users that have created macros that apply to all Excel workbooks will have the Personal Macro Workbook (PMW) created already, and can move to the next section.  Fortunately, it’s not that hard – we’ll create a dummy macro which will create the PMW, then get on with disabling that pesky F1 key.

To create the PMW:

  • Start recording a macro by clicking on this icon if it’s visible in the lower-left corner of your Excel window. Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.comIf it’s not there, right click in that space to bring up the Customise Status Bar menu.  Select the Macro Recording button to make it appear.
  • A dialog box will appear, asking you for the macro name and location. The name can be anything, but it’s important to select Store macro in: Personal Macro Workbook.
  • The Record Macro button will have changed to a blue square – click this to stop recording. All we needed to do was create a macro which didn’t do anything, remember!
  • That’s it!

If You Have Created the Personal Macro Workbook Already

Now all we have to do is enter some VBA code into the Personal Macro Workbook.

  • Press Alt+11 to get to the Visual Basic Editor.
  • Expand “Personal.xlsb” from the menu on the top left, and double click on “ThisWorkbook”. It might be hidden within a subfolder called Microsoft Excel Objects.
  • Copy/paste the following three lines of code into the window that appears:
Private Sub Workbook_Open()
 Application.OnKey "{F1}", ""
 End Sub
  • Close Excel completely, and make sure you save when you are asked to save Personal.xlsb.Set and Forget: Take This 5-Step Microsoft Excel Option Healthcheck | ExcelEfficiency.com
  • Restart Excel, and press F1 to test if the code works.

Conclusion

Now that your settings are updated just how you need them, you’re free to get back to your Excel work being more productive than ever!

Did you change any additional Excel options as you were working through this healthcheck?  Let us know in the comments!

You may also like...

2 Responses

  1. Henry says:

    Did it step by step on excel 2016. But still cannot disable F1 help function.

Leave a Reply

Your email address will not be published. Required fields are marked *