Microsoft Excel is a powerful spreadsheet tool, and while you can customize it with awesome add-ins, some of the best options are inside Excel itself.
We won’t discuss temporary fixes that only last for one session here, but rather permanent tweaks so you change how Excel works.
1. Hide the Start Screen on Startup
One of the big annoyances in Office 2013, the Start screen offers various templates you might want to try in Excel. While it also lets you quickly access recent documents, it’s a pain to click “Blank Worksheet” every time you fire up Excel.
Thankfully, this is easy to shut off without any Registry tweaking. Head to File > Options and under the General tab, there’s an option at the bottom to Show the Start screen when this application starts. Uncheck this, and you’ll be taken directly to a blank document when starting Excel.
2. Change the Enter Key’s Behavior
By default, pressing Tab in Excel will move the focus one cell to the right, while pressing Enter moves one cell down. If you use a different direction more frequently and would like to override this, it’s a simple fix.
Visit File > Options again; in the Advanced tab, find the first option: After pressing Enter, move selection and choose your preferred direction. It’s kind of a waste to make it go right, since the Tab key already does that — especially if you’re looking to get around with just keyboard shortcuts.
3. Add or Remove Initial Sheets
While older versions of Excel included three sheets with every new workbook, the latest versions only give you a single sheet. This makes sense, as having to hide two blank sheets every time you made a new document is annoying. If you prefer a different number, you can change the default amount of sheets in a created workbook.
Again in File > Options, the General tab has the heading When creating new workbooks. Under that, change the value in Include this many sheets: and every new workbook will be ready to go. Don’t forget you can use powerful search functions to find something if you get overloaded with sheets.
4. Always Show Page Breaks
When printing a spreadsheet, you usually need to print it all on one page. It can be hard to judge how printing is going to cut your workbook off when working, so enabling page break markers let you know exactly where Excel wants to split the pages.
File > Options > Advanced holds the key. Under the header Display options for this worksheet, there’s a checkbox to Show page breaks. Enabling this will show dotted lines in your spreadsheet; these are where Excel will cut split the content onto a separate page.
5. Create Custom Lists
Excel includes default lists, such as days of the week, that allow you to quickly populate cells. For example, if you type Monday in cell A1 and Tuesday in cell B1, then highlight them both and drag the fill handle down, the below cells will be filled with Wednesday, Thursday, and so on.
You can make your own lists for anything you can imagine. To do so, open the editor by using File > Options > Advanced again. The General heading near the bottom contains an Edit Custom Lists button where you can set them up. Either manually type the items into the window one at a time, or type them into your current spreadsheet and use the Import button at the bottom to grab them.
6. Add Text Expansion
We’ve shown you how to save time with text expansion, but did you know that Excel includes its own built-in version? If you don’t want to use a dedicated program for text expansion or want separate rules for Excel, you can use this to save tons of time.
Under File > Options > Proofing, you’ll find an AutoCorrect Options button. Click this to view everything Excel automatically changes for you (such as (C) to ©, for example). Of course, we’re interested in making our own phrases here.
Type a short, memorable string in the Replace field and its full version in the With box. You might want to set “/t” to write “Total:” or “TBD” to expand into “To Be Determined,” for example. Anything you type frequently can be automated in this menu, so be sure to add your most common phrases to cut down on repetitive work.
7. Be Alerted when Actions May Be Slow
Most Excel users work with manageable spreadsheets, but sometimes you might have to import a huge amount of data that makes for a large file. If you’re on an older computer that isn’t equipped to handle heavy actions, copying and pasting thousands of cells or running complex formulas may bring your computer to a crawl.
Excel includes an alert for when you’re about to do something that could potentially slow down the system, but you can adjust its value to be more sensitive. Our old friend File > Options > Advanced holds this value under the Editing options heading at the top of the page. Make sure Alert the user when a potentially time consuming operation occurs is checked, then you can change the number of cells that must be affected for this alert to trigger right below it.
This value is set to 33,554 by default — far more cells than I’ve ever edited at once, but your needs may be different. Those still using Windows XP for your Office needs should probably lower this number to avoid freezing or crashing.
8. Customize the Ribbon
Office 2007 brought major changes with its controversial Ribbon interface. It groups similar actions together into headings, but not everybody is a fan.
We’ve shown you many ways to customize the Ribbon by adding your favorite commands to it, or even how to completely remove the Ribbon if you hate it. Adding your own tab with your most-used features goes a long way in making Excel more suited for your unique workflow.
9. Deck Out the Quick Access Toolbar
The Quick Access Toolbar (QAT) is a strip of icons that sits at the top of the screen. Similar to the Ribbon, it can be customized to add your most-used commands for easy access; this is especially useful for commands that are elsewhere buried deep in menus.
Saikat has detailed everything you need to know about editing the QAT, so here’s a bonus tip for Excel.
Locate the QAT at the top of your screen, and to the right of the last icon you’ll see a little arrow that opens a drop-down menu. Click it and choose More Commands near the bottom to open the full editing menu. Above the left-side box, change Choose commands from to Commands Not in the Ribbon to view all the available choices.
Find Calculator in the list and double-click it to add the item to your QAT. Then click OK and the underappreciated Windows calculator will always be one click away in Excel! This is useful when you need to do a quick calculation without polluting cells in your spreadsheet.
Your Spreadsheets, Personalized
Now you’ve got plenty of ways to make Excel bend to your will. Spreadsheets can be tedious to edit if you’re fighting against the software, so taking the time to make Excel work how you do is well worth it.
How do you customize Excel for your needs? Will you implement any of the tweaks here? Leave us a comment with your thoughts!