General

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.

Additional Ways of Displaying a Hidden First Column
A few ways to show a hidden first column in a worksheet.

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.

Annoying Excel Cuts and Copies
Working around how Excel uses the clipboard.

Appearance of Excel on the Taskbar
Do you want Excel to use a task button, on the Windows Task Bar, for each of your open worksheets? Then just make this simple change to get the appearance you want.

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.

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.

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.

Cannot Shut Down Excel
Sometimes Excel can generate error messages that are downright confusing. This tip discusses one particular error message that stops the Excel program from quitting.

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
How to change the arbitrary date used to set the beginning point for serial numbers in Excel.

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 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
Page breaks in a worksheet are helpful, as they allow you to format your data for printout. It can be helpful to see, on-screen, where those page breaks are placed. This tip explains how to make the breaks visible.

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.

Dealing with Circular References
Circular references occur when a formula includes a reference to the cell in which the formula appears. Here's how you can recognize circular references and track them down.

Dealing With the Euro
How to add the Euro symbol to your Excel worksheets.

Defining a Name
How to name a cell or range of cells.

Deleting Names
How to delete named ranges in Excel.

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
If you inherit a computer system from someone else or if you are just starting out with Excel, it may not be entirely clear to you which version of the program you are using. You can easily find out by following the steps outlined in this tip.

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 F1 Key
The function keys are used for a variety of purposes in Excel. If you want to disable any of the function keys (so they don't actually perform their original purpose), there are only a few options open to you. This tip explains some steps you can take to disable the F1 key, but the same techniques can be applied to any other function key, as well.

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.

Disabling the Windows Start Menu Key
The Start Menu key is the one on your keyboard that has the Windows logo on it. You cannot disable the key from within a macro, but this tip provides a resource where you can find out how to do it by editing the Registry.

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.

Displaying Toolbars
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.

Dividing Values
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
Apply an AutoFilter, and drop-down lists appear next to the top of each filtered column. Choose a drop-down list, and you see different filters you can apply to the column. If you want to increase the font size used in the drop-down lists, you're out of luck—Excel doesn't allow any changes. You can, however, get around the problem by adjusting the zoom factor used to display your information.

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 Custom 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
While Excel doesn't have the capability to embed fonts, this Excel tip includes a workaround for the situation.

Embedding Your Phone Number in a Workbook
Using the custom properties in Excel, you can easily add your phone number to a workbook. This allows people to know how to contact you if they should ever have problems with the workbook.

Excel Proficiency Testing
Need to figure out how good someone is with Excel? You need what is commonly called a "proficiency test." Here's the low-down on what is available and what you can do to make your determination.

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.

Exporting from Excel to AutoCAD
If your office uses AutoCAD, you may wonder how you can get information stored in Excel into your AutoCAD drawings. There are known limitations to placing Excel worksheets into AutoCAD drawings, as discussed in this tip. Here you'll also find some suggestions on ways to work around the limitations.

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 Commands in Excel 2007
Excel 2007 introduced a radically new user interface that does away with the traditional program menus. If you knew where some commands were in Excel, this can make finding them in Excel 2007 a challenge. Here's a tip on a handy workbook you can use to make finding commands much easier in the new interface.

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 a Page Break
To force Excel to place a page break where needed, follow these easy steps.

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
Testing is an intrinsic part of developing any software or any worksheet that will be used by others. If you need to create data to use in your testing, the information (and macros) in this tip will be useful.

Getting Audible Feedback
Assuming you have a sound card and speakers on your computer (and who doesn't these days), you can configure Excel to make sounds as a form of feedback. This is a simple change you can make in the Options dialog box, and it may enhance your use of the program.

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
Tired of closing a new spreadsheet every time Excel is opened?

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.

History of Excel
If you need to know the history of Excel, there are a number of different resources available to you. This tip provides some guidance on places you can find some good, reliable information on the Web.

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.

Inserting a Row or Column
Excel makes it very easy to insert a column or row in a worksheet.

Inserting Cells
Using the insert menu to add cells to an Excel table.

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
What to do when Excel returns a "object library not registered" error message.

Limiting Precision
There may be times you need to limit the amount of precision Excel uses in its calculations.

Limiting What is Shown in the Formula Bar
Want to limit how much of the Formula bar is displayed, as you can do in Excel 2007? You are, unfortunately, out of luck if you are using an earlier version of the program.

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.

Making Column Headers Vertically Wider
The header and column labels that appear on a worksheet are a very important part of working with Excel. If your column headers are smaller than you want them to be, it could be because of a font setting in Excel.

Missing 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.

Moving and Selecting Sheets With the Keyboard
Need to navigate through your worksheets, but you don't' want to take your hands off of the keyboard? Using the shortcuts provided in this tip, you can do just that.

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.

Nifty Zooming
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
Using Excel to open non-Excel files.

Precision and Number Display
How Excel handles precision and what it displays may be two 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?

Refusing Online Help
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.

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.

Reversing Dimensions
The maximum dimensions for a worksheet is hard-coded into Excel. This tip explains why this is and, perhaps, one way you can "fudge" the dimensions.

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.

Rounding Religious Wars, Take Two
How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. This tip is the second part of a continuing discussion on this issue.

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 Personal.xls
When you exit Excel and you've made changes to a workbook, the program asks if you want to save those changes. It can be frustrating if the request happens all the time relative to the Personal.xls workbook, particularly if you don't make any changes to it. This tip explains what is really happening when this prompt to save keeps coming up.

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
When you change the display resolution of your screen, it can cause some problems with a few programs. Excel is no exception; you might open the program to find it "off screen" in some way. This tip explains how you can again gain control of the improperly sized program.

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.

Selecting an Entire Worksheet
There are several easy ways to select an entire worksheet in Excel.

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
When you open a workbook, you normally expect Excel to automatically calculate the formulas it may contain. If you aren't seeing automatic calculation by default, then the information in this tip will be helpful.

Setting Your Default Directory
How to set a new default directory for your Excel file.

Shortcut for Viewing Formulas
If you need to switch between viewing formulas and viewing the results of those formulas, you'll love the keyboard shortcut introduced in this tip. It's much easier to use than the normal menu method of toggling the display.

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 troubeshoot 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 problem 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.

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 specify whether each open workbook should have its own task on the Taskbar or not. This tip explains how to make this configuration change.

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 Automatic Backups in Excel 2007
Excel can make backups whenever you save your workbook. If you want to turn off the feature, you might wonder where the control is located. Here's how to find it.

Turning Off Dynamic Menus
If you are using a version of Excel that uses menus (pre-Excel 2007), then you may want to adjust the way that the program displays those 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.

Understanding Lists
What is a list of data, and how do you create one? Here are some guidelines you may find helpful.

Understanding R1C1 References
Excel supports two types of cell referencing. This tip examines both types and explains how you can switch between them.

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 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.

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
Did you know that you can view more than two places at once in a worksheet? You do this by opening new windows, as described in this tip.

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.

Working With Multiple Workbooks
Excel allows multiple workbooks to be open at the same time.

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.

 

More Information

The following are additional topics related to the subject of 'General'. A bracketed number after the topic indicates how many articles are related to that subject.