Excel XP Tips and Tricks!
|Excel XP Tips|
Prevent error values from printing in Excel 2002
|In previous versions of Excel, you were forced to create custom formatting or
formula solutions to avoid displaying worksheet errors like #DIV/0! or #VALUE!
when you printed a worksheet. Excel 2002 simplifies things by letting you
control how these errors are handled when it comes time to print. You can
simply have the offending cells appear blank, or display a substitute string in
place of the error.
To take advantage of this feature, select File | Page Setup from the menu bar.
Then, click on the Sheet tab. Finally, select one of these choices from the
Cell Errors As dropdown list:
Click OK when you've finished. Choosing Displayed prints the original error
value, while the other choices replace the errors with blanks, dashes, or #N/A
|2. Paste column widths in Excel 2002||Have you ever pasted data and been disappointed that
the column widths didn't come along for the ride? In
Excel 2002 there's a quick fix for that. Copy and
paste the data as you normally would. Then, immediately
click on the Paste Options icon that appears at the
bottom right corner of the pasted selection. From the
shortcut menu, select the Keep Source Column Width
option. The selection will then take on the same column
widths as the original data.
Link an Excel worksheet to a Word document
|Microsoft Office XP includes a new option that lets you easily link an Excel worksheet to a Word document. Follow these steps to see how it works:
In Excel, click and drag to select the cells you want to copy to your Word document.
Click Copy to place your selection on the clipboard.
In the Word document, click where you want the worksheet to be located and click Paste.
Click the Paste Options Smart Tag button that appears at the bottom-right corner of the worksheet.
If you want the worksheet to remain an Excel object, select Keep Source Formatting And Link To Excel from the Paste Options menu. If you want to convert the worksheet to a Word table, click Match Destination Table Style And Link To Excel.
Every time a change is made to the data in the cells in the original worksheet, it will be reflected in the Word table. If you choose to keep the source formatting, any formatting changes made in Excel will also be copied.
|4. Change the number of worksheets in a new Excel 2002 workbook||By default, Excel creates three worksheets in every new
workbook. If you find that you routinely don't use all three
or that you require more, change the default number that
Excel creates. To do so, select Tools | Options from the
menu bar. Then, click on the General tab and change the
number in the Sheets In New Workbook spinner box to the
number of desired sheets. Click OK when you're finished.
Copy an Excel Table and Its Formatting into Word
When you copy a table of data from Microsoft Excel version 2002 into Microsoft Word version 2002, you can choose to keep the formatting you applied to the table in Excel. Or you can match the destination table style to format your table in the Word default table style.
To copy a table from Excel to Word:
Open the Word document you want to copy to and the Excel worksheet that contains the table.
In Excel, select the rows and columns of the table you want to copy.
1. On the Excel Edit menu, click Copy.
2. Switch to Word, and then click where you want the table to appear.
3. On the Word Edit menu, click Paste.
4. Click the Paste Options smart tag, and then click one of the following:
To keep the formatting that you applied in Excel, click Keep Source Formatting. (Or, to link to the table so that it automatically updates with new data, click Keep Source Formatting and Link to Excel.)
To match the style of a table already in your Word document, click Match Destination Table Style. (Or, to link to the table instead of copying it, click Match Destination Table Style and Link to Excel.)
Easily add more information to your Excel 2002 chart data labels
|Prior to Excel 2002, the data label associated with a chart data
point could show only one data element at a time. For instance,
you could display the exact value that the data point
represented, or the percentage of the total that it represented,
but not both. If you wanted to show additional information about
a data point, such as the series name, you had to manually add a
label and enter the appropriate value -- which of course had to
manually be changed if the chart's underlying data changed.
Excel 2002 simplifies matters by allowing you to mix and match
label elements as needed. In addition, the number of options
available to you has increased (some options only apply to
certain chart types). Your available choices now are:
- Series Name
- Category Name
- Bubble Size
To configure data labels, select your chart and choose Chart |
Chart Options from the menu bar. Then, click on the Data Labels
tab. Select the appropriate check boxes in the Label Contains
panel and click OK.
Restoring Excel 2002's task pane at startup
|By default, Excel 2002 displays the New Workbook task pane when
you launch it. This task pane lets you quickly access recently
opened files or create a new workbook file. However, you may find
that the task pane mysteriously stops appearing when you launch
Excel--particularly if you followed our recent tip regarding
Excel's Personal Macro Workbook.
The first thing to check if Excel's startup task pane doesn't
appear is that it's actually configured to show when you launch
Excel. To do so, select Tools | Options from the menu bar and
click on the View tab. Ensure that the Startup Task Pane check
box is selected and click OK.
If the Startup Task Pane check box is already selected, it
probably means that the Personal Macro Workbook is preventing the
task pane from being displayed. By design, Excel hides the task
pane if a workbook is opened at the same time that Excel is
launched. For example, if you double-click on a workbook file
icon, Excel assumes you already have what you need to start
working and doesn't bother to display the New Workbook task pane.
When you set up a Personal Macro Workbook, Excel launches a file
named PERSONAL.XLS, even though the file is hidden from view.
Most likely, you don't want Excel to treat the workbook that
stores your macros the same as a typical workbook--and losing the
easy access to the New Workbook task pane effectively eliminates
the productivity benefit that this highly touted feature is meant
to supply. You can re-display the task pane by choosing View |
Task Pane from the menu bar, but a more efficient solution is to
add a simple VBA procedure to your PERSONAL.XLS file that ensures
the task pane is displayed.
To add the code, choose Tools | Macro | Visual Basic Editor from
the menu bar. In the Project Explorer, expand the PERSONAL.XLS
node and double-click on the ThisWorkbook icon. Then, select
Workbook from the Object dropdown list. By default, a skeleton
procedure for the workbook's Open event should be created. At the
insertion point, add the following statements:
On Error Resume Next
Application.CommandBars("Task Pane").Visible = True
Then, close the VBE. When you exit Excel, be sure to click Yes
when prompted to save changes to your Personal Macro Workbook
file. From now on, the task pane will be displayed when you
launch Excel (although it will still be hidden if you launch
Excel by double-clicking on an existing workbook icon).
|8. Make Excel 2002 formulas more readable with named constants||When you create a set of formulas that incorporate a value that
doesn't change, you can make your formulas more readable by
replacing the value with a named constant. Just as you can name
worksheet ranges, you can assign a name to a specific value. To
name a constant, display the Define Name dialog box by choosing
Insert | Name | Define from the menu bar. After typing a name for
the constant, replace the information in the Refers To text box
with the constant value and click OK. You can now substitute the
name you used in place of the value as you construct formulas.
Check for numbers stored in a text format
Excel can't perform calculations on cell values formatted as text, such as number values that you've imported as text from other databases into Excel. Excel 2002 includes a feature that alerts you to these cells and optionally converts them to numbers.
To activate this feature, follow these steps:
1. Go to Tools | Options.
2. On the Error Checking tab, select the Enable Background Error Checking and Number Stored As Text check boxes, and click OK.
Now, when Excel finds a number in a cell formatted as text, it will place a green error indicator in the upper left corner of the cell. To convert cells to a number format, select the cell, click the Error Checking Options button displayed to the left of it, and select Convert To Number.
Use Excel 2002 charts for what-if analysis
|Although you likely use charts most often to analyze existing
data, you can also use them to project hypothetical results. Just
as changing the source data in a worksheet cell adjusts the
graphical representation shown in the chart, manually moving
charted data points adjusts the underlying data. To see this
firsthand, enter the headings 2001 and 2002 in cells B1 and C1.
Then, in the range A2:A5, enter the row headings North, South,
East, and West. Finally, enter some random numbers in range B2:C5
to complete the sample data.
At this point, select the data range and click the Chart Wizard
button. When the Wizard opens, click the Finish button to
generate a standard column chart. Then, click on one of the
columns, which will select the entire data series. Wait a moment,
so as not to accidentally double-click, and then click on the
column again. Now only the column you clicked on is selected.
Click and drag the sizing handle found on the column's top edge
up or down. As you do so, a label appears to show the value that
the resized column represents. When you release the mouse button,
you'll see that the related value in the worksheet cell has
changed accordingly. Note that although we used a column chart in
this example, you can apply this technique to other chart types
Apply a smart tag action to several cells at once (Excel 2002)
|Excel 2002 uses smart tags to simplify fixing several types of
common errors. When you want to apply the same error correction
smart tag action to a number of cells, you don't have to work
with each cell individually.
For instance, say that you've imported data into Excel and it
contains numbers formatted as text. When you select an individual
cell, the smart tag button appears and you can use the associated
menu to convert the data to a number. While smart tags are
designed to be timesavers, applying such a change to each cell
individually would be a pain.
Fortunately, you can apply a smart tag action to a range. The
range must be contiguous and the cell that's active when you
finish the selection must contain the appropriate smart tag.
After you make the selection, the smart tag button appears next
to the highlighted range. Simply choose the appropriate smart tag
menu item to apply the action to all of the relevant cells in the selection.
Simplify summary formulas with 3-D range references in Excel 97-2000-2001-2002
|It's common to set up workbooks so that data is logically split
across several sheets. For example, you may dedicate worksheets
to individual months or quarters. When you use this organization
technique, you often also create a summary sheet that
consolidates the information. If so, you may wind up creating a
lot of long, cumbersome formulas, such as:
More flaws and bugs impact Excel users in 97-2000-2002-2003
|Microsoft has patched another security vulnerability that
affects several versions of Excel. In addition, they've released
a Critical Update to fix a bug that pertains to the recently
released Excel 2003.
The security problem involves issues with macros created using
the legacy Excel 4.0 Macro Language (XLM). The vulnerability
allows commands to run without first triggering the usual macro
warning. The problem affects Excel 97 through 2002 and Microsoft
rates the need to fix the flaw as "Important."
The Office 2003 bug is linked to OfficeArt shapes. If you open a
document that was saved in a previous version of Office, the
document may become corrupted or not open completely.
If you have Office 2000 or above, you can scan your system to
see which patches are needed using the service provided on the
Microsoft Office Product Updates site. However, Office 97 users
must manually download and apply the updates. For complete
details on the vulnerabilities, what software is affected, and
where to manually download the patches, see the links below:
Microsoft Office Product Updates site
Vulnerability in Microsoft Word and Microsoft Excel Could Allow
Arbitrary Code to Run (831527)
Overview of the Office 2003 Critical Update
Format your cell borders with new ease in Excel 2002
|Cell borders do a lot to improve a worksheet's readability, but
prior to Excel 2002 they were often a pain to set up and
maintain. Although the Borders palette supplies you with several
predefined border formats, customizing a border in Excel 2000 and
earlier often requires a trip to the cumbersome Borders sheet
found in the Format Cells dialog box. Because you frequently need
to perform minor fixes to your borders when you move or paste
cells, such trips to the Format Cells dialog box aren't uncommon.
Excel 2002 improves upon the process with the addition of the
Draw Borders feature. To use this feature, open the Borders
palette and click the Draw Borders button. Doing so displays the
Borders toolbar and automatically turns your mouse pointer into a
pencil icon. As the feature's name suggests, you can add borders
by simply clicking and drawing along the edges of cells you want
enclosed by a border. The Line Style and Line Color buttons on
the Borders toolbar let you quickly set the border's attributes.
If you want to create a grid instead of just a border that
surrounds the outside edges of a range, click on the dropdown
arrow next to the Draw Border button (which resembles a pencil)
and choose Draw Border Grid. To remove border lines, click the
Erase Border button and draw over the border edges you want
cleared. When you've finished, simply close the Borders toolbar
to restore Excel's usual cell selection mouse pointer.
|15. Reduce unnecessary stops while spell checking in Excel 97-2000-2001-2002||Spell checking worksheets helps you catch typos, but it can be
cumbersome. For instance, if a worksheet contains acronyms,
abbreviations, or field names, you may find yourself repeatedly
hitting the Ignore button. Fortunately, Excel can be configured
to skip some words that you may not want checked by ignoring all
uppercase words. To do so, run the spell checker on data that
causes the Spelling dialog box to be displayed. Then, select the
Ignore UPPERCASE check box and click OK. You should be aware that
this setting carries through to all subsequently spell-checked
workbooks until it's specifically disabled.