Download the Cheat Sheet with important Excel Preferences 👉 HERE and follow along.

There are several default Excel settings you should know about to enhance your productivity. We’ve collected the most important ones for you to review now. These settings can help you work more efficiently and avoid common issues.

Setting 1: What Happens When You Press ENTER

When you press ENTER in Excel, three things happen:

  1. You exit ‘editing’ mode.
  2. The data is saved in the cell.
  3. The cursor moves to the next row in the same column.

Many users change this last action.

By default, the cursor moves down, which is great if you’re entering data vertically. But if you’re entering data horizontally, this can be annoying. You’ll need to move the cursor back to the same row and over to the next column.

Some users prefer to stay in the current cell to do things like fill down a formula or format text.

💡 Quick Tip: To stay in the current cell, press CTRL + ENTER instead of ENTER.

If you want to change what happens when you press ENTER, follow these steps:

  1. Click on the File tab.
  2. Select Options at the bottom left.
  3. Go to the Advanced category.
  4. In the Editing Options section, find the dropdown for “After pressing Enter, move selection.” Choose your preferred direction or uncheck the box to stay in the current cell.

Reviewing and adjusting this setting can save you time and make your data entry process smoother.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Setting 2: Shortcuts for Long Phrases

If you often type the same long phrases, like a company name, it can take up a lot of time and lead to typos. For example, typing “ACME Sprockets and Widgets, Inc.” repeatedly can be tedious.

Wouldn’t it be easier to type “ASW” and have Excel change it to “ACME Sprockets and Widgets, Inc.”? This is possible with the AutoCorrect feature.

What is AutoCorrect in Excel?

AutoCorrect is a feature that automatically performs a spell check in Excel. But it can also replace abbreviations with full phrases. It saves time and reduces the risk of typos by allowing you to define shortcuts for frequently used text.

How to Set Up Shortcuts for Long Phrases in Excel

  • Click on the File tab.
  • Select Options at the bottom left.
  • Go to the Proofing category.
  • Click on AutoCorrect Options.
  • In the Replace field, type your abbreviation (e.g., “ASW”).
  • In the With field, type the full phrase (e.g., “ACME Sprockets and Widgets, Inc.”).
  • Click Add to save the entry.

Now, when you type “ASW” and press ENTER or Space, Excel will replace it with the full phrase.

💡 Pro Tip: If you want to use both the abbreviation and the full phrase, add a small modifier to the abbreviation. For example, use “msdnx” for “Microsoft Developer Network” and “MSDN” for the short form. The “x” stands for “expanded.”

This small trick can save you a lot of time and effort, making your work in Excel much more efficient.

Setting 3: Create Custom Lists

The Fill Series tool in Excel (the little handle at the bottom-right of the green cursor) can save you a lot of typing time. You can quickly autofill in sequences like months, days, dates, and numbers.

What is the Fill Series Tool in Excel?

The Fill Series tool (Excel AutoFill) is a feature in Excel that allows you to automatically fill cells with a series of data. For example, if you type “January” in a cell and use the Fill Series handle to drag down, Excel will automatically fill the subsequent cells with “February,” “March,” and so on. This tool is handy for creating lists and auto fill numbers or patterns without having to type each entry manually.

But what about lists specific to you? Lists that you use every day, like a list of departments? Imagine typing “Admin,” pulling the Fill Series handle down, and seeing all your departments listed automatically.

How to Create Custom Lists in Excel

To create a Custom List, take the following steps:

  1. Type Your List: Enter your list in a spreadsheet. Tip: Sort your list alphabetically, numerically, or chronologically, as this order will be used by the Fill Series feature.
  1. Highlight and Navigate:
    • Highlight your list.
    • Click on the File tab.
    • Select Options at the bottom left.
    • Go to the Advanced category.
  2. Edit Custom Lists:
    • In the General section, click on “Edit Custom Lists…”.
  • The highlighted range should appear in the “Import list from cells:” field. Click Import to add the list to your Custom Lists library..

💡 Note: If you didn’t create the list in Step 1, you can select New List and manually enter your items in the List Entries field, then click Add.

Testing Your Custom List:

  • Select an empty cell.
  • Type any item from your list.
  • Use the Fill Series handle to complete the list.

💡 Your custom list can be used in any new or existing Excel file on this computer. However, remember that this list is specific to the computer it was created on and won’t travel with files you share with others.

Setting 4: Pivot Table – Set Your Preferred Layout

If you regularly use Pivot Tables, you probably have a preferred layout you customize each time. These customizations might include:

  • Position of Subtotals
  • Presence of Grand Totals
  • Report Layout (Compact, Outline, or Tabular)
  • Blank Lines between hierarchy levels
  • Repeated item labels
  • Error message customization
  • Empty cell customization

While these changes are not difficult, they can be time-consuming and may lead to inconsistencies if done in a hurry. Fortunately, Excel allows you to set default options for Pivot Tables, ensuring your preferred settings are applied every time you create one.

How to Set Default Pivot Table Layout Options

  1. Click on the File tab.
  2. Select Options at the bottom left.
  3. Go to the Data category.
  4. In the Data Options section, click on Edit Default Layout….

The Edit Default Layout dialog box allows you to set your preferred Pivot Table adjustments for a consistent look and feel.

Example:

Instead of starting with a basic Pivot Table like this one …

… you can begin with one that already has your desired settings applied:

Import Existing Pivot Table Settings

If you have an existing Pivot Table with all your preferred customizations, you can import those settings:

  • Click anywhere in the existing Pivot Table.
  • Open the Edit Default Layout… dialog box.
  • Click Import.

Setting Default Pivot Table Colors

If you prefer a specific color scheme from the PivotTable Styles gallery, you can set it as the default:

  • Right-click on the preferred color scheme.
  • Select Set as Default.

This will save you time and ensure consistency across all your Pivot Tables.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Setting 5: Excel Startup Settings

Remember when Excel used to open directly to a blank workbook? Now, it starts with the Getting Started panel. While this panel offers useful options, some users prefer starting with a blank document.

What is the Getting Started Panel?

The Getting Started panel appears when you open Excel and offers several options:

  • Start a new blank file
  • Open a recent file
  • Start a new file from a template
  • Open an existing file
  • Check your Office version
  • View your account status
  • Provide feedback to Microsoft
  • Change program settings

If you miss the old way of opening Excel directly to a blank workbook, you can turn off the start screen. Here’s how:

How to Turn Off the Start Screen in Excel:

  • Click on the File tab.
  • Select Options at the bottom left.
  • Go to the General category.
  • Scroll to the Startup Options section at the bottom.
  • Uncheck “Show the Start screen when this application starts”.

To enable the Getting Started panel again, follow the same steps and recheck the box.

Setting 6: Keep Leading Zeroes

Typing numbers with leading zeroes in Excel can be frustrating. Pressing ENTER often causes Excel to remove those zeroes. This happens because Excel typically sees leading zeroes as insignificant.

However, leading zeroes are crucial in many cases, such as:

  • ZIP/Postal codes
  • Part numbers
  • Employee numbers
  • Credit card numbers
  • Phone numbers

Previously, you had to enter numbers as text or convert cells to text format to keep the zeroes. Now, there’s a better solution!

How to Keep Leading Zeroes in Excel

  • Click on the File tab.
  • Select Options at the bottom left.
  • Go to the Data category.
  • Find the Automatic Data Conversion section.
  • Uncheck the option labeled “Remove leading zeroes and convert to a number”.

This change will ensure Excel keeps your leading zeroes intact.

Handling Delimited Text Files

When opening .CSV or .TXT files with numbers that have leading zeroes, Excel will now prompt you to decide whether to keep or remove the zeroes.

This new feature will be a great help to many Excel users.

Download the Cheat Sheet

Grab our free one-page quick guide with all important Excel options. It includes all Excel settings you should review now.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.