Tips, Tricks, and Answers
The following articles are available for the 'Configuring Excel' topic. Click the article''s title (shown in bold) to see the associated article.
Activating the Formula Bar with the Keyboard
Hate to take your hands off the keyboard while working on a worksheet? Here's one way to activate the Formula Bar without the need of using the mouse.
Adding a Little Animation to Your Life
Tired of the same old boring Excel look? You can add some life to your worksheets by introducing some animation. Here's what Excel can do and how you can use this feature to add some excitement to your project.
Adding an Equal Sign Tool
In older versions of Excel (97 and 2000) the program had an equal sign tool that appeared right next to the Formula bar. If you miss this tool, you can add it to the Quick Access toolbar by following the steps in this tip.
Automatically Loading Add-ins
Want to load a particular add-in for use with a specific worksheet? Here's a quick way to do it using macros.
Cell Movement After Enter
What happens when you press Enter in a cell depends on how you have Excel configured. Here's the way you can control the action Excel takes.
Changing an Invalid Autosave Folder
Excel allows you to specify where it stores various files used by the program. One location you can specify is where Autosave files should be saved. If you specify a folder that later becomes unavailable, however, you could end up with a headache.
Changing Error Checking Rules
Excel can check the data and formulas in your worksheet to see if it detects any errors. The rules used for this checking can be modified, if you desire.
Changing Excel's Starting Date
When keeping track of dates, internally, Excel uses special date serial numbers that are based upon a specific day in the past. In reality, however, Excel can use one of two starting dates for its date serial numbering.
Changing Gridline Color
Gridlines are very helpful in seeing where cells are located on the screen. You are not limited to black gridlines; hereís how to change them to a color of your choosing.
Changing Input Conventions
Different cultures have different conventions for displaying numbers and for parameters in Excel's worksheet functions. Hereís how you can change which conventions Excel uses.
Changing Ribbon Tool Defaults
The tools available on the Ribbon allow you to easily format information in a worksheet. If you'd like those tools to default to particular settings, you are out of luck. Instead, use the technique described in this tip.
Changing the Ribbon's Size and Look
The Ribbon, while debatably handy, can be downright difficult to use for those with a sight impairment. Here are some ideas on how to make the Ribbon tools more accessible to those having a hard time using them.
Choosing Direction after Enter On a Workbook Basis
Excel lets you specify how it should behave when you press Enter. If you change this behavior, Excel assumes you want it changed for all workbooks on which you might be working. Here's how to adjust it so that the behavior can vary based on which workbook you are using.
Controlling Display of Page Breaks
Do you want page breaks displayed on the screen? Excel allows you to specify whether it should show those page breaks or not.
Controlling Display of the Formula Bar
The Formula Bar is a regularly used feature in the Excel interface. You can, however, modify whether Excel displays the Formula Bar or not.
Controlling How Excel Interprets Percentages
When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it comes to interpreting percentages, however, you can configure the program to match your intent for the numbers you type. Here's how.
Controlling the Behavior of the Mouse Wheel
The mouse wheel, by default, controls scrolling vertically through your worksheet. If you don't want the wheel to control scrolling, you can turn the capability off.
Controlling Where You Edit Cell Contents
You can edit cell information either in the Formula bar or in the cell itself. Here's how you can configure Excel to specify where you want to perform your edits.
Disabling Page Layout View
Excel allows you to display your workbooks using a couple of different views. If you want to disable one of the views, it may be tougher than you thought.
Both Excel and Windows have scores of defined shortcut keys. This can be great for productivity, but it can be a real pain if you press a shortcut key by mistake and it ends up changing how your system works. This is the case with the Shift+Ctrl shortcut key, and here's how to change that behavior.
Displaying Excel's Developer Tab
The Developer tab of the ribbon is the gateway to many advanced features in Excel, including those features related to working with macros. The Developer tab is not visible by default in Excel, but here's how to make sure it remains displayed on your system.
Displaying Page Breaks
Page breaks can be added to a worksheet manually or automatically. If you want to see where Excel places page breaks, youíll want to pay attention to this configuration tip.
Enlarging the Formula Bar
The Formula bar is used to display the formula that appears in a cell. You may want to modify how the Formula bar is displayed. Your modification options are limited, as discussed in this tip.
Entered Values are Divided by 100
Enter a value into a cell and "poof!" it is automatically divided by 100. What's going on, and how can you fix it so that Excel actually enters what you type into the cell?
Fixing the Decimal Point
Donít want to always type the decimal point as you enter information in a worksheet? If you are entering information that always has a certain number of digits after the decimal point, you can instruct Excel to always assume where the decimal point should be placed, even when you donít type one.
Getting Audible Feedback
Want to get a little bit of sound with your data? Excel can provide audible feedback that you may find helpful. Here's how.
Getting Rid of Numbered Columns
Excel normally refers to columns as A, B, C, etc. It also has a referencing format that allows columns to be referred to by number (1, 2, 3, etc.). Having Excel pop into this alternative referencing format all on its own can be a frustrating experience. Here's what is happening and how to fix it.
Increasing Undo Levels
Excel maintains a record of most of the commands you execute so that you can later ďundoĒ those commands, if desired. It is helpful to understand the way that Excel maintains this list of commands and how it can be changed.
Loading Unwanted Files at Startup
Imagine how painful it would be if every time you started Excel it tried to load all the files in your root directory? That is what was happening to Stephen, and here's how to fix it.
Problem with Missing Context Menu Option
When you right-click a cell, does it seem that the Context menu is missing an item or two? Here's how to get those items back.
Saving Excel Configuration Settings
Excel lets you change lots of settings that affect the configuration of your system. At some point you may want to save those configuration changes so that you can have a "snapshot" of your Excel system. That may be easier said than done, though.
Selected Cells Not Shaded
Does the pale coloring scheme used to mark selected cells by Excel 2007 drive you nuts? You arenít alone. Hereís an idea of what you can do to reduce the frustration.
Setting a Default File Format
Excel normally saves workbooks using a default file format that is peculiar to your version of the program. You can configure Excel to save workbooks in an entirely different format by using the information in this tip.
Setting the AutoRecover Directory
Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ended abnormally. You can specify where you want these AutoRecover files stored by using the information in this tip.
Setting the Calculation Default
Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, unless certain criteria are met. Here is a discussion of how this all works.
Slash Key No Longer Works as Expected
Press the slash key and Excel may display a series of keyboard commands near the ribbon. If this behavior drives you nuts, here's how to control it.
Specifying the Behavior of the Enter Key
You type information in a cell and press Enter. What happens then? Excel allows you to specify exactly what should happen, as described in this tip.
Specifying the Number of MRU Files
MRU (most recently used) files can be a great help when you work with a given set of common workbooks. Excel allows you to easily adjust the number of MRU files it tracks for you.
SUMIF Doesn't Recalc Automatically
What are you to do if you suspect that some of your worksheet functions aren't recalculating automatically? Here's some ideas you can check out.
Tab Key Jumps a Screen at a Time
Have you ever pressed the Tab key, expecting to move to the next cell in your worksheet, only to have Excel completely change what you see on the screen? This could be due to a setting deep inside Excel related to transitioning from other spreadsheet programs.
Tab Key Won't Move from Cell to Cell in Locked Worksheet
Normally the Tab key can be used to move from one cell to another in Excel. If this cell movement doesn't work for you, it could be because of the way you have the program configured.
Turning Headers On and Off
Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off completely.
Turning Off AutoFill
AutoFill is a great editing tool that comes in particularly handy when you are first creating a worksheet. You may, at some point, want to turn off the feature. This tip shows how you can do this.
Turning Off Display of Zeros for All Worksheets
Some people like zero values displayed; others do not. Excel allows you to easily turn the display on or off for a single workbook. Here are some ideas for doing it for all workbooks.
Turning Off Error Checking
A little green triangle in the corner of a cell means that Excel thinks there is an error with the cell contents. If those triangles bother you, here's how to turn them off.
Turning Off Insert Options
When you insert rows, columns, or cells in a worksheet, does the resulting Insert Options icon bother you? Here's how to get rid of it.
Turning Off Names
You can use some of the tools in Excel to convert cell references in formulas into names. Converting back (from names into cell references) is not so easy, requiring manually editing the formulas.
Turning Off Paste Options
Paste some information into a worksheet and Excel helpfully displays some options related t the paste operation. If you don't want to see those options, here's how to turn the feature off.
Turning Off ScreenTips
ScreenTips are one of those artifacts of Microsoft trying to make Excel be overly helpful. If the ScreenTips bother you, you can turn them off by following this tip.
Understanding Manual Calculation
When you make changes in a worksheet, Excel automatically recalculates everything that may be affected by that change. If your worksheet is complex or huge, having the program recalculate after every change can slow down data entry. Here's how to force Excel to only recalculate when you want to recalculate.
Working with Lotus 1-2-3 Spreadsheets
If you've got some older data around your office that started in an old Lotus 1-2-3 system, you may want to open it in Excel. How Excel works with that older data depends on a couple of program settings you make.