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.
PS: 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.
Learn With Us
