Excel is the ultimate productivity tool, but it has some incredibly frustrating default habits. It treats your data like it knows better than you do, leading to endless manual corrections. Before you start working on your next spreadsheet, adjust a few hidden options so Excel finally starts working on your terms.

Stop Excel from eating your leading zeros

Protect your zip codes and ID numbers

Anyone who has ever typed a zip code, tracking number, or ID starting with a zero into Excel knows the immediate flash of frustration that follows. You type “01234,” press Enter, and Excel instantly converts it to “1234.” The software assumes it’s helping by treating the entry as a numeric value, when in fact you need those opening zeros to stay intact.

For years, users had to rely on workarounds like typing an apostrophe before entries or pre-formatting columns as text. Thankfully, modern versions of Excel include a dedicated setting to prevent this behavior permanently.

To protect your zeros:

1. Click File.
2. Select Options.
3. Open the Data menu.
4. Scroll down to the Automatic Data Conversion section.
5. Uncheck Remove leading zeros and convert to a number.

When you click OK, Excel will preserve your data exactly as entered going forward. Since this is an application-level setting, it sticks across future workbooks until you change it again.

Kill automatic hyperlinks before they launch your browser

Make editing text cells painless

By default, Excel assumes that email addresses and website names should become clickable links. If you’re building contact lists, inventories, or documentation sheets, those automatic hyperlinks quickly become annoying.

The moment Excel converts plain text into a hyperlink, it changes the formatting to a blue, underlined font. Worse yet, clicking the cell to fix a typo or clear the formatting can open your browser or email app when all you wanted to do was edit the text.

Here’s how to disable this behavior permanently so URLs stay as text:

1. Click File.
2. Select Options.
3. Open the Proofing menu.
4. Click AutoCorrect Options.
5. In the AutoFormat As You Type tab, uncheck Internet and network paths with hyperlinks.

After you click OK twice to save the change, Excel will stop converting all future URLs and email addresses across all workbooks. If you need one later, you can still add it manually (Ctrl+K).

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.

Change your default typeface and font size

Stop squinting at your data

Excel’s default font is perfectly usable, but it’s not necessarily the right one for your workflow. If you constantly create a new spreadsheet and immediately change the font or size, Excel can save those preferences.

Here’s what to do:

1. Click File.
2. Select Options.
3. Open the General menu.
4. Scroll down to the When creating new workbooks section.
5. Choose your preferred font under Use this as the default font, then adjust the Font size.

From that point onward, every brand-new Excel workbook you create will use those settings. Existing spreadsheets keep their original formatting, so this change only affects future files.

Control exactly where your cursor goes next

Tweak your Enter key behavior

Pressing Enter in Excel activates the cell below by default, which is ideal for vertical data entry. But what if you’re auditing formulas and fine-tuning cell values, or your workflow typically involves entering data across rows rather than down columns?

Fortunately, Excel lets you customize this behavior to fit how you actually work:

1. Click File.
2. Select Options.
3. Open the Advanced menu.
4. Find the Editing options section.
5. Locate the checkbox and drop-down menu for After pressing Enter, move selection.

If you spend a lot of time auditing complex formulas or modifying specific variables, uncheck this box entirely. This keeps the cursor in the active cell you just edited, allowing you to press Enter to lock in a tweak while keeping your focus on the formula bar.

On the other hand, if you’re primarily entering data horizontally, leave the box checked but switch the Direction drop-down to Right. While you can technically press Tab or the Right Arrow key to navigate across a row, decades of Excel use have wired our muscle memory to rely heavily on Enter. Tweaking this setting ensures your deepest typing habits match your actual spreadsheet layout.

Once saved, whichever preference you choose stays active across every workbook.

Clean up messy PivotTables before you create them

Switch to a tabular layout by default for cleaner reports

PivotTables are one of Excel’s most powerful tools. However, by default, they’re structured using the Compact layout, which collapses multiple row fields into a single, generic column labeled “Row Labels.” As you can see below, this arrangement forces a nested, stair-step appearance that is tedious to sort, filter, or read across a single row.

Switching your global default to the Tabular form breaks each field out into a clearly labeled column while repeating all item labels for added clarity. This structural shift means you get a clean, standardized layout that makes tracking and scanning much easier.

If you regularly switch PivotTables to Tabular Form manually, you can change the default behavior so you don’t have to make that change again:

1. Click File.
2. Select Options.
3. Open the Data menu.
4. Click Edit Default Layout.
5. Change Report Layout to Show in Tabular Form, then check Repeat All Item Labels to keep categories fully populated and easier to sort or filter.

After you click OK to save the changes, all new PivotTables you build will use this new layout. Existing PivotTables will not update, so older spreadsheets still need manual adjustments.

Enjoy a spreadsheet that actually works for you

Excel should adapt to your workflow, not the other way around. Changing these defaults helps protect your data, reduce repetitive fixes, and make everyday spreadsheet work smoother. If you want to keep refining your setup, making a few key Excel UI changes can make the program even faster and easier to use.