Office 97 - Excel Tips

Excel on the Net

Open
Web Pages You can open a Web page directly in Excel 97. Select File/Open from the Excel menu bar, and type or paste the URL of the Web page in the File Name box. Excel automatically converts the page to a worksheet.

Hyperlink
Your Cells As with Word and PowerPoint documents, you can add hyperlinks to your Excel spreadsheets, but there's a twist: Excel doesn't want to turn regular words into links, preferring to use the destination URL or filename as the link. To create a hyperlink with a name that means something, make the hyperlink a formula:

Click the Paste Function button on the toolbar to bring up the dialog box.

Under the Function category, select Lookup & Reference, and under Function Name, select Hyperlink. Click OK.

In the Hyperlink dialog box, enter the URL of the link and the name you want displayed.

Click OK, and the named link appears in blue underlined text in your spreadsheet.

Use
WorkSheets
as
Web Pages You can convert your Excel 97 workbooks to Web pages. In the File menu, choose Save As HTML, which runs the Internet Assistant Wizard. The Wizard helps you choose the ranges that you want to convert in your sheet. From there, you can change cells to HTML tables and charts to GIF files.

Convert
Preformatted
HTML Text
to a Table When you bring preformatted HTML text into Excel 97, the characters appears all in one cell, making it tough to work with. But you can convert preformatted HTML text to a table by selecting the column or range of text, then selecting Data/Text To Columns. A Wizard walks you through the steps of breaking the text into columns.

Charts and Text

Find
Chart
Tips Beneath any element of an Excel 97 chart, there's a wealth of information. To access it, simply move your mouse cursor over the data point you're interested in. Chart Tips pop up automatically to identify the different components of the chart.

Put Art
in your Charts Want to add an impressive background to an Excel bar or line chart? The option exists, and the effects can be stunning:

Right-click the plot area (the part behind the data points), and select Format Plot Area.

Under the Patterns tab, click the Fill Effects button, and experiment with the options under the Gradient, Texture, and Patterns tabs. You can even import a photograph or other graphic under the Picture tab.

Once you've gotten the desired effect, click OK twice.

Rotate
Column
Headers Column headers never seem to fit the way you want them to. But you can solve this problem using Excel 97's new Rotate Text tool:

Select the cells containing the column headers.

Right-click the selection, then pick Format Cells.

Click the Alignment tab, and set Orientation to 45 degrees.

Click OK, and you're set.

Lay Out
Text
Smoothly Use the Merge Cells tool in Excel 97 to smoothly display blocks of text in a spreadsheet:

Select the group of cells where you want the text to appear, and right-click to bring up the shortcut menu.

Choose Format Cells.

Select the Alignment tab, and click the Wrap Text and Merge Cells options. Now the text will smoothly fill the merged cells without spilling over into adjacent cells.

Make
Ideal
Indentations Excel 97's new indent feature opens up many formatting options, including outline-style multiple indentation. (You can indent as many as 16 levels.) To indent a range of cells, select the desired area, and click the Increase Indent button in the toolbar. To indent one cell in the range more than the others (in other words, to form a hanging indent), select that cell, and click the Increase Indent button again.

Using
Trendlines
to Forecase
for your Data Trendlines help you forecast a trend using the data you've already collected. You can create a trendline from the data you already have, and extend it forward or backward to forecast a range of numbers (hours, sales, or quantities) over a defined period of time. Here's how:

Select the range of cells in your table that you want included in the forecast.

Click the Chart Wizard icon.

Leave all the options as they are, and click Finish to create a default column chart.

Right-click any data column in the chart, and select Add Trendline.

In the Add Trendline dialog box, click Linear, then select the options Tab.

On the Options tab under Forecast, click the up arrow until it shows the range of periods you want included. (For example, if your chart is set up for the periods to equal quarters and you want to forecast for the next six quarters, you would click Forward 6 Periods.)

Click OK. The trendline will appear on the chart.

Using these basic instructions, you can experiment with other types of trendlines to determine which yields the most effective forecast.

Conditional
Formatting Color-coding your data lets you tell at a glance whether your numbers are up or down. Using Excel's conditional formatting features, you can set up your sheet so that cells change colors depending on what their values are--a great way to make your cells easy to read:

Select the cells that you want to color-code. To select noncontiguous cells, hold down Ctrl as you click.

Select Format/Conditional Formatting. In the Conditional Formatting dialog box, you'll set the conditions for the cells. In the first field on the left, decide whether you want the condition based on the value of a cell or formula. In the second field, select which conditional terms apply. In the third field, fill in the value for the cell, or use the third and fourth to fill in a range. For example, if you wanted to create a conditional format for a Total cell, you could select Cell Value Is for the first field, "Greater than or equal to" for the second, and enter 1,000 in the third field as the value.

In the same dialog box, click Format to set the cell's appearance when the condition is met. You can set options related to font, border, and pattern. If you just want to set the color, choose the Patterns tab, and select a color for the cell. For our example, you might select green, so that the Total cell will glow green when it hits the 1,000 mark.

Click OK to complete the first condition.

If you want to add another condition to the cell, click Add and repeat the steps to fill in Condition 2. Otherwise, click OK to put the conditional format in place.

Give
your Tables
Texture Spruce up your Excel charts by adding a customized background. Right-click the chart, and select Format Plot Area. Click the Fill Effects button under the color palette, select the Texture tab, and choose a texture from the ones provided, or import one of your own. Click OK twice, and your table will have the new background. Be sure to add textured backgrounds in Excel before you paste a chart into Word or PowerPoint--you can only add a texture from within Excel.

Editing

Undo
it All Don't be afraid to experiment with Excel formulas or chart formatting. Now you have a safety net: Excel 97 includes the capability to undo and redo multiple commands. Just click the Undo or Redo toolbar buttons as often as you need.

Speed Up
with
Expand
Collapse Most dialog boxes and Wizards in Microsoft Excel 97 now have Expand/Collapse dialog buttons, so it's easy to select cells from your spreadsheet while working in a dialog box. The button looks like a small grid with a red arrow in it. Click it, and the Wizard or dialog box shrinks to a single line, letting you select a cell or a range of cells. Click the button again, and the dialog box expands to its original size.

Share
WorkBooks It's easy to collaborate on group documents over a LAN with Excel 97. In your worksheet, select Tools/Share Workbook from the menu bar. In the Share Workbook dialog box, check the box marked "Allow changes by more than one user at the same time," and click OK. Once a workbook is shared, more than one person on the LAN can edit it simultaneously. When you're working on a workbook at the same time as other people, you won't see their changes until they save the file.

Track
Collaborative
Changes Once you've made a workbook a shared workbook, Excel 97 tracks all changes and makes notes on who made them. You can check changes based on when you last saved, who made the changes, or even whether a particular cell has been altered:

Select Tools/Track Changes/Highlight Changes from the menu bar.

In the Highlight Changes dialog box, check the "Track changes while editing" box, then click the When, Who, and Where drop-down boxes below to find what you're interested in tracking. (If you select the Where box, drag to select the range of cells you want to track.) If you want Excel 97 to highlight all the cells that have changed, set the When field to All and the Who field to Everyone, leave the Where field unchecked, and click OK.

To check the changes in the file, position the cursor over the color-coded cells.

Print
Disconttinuous
Portions
of a
WorkSheet Sometimes you don't want to print an entire worksheet, but the parts you do want to print aren't anywhere near each other. To print the important bits only, choose View/Page Break Preview from the main menu. Hold down the Ctrl key and select the parts of the sheet you want to print. Then choose File/Print Area/Set Print Area from the main menu, and click OK.

Break Up
Large
Amounts
of Text Reading large amounts of text within a table can be frustrating, especially if the information stretches across the screen or is cropped by the next cell. If readability is key for your table, you can manually break up large chunks of text by pressing Alt-Enter. The row expands, and the next piece of text you enter sits on a new line. Note that breaking text in this way will result in a longer table, both onscreen and in print.

Formulas

Correcting
Formulas
Automatically Do you frequently mess up formulas with little mistakes, such as entering a semicolon instead of a colon to specify a range? Excel 97 automatically detects and corrects the 15 most common mistakes people make when entering formulas, so you don't need to knock yourself out getting the formula exactly right. Save your brainpower for making the budget work.

Find
Reference
Cells
Easily Excel 97's Range Finder makes it a cinch to audit cell dependencies in complex formulas. Double-click in any cell that contains a formula, and the result you usually see is replaced by the formula that created it, with each element in a different color and each reference cell in the formula color-coded to match. Range Finder's color-coding makes it much easier to edit a formula or select a chart component.

Use
Plain
English
for
Formulas Want to create a formula, but avoid tinkering with cell references? Who doesn't? In Excel 97, you can use regular words and watch Excel figure out the formula:

Say you have two columns of data, titled Revenues and Cost. To figure out profit or loss, type the word-based formula cost-revenues in a new cell, and hit Enter.

Double-click the cell with the formula to bring up Excel's Range Finder, which makes it simple to see where your cell references are coming from. The natural-language formulas are "smart," so you can change column and row headings and still maintain the correct cell references.

To copy this formula down the entire column, use the mouse to grab the AutoFill handle in the formula cell, and drag it down to fill all the cells next to data columns.

Hosted by www.Geocities.ws

Excel on the Net
Open Web Pages	You can open a Web page directly in Excel 97. Select File/Open from the Excel menu bar, and type or paste the URL of the Web page in the File Name box. Excel automatically converts the page to a worksheet.
Hyperlink Your Cells	As with Word and PowerPoint documents, you can add hyperlinks to your Excel spreadsheets, but there's a twist: Excel doesn't want to turn regular words into links, preferring to use the destination URL or filename as the link. To create a hyperlink with a name that means something, make the hyperlink a formula: Click the Paste Function button on the toolbar to bring up the dialog box. Under the Function category, select Lookup & Reference, and under Function Name, select Hyperlink. Click OK. In the Hyperlink dialog box, enter the URL of the link and the name you want displayed. Click OK, and the named link appears in blue underlined text in your spreadsheet.
Use WorkSheets as Web Pages	You can convert your Excel 97 workbooks to Web pages. In the File menu, choose Save As HTML, which runs the Internet Assistant Wizard. The Wizard helps you choose the ranges that you want to convert in your sheet. From there, you can change cells to HTML tables and charts to GIF files.
Convert Preformatted HTML Text to a Table	When you bring preformatted HTML text into Excel 97, the characters appears all in one cell, making it tough to work with. But you can convert preformatted HTML text to a table by selecting the column or range of text, then selecting Data/Text To Columns. A Wizard walks you through the steps of breaking the text into columns.

Charts and Text
Find Chart Tips	Beneath any element of an Excel 97 chart, there's a wealth of information. To access it, simply move your mouse cursor over the data point you're interested in. Chart Tips pop up automatically to identify the different components of the chart.
Put Art in your Charts	Want to add an impressive background to an Excel bar or line chart? The option exists, and the effects can be stunning: Right-click the plot area (the part behind the data points), and select Format Plot Area. Under the Patterns tab, click the Fill Effects button, and experiment with the options under the Gradient, Texture, and Patterns tabs. You can even import a photograph or other graphic under the Picture tab. Once you've gotten the desired effect, click OK twice.
Rotate Column Headers	Column headers never seem to fit the way you want them to. But you can solve this problem using Excel 97's new Rotate Text tool: Select the cells containing the column headers. Right-click the selection, then pick Format Cells. Click the Alignment tab, and set Orientation to 45 degrees. Click OK, and you're set.
Lay Out Text Smoothly	Use the Merge Cells tool in Excel 97 to smoothly display blocks of text in a spreadsheet: Select the group of cells where you want the text to appear, and right-click to bring up the shortcut menu. Choose Format Cells. Select the Alignment tab, and click the Wrap Text and Merge Cells options. Now the text will smoothly fill the merged cells without spilling over into adjacent cells.
Make Ideal Indentations	Excel 97's new indent feature opens up many formatting options, including outline-style multiple indentation. (You can indent as many as 16 levels.) To indent a range of cells, select the desired area, and click the Increase Indent button in the toolbar. To indent one cell in the range more than the others (in other words, to form a hanging indent), select that cell, and click the Increase Indent button again.
Using Trendlines to Forecase for your Data	Trendlines help you forecast a trend using the data you've already collected. You can create a trendline from the data you already have, and extend it forward or backward to forecast a range of numbers (hours, sales, or quantities) over a defined period of time. Here's how: Select the range of cells in your table that you want included in the forecast. Click the Chart Wizard icon. Leave all the options as they are, and click Finish to create a default column chart. Right-click any data column in the chart, and select Add Trendline. In the Add Trendline dialog box, click Linear, then select the options Tab. On the Options tab under Forecast, click the up arrow until it shows the range of periods you want included. (For example, if your chart is set up for the periods to equal quarters and you want to forecast for the next six quarters, you would click Forward 6 Periods.) Click OK. The trendline will appear on the chart. Using these basic instructions, you can experiment with other types of trendlines to determine which yields the most effective forecast.
Conditional Formatting	Color-coding your data lets you tell at a glance whether your numbers are up or down. Using Excel's conditional formatting features, you can set up your sheet so that cells change colors depending on what their values are--a great way to make your cells easy to read: Select the cells that you want to color-code. To select noncontiguous cells, hold down Ctrl as you click. Select Format/Conditional Formatting. In the Conditional Formatting dialog box, you'll set the conditions for the cells. In the first field on the left, decide whether you want the condition based on the value of a cell or formula. In the second field, select which conditional terms apply. In the third field, fill in the value for the cell, or use the third and fourth to fill in a range. For example, if you wanted to create a conditional format for a Total cell, you could select Cell Value Is for the first field, "Greater than or equal to" for the second, and enter 1,000 in the third field as the value. In the same dialog box, click Format to set the cell's appearance when the condition is met. You can set options related to font, border, and pattern. If you just want to set the color, choose the Patterns tab, and select a color for the cell. For our example, you might select green, so that the Total cell will glow green when it hits the 1,000 mark. Click OK to complete the first condition. If you want to add another condition to the cell, click Add and repeat the steps to fill in Condition 2. Otherwise, click OK to put the conditional format in place.
Give your Tables Texture	Spruce up your Excel charts by adding a customized background. Right-click the chart, and select Format Plot Area. Click the Fill Effects button under the color palette, select the Texture tab, and choose a texture from the ones provided, or import one of your own. Click OK twice, and your table will have the new background. Be sure to add textured backgrounds in Excel before you paste a chart into Word or PowerPoint--you can only add a texture from within Excel.

Editing
Undo it All	Don't be afraid to experiment with Excel formulas or chart formatting. Now you have a safety net: Excel 97 includes the capability to undo and redo multiple commands. Just click the Undo or Redo toolbar buttons as often as you need.
Speed Up with Expand Collapse	Most dialog boxes and Wizards in Microsoft Excel 97 now have Expand/Collapse dialog buttons, so it's easy to select cells from your spreadsheet while working in a dialog box. The button looks like a small grid with a red arrow in it. Click it, and the Wizard or dialog box shrinks to a single line, letting you select a cell or a range of cells. Click the button again, and the dialog box expands to its original size.
Share WorkBooks	It's easy to collaborate on group documents over a LAN with Excel 97. In your worksheet, select Tools/Share Workbook from the menu bar. In the Share Workbook dialog box, check the box marked "Allow changes by more than one user at the same time," and click OK. Once a workbook is shared, more than one person on the LAN can edit it simultaneously. When you're working on a workbook at the same time as other people, you won't see their changes until they save the file.
Track Collaborative Changes	Once you've made a workbook a shared workbook, Excel 97 tracks all changes and makes notes on who made them. You can check changes based on when you last saved, who made the changes, or even whether a particular cell has been altered: Select Tools/Track Changes/Highlight Changes from the menu bar. In the Highlight Changes dialog box, check the "Track changes while editing" box, then click the When, Who, and Where drop-down boxes below to find what you're interested in tracking. (If you select the Where box, drag to select the range of cells you want to track.) If you want Excel 97 to highlight all the cells that have changed, set the When field to All and the Who field to Everyone, leave the Where field unchecked, and click OK. To check the changes in the file, position the cursor over the color-coded cells.
Print Disconttinuous Portions of a WorkSheet	Sometimes you don't want to print an entire worksheet, but the parts you do want to print aren't anywhere near each other. To print the important bits only, choose View/Page Break Preview from the main menu. Hold down the Ctrl key and select the parts of the sheet you want to print. Then choose File/Print Area/Set Print Area from the main menu, and click OK.
Break Up Large Amounts of Text	Reading large amounts of text within a table can be frustrating, especially if the information stretches across the screen or is cropped by the next cell. If readability is key for your table, you can manually break up large chunks of text by pressing Alt-Enter. The row expands, and the next piece of text you enter sits on a new line. Note that breaking text in this way will result in a longer table, both onscreen and in print.

Formulas
Correcting Formulas Automatically	Do you frequently mess up formulas with little mistakes, such as entering a semicolon instead of a colon to specify a range? Excel 97 automatically detects and corrects the 15 most common mistakes people make when entering formulas, so you don't need to knock yourself out getting the formula exactly right. Save your brainpower for making the budget work.
Find Reference Cells Easily	Excel 97's Range Finder makes it a cinch to audit cell dependencies in complex formulas. Double-click in any cell that contains a formula, and the result you usually see is replaced by the formula that created it, with each element in a different color and each reference cell in the formula color-coded to match. Range Finder's color-coding makes it much easier to edit a formula or select a chart component.
Use Plain English for Formulas	Want to create a formula, but avoid tinkering with cell references? Who doesn't? In Excel 97, you can use regular words and watch Excel figure out the formula: Say you have two columns of data, titled Revenues and Cost. To figure out profit or loss, type the word-based formula cost-revenues in a new cell, and hit Enter. Double-click the cell with the formula to bring up Excel's Range Finder, which makes it simple to see where your cell references are coming from. The natural-language formulas are "smart," so you can change column and row headings and still maintain the correct cell references. To copy this formula down the entire column, use the mouse to grab the AutoFill handle in the formula cell, and drag it down to fill all the cells next to data columns.