Tips & Tricks
Tweaking Excel: How to Exploit Your Options
Microsoft Excel, like just about every other software program, doesn't always do things exactly the way you want. Needless to say, this can be downright frustrating. Fortunately, your frustrations can sometimesbe reduced simply by changing a few default settings.
Oh, the joy of having a software application perform to suit your preferences. The good news is that in some cases, it’s not terribly difficult to achieve. Microsoft Excel is a case in point.
As with most Microsoft programs, clicking on Tools, then Options gives you access to the majority of the Excel default settings you can play with. In the Options dialog box there are 13 tabs with various settings you can tweak to meet your wants and needs. Let’s focus on some of the most practical and useful setting changes.
Using the View Tab
The settings on the View tab control how things are displayed on a worksheet screen. In the Show section under this tab, you can select or clear options for whether to display or hide the formula bar (useful), the status bar (useful) and the start-up task pane (which is probably an annoyance for many of you). Clicking on the Windows In Taskbar option forces Excel to display multiple workbooks as separate buttons on the Windows taskbar.
The options under the Objects section allow you to display or hide graphic objects in the workbook. Show All is the default and displays everything. Show Placeholders displays pictures and charts as gray rectangles—you would want to do this as displaying object placeholders can significantly increase the speed of scrolling through a worksheet. Hide All hides all graphic objects and is useful when you want to view or print a worksheet without graphical items.
The Windows Options section is where you set the display options for the active window. I sometimes find it useful to turn off gridlines and turn on page breaks and formulas.
Edit Tab Features
The Edit tab contains all sorts of useful things to control the options for editing worksheet data. Be aware that any options checked in this tab affect all worksheets in a workbook.
The most useful setting here is Move Selection After Enter. This controls which adjacent cell you jump to after you press Enter in the currently active cell. The default jump is Down. You can also set it to Right, Left and Up.
The Edit Directly In Cell option lets you make edits right inside a cell by double-clicking on it. This is faster than editing the cell contents only in the formula bar.
By choosing Cell Drag And Drop, you can move and copy cells and data by dragging them around the worksheet. This is helpful in some situations—but it can be dangerous unless you also enable the Alert Before Overwriting Cells option. It displays a message if you drop cells over other cells that contain data.
Now for two “oh, that’s why that happens” settings. With Enable Auto-Complete For Cell Values turned on, if the first few letters you type match an existing entry in that column, Excel fills in the remaining text for you. When Enable Automatic Percent Entry is turned on, all numbers less than 1 will be multiplied by 100 when they are entered in cells formatted in the Percentage format. These are often helpful, but it’s nice to be able to turn them off when they are not.
Looking at the General Tab
In the Settings section on the General tab, you can specify display, viewing and opening settings for your workbook.
When you enable Function ToolTips, it displays brief descriptions of the names of buttons and boxes on toolbars. A ToolTip is displayed when the mouse pointer rests on the button or box. These are really helpful.
Recently Used File List displays a list of recently used files at the bottom of the File menu. This lets you very quickly access your recent work. The default is three files, the maximum is nine. You can also set it to zero to hide your tracks.
And then there’s the Standard Font option, which as you might expect, controls the default font (and its size) for new worksheets and workbooks.
Provide Feedback With Sound plays various sounds that are associated with Microsoft Office program events, such as opening, saving and printing files as well as error messages. Normally I am not a fan of extra sounds and animation, but I find the extra audible feedback is helpful to confirm things are happening as I want them to and to warn me when they are not. Note that this setting affects all other Microsoft Office programs. To change the sounds assigned to different events, go to the Sounds Properties dialog box in the Windows Control Panel.
The Sheets In New Workbook option lets you set the number of worksheets you want when you create a new workbook. The default number is three.
Exploring Custom Lists
The Custom Lists tab is a cool one. Excel uses custom lists to create a list of entries in sequential cells when you drag and drop the fill handle (a little black square in the lower-right corner of a cell) into any cell containing an entry from a custom list. By default Excel contains custom lists for the days of the week and months of the year in full words and shortforms.
But here’s the real power—a tip that has actually gotten me hugs following some of the Excel presentations I’ve done: You can add custom lists for anything you want. I find lawyers tend to like this for creating custom ID numbers for documents in a document brief or for a deposition. You can also import a custom list from a worksheet. One important trick to remember, though, is the first character in a custom list item can’t be a number.
Color Tab Uses
Under the Color tab, you’ll find the standard colors that Excel uses. More importantly, for those who want to tweak which colors appear on charts and graph lines, the Chart Fills setting specifies the first eight colors Excel will use for chart fills and Chart Lines specifies the first eight colors to be used for chart lines. Click on the Modify button to create custom colors, and on the Reset button on the color palette to return to the original colors.
Also, if you want to copy a color palette from another open workbook into your currently active workbook, use the Copy Colors From option.
Safeguards: Your Save and Security Tabs
Listen up, because the Save tab is an important one. The Save AutoRecover Info Every setting automatically makes a workbook recovery file at the time interval you enter in the Minutes box (which can be any number from 1 to 120). Then if your computer crashes, Excel automatically opens the AutoRecover file the next time you start Excel. It may contain edits that would otherwise be lost from your original workbook.
But remember that AutoRecover does not replace the Save command—you must still save your workbook when you finish working on it. If you just hit Ctrl+S every few minutes, you’ll never have to rely on AutoRecover.
The Security tab can also be a valuable safeguard in certain instances. The Password To Open option allows you to set a password to prevent others from opening a workbook unless they have the specified password. This is handy if you want an extra level of privacy when sending a spreadsheet as an attachment to an e-mail message.
Spelling and Beyond
Lastly (at least for now) is the Spelling tab. On this tab you can control the Dictionary language for the worksheet, and which custom dictionary is used for extra words. Although it’s likely you’re mostly dealing with numbers in a spreadsheet, remember also that Excel has the same spell-checking functionality that Word has—just click on Tools, then Spelling or hit F7.
There you have it, a bunch of ways to make Excel work more the way you want it to. I’ve only reviewed some of the setting changes you can make in the Options dialog box, so I encourage you to open up and explore the others yourself to see if there’s something else you can change to suit your preferences.
About the Author
Dan Pinnington helps lawyers avoid malpractice claims and looks for good tech tips in Toronto, ON. He is also Editor-in-Chief of Law Practice magazine.