Whether you're an experienced Excel pro or you've just switched from another spreadsheet software, many of the default settings make the program frustrating to use. But you don't have to settle for these defaults! Here are some tweaks that will make life easier and speed up your workflow.
Turn on Focus Cell
Have you ever lost track of the correct column or row while visually scanning data in an Excel worksheet? If your workbook is set up with the default settings, this can happen a lot, especially if you're working on a wide screen with a large dataset. This can make working in Excel particularly frustrating.
One feature I always activate to make this easier isFocus Cell, which highlights the column and row of the active cell. This ensures I don't inadvertently jump to a different field or record when reading and analyzing data.
Focus Cell is available to those using Excel for Microsoft 365 on Windows or Mac andExcel for the web.
To turn on Focus Cell, open the "View" tab on the ribbon, and click "Focus Cell" in the Show group. On the other hand, if you're using Excel on a Windows computer, press Alt > W > E > F.
Add Focus Cell to yourQuick Access Toolbar so you can turn it on and off in a single click.
Notice how the active cell retains its color, while the remaining cells down the column and across the row adopt the guiding color. Focus Cell is an on-screen feature only—you won't see it when you print.
Once you trigger this feature, it remains active in any new workbook you create or existing workbook you open on your computer. You can turn it off again by clicking the same icon.
To change the color used to highlight the relevant columns or rows, click the down arrow next to the same button, and hover over "Focus Cell Color."
Keep leading zeros
Microsoft Excel removes leading zeros from numbers by default, which, while handy in many circumstances, can be frustrating if you typically enter phone numbers or serial numbers that begin with zeros.
While you can format individual cells to retain leading zeros by applying custom number formatting or typing an apostrophe before the first zero, you can make this a permanent behavior through the Excel options dialog box.
5 Excel Custom Format Tricks You Didn’t Know You Needed
Venture outside the traditional number formats in Excel.
First, press Alt > F > T to launch the Excel options dialog box. Then, head to the "Data" tab, uncheck "Remove Leading Zeros And Convert To A Number," and click "OK."
Now, when you type a number with a leading zero into a cell and press Enter, Excel converts this to a textual value, meaning it keeps all the characters you typed.
This setting tweak only applies to data entered after you make the change. All numbers you typed with leading zeros before deactivating the setting will still be stored as numbers without leading zeros. You can also reactivate the setting once you've finished typing the serial numbers or phone numbers to revert Excel to its default behavior for all subsequent numbers.
Hide the Copilot icon
Ever since Microsoft included Copilot as part of the Microsoft 365 subscription, an icon has appeared next to the active cell in Excel by default. Even though this is supposed to be a helpful feature that provides quick access to AI tools, it's annoying and distracting.
One way to get rid of this is todisable Copilot in Excel, though this is counterintuitive if you still want to access Microsoft's AI feature. Instead, you can make a small change to your Copilot settings. First, press Alt > F > T to open the Excel Options dialog box. Then, select "Copilot" in the left-hand menu, check "Show Copilot Icon Only For Highly Relevant Suggestions," and click "OK."
Now, rather than appearing all the time, the icon will only be displayed if Copilot has a suggestion it thinks will be helpful. Also, because you didn't disable Copilot altogether, you can still access its functionality by clicking the "Copilot" icon in the Home tab on the ribbon.
If the icon still appears after you've changed this setting, restart Excel and try again.
How to Avoid Paying for Microsoft 365 Copilot by Downgrading to Classic
Don't be forced to pay more against your will!
Turn off auto-hyperlinking
When you type a link or email address, like www.howtogeek.com or tony@htg.com, into a cell in Excel and press Enter, it turns into a hyperlink. Even though this can come in handy if you plan to share your workbook and want your collaborators to click the links, most of the time, it can get in the way of data entry, mess with your spreadsheet's formatting, and hinder general usability.
In the screenshot below, the auto-hyperlinking feature has turned the text blue and reduced the font size from 14pt to 11pt.
What's more, to edit the cell without triggering the link, you have to double-click or long-click the cell, or use the formula bar at the top of the Excel window. All these workarounds severely disrupt your workflow.
To prevent Excel from turning typed URLs into clickable links, press Alt > F > T to open the Excel Options window, and in the Proofing section, click "AutoCorrect Options."
Then, head to the "AutoFormat As You Type" tab, deselect "Internet And Network Paths With Hyperlinks," and click "OK" twice to close the dialog boxes.
Now, when you type a link into a cell and press Enter, it is registered as an ordinary text value, not a clickable link.
Open certain files when you launch Excel
If you have a set of Excel workbooks that you always open at the start of the working day, doing this manually can eat up much of your morning routine, even if you pin them to the Favorites list on the Excel welcome page.
Instead, you can force Excel to open all files saved in a nominated folder whenever the program opens. First, create a dedicated startup folder, and move all the workbooks in question to this folder.
Next, right-click the folder name in the address bar, and click "Copy Address."
Now, in Excel, open the Excel Options window by pressing Alt > F > T, and click "Advanced." Then, scroll down to the General section, paste (Ctrl+V) the file path you just copied into the "At Startup..." field, and click "OK."
Finally, close and reopen Excel, and see that the files in the nominated folder all open immediately.
You can add or remove files from the nominated folder at any time. If there are no files in the nominated folder, a blank Excel workbook opens instead. To revert to the default opening behavior (in other words, to open the welcome screen when you launch Excel), delete the pasted file path from the Excel options window.
Stay in the same cell when you press Enter
After entering data or a formula into a cell in Excel, you might press Enter to commit it. Because Excel assumes that you then want to enter data in the next cell down, that cell becomes active.
However, you might find it more useful to stay in the same cell when you press Enter. For example, if you entered a formula, maybe you want to review or copy it before moving to the next task. On the other hand, perhaps you're trialing different figures for a calculation, and you want to keep the same cell active so you can type new entries until you get the figure you expect.
While you can press Ctrl+Enter to do this on one-off occasions, you can override the default next-row behavior permanently in just a few steps. First, press Alt > F > T to open the Excel Options dialog box, and in the Advanced area, uncheck the dialog box next to "After Pressing Down, Move Selection."
After clicking "OK," enter a value or formula into a cell, and press Enter. Notice that the cell you just typed into remains active.
Use theArrow keys to navigate to different cells in your workbook.
Define a personalized PivotTable layout
Whenever you insert a PivotTable, Microsoft Excel defaults to the compact layout, which stacks all row fields into a single column with indentations to show relationships. Subsequently changing the PivotTable to different layouts in the Design tab on the ribbon is guaranteed to waste time, especially if you need to make the same changes each time.
My Go-To Excel Trick for Analyzing Big Data Sets Fast
PivotTables are an underrated tool in Microsoft Excel.
To speed up this process, you can define a personalized layout that all future PivotTables will adopt. To do this, bring up the Excel options dialog box by pressing Alt > F > T. Next, in the Data tab, click "Edit Default Layout" in the Data Options area.
Then, there are two ways to redefine the PivotTable layout settings:
- Select different options in the "Subtotals," "Grand Totals," and "Report Layout" fields, check or uncheck "Insert Blank Line After Each Item," and modify more settings in the PivotTable Options window.
- Use the layout of an existing PivotTable you've already reconfigured by placing your cursor in the "Layout Import" field, selecting a cell in the PivotTable containing the preferred layout, and clicking "Import."
Whichever method you use, after you click "OK," all subsequent PivotTables you insert will adopt the new layout.
While you're playing around with the settings in Excel, considerswitching to dark mode. If you spend all day staring at spreadsheets and find the white Excel canvas gives you a headache, switching to a darker interface can increase your comfort.
Microsoft 365 Personal
- 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.
