What is Excel Options?

The Excel options dialogue box is accessed via the file menu. The keyboard shortcut is ALT, F, T. This is where the settings which drive Excel’s behavior are stored. The following visual and screenshots give a useful selection to consider. However, there are many other settings in Excel Options that may be helpful for specific situations.

Type Controls Settings to consider
General Font size, user name, etc. User choice
Formulas Formula performance Enable iterative calculation: off
Error handling Enable background error checking: off
Proofing Text control Ignore internet and file addresses: off
Save Where, how many, how often Don’t show the backstage: on
Language Languages used Set the default languages
Advanced Editing After pressing enter, move selection: off
Editing Allow editing directly in a cell: off
Editing Extend data range formats and formulas: off
Editing Enable AutoComplete for cell values: off
Cut, copy and paste Show paste options buttons when content is pasted: off
Cut, copy and paste Show insert options buttons: off
Display User choice but note gridlines and comment display
Customize ribbon Add delete tabs and commands User choice
Quick access toolbar Works with ALT and a number Commands given shortcut number in order added
Add-ins Increased functionality User choice
Trust center Security User choice

In the formulas section, the user can change the calculation settings to be automatic (most common) or manual (important for large files). Automatic calculation means that Excel will recalculate a formula and all its dependencies each time there is a change. Manual calculations, on the other hand, only recalculate formulas when the user specifically requests a calculation. This can be actioned using the F9 keyboard shortcut.

Iteration Settings

The third option, “Automatic except for data tables”, is appropriate for files with extensive use of data tables.

The iteration settings control how Excel responds to circular references. It is best practice to work with this setting unchecked (turned off). Often when using an existing file, as opposed to building a model, it is necessary to have this setting checked (turned on) to work effectively with the completed model.

In the Advanced tab under the Display Settings, you can select to show gridlines, the formula bar, row and column headers or to make these features invisible.

Comments can be presented in full, shown by an indicator (usually a red triangle in the top right-hand cell corner) or invisible, using settings in this section of the dialogue box.

Turning off some of the default Excel settings, such as the cursor moving down one cell after Enter is pressed and other autocomplete functionalities, allows the user to control Excel’s behavior.

One very useful setting is to stop Excel performing edits within the cell but in the formula bar instead. This is helpful when the file is large and complex since it is much easier to see what is going on when a complex formula is displayed in the formula bar rather than in the cell itself. This is done by having Allow Editing Directly in cells unchecked.

Some of the keyboard shortcuts for sections of the Excel Options dialogue box are not user friendly so clicking is always an option. Pressing TAB advances forward from field to field while SHIFT TAB goes backwards.

Customizing the Ribbon

Customizing the ribbon allows you to create your own tabs and the commands of those tabs. With the commands, you will always store it in a group. Excel allows you to create as many groups as you want so you can ensure that the tab is organized.

Customizing ribbons also allows you to add commands to default tabs if you create a custom group in the tab. Here are the steps to do that:

  1. Click on Customize Ribbon
  2. Make sure you’ve selected New Group
  3. Click New Tab
  4. Select a command and press Add
  5. Once you’ve selected all the commands, press OK

If you don’t see your desired command, click on Choose commands from the drop-down box and select All Commands.

Featured Product