This post may contain affiliate links. Please read my disclosure for more info:

17 Excel settings you’re not using…but should be

Excel settings you're not using Thumb

Excel settings you're not using

If there is one thing Excel has in abundance, it’s settings.  To new and even experienced users, it can be overwhelming.  It is difficult to know which settings to use and when to use them.  In this post, I’d like to share a few settings you’re probably not using, but you might find useful.  They are in no particular order, so make sure you read them all, as you never know which one might be the nugget of gold you need.

Also, good news for VBA fans, the VBA code to control each setting is included.

 

1. Change the direction of Groups (outlining)

Groups are found on the Data Ribbon.

Excel Settings - Data Group

The Group Outline setting creates small boxes to collapse [-] and expand [+] rows or columns quickly.  By default, the Group Outline will be below when used with Rows or to the right when used with Columns.  But it doesn’t have to be this way, there is an option have the button appear at the top or the left.

Click: Data -> Outline -> More Settings (the small arrow).  The Settings window will open.

Excel Settings - Data More Settings

Toggle the options in the Settings window to have the Groups where you need them to be.

Excel Settings - Outline Settings

The direction is set on a sheet by sheet basis, all Groups on a sheet must be in the same direction.

VBA Code:

'The settings for Group Outline of rows can be below or above
ActiveSheet.Outline.SummaryRow = xlBelow
ActiveSheet.Outline.SummaryRow = xlAbove
'The settings for Group Outline of columns can be Right or Left
ActiveSheet.Outline.SummaryColumn = xlRight
ActiveSheet.Outline.SummaryColumn = xlLeft

 

2. Multi-threading

Multi-threading enables Excel to calculate multiple calculation chains at the same time, reducing overall calculation times.  But, before you break out the champagne and celebrate the end of slow calculations:

  • This feature was introduced in Excel 2007 and should be enabled by default, so you may already be getting the benefit of this.
  • There is some overhead involved, so shorter calculation times do become a little bit slower (but it’s hardly noticeable).  Test it with a Calculation Timer to see if you get a speed benefit.

To turn multi-threading on or off click File -> Option -> Advanced -> Formulas -> Enable multi-threaded calculation

Excel Settings - Multi-treaded

This is an application level setting and will apply to all open workbooks in the Excel session.

VBA Code:

'Turn off multi-threaded calculation
Application.MultiThreadedCalculation.Enabled = False
 
'Turn on automatic-multi threaded calculation
Application.MultiThreadedCalculation.ThreadMode = xlThreadModeAutomatic
'Set the number of processors if not set to automatic
Application.MultiThreadedCalculation.ThreadCount = 2

 

3. Hide comments

Comments are indicated by the small red triangle in the top corner of a Cell.

Excel Settings - Comments

Right-clicking on the Cell reveals the Comment related options.

Excel Settings - Comments Right Click

The Hide Comment option from the menu will hide the comment, but not the red flag.  If pasting an image of the cells into a PowerPoint slide, we want to hide the comment and the red flag.

To change how comments and flags are viewed click: File -> Options -> Advanced -> Display -> [Select relevant option]

Excel Settings - Display Comments

This is an application level setting and will apply to all open workbooks in the Excel session.

VBA Code:

Application.DisplayCommentIndicator = xlNoIndicator
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
Application.DisplayCommentIndicator = xlCommentAndIndicator

 

4. Don’t recalculate on Save

I’ve written previously about manual calculation mode.  However, even when in manual calculation mode, Excel will recalculate the workbook before saving it.  How annoying is that?  At the end of the day, when you’re ready to go home Excel tries to recalculate your slow workbook when you click Save.

It is possible to change this setting, so that Excel will save the workbook without recalculating first.  This option is not included in the standard Formulas Ribbon, it’s buried elsewhere in the settings.

Click: File -> Options -> Formulas -> Calculation options -> Recalculate workbook before saving

Excel Settings - Recalculate before saving

This is an application level setting and will apply to all open workbooks in the Excel session.  This setting is saved into the workbook, and will change Excel’s application settings if it is the first workbook opened in a session.

VBA Code:

'Before applying the setting to Calculate before Save the
'the calculation mode must be manual
Application.Calculation = xlManual

'The setting can be False or True
Application. CalculateBeforeSave = False
Application.CalculateBeforeSave = True

 

5. Hide scroll bars

Hiding the scroll bars can give your Excel dashboard a more professional look.  If it all fits into one screen, then the scroll bars are just screen junk, which we can get rid of.

To toggle the scroll bars on or off click File -> Options -> Advanced -> Display options for this workbook -> Show horizontal/vertical scroll bar

Excel Settings - Scroll Bars

Scroll bars are set at the workbook level, so the setting will be applied to all sheets in a workbook.

VBA Code:

'Hide scroll bars
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False

'Display scroll bars
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True

 

6. Setting the default file save location

One little setting can save a bit of time every day.  If you are regularly saving workbooks in the same location, then set the default location to that folder.  It might only save minutes per day, but that soon adds up.

Click: File -> Options -> Save -> Default local file location

Excel Settings - Default File Location

This is an application level setting and will be applied as soon as the OK button is clicked.

VBA Code:

Application.DefaultFilePath = "C:\Users\marks\documents"

 

7. Make worksheet invisible (very hidden)

We all know that worksheets can be visible or hidden.  But did you know they can be “very hidden”?  A very hidden worksheet is so hidden that it’s not even in the list of hidden worksheets.

This setting can only be applied in the Visual Basic Editor.  Press Alt + F11 to open the Visual Basic Editor.  Double click on the worksheet and change the Visible setting to xlSheetVeryHidden.

Excel Settings - Very Hidden

Note: There must be at least one visible worksheet in each workbook.

VBA Code:

ActiveSheet.Visible = xlSheetVeryHidden
ActiveSheet.Visible = xlSheetHidden
ActiveSheet.Visible = xlSheetVisible

 

8. Hide auto-calc checks

Excel tries to identify problems with your formulas.  It tells you if you’ve excluded a cell, or if formulas are inconsistent from one cell to the next.  This warning is indicated with a small green triangle (default setting).

Excel Settings - Error Checking Triangle

In my experience, Excel rarely gets this warning right.  Spreadsheets can be complex, but Excel’s logic for this warning is not complex enough.  Therefore, it is often better to turn off this setting, and much like the comments flag, it should be turned off when pasting cells as a picture into a PowerPoint slide.

To toggle the options, click: File -> Options -> Formulas -> Error Checking -> Enable background error checking

Excel Settings - Enable Background Checking

This is an application level setting and will apply to all open workbooks in the Excel session.

VBA Code:

Application.ErrorCheckingOptions.BackgroundChecking = True
Application.ErrorCheckingOptions.BackgroundChecking = False

 

9. Automatically insert decimal places

Number significance is an important issue.  For those working in large organizations, or in scientific fields, numbers are often displayed in thousands or millions.  It may be possible to simplify your data entry by having Excel automatically apply decimal places.

Click: File -> Options -> Advanced -> Editing Options -> Automatically insert a decimal point

Excel Settings - Automatic Decimal Places

After applying the settings above the number 1234, is entered into Excel as 12.34 (2 decimal places automatically applied).

VBA Code:

Applicaiton.FixedDecimal = True
Application.FixedDecimalPlaces = 5

Applicaiton.FixedDecimal = False

 

10. Hide row numbers & column letters

Hiding row numbers and column headers can significantly tidy up the Excel interface for users receiving your workbook.  It provides similar benefit to hiding the scroll bars (see above).

This option is available in the Ribbon.  Click: View -> Show -> Headings

Excel Settings - View Headers

The option is also available in the File -> Options -> Advanced, but it is much easier to access the Ribbon.  Unlike the scroll bars, this setting is on a sheet by sheet basis.

VBA Code:

ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHeadings = False

 

11. Alert for time consuming operations

When making changes to large numbers of cells Excel will show a warning message.  By default, Excel will show this when it affects over 33,554 cells.  But what if you regularly deal with spreadsheets where this error message appears?  Why not change the setting and have Excel warn you when the affected cells are higher (or lower, depending on your needs)?

Click: File -> Options -> Advanced -> Editing Options -> Alert the user when a potentially time consuming operation occurs

Excel Settings - Time Consuming Operations

This is an application level setting and will apply to all open workbooks in the Excel session.

VBA Code:

Application.LargeOperationCellThousandCount = 100000

 

12. Display Sheet Tabs

It is possible to hide all the sheet tabs for an individual workbook.  On opening, the active sheet will be visible, but the user will not have tabs at the bottom to navigate between sheets.

To toggle the option, click: File -> Options -> Advanced -> Display options for this workbook -> Show sheet tabs

Excel Settings - Sheet Tabs

This option is applied to each workbook individually.

VBA Code:

ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayWorkbookTabs = False

 

13. Fix scroll range (VBA)

Worksheet protection is not the only way to prevent users from clicking or scrolling into protected areas of the sheet.  Fixing the scroll area prevents users from accessing any Cells outside of the area.

This setting can only be applied in the Visual Basic Editor.  Press Alt + F11 to open the Visual Basic Editor.  Double click on the worksheet and change the ScrollArea setting to the range of cells a user can access.

Excel Settings - Scroll Area

This setting is applied on a sheet by sheet basis.

VBA Code:

ActiveSheet.ScrollArea = "B4:E30"

 

14. Gridlines

Have you ever tried to give you worksheets a white background by filling all the cells with white?  I did it for years.  But it never seemed to work when cells were dragged or moved.  Eventually, it occurred to me that I could just hide the Gridlines . . . much easier.  From the Ribbon select View -> Show -> Gridlines

Excel Settings - View Gridlines

This setting is applied on a sheet by sheet basis.

VBA Code:

ActiveWindow.DisplayGridlines = True
ActiveWindow.DisplayGridlines = False

 

15. Hide outlining

If you have used Group Outlining during the development phase, but want to hide the unsightly [+] and [-] symbols from the user, you can.  This setting does not remove the outlining.  The outlining remains, but the [+] and [-] symbols are not displayed, and as a result the user cannot use them.

Click: File -> Options -> Advanced -> Display options for worksheet -> Show outline symbol if an outline is applied.

Excel settings - show outlining

This setting is applied on a sheet by sheet basis.

VBA Code:

ActiveWindow.DisplayOutline = False
ActiveWindow.DisplayOutline = True

 

16. Mark document as final

Let everybody know that a workbook has been finished by marking it as final.  Any user opening a file marked as final, we receive the following message at the top of the screen.

Excel Settings - Mark as Final Warning

Click: File -> Info -> Protect Workbook -> Mark as Final

Excel Settings - Mark as Final

VBA Code:

ActiveWorkbook.Final = True
ActiveWorkbook.Final = False

 

17. Setting standard font and size

Probably the most obvious setting to change is the font and font size.  This application level setting will apply to all new workbooks created.

Click: File -> Options -> General ->  Use this as the default font / Font Size

Excel Settings - Default Font

After making the change Excel requires a restart for the change to take effect.

Excel Settings - Default Font Warning

VBA Code:

 Application.StandardFont = "Calibri"
 Application.StandardFontSize = "10"

 

Conclusion

There you have it, 17 excel settings which most Excel users are not using, but could benefit from.  I don’t expect you to apply all of them, or even remember all of them.  Often, just being aware these settings exist will enable you to return to them at a future point when you need to apply them.

Save

Leave a Reply

Your email address will not be published. Required fields are marked *