Excel: The Must-Know Excel Settings To Customize | Part 2

In our previous tutorial, you've seen 5 settings in Microsoft Excel that may help you get a better spreadsheet experience.

In this second part of the tutorial, you will find 5 more advanced settings that may be your timesaver and even protect your Excel files from crashing!

Circular References

In Excel, a circular reference occurs when you enter a formula in a cell that links directly or indirectly to itself. This creates an endless loop of calculations between multiple cells.

Usually we create circular references by mistake, but their impact can be destructive. The processing speed of Excel will largely slow down and the endless iterative calculations may directly crash your workbook. Pretty scary, huh? Here's a tip to help you!

Click File > Options > Formulas. In the "Calculation options" section, make sure the "Enable iterative calculation" option is unchecked.

change iterative calculation option in excel setting

This will prevent Excel from doing iterative calculations every time a circular reference is created, and you will be informed with an error message at the same time.

I recommend to keep this box unchecked at all time. Note that once you turn it on you will not get any more warnings about circular references.

Of course, this setting will just help us detect circular references, but not to fix them. I will share with you some tips to kill circular references in the future!

Double Click to Audit Formulas

By default, Excel allows you to edit the data of a cell when you double click on it. But when you do some formula auditing, you may prefer to check the elements of the formulas rather than edit the data itself.

Here's an example, when you double click on the net salary amount (C4), by default the formula Net salary = Gross salary (C2) * (1 - Tax rate(C3)) will show in the cell which allows you to edit it directly.

double click a cell to edit data in excel

But instead, you may want to locate the elements "Gross salary" and "Tax rate" to audit the formula. Like this:

double click a cell and all elements are highlighted in excel

To do so, click File > Options > Advanced. In the "Editing options" section, uncheck "Allow editing directly in cells" box.

change editing options in excel setting

Now if you double click the cell C4, the cells of Gross salary (C2) and Tax rate (C3) will be highlighted.

double click a cell and all elements are highlighted in excel

Here you can press the tab key to navigate across these 2 elements.

double click a cell and navigate across elements in the formula in excel

This is definitely a timesaver when auditing formulas in your spreadsheets. It allows you quickly locate the elements inside the formulas. So how to edit the formulas with this setting? It's simple: press F2 on your keyboard or click in the formula bar.

Quick Access Toolbar

The Quick Access Toolbar is a huge timesaver for frequent Excel users. It is located either above or below the main ribbon menu in Excel.

quick access toolbar in excel

As its name implies, this toolbar gives quick access to useful commands in Excel. And this can be customized!

For example, you may want to add the freeze panes command into the Quick Access Toolbar. To do so, click File > Options > Quick Access Toolbar.

customize quick access toolbar in excel setting

Here, select Freeze Panes in the list on the left side, then click on the Add button. This command will be added to the right-side list.

Press OK and the freeze panes command will appear in your Quick Access Toolbar!

add freeze pane to the quick access toolbar in excel

Developer Tab

The Developer tab is hidden by default. Advanced users may want this displayed on the ribbon menu when working with macros. Here I will guide you to add this tab.

Click File > Options > Customize Ribbon, then check the "Developer" box in the "Main Tabs" list on the right side.

add developer tab in excel option setting

Press OK and the Developer tab will appear on the ribbon!

show developer tab in excel ribbon

If you need to remove this tab, just uncheck the "Developer" box in the settings.

Enable Macros

Macros are small computer programs that help you automate tasks within Excel. When you open a workbook containing macros, you may see a warning message:

warning message about macros disabled in excel

That's because Excel disable all macros by default. You can change this setting to enable macros all the time.

Click File > Options > Trust Center, then click on the Trust Center Settings button on the right side.

vba macros setting in excel

Once you are inside the Trust Center, click Macro Settings tab on the left. Here, select "Enable all macros" (not recommended; potentially dangerous code can run) option.

vba macros setting in excel

You may wonder if it's a good idea to activate this "not recommended" option. Great question, since macros can be dangerous if they are used to steal data or hijack computers.

If you only use macros occasionally, the second option "Disable all macros with notifications" may be more suitable.

But for heavy macro users, it makes sense to select "Enable all macros". Just keep in mind that with great power comes great responsibility, and be very careful about macros coming from other people.


And that's it for this tutorial! Now you can customize these settings and become an advanced user :-)

Here's a summary of the Excel settings covered in the second part of this tutorial:

Check out the other 5 useful settings in the first part of this tutorial if you haven't read it. If you know other great tips, feel free to let us know!

Other articles you might like on ExcelFrog.com