Excel logo next to an OFF switch for Excel settings to change

Get the Excel Settings Cheat Sheet

A free one-page PDF with the Excel settings worth changing. Keep it next to your screen and set up Excel in a few minutes.

Setting 1: Stay in the Same Cell 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.

Excel moves the selection down one cell after pressing Enter on a formula

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: Press Ctrl+Enter instead of Enter to confirm an entry and stay in the same cell. It’s one of the Excel shortcuts worth knowing early.

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. Under Editing options, uncheck After pressing Enter, move selection to stay in the same cell. To move in another direction instead, keep it checked and pick Down, Right, Up or Left.
Excel Options Advanced tab with After pressing Enter, move selection and the Direction drop-down

Click OK. Enter now confirms your entry and keeps you in the same cell, ready to copy a formula down.

Setting 2: Use AutoCorrect as a Shortcut for Long Text

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.

Typing an AutoCorrect shortcut in Excel that expands into a full company name

What Is AutoCorrect in Excel?

AutoCorrect fixes common typos as you type. You can also add your own entries, so a short code turns into a full phrase. The list is shared with Word, PowerPoint and Outlook, so a shortcut you add in Excel works there too.

How to Add an AutoCorrect Shortcut

  1. Click the File tab.
  2. Select Options at the bottom left.
  3. Go to the Proofing category.
  4. Click AutoCorrect Options.
  5. In the Replace box, type your shortcut (for example, ASW).
  6. In the With box, type the full phrase (for example, ACME Sprockets and Widgets, Inc.).
  7. Click Add, then OK twice.
AutoCorrect dialog in Excel replacing ASW with ACME Sprockets and Widgets, Inc.

Now type ASW and press Space or Enter. Excel replaces it with the full phrase in any workbook. Open a new file with Ctrl+N and try it.

💡 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.”

Setting 3: Create Custom Lists for AutoFill

The fill handle is the small square at the bottom-right corner of the selected cell.

Drag it, and Excel fills in months, days, dates and numbers for you.

What Is the Fill Handle in Excel?

When you drag the fill handle, Excel uses AutoFill. Type January in a cell, drag down, and Excel fills in February, March and so on. The months come from a built-in custom list, and you can add your own lists next to it.

But what about your own lists, like the departments you type every day? Type Admin, drag the fill handle down, and all your departments fill in automatically.

Department list in Excel column A highlighted in yellow

How to Create Custom Lists in Excel

To create a Custom List, take the following steps:

  1. Type your list in a column, in the order you want Excel to fill it.
Department list typed in column A from Admin to Photography

Select your list.

  • Go to File > Options > Advanced.
  • Scroll down to the General section and click Edit Custom Lists.
Excel Options Advanced tab with the Edit Custom Lists button in the General section
  • Check that your range shows in Import list from cells, then click Import.
  • Click OK twice.
Custom Lists dialog importing the department list from cells A1 to A10

💡 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.

Should you include the header? If you import it (for example, Department), typing Department and dragging fills the whole list below it. Leave it out of the range if you don’t want it in the list.

Test Your Custom List

  • Select an empty cell.
  • Type any item from your list.
  • Drag the fill handle to complete the list.
Dragging the fill handle from Admin to fill the custom department list in Excel

You can start from any item. Type Admin, drag down, and Excel continues from there. Keep dragging and the list repeats.

💡 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: Set a Default PivotTable 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

These changes aren’t hard, but repeating them on every PivotTable takes time and leads to reports that don’t match. Excel lets you set default options instead, so every new PivotTable starts the way you like it.

This works in Microsoft 365, Excel 2024, Excel 2021 and Excel 2019 for Windows.

If PivotTables are part of your daily work, these PivotTable tips and tricks save even more time.

How to Set Default PivotTable 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….
Excel Options Data tab with the Edit Default Layout button for PivotTables

In the Edit Default Layout dialog, set your Subtotals, Grand Totals and Report Layout. Click PivotTable Options for more.

For example, uncheck Autofit column widths on update so your columns stop jumping every time you refresh.

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

Default PivotTable in compact form with subtotals

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

PivotTable in tabular form without subtotals after setting a default layout

Import the Layout From an Existing PivotTable

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.
Edit Default Layout dialog with the Import button for an existing PivotTable layout

Click OK twice. Every new PivotTable now starts with this layout, in any workbook. Existing PivotTables keep their current layout.

Set a Default PivotTable Style

To change the colors, select the PivotTable and open the PivotTable Styles gallery on the Design tab. Pick a built-in style or create one in your brand colors. To make it the default:

  • Right-click on the preferred color scheme.
  • Select Set as Default.
Right-click menu in the PivotTable Styles gallery with Set As Default

Set as Default only applies to new PivotTables in the current workbook. In a new file, set it again, or save a workbook with your style as a template.

Setting 5: Skip the Start Screen

When you open Excel, you land on the Start screen first. That’s handy if you pick recent files from there. If you open your files from File Explorer, you might prefer to go straight to a blank workbook.

What Is the Excel Start Screen?

The Start screen shows up every time you open Excel. From there you can:

  • 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

To skip it and open a blank workbook straight away, turn off the Start screen:

How to Turn Off the Start Screen in Excel

  1. Click the File tab.
  2. Select Options at the bottom left.
  3. Go to the General category.
  4. Scroll down to Start up options.
  5. Uncheck Show the Start screen when this application starts.
  6. Click OK.
Excel Options General tab with Show the Start screen when this application starts

To bring the Start screen back, follow the same steps and check the box again.

Setting 6: Keep Leading Zeros

Type 00123 in a cell and Excel turns it into 123. Excel treats the entry as a number, and numbers don’t keep leading zeros.

But leading zeros matter in many cases, such as:

  • ZIP/Postal codes
  • Part numbers
  • Employee numbers
  • Account numbers
  • Phone numbers

Before, you had to type an apostrophe first or format the cells as Text. Now there’s a setting for it.

It’s available in Microsoft 365 for Windows and Mac and in Excel 2024.

How to Keep Leading Zeros in Excel

  1. Click the File tab.
  2. Select Options at the bottom left.
  3. Go to the Data category.
  4. Scroll down to Automatic Data Conversion.
  5. Uncheck Remove leading zeros and convert to a number.
  6. Click OK.
Excel Options Data tab with Remove leading zeros and convert to a number under Automatic Data Conversion

Excel now stores entries like 00123 as text, so the zeros stay. You may see a green triangle that says Number Stored as Text. That’s expected. Text can’t be used in calculations, so if you need a real number, use a custom number format such as 00000 instead.

The same section has three more options. Keep first 15 digits of long numbers stops Excel from cutting off 16-digit values like credit card numbers. The other two stop Excel from turning numbers around the letter E into scientific notation and letter-number combinations like JAN1 into dates.

Get a Warning When Opening CSV Files

Keep the option to notify you of automatic data conversions checked too. When you open a CSV or TXT file, Excel then shows a message before it changes your data.

Click Don’t Convert and the leading zeros stay.

Excel warning before removing leading zeros from a CSV file with Convert and Don’t Convert buttons

Download the Excel Settings Cheat Sheet

Get the Free Cheat Sheet

One page. The Excel settings worth changing. Free PDF.

Frequently Asked Questions

How do I make Excel stay in the same cell after pressing Enter?

Go to File > Options > Advanced and uncheck After pressing Enter, move selection. Click OK. If you only want to stay in the cell once, press Ctrl+Enter instead of Enter.

How do I stop Excel from removing leading zeros?

In Microsoft 365 or Excel 2024, go to File > Options > Data and uncheck Remove leading zeros and convert to a number under Automatic Data Conversion. In older versions, format the cells as Text before you type, or start the entry with an apostrophe.

How do I change the default PivotTable layout in Excel?

Go to File > Options > Data and click Edit Default Layout. Set your subtotals, grand totals and report layout, or select a PivotTable you like and click Import. Every new PivotTable in any workbook then starts with that layout. This works in Microsoft 365, Excel 2024, Excel 2021 and Excel 2019 for Windows.

Why is my custom list missing on another computer?

Custom lists for AutoFill are saved on the computer where you created them, not in the workbook. On another computer, add the list again in File > Options > Advanced > Edit Custom Lists.

Do Excel AutoCorrect shortcuts work in Word and Outlook too?

Yes. Plain-text AutoCorrect entries are shared across the Office apps that support AutoCorrect. A shortcut you add in Excel also works in Word, PowerPoint and Outlook on the same computer.

How do I open Excel to a blank workbook instead of the Start screen?

Go to File > Options > General and uncheck Show the Start screen when this application starts under Start up options. Click OK. Excel now opens straight to a blank workbook.

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.