Excel Tip of the Week #27 - Excel options

This week, we have a General User post in which we’ll take a look at how to customise Excel options to make best use of the programme.  This Tip’s a little longer than usual as there’s a lot to talk about.

The layout of the options between Excel 2010/2013 and Excel 2007 is different, but many of the same options are available.  As 2010/2013 are more current, I’ll be using that in my examples.  To access the Options menu, go to File => Options. (In 2007: Office button => Excel options).

Under ‘General’ are some useful options for determining what new workbooks in your Excel will look like.  You may have a favourite font, or may find that you are annoyed by the default to create new workbooks with three sheets – you can see on my Excel that I have it set to start with just one.

You can also choose to open in Page Layout or Print Preview mode if that’s useful to you.

This is an excerpt from the ‘Formulas’ options.  You can set how your workbook is calculated.

The default is to calculate automatically – all formulas are recalculated whenever data is added or changed.

If your workbook is very large and formula-heavy, it may be more efficient to change to Manual; this will mean that the workbook will only update formulas when you instruct it to.  The option to recalculate the workbook appears at the bottom left of the screen (where it will normally say ‘Ready’).  Alternatively you can press F9 to recalculate the workbook.

This options section also has a list of which kinds of errors are automatically flagged by Excel – you can add or remove some if you don’t find that particular error a problem.  Do remember that this is an Excel-wide setting so don’t unselect a type of error unless you don’t want to be told about errors of that type in all your Excel workbooks.

Under the ‘Save’ menu are options that set how often Excel backs up your document in case it crashes unexpectedly, as well as where these versions are saved.  Saving more frequently will mean that the document is better protected but will also slow down your workflow slightly.

Added in Excel 2010 is a feature whereby the Autosaved version of a file is kept automatically when the workbook is closed without saving; this is designed to help reduce errors where a workbook is closed inadvertently.  If this is not desired (e.g. if the workbook is extremely large or its content is sensitive), you can disable it here.

There are many more options available under ‘Advanced’; most are very specialised but you may find useful:
- Toggle use of American-style numbers (1,000.00) and European-style (1.000,00)
- Alter the direction the cursor moves when you press Return (default is down)
- Increase or reduce the number of Recent Documents shown on the File menu

Finally, the ‘Customize Ribbon’ and ‘Quick Access Toolbar’ options let you choose to add or remove certain features from what’s immediately visible within Excel.  This includes customising what appears on the Home menu as well as what appears in the QAT (the list of icons at the extreme top left of the screen).  You can also right click any item on the Ribbon to add it to the QAT directly.

These are just a sample of the many hundreds of options and customisable elements that you can use to make your version of Excel, your own.  It’s worth reading through all the options sometime, so you are familiar with what’s possible.

Previous post - LEN, FIND, SEARCH 
TOTW index
Next post - Text to columns

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.