Bottom Line: Learn how to customize 10 essential settings in Excel to improve your workflow, save time, and enhance productivity.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I use in the video.

Excel Settings.xlsxDownload Excel Settings Worth KnowingExcel offers a wide range of customizable settings that can streamline your workflow and improve productivity. With over 200 options to choose from, it can be hard to know where to start. In this post, we’ll focus on 10 essential settings that will save you time, simplify your tasks, and enhance your overall Excel experience.

  1. Create Custom Lists for AutoFillHave you ever used the Fill Handle to auto-complete months or days? You can customize this feature by creating your own custom lists. Here’s how to set it up:

  2. Go to: File > Options > Advanced > Edit Custom Lists

  3. Then create a new list by typing your entries or import a list from an existing sheet.

With this setting, you can easily auto-complete regions, product names, or any other list specific to your work.

  1. Change the Gridline Color for Better VisibilitySometimes borders can blend in with the default gridlines, making them hard to see. You can change the color of gridlines for better contrast:

  2. Go to: File > Options > Advanced > Gridline Color

  3. Then choose a bright color to help differentiate the gridlines from your custom formatting.

This gridline setting applies only to your current worksheet, not the whole workbook.

  1. Turn Off Annoying Page Break LinesWhen previewing or printing your Excel sheets, page breaks can appear as dotted lines, which may be distracting. Here’s how to turn them off:

  2. Go to: File > Options > Advanced > Display Options for this Worksheet

  3. Then uncheck “Show page breaks.”

This will remove those dotted lines and give you a cleaner workspace.

  1. Bypass the Backstage View for Faster Saving and OpeningBy default, pressing Ctrl` + `S or Ctrl` + `O opens the Backstage view. You can skip this and open the standard File Explorer instead:

  2. Go to: File > Options > Save

  3. Then check “Don’t show the Backstage when opening or saving files with keyboard shortcuts.”

Now you can quickly save or open files without the extra step.

  1. Turn Off Structured References in Excel TablesExcel tables use structured references by default, which can confuse users unfamiliar with them. You can switch to regular cell references:

  2. Go to: File > Options > Formulas

  3. Then uncheck “Use table names in formulas.”

This way, formulas will use standard cell references for a more familiar approach.

If you're interested in more details on this topic, check out this tutorial.

  1. Set a Default Pivot Table LayoutIf you don’t like the default compact layout of PivotTables, you can change it to a tabular format:

  2. Go to: File > Options > Data > Edit Default Layout

  3. Set your preferred PivotTable format (e.g., tabular layout with blank rows).
  4. If you'd like to change even more settings, you can do so by clicking the PivotTable Options button.

Every new PivotTable you create will automatically adopt your chosen layout.

  1. Disable Automatic Grouping of Dates in PivotTablesBy default, dates in PivotTables are automatically grouped by years, quarters, and months. To disable this:

  2. Go to: File > Options > Data

  3. Then check “Disable automatic grouping of dates in PivotTables.”

This allows you to display individual dates without automatic grouping.

  1. Ungroup Dates in AutoFiltersWhen using AutoFilters, dates are collapsed by default, making it harder to find individual dates. You can change this:

  2. Go to: File > Options > Advanced

  3. Then uncheck “Group dates in AutoFilter menu.”

Now all dates will appear expanded, making it easier to filter specific dates.

  1. Customize the Quick Analysis ToolThe Quick Analysis tool provides an easy way to analyze data. If you find it appearing too often:

  2. Go to: File > Options > General

  3. Uncheck “Show Quick Analysis options on selection.”

You can still access the tool by pressing Ctrl` + `Q whenever you need it.

  1. Change Excel’s Default Workbook SettingsAnd finally, if you want all new Excel workbooks to open with only one sheet, want to collapse the search box, or prefer to use a specific font size:

  2. Go to: File > Options > General

  3. Then adjust settings to your preference..

This makes it easier to create workbooks that match your preferred style without manual adjustments.

Bonus: Automatically Keep Leading Zeros in NumbersWorking with numbers that have leading zeros, like ZIP codes? Excel automatically removes these unless you store the data as text. But there’s a new option in Beta to make this easier:

  1. Go to: File > Options > Data > Automatic Data Conversion
  2. Then uncheck “Remove leading zeros and convert to number.”

Now Excel will keep leading zeros without the need for apostrophes or manual text formatting.

This post dives deeper into Data Conversion settings, if you are interested in learning more.

ConclusionThese 10 Excel settings can make a big difference in how efficiently you work with data. Whether it's customizing lists or formatting tables, these tips will save you time and simplify your workflow. I encourage you to explore Excel options and tweak the settings to suit your needs.

What Excel settings do you find most useful? Let us know in the comments!

Link to post: Hidden Excel Settings that Save Time and Frustration

See All 107 Episodes of "Excel Campus"