This article is a part of our blog posts on how to build simple, readable and better financial models. The objective is to teach you how to be more productive, make lesser errors and make it easy for others to use the financial models build by you.

The first step in building a successful financial model is to setup your excel environment for increased productivity. There are several settings in excel and changing the settings listed below will help you become more productive and efficient.

1. Disable the Option to Allow Editing Directly in Cells

By default, excel allows you to edit the formulas directly in the cell as well as in the formula bar. The option to edit directly in cells is handy as it allows you to edit the formulas from right within the cell. However, while building a financial model it is better to turn this setting off. Once turned off, excel will allow you to edit a formula only in the formula bar.

Benefits of turning off ‘Edit directly in Cells’  

There are many benefits of turning off this setting.

• One benefit is that when you double click a cell containing a formula, it will select all the cells involved in that formula. You can then press tab to jump to each selected cell.
• Another benefit is that if you double click a cell which contains a formula with a link to another workbook, it will automatically open the other workbook and highlight the linked cell.

Turning off the ‘Edit Directly in Cells’ Option 

Follow the following steps to turn this off.

Excel 2003: Tools -> Options -> Edit -> Un-tick ‘Edit directly in cell’

Excel 2010: File -> Options -> Advanced -> Un-tick ‘Allow editing directly in cells’

Click File menu, and then click Options. In the Advanced category, clear the Allow editing directly in cells check box to turn in-cell editing off.

2. Move Selection After Enter

By default, when you’ve entered a formula and you press enter, excel will automatically move the cursor to the next cell. If you’re doing some data entry, this default behaviour is fine. However, if you’re building financial models, and you need to review every formula you write then it would be better if you stayed on the same cell. To change this setting, use the following steps:

Excel 2003: Tools -> Options -> Edit -> Un-tick ‘Move selection after Enter’

Excel 2010: File -> Options -> Advanced -> Un-tick ‘After pressing Enter, move selection’

3. Change Macro Security

Since developing financial models involves working with macros, you need to change the Macro settings to unblock the macros. The recommended setting is to set your spreadsheets to ‘Medium’ macro security. Change this setting as follows:

Excel 2003: Tools -> Macro -> Security -> Select ‘Medium’

Excel 2007 and above: Security is set to ‘Medium’ by default.

In excel you also need to unhide ‘Developer’ tab which contains many macro tools and options. This setting is also available under Excel Options.

4. Install Analysis Toolpak

Excel comes with a powerful ‘Analysis Toolpak’ add-in which contains various data analysis functions. The analysis tools allow you to perform various statistical data analysis. It’s like a set of pre-designed macros. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables. Enable the Analysis Toolpak add-in by following these steps:

Excel 2003: Tools -> Add-Ins -> Tick ‘Analysis ToolPak’

Excel 2007 and above: Already enabled by default but you can check by visiting Excel options -> Add-in

Note that if the Analysis Toolpak add-in is not listed in the list of add-ins, you may have to install it by following the onscreen instructions.

5. Show/Hide Office Ribbon

In Excel 2007 and above, the Office Ribbon, clutters the screen and takes up a lot of screen space. It is recommended to keep the ribbon hidden by working on the spreadsheet and display it when you need it.

You can hide/show the ribbon with these keystrokes: Ctrl +F1

6. Calculation Mode

By default, excel automatically recalculates the formulas on the spreadsheet everytime you make a change. For financial modelling, it is preferred to change this setting to ‘Manual calculation mode’. This way you can have more control over when the calculation occurs. Change this setting with the following steps:

Excel 2003: Tools Options -> Calculation Select ‘Manual’

Excel 2010: File -> Options -> Formulas -> Select ‘Manual’

Note that you can also recalculate the formulas anytime, by pressing the F9 key.