Tips, Tricks, and Answers
The following articles are available for the 'General' topic. Click the article''s title (shown in bold) to see the associated article.
A Ruler in Excel
A few workarounds for the fact that Excel does not have a built-in ruler.
Accessing Old Excel Data
If you have some legacy data stored in very old spreadsheet files, you may be wondering how to get that data into an Excel worksheet. Here's some ideas that can help you track down a solution.
Adding an Equal Sign Tool
Excel used to provide, prior to Excel 2002, an equal-sign tool near the Formula bar. If you miss this tool, you may want to add a similar tool back to your toolbar using the information in this tip.
Always Open at 100% Zoom
Tired of shared workbooks opening at some strange zoom factor that makes viewing your data difficult? Here’s how to make sure that your workbooks always open at a standard zoom factor you decide upon.
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.
Automatic Recalculation Not Happening
Excel 97, under certain circumstances, has a problem recalculating worksheets reliably. This tip tells you how to patch that issue using a link to Microsoft's Knowledge Base.
Backing Up Your AutoCorrect Entries
Want to protect the information that you may be stored in your AutoCorrect entries? Just find a special type of file on your system and you can back up to your heart's content.
Backing Up Your Customized Toolbars
After customizing your Excel toolbars, it is a good idea to make a backup of the file that contains the information. Here's how to do it.
Big File Memory Blues
Excel places limits on how much memory your workbooks can use. Despite these limits, it is possible to create a workbook that you later cannot open. Here's the lowdown on why this occurs and what you can do about it.
Calculating Monthly Interest Charges
Trying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a myriad of ways you can calculate balances. Here's a discussion on a few ways you can try.
Canceling a Command
Need to cancel a command you've already started? It is as easy as pressing a single keystroke.
Canceling a Menu
Want to cancel whatever menu command you started to use in Excel? The normal method is by pressing Esc, but this is not the only way to turn your attention to a different task. This tip explains three or four different ways you can stop dead in your tracks.
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 Horizontal Orientation
One of the international features of Excel is the ability to switch the orientation of how information is presented. This tip shows how you can change Excel’s configuration to make text appear right-to-left instead of left-to-right.
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 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 Toolbar Location
Toolbars don’t need to be tethered to the top of your program window. Although they are right at home there, you may want them some place else on your desktop. This tip explains how to “undock” toolbars and move them wherever your fancy strikes.
Changing Your Company Name
The installation process for Office and Excel allows you to specify a company name. If your company name later changes, you may be faced with the task of figuring out how to change it in the program. It is not as easy as it might at first appear, as described in this tip.
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.
Conditionally Playing an Audio File
You can add audio files to an Excel worksheet, but what if you want a particular audio file to play only when a value in a cell passes a threshold you specify? There is no built-in way to do it in Excel, but you can implement this capability using the techniques in this tip.
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.
Counting the Times a Worksheet is Used
Do you need to know how many times a worksheet has been used? Excel doesn't track that information, but you can develop some ways to track it on your own.
Defining a Name
One of the great features of Excel is that it allows you to use named ranges. These can make your formulas much easier to understand and work with. Here's how to start creating your own named ranges.
Excel allows you to associate names with cells or ranges of cells. If you ever want to delete those names, you'll need the info in this tip.
Determining Your Serial Number
The serial number assigned to your copy of Excel is valuable. It allows you to get support and is necessary for some upgrading needs. This tip explains how to find the number if you've forgotten it.
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.
Disabling Excel's Help System
The Help system built into Excel can be quite a lifesaver when you need to find that quick tidbit that is slipping your mind. But if you don't want people to access those answers—say, if they need to take a test about Excel—then you need a way to disable it.
Disabling the Insert Key
Tap the Insert key and you can start overwriting information already in a cell. If you don't want to do this, one way to avoid the problem is to disable the Insert key itself. Here's some ideas on how it can be done.
Disappearing Status Bar
Ever had your Excel status bar disappear unexpectedly? Here's some ideas on why this may be happening.
Disappearing Toolbar Buttons for Macros
One of the important configuration files for Excel is known as the XLB file. You should periodically make backups of this file, in order to protect the customizations you've performed on Excel's toolbars.
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.
Toolbars allow you to access common tasks quickly and easily. Excel provides a wide variety of toolbars, and you can even create your own. This tip explains how to make Excel’s various toolbars visible on the screen.
When working with large numbers, you may need a way to quickly divide a range of those numbers by a specific value. Here's the absolute fastest way to make the change.
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.
Easily Deploying Customizations
When you create a whole set of customizations for Excel, you may want to share them with others in your office or workgroup. This tip provides some guidance on how you can best do that.
Editing the Custom Spelling Dictionaries
Excel provides spell-checking capabilities on the text you enter in a worksheet. It utilizes the same dictionaries and custom dictionaries used by other Office programs. If you need to edit the custom dictionary, how you do it depends greatly on the version of Excel you are using.
Embedding Fonts in Excel
Unlike some other programs (like Word), Excel doesn't provide a way for you to embed fonts in a workbook. Here's a workaround for that shortcoming.
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.
Excel Won't Display Different Windows in the Taskbar
If you want individual Taskbar buttons for each open Excel workbook but cannot seem to get those buttons, the culprit could be two individual settings. This tip explains how to correctly change those settings, in both Excel and Windows.
Fill Color Doesn't Display
If you apply a fill color to a range of cells and notice that the color doesn’t show up on the screen, it could be because of a setting in the Accessibility Options of Windows. This tip explains the potential cause and shows you how to change it.
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 "Can't Find Files" Errors
If you get errors about unfindable files when you first start Excel, it can be frustrating. Here's how to track down and fix the problem.
Floating Information in a Frozen Row
You can freeze information in rows or columns using one of the built-in features of Excel. As you move up or down in the worksheet, the information in the first rows can remain visible. If you want something in a row to always remain visible as you move left and right, the proposition becomes a bit trickier. This tip explains a couple of ways you can approach 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.
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.
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 Context-Sensitive Help
Need help on a particular topic, dialog box, or command? You can use Excel’s powerful help system to get just the information you need. Just press Shift+F1 and you can get help on any specific item you want.
Getting Rid of the Startup Spreadsheet
When you start Excel, you normally see a blank worksheet displayed. Here's how to get rid of it.
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.
Hiding a Huge Number of Rows
Need to hide a large number of rows? It's easy to do if you combine a few keyboard shortcuts. Here are several techniques you can use.
Identifying Merged Cells
Merging cells is a common task when creating worksheets. Merged cells can play havoc with the normal functioning of some of Excel's tools, however. Knowing where those cells are located could be helpful so that you can determine whether to remove them or not.
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.
When developing worksheets, you often need to make room in your existing information for new information. One of the chief ways to do this is to insert cells, as described in this tip.
Inserting Different Dashes
Excel supports several types of dashes. This tip describes those different types and explains how to enter them in a cell.
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.
Library Not Registered Error
There are lots of error messages that can pop up in Excel from time to time. One of the most confusing may be the "library not registered" error message.
There may be times you need to limit the amount of precision Excel uses in its calculations. Here is one way to accomplish that.
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 Accuracy of Significant Digits
If you work in the sciences or mathematics, you know that significant digits are important. This tip answers questions about whether you can force Excel to maintain the same number of significant digits that you enter into a cell.
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.
Moving from Sheet to Sheet 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.
If you are using a mouse that has a center wheel, you can use the wheel to zoom in and out of your work. This tip shows how easy it is to make the necessary configuration change in Excel.
Opening Non-Excel Files
Not all data is created in Excel. Indeed, you may have data in files created by many other types of programs. You might be able to open those files in Excel; just try!
Precision and Number Display
Do you need to work with very precise numbers in your worksheets? If you do, then it is imperative you understand precision within Excel. How Excel handles precision and what it displays may be two entirely different things.
Precision in Excel
The best way to understand why Excel provides the answers it does is to examine how it handles precision. This tip provides a high-level overview of precision, with a definite Excel flavor.
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 toolbar button you can use instead, you'll want to use the macro in this tip.
Quickly Switching Between Spreadsheet Windows
Using the keyboard to switch between Excel spreadsheets.
Reducing the Size of the Save As Dialog Box
Dialog boxes are designed to be limited in scope, so that they cover only a portion of your screen. What if a dialog box fills the entire screen, however? How do you get it back to a more manageable size?
Removing Personal Information
As you create and work on your workbooks, Excel can include sensitive personal information with the data. If you want to get rid of this information, you can do so by using the tools described in this tip.
Rounding Religious Wars
How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. This tip discusses the brouhaha and looks at different takes on the issues involved.
Running Out of Memory
Do you get an error when you try to insert just one more chart in your workbook? It could be because of an obscure resource-related problem in Excel, or it could just be that you are running out of resources. This tip explains how to track down what the problem may be.
Saving Changes in the Personal Workbook
The Personal workbook is a special place used to store information and macros that you can access from all the other workbooks on which you work. If you are confused as to why Excel continually asks whether you want to save this special workbook, here are some ideas on why it may be happening.
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 an Entire Worksheet
While editing, you may need to select everything in a worksheet. Excel provides three easy ways you can accomplish this.
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.
Selecting Noncontiguous Ranges with the Keyboard
It's easy to select non-contiguous ranges using the mouse, but may seem more daunting if you are simply using the keyboard. Here's how you can do it, using just the Go To dialog box.
Selecting Tabs in Dialog Boxes
A Windows tip to assist with Excel tab access.
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.
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.
Setting Your Default Directory
You may want to have all your Excel workbooks stored in a specific location on your system. Here's how to set the default directory used by the program.
Slow Excel Response Times
Many people use Excel on a notebook computer, so they can do work at the office and elsewhere. In some situations, Excel may run very slowly when not connected to the office network. This tip helps troubleshoot the problem and make Excel work as quickly as it can.
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.
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
The two newest versions of Excel rely 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.
Task Pane Doesn't Appear Properly
Excel 2002 and 2003 include a Task pane that provides quick access to common tasks. The Task pane is normally visible when you first start Excel. If it isn't, here's the reason why and what you can do about it.
Taskbar Setting isn't Sticky
Understanding how Excel sets the taskbars upon opening.
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.
Turning Off Dynamic Menus
You may want to adjust the way that Excel displays its various menus. This tip explains how you can turn off the dynamic menu feature in Excel.
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 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.
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 the Same Range Name on Different Worksheets
Defined names can be a great boon when working in a worksheet. Usually names are available throughout an entire workbook, but you may want to limit the scope of a name so that it is only applicable within a specific worksheet. Here's how to do it.
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.
Viewing Workbook Statistics
Excel keeps track of a range of stats about each workbook you use. If you want to take a look at those stats, it's easy; just display the Properties dialog box for the workbook.
Viewing Your Work Full-Screen
Want to use the maximum space possible for displaying information on screen? You'll want to learn how to use the full-screen display mode of Excel, then.
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.
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.
Workaround for Multiple Rows of Worksheet Tabs
If you've got a lot of worksheets in your workbook, you may want to display their tabs in to rows at the bottom of the Excel window. There is no way to do this, but you might like one of the other helpful suggestions in this tip.
Working With Multiple Workbooks
Need to do work in more than one workbook at a time? For many, this ability is a necessity. Excel allows you to easily work with multiple workbooks, as described in this tip.
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.
Zooming with the Keyboard
Excel doesn't provide a keyboard shortcut that allows you to zoom in or out on your workbook. It is easy, however, to create a couple of short macros that can do the zooming and be tied to keyboard shortcuts.