Excel Options
September 30, 2020
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:
- Click on Customize Ribbon
- Make sure you’ve selected New Group
- Click New Tab
- Select a command and press Add
- 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.
