| Excel XP Tips | |||
|---|---|---|---|
| 1.
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: Displayed <blank> -- #N/A 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 placeholders. |
||
| 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.
|
||
| 3.
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. |
||
|
5.
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.) |
||
| 6.
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 - Value - Percentage - 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.
|
||
| 7.
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. |
||
|
9.
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. |
||
| 10.
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 as well. |
||
| 11.
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. |
||
| 12.
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: =SUM(January!D38+February!D38+March!D38+April!D38+May!D38+ June!D38+July!D38+August!D38+September!D38+October!D38+ November!D38+December!D38) |
||
| 13.
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 http://office.microsoft.com/productupdates/default.aspx Vulnerability in Microsoft Word and Microsoft Excel Could Allow Arbitrary Code to Run (831527) http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/MS03-050.asp Overview of the Office 2003 Critical Update http://support.microsoft.com/?kbid=828041 |
||
| 14.
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. |
||
|
|||
 PC Problems? Use My Email Support!!
|
|||
|
|
|||
