Excel Settings You Need to Change

Raise your hand if Excel’s default settings have ever driven you just a little bit crazy.

Maybe your invoice numbers lose their leading zeros…
Maybe you’re tired of Excel shoving GETPIVOTDATA into your formulas…
Maybe the new Copilot icon is following your every move!

You’re not alone. Excel’s defaults are made for average users. But you? You're here to level up. So let’s fix those annoyances and make Excel work for you.

Here are 15 simple but game-changing Excel settings you’ll wish you’d changed sooner.

Watch the Video

Get the Cheatsheet

1. Prevent Excel from Dropping Leading Zeros

Ever entered a phone number like 075551234 only to have Excel change it to 75551234?

Fixes:

• For one-offs: type an apostrophe before the number ('075551234) to force it into text.
• For bulk entries, Microsoft 365 users can:
  Go to File → Options → Data → Automatic Data Conversion and uncheck
  “Remove leading zeros and convert to number.”

• Format the column as Text before entering data.
• Bonus: Use a custom number format like 00 000 0000 to auto-insert spacing in phone numbers.

2. Turn Off Table Structured References

Excel tables auto-format formulas using structured references like =SUM(Table1[Profit]). Some love it, others… not so much.

To turn them off:

File → Options → Formulas → Uncheck “Use table names in formulae.”

Now Excel will return to standard A1 style when referencing table data.

Note: I highly recommend you keep structured references turned on. If you’re struggling with making them absolute, check out this tutorial on how to make Excel Table Structured References Absolute and you’ll never look back.

3. Remove Gridlines for Cleaner Reports

When building dashboards or summary reports, gridlines can clutter your visuals.

Cleaner look:

• View tab → Uncheck Gridlines

• Bonus tip: Make your reports pop with a custom background. Go to the Page Layout tab → Background to add custom backgrounds for extra polish.

4. Custom Auto-Correct Shortcuts for Common Text

Typing “Quarterly Revenue Report” over and over?

Save time with:

File → Options → Proofing → AutoCorrect Options
Add entries like:

• Replace: RR
• With: Quarterly Revenue Report

Now Excel auto-replaces short codes with full text as you type.

5. Hide the Copilot Icon

In Microsoft 365, the Copilot icon can be intrusive.

Hide it until you need it:

File → Options → Copilot → “Show only for relevant suggestions.”

You’ll still have access via the Home tab—but without the intrusion.

6. Set Your Default PivotTable Layout

Sick of manually switching from Compact to Tabular layout and disabling subtotals?

Do this once:

File → Options → Data → Edit Default Layout
Set your preferences as you wish (Tabular, no subtotals, etc.) and save time on every future PivotTable.

7. Disable GETPIVOTDATA

Excel replaces simple references like =C5 with =GETPIVOTDATA(...) when working with PivotTables.

To stop it:

• Select a cell in a PivotTable
• PivotTable Analyze tab → Options → Uncheck “Generate GETPIVOTDATA.”

Simple references stay simple.

P.S. GETPIVOTDATA has its perks. Check out this explainer video if you’re curious!

8. Ensure Backward Compatibility

Sharing files with users on older Excel versions?

To avoid formula breakage:

File → Info → Inspect Workbook → Check for Issues → Check Compatibility

Select the version (e.g., Excel 2019), and Excel will highlight incompatible features.

You can even:

• Click Find to locate issues
• Use Help for explanations
• Copy results to a new sheet to review and resolve

9. Use the Clipboard to Copy/Paste More Efficiently

Excel normally only remembers your last copy. But the Office Clipboard stores up to 24 items.

Enable it:

Home tab → Clipboard launcher icon

You can copy text, images, formulas, and even items from other Office apps or browsers.

10. Change the Ruler Units

Designing templates or labels?

To switch ruler units:

View tab → Page Layout → Enable Ruler

File → Options → Advanced → Display section → Ruler Units: [choose Inches, Centimeters, or Millimeters]

11. Auto-Open Your Most Used Files

Tired of opening the same Excel files manually every day?

Automate it:

• Save your go-to Excel files in a folder
• Copy the folder path (Shift + Right Click → “Copy as Path” or Ctrl + Shift + C)
• File → Options → Advanced → General → “At startup, open all files in:”
  Paste the path.

Now Excel opens your essentials automatically on startup.

12. Stop Excel from Auto-Linking URLs

When you type www.MyOnlineTrainingHub.com, Excel turns it into a hyperlink upon pressing Enter. Not always helpful.

To disable:

File → Options → Proofing → AutoCorrect Options → AutoFormat As You Type tab → Uncheck “Internet and network paths with hyperlinks.”

13. Make the Mouse Pointer a Lasso

Working with images, charts, or shapes?

To quickly select multiple objects:

Home → Find & Select → Select Objects
Drag to select everything in one go.

Tip: Add this tool to your Quick Access Toolbar for faster access.

14. Stop Excel Files from Opening in Your Browser

If you use Microsoft Edge, Excel files open in Excel Online by default.

To fix:

• In Edge, go to Settings → Downloads
• Toggle OFF “Open Office files in the browser”

Now files download instead and open in your desktop app.

15. Use Focus Cell for Easier Navigation (Microsoft 365)

When working with large datasets, Focus Cell helps highlight the row and column of the active cell.

Enable it:

• View tab → Focus Cell
• Shortcut: Alt + W + E + F

Choose your highlight colour and enable auto-highlight when using Find (Ctrl + F) for easier data tracing.

Your Excel, Your Way

These settings aren't just tweaks. They're quality-of-life upgrades that save time, reduce frustration, and make Excel behave your way.

To help you remember everything, I’ve created a free downloadable cheat sheet that outlines every setting we’ve covered.

And if you’re ready to go deeper and truly master Excel, check out the Excel Expert Course. It’s the structured, practical path to becoming the go-to Excel expert in your workplace.