Tips, Tricks, and Answers
The following articles are available for the 'Tools' topic. Click the article''s title (shown in bold) to see the associated article.
Accessing Dependent and Precedent Information
The auditing tools provided in Excel can provide some very helpful information about how your formulas and data are related to each other. If you want to grab the dependent and precedent information maintained by the auditing tools so you can use it in different ways, this tip can help.
Converting Imported Information to Numeric Values
If the information you import into Excel is treated as text by the program, you may want to convert it to numeric values. This tip explains some great ways you can approach the problem and get the numeric values you need.
Counting with Subtotals
There are a variety of ways you can count information in different groupings. One convenient way is to use the subtotaling capabilities provided in Excel.
Want to create your own add-in? Excel makes it easy to do. Here are all the steps you need.
Customizing Quick Access Toolbar Icons
The standard way to customize Excel is to add tools to the Quick Access Toolbar. The program provides only a limited capability to change the icons used to represent those tools.
Excel Refuses to Put Page Breaks between Subtotal Groups
Page breaks not appearing where you expect them in your subtotaled data? It could be because of a setting you made in your page setup.
Finding the Analysis ToolPak Add-In
The Analysis ToolPak is used to add some very handy capabilities to Excel. If you don't have it installed, and you can't find your original Microsoft Office discs, then you may be at a loss as to what you can do. Here are some ideas.
Fixed-Width Settings when Converting Text to Columns
The Convert Text to Columns capabilities of Excel are very helpful when pulling apart information. When working with fixed-width data, you may want a way to "override" what Excel suggests as breaking places for the data. Here are some ideas.
Inserting a Sound File in Your Worksheet
Some worksheets are better understood through the spoken word or with musical accompaniment. Sound files can be easily inserted into a worksheet, providing a multimedia experience for all who use it.
Leaving Trace Precedents Turned On
The Trace Precedents auditing tool can be quite helpful in seeing which cells "feed into" a particular formula. The results displayed by the tool cannot be saved with the workbook, but you can create a macro that will display precedents whenever a workbook is opened.
You've turned on Highlight Changes, but how do you know what has been changed? This tip explains how Excel displays those changes.
When you add subtotals to a worksheet, Excel typically places them in the same column that you are subtotaling. If you want those subtotals moved to a different column, you'll appreciate the techniques described in this tip.
Don't want your form controls to print out with your worksheet? Here's how to make sure that Excel excludes them from the printed page.
You've reviewed the changes that were made to your workbook using the Highlight Changes tool. Now you need to remove the revision marks. Here's how to do this.
Have you ever wanted to take a "picture" of a part of a worksheet and put it in another section? This tip explains how to use the Camera tool to capture snapshots of your worksheets.
Tracing Dependent Cells
Cells that use the information in a particular cell are called dependent cells. Excel provides auditing tools that allow you to easily determine whether any other cells are dependent on whatever cell you want to analyze.
Tracing Precedent Cells
Cells that affect another cell are called precedent cells. If you need to know which cells affect a particular cell, Excel provides some great auditing tools you need to discover.
Turning Off Speech Capabilities
Excel can talk to you, reading back whatever you enter into a cell. If you want to turn this capability off, you'll want to note the ideas in this tip.
Turning Off Track Changes without Unsharing
The Track Changes tool in Excel can be helpful, but it can also be aggravating because it doesn't allow you to use it on a shared workbook. This tip examines the precise interaction between Track Changes and workbook sharing.
The primary way to extend what Excel can do is through the use of add-ins. This tip explains what they are and the benefits of using them.
Excel provides some great tools that can help you see the relationships between the formulas in your worksheets. These are known as auditing tools and are introduced in this tip.
Undoing Smart Tag Exclusions
Depending on who you ask, Smart Tags can be really cool or really distracting. If you fall on the "cool" side, you may make a mistake and exclude a Smart Tag that you later want to reinstate. Here's how to do it.
Using Check Boxes
Check boxes, just like those used in Windows dialog boxes, can be a great addition to a worksheet. Here's how to add them and configure them for your use.
Using Custom Add-Ins
Once you've created your custom add-in, you need to know how you or other people can use it. Here are the simple steps to making your add-in part of Excel.
Using Multiple Print Settings
Do you have a worksheet from which you need to print only portions of the data available? There are two ways you can control what gets printed—by using a macro or by using custom views.
Using Revision Tracking
Want to keep track of the changes other people make to your workbook or even your own changes? Excel makes gathering this information easy. Here's how to use the handy Revision Tracking feature.
Using Subtotals and Totals
You can insert subtotals and totals in your worksheets by using either a formula or specialized tools. This tip explains how to do both.
Watching Cell Values
Want to know what is happening in certain cells in your worksheet? Using the Watch Window is a great way to keep an eye on specific targets.