Tips, Tricks, and Answers
The following articles are available for the 'General Information' topic. Click the article''s title (shown in bold) to see the associated article.
A Shortcut for Switching Focus
While not technically an Excel-only tip, the shortcuts described in this tip will help you switch focus from your workbook to the Windows desktop. These shortcuts will make working in the Windows environment easier.
Appearance of Excel on the Taskbar
Do you want Excel to use a task button, on the Windows Taskbar, for each of your open worksheets? Then just make this simple change to get the appearance you want.
Arranging Workbook Windows
If you find yourself working with a number of different workbooks at the same time, you may want to arrange your desktop so you can see each of the workbooks. Here's an easy way to arrange the workbook windows to reflect your working desires.
Can't Access the Registry
Many Windows applications rely on information stored in the Registry. If that information cannot be accessed, the application may not work as desired. Here's how to overcome a specific Registry-related problem with Excel VBA.
Changing the Color Used to Denote Selected Cells
When entering data into a range of cells, the cell in which you are working appears in a different color than the other cells in the selection. You can change these colors within Windows (not Excel) and this tip explains how.
Changing the Default Font
It makes sense that when Excel creates a blank workbook, it must figure out which font to use for that workbook. Fortunately, Excel allows you to modify the default font (and font size) it will use.
Changing Your Name
One of the many pieces of information that Excel keeps track of is your name. If you want to change your name for Excel's purposes (perhaps to add or remove a middle initial), then you need the information in this tip.
Clearing Large Clipboard Entries
Need to clear out a large amount of information saved on the Clipboard? All you need to do is to replace it with a small amount of information, as described in this tip.
Closing Excel when Closing the Last Workbook
Have you noticed that when you close the last workbook in Excel, the program window itself often stays open? This relatively new development in Excel can be frustrating to those who want the program to go away when you are done working with that workbook. Here's some ideas on why it happens and what you can do about it.
Controlling Automatic Backups
Excel can make backups whenever you save your workbook. If you want to turn the feature on or off, this tip explains how to do it.
Converting PDF to Excel
Reports and other formal documents are often distributed in PDF format so that they can be read and printed on a variety of different computer systems. If you need to get information out of a PDF file and into an Excel workbook, the task can be challenging.
Creating New Windows
If you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your data. It is easy to do, as described in this tip.
Determining Your Version of Excel
Want to find out exactly what version of Excel you are using? Here's how to get to the info.
Differences between Tables and Named Ranges
Excel allows you to define the data in a worksheet as a table. Doing so can provide some clear benefits over simply accessing the data using named ranges.
Disabled Page Setup Tools
It can be frustrating if you expect to use some of Excel's tools normally available on the ribbon but those tools are disabled for some reason. Here's one such scenario and some ideas on what is happening.
Disabling a Function Key
Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather easy to do. Here's how to do it.
Disabling the F1 Key
Tired of hitting the F1 key by mistake and pulling up the Help system? Here are a couple of ways (one drastic and one not so drastic) that you can disable that pesky F1 key.
Disappearing Status Bar
Ever had your Excel status bar disappear unexpectedly? Here's some ideas on why this may be happening.
Displaying a Count of Zeros on the Status Bar
Excel allows you to display the results of several common worksheet functions on the status bar. The available functions are limited, and you may wonder if you can add other functions to what is available.
Displaying a Hidden First Column
Hiding columns is easy, even hiding column A. How, then, do you get that left-most column displayed again? Here are a few techniques you can use.
Dividing the Screen Unevenly between Two Workbooks
When working with multiple workbooks, you'll typically want to resize the workbook windows so you can see the data from each worksheet at the same time. Excel provides some features for divvying up the screen between workbooks, but you may desire a more customized approach to dividing the screen.
Drop-Down List Font Sizes
Excel has several features that cannot be customized. The font size in the drop-down lists is one of them. If you need make changes to this, however, there is a potential workaround. This tip explains more.
Embedding Your Phone Number in a Workbook
Want to provide a bit of contact information in a workbook? A great place to do it (out of sight, but not inaccessible) is in the properties that Excel maintains for each workbook.
Empty Cells Triggers Error
By default, Excel provides some feedback on your formulas so that you can easily locate potential errors. If you get tired of Excel pointing out some of these errors, you can adjust how it does its checking.
Error when Double-Clicking Workbook Files
When you double-click an Excel workbook on your system, Windows has to do a lot of behind-the-scenes work to start Excel and allow it to display the workbook. That means there are plenty of opportunities for things to go wrong. This tip provides some ideas on how you can track down and correct the things that may go wrong.
Need to find out how good you are with Excel? Here are some places you can check out to quiz yourself.
Excluding a Specific Add-In at Startup
Got an add-in that you don't want loaded each time that Excel starts up? Here's a few ways that you can exclude it.
F4 No Longer Changes Cell References
Excel has a wide variety of keyboard shortcuts that can help make it easier to use the program. When one of those shortcuts doesn't work like we expect, it can throw us off. Here's one such situation with the F4 key.
Finding the Number of Significant Digits
When looking at a number, you may wonder how many significant digits it contains. The answer is not always an easy one, because of the rules of what constitutes significance. This tip provides a quick overview and possible solution to the question.
Fixing a Numeric Keypad Key
We all expect the keyboard keys to operate as normal, and when they don't, it can be bothersome. Geraldine had such a problem, and this tip offers a few things she can check to solve the problem.
Forcing Stubborn Recalculation
Have you ever recalculated a worksheet, only to notice that not everything calculated as it should? Here's a way you can force a complete recalc, along with some ideas on why you might not be seeing the results that you expect.
Frequent Workbook Recovery Prompts
When you open a workbook, Excel examines that workbook to make sure it can understand the data it contains. This can lead to warnings if Excel can't make sense of something. This tip examines why this may happen and provides some ideas on what to do about it.
Generating Random Testing Data
Need to test your formulas? Then you need some testing data that you can use to see if the formulas function as you expect. Here's some different ways you can generate some random data for testing.
Going to the Corners of a Selected Range
When you select a range of cells (particularly if it is a large range of cells), you may not be quite sure if you've selected everything you needed to. One way to check is to move around the corners of the selected range, using the shortcut key described in this tip.
Grabbing a User's Name from Excel
One of the settings you can make in Excel is to specify a user's name. This name is accessible through macros, and can therefore be used within your worksheets. This tip examines the simplest method of accessing the userís name.
How Excel Stores Dates and Times
Excel stores dates and times internally using what is called a serial number. This tip explains how that serial number is derived.
Ignoring Other Applications
Do you want Excel to ignore other applications that may be running on your computer? You can configure the program to do just that.
Inadvertantly Getting Rid of Frozen Panes
Excel provides quite a bit of flexibility in displaying your data. You can have multiple windows visible for the same worksheet and you can freeze panes in each window, as desired. If you want to have Excel automatically copy pane information from an original window to a new window, you'll love the information in this tip.
Inserting the User's Name in a Cell
Need to understand who is using a particular workbook? There are a number of ways you can find out, as discussed in this tip.
Iterating Circular References
Does your data require that you perform calculations using circular references? If so, then you'll want to be aware of the way in which Excel handles those references.
Jumping to the Real Last Cell
Jumping to the last cell in a worksheet should be easy, but you may not always get the results that you expect. This tip looks at why this could be the case and how you can get around the problem.
Loading Lotus Spreadsheet Files
Spreadsheet programs have been around for a long time, and you may have some data saved in a format used by a spreadsheet program you no longer use. One such program is Lotus 1-2-3. If you need to get your old Lotus files into a modern version of Excel, here are some ideas you can try.
Losing Data in a Shared Workbook
When you create a shared workbook, you run the risk of losing some of the data in that workbook. Here's a discussion about why this occurs and what you can do about it.
Maintaining the Active Cell
Move from one worksheet to another, and Excel selects whatever cell was last used in the worksheet you are selecting. If you don't want this behavior (you want to have the same cell selected on the new worksheet as on the old), then you can apply the techniques in this tip.
Making AutoComplete Work for an Entire Column
AutoComplete is a great feature for quickly adding data to a worksheet. If you are confused by why some things are picked up by AutoComplete and others aren't, you may find this tip helpful.
Measuring Efficiency of Formulas and Macros
As the limits on what you can store in Excel have increased, so has the need to consider how to make your workbooks and macros as efficient as possible. This tip examines some resources you can use to improve the performance of your formulas and macros.
Moving and Selecting Sheets with the Keyboard
Hate to take your fingers off the keyboard? Here's how you can move from worksheet to worksheet without touching the mouse.
Odd Arrow Key Behavior
Press the up or down arrow keys, and you expect Excel to change which cell is selected. If this doesnít occur on your worksheet, it could be due to a simple inadvertent keypress on the keyboard.
OLAP Support in Excel 2007
In earlier versions of Excel the program provided support for OLAP cubes. Not so in Excel 2007. Here's why and an idea of how you can work around the fact it is no longer included.
Picking a Workbook Format
Need to share workbook information with a wide number of people? It can be puzzling to figure out which version of Excel to use. Here are some ideas that may help.
Quickly Changing Windows
Need to quickly move from one open Excel window to the other? The program provides a couple of handy shortcuts you can use, but if you want to create a Quick Access Toolbar tool instead, you'll want to use the macro in this tip.
Need to modify how a cell reference, in a formula, is constructed? The shortcut described in this tip will help you step through each of the various referencing modes that can be used by a cell.
Relative Worksheet References
Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it adjusts everything except the names of any worksheets that may be referenced in the formula. Here's how you can get around that lack of change.
Add-ins are used to extend Excel's capabilities in lots of different ways. If you want to get rid of an add-in completely, you'll love the technique described in this tip.
Restoring the Analysis ToolPak
Add-ins for Excel, such as the Analysis ToolPak, are stored in files on your hard drive that can be deleted. If you delete the ToolPak files by accident, you may be looking for a way to get the add-in back where it belongs.
Saving Movement on Enter with a Workbook
Press Enter when working in a workbook and Excel moves to a cell adjacent to the one in which you were working. If you want, you can adjust the direction in which Excel makes the move. Here's how to save a direction with each workbook you create.
Saving Non-Existent Changes
Open a workbook, look at the data, start to close the workbook, and you are asked if you want to save your changes. What gives? You made no changes, right? Here's why you see that message even if you made no overt changes to your data.
Seeing Excel's Program Window
Have you ever opened Excel to find that the window you saw yesterday is not the same as it is today? Sometimes, for various reasons, your program window appears too big for your screen. Here are some things you can do to correct this problem.
Selecting a Suggestion with the Keyboard
Excel tries to anticipate what you want to type into a cell, particularly when it comes to entering formulas. Here are the keys you need to be able to use the keyboard to select from the suggestions that Excel makes.
Selecting Multiple Cells by Mistake
Click on a cell and you expect the single cell to be selected. If you instead get a group of cells, it can be frustrating to figure out why.
Setting the Width for Row Labels
Excel displays, by default, a row label or heading at the left side of each row on the screen. As you scroll down the screen, the width of this heading increases to accommodate the number of digits it needs to display. This can cause some interesting side effects, as discussed in this tip.
Shortcut to Move between Two Worksheets
Moving between to adjacent worksheets is easy; Excel provides a shortcut key to do the trick. If you want to move between two non-adjacent worksheets, you'll need to get creative.
Slowing Down Mouse Selection
Ever tried to select a range of cells using the mouse, only to have the cells scroll by so quickly you can't make the selection? It's happened to all of us; here's what you can do to get around the problem.
Speeding Up Large Worksheets
If your worksheet gets large enough, you may notice a severe slowdown when it is recalculated. This tip provides some guidance on how you can perhaps speed up those operations by changing some formulas to static values.
Does your worksheet or workbook not always recalculate like you expect? If so, then some of the ideas in this tip may help solve the situation.
Starting in Safe Mode
By using a command-line switch, Excel can be started in safe mode. This means that the program is loaded with bare-bones functionality. Knowing how to start in this mode can be very helpful when you are trying to troubleshoot any errant behavior by the program.
Status Bar Summing No Longer Available
When you select a range of cells, Excel normally displays the sum of those selected cells on the status bar. If the sum no longer appears, here's how you can get it back.
Stopping Help from Using Online Resources
Excel relies upon the Internet to grab help information. If you donít want Excel to seek help online, you can configure the program using the information in this tip.
Synchronous Scrolling with More than Two Windows
Synchronous scrolling of different windows can be very helpful with some worksheets. Excel allows you to synchronize the scrolling of two windows, but you may want to synchronize more than that.
Tasks for Each Workbook
Excel allows you to control how it uses the Windows Taskbar. This tip explains the two ways Excel can use the Taskbar and how you can configure Excel to use either of those ways.
Thoughts and Ideas on Significant Digits in Excel
Ruminations and reflections about significant digits in Excel. Includes examples of how significant digits can affect the outcome of various functions and formulas.
What is a list of data, and how do you create one? Here are some guidelines you may find helpful.
Understanding R1C1 References
Referring to cells is typically done using a letter and a number, which represent the column and row. That's not the only way that Excel can refer to cells, however. Here's an alternative method of designating cell references.
Understanding Relative and Absolute Addressing
In Excel you can reference a cell in a formula by entering the coordinates for the cell you want to reference. This can affect how that formula is later copied to other cells. If you want to modify how Excel changes your cell references in formulas, you need to understand the difference between relative and absolute addressing.
Using a Single Instance of Excel with Two Monitors
Working on a computer system that has multiple monitors can help increase your productivity. If you want to work with multiple workbooks in a single instance of Excel across those monitors, hereís the way to do it.
Using More CPU Power when Calculating
Today's PCs are more powerful than ever, but you can still have slowdowns when it comes to calculating large workbooks. Here's a high-level overview of how Excel uses the CPU in your system, and why the CPU's usage may not be at the level you desire.
Viewing More than Two Places in a Worksheet
If your worksheet gets big enough, it is easy to spend a lot of time navigating back and forth between different areas. Why not look at two places in the same worksheet at the same time? Here's how.
Weird Actions for Arrow Keys and Enter
If your arrow keys and the Enter key arenít working as you expect them to, the problem could have any number of causes. This tip discusses some of the things you can try in order to rectify the situation.
Weird Mouse Shortcut
If you like to use the mouse in your worksheet navigation efforts, you'll want to pay attention to this tip. Here you discover a way you can navigate using the mouse and the borders of the selected cell.
Where Is that Name?
Want to easily see the location of named ranges in your worksheet? It's easy; all you need to do is use the familiar Zoom tool in a way you've never thought of before.
Working in Feet and Inches
Your chosen occupation may require that you work with linear distances in feet and inches. Excel can do this, to a degree, but you need to understand what the limitations are.
Working with Record Numbers
Want to keep track of various rows in a data table through the use of record numbers? Here are some options and considerations you should keep in mind.