MicroSoft Excel Tips

To size and move legends to use chart space efficiently (Excel 97/2000/2001/2002):
Get to step 3 of the chart wizard. Click on the Legend tab. Select a location for the legend from the Placement panel and observe the impact that the change has on the chart preview. Click Finish or apply other property changes you want to make. If you want to change the legend placement after the initial chart setup, simply select the chart and choose Chart | Chart Options from the menu bar. You can then make the same option changes as when the wizard was active. You can also manually move the legend and change its dimensions. First, click on the legend to select it. Then, simply drag it to the desired location with your mouse. To change the legend box's dimensions, just click and drag the legend's sizing handles.

 To display text vertically in a cell (Excel 97/2000/2001/2002):
Select the appropriate cell and choose Format | Cells from the menu bar. Then, switch to the Alignment tab. Finally, click on the preview text box on the left side of the Orientation panel and click OK.

 To access Excel 2002's special paste options:
Copy some data. Then, examine the standard Paste toolbar button. You'll see that in Excel 2002 it now has a dropdown arrow attached to it. When you click the arrow, Excel displays a menu of often-used Paste Special settings that you can apply with a single click.

To apply previously created validation rules to new cells (Excel 2002):
First, select a range containing the cells that already have the validation rules you want applied. Press  [Ctrl]C to copy the range. Then, right-click on the first cell in the range where you want to apply the rules. From the shortcut menu, select Paste Special. When the Paste Special dialog box appears, select the Validation option and then click OK.

To print an entire workbook at once (Excel 97/200/2001/2002):
Choose File | Print from the menu bar and select the Entire Workbook option in the Print What panel. If the file only contains a few sheets, click on the first sheet tab in the workbook. Then, while holding down the [Shift] key, click on the last tab. Now, simply click the Print button on the Standard toolbar. Since the default Print What setting in Excel is Active Sheet(s), you'll produce the same result as if you had used the Print dialog box to set the Entire Workbook option. Note that regardless of which technique you use, printing all of the sheets does maintain any print area settings that may be in place, so some sheets may not be printed in their entirety.

 To counting text entries in Excel (97/2000/2001/2002):
Select  the COUNTA function. For example, the formula:
=COUNTA(B2:B15) returns the number of name entries. Note that this counts any non-blank cells in the specified range, so a cell will be counted even if just displays an error message.

To remove unnecessary smart tag indicators (Excel 2002):                                                            Hover your mouse pointer over the cell containing the indicator. Then, click on the smart tag options button that appears and select Remove This Smart Tag from the resulting menu.

To assign a password to access a workbook (Excel 97/2000/2001/2002):

Open the file and choose File | Save As from the menu bar. Then, click the Options button on the 

Save As dialog box (if your dialog box doesn't have that button, choose General Options

from the dialog box's Tools menu). Type the case-sensitive password you want to use in the 

Password To Open text box. If you also want to control whether edits can be made, enter a 

different password in the Password To Modify text box. Click OK. Confirm any password 

selections you've made. If you assigned two passwords, the first one you confirm is the one you 

entered in the Password To Open text box. When you return to the Save As dialog box, 

click Save and click Yes to save over the original workbook with the new password-protected 

workbook. When you reopen the file, you'll be prompted to enter its password. Enter it in the 

Password text box and click OK. If you supply an incorrect password, Excel displays a 

warning and the open process is cancelled. If you also assigned a password to control 

modifications, you'll be prompted to enter it. 

To apply commonly used number formats with shortcut keys (Excel 97/2000/2001/2002):

[Ctrl][Shift][~] applies the General number format.

[Ctrl][Shift][$] applies the Currency number format with two decimal places/negative values in parentheses.

[Ctrl][Shift][%] applies the Percentage number format with no decimal places.

[Ctrl][Shift][^] applies the Scientific number format with two decimal places.

[Ctrl][Shift][#] applies the Date format in the form 15-Mar-04.

[Ctrl][Shift][@] applies the Time format in the form 12:00 AM.

[Ctrl][Shift][!] applies the Number format with two decimal places, thousands separator, and minus

 sign (-) for negative values.

To enter the same data into multiple cells simultaneously (Excel 2002):

Select the cells in which you'd like the data to appear. To select adjacent cells, just click and drag 

until you've selected the cells you want. To select nonadjacent cells, press and hold the [Ctrl] key

and then click on each cell you'd like to select. After you've selected the appropriate cells, enter the 

data that you want them to hold. When you've finished, press [Ctrl][Enter]--the data appears in all 

selected cells.

To create hyperlinks to data in Office applications (Excel 97/2000/2001/2002):

Select the cell containing the data to which you want the hyperlink to point. Then, choose Edit | Copy 

3from the menu bar. Next, select the cell that will contain the hyperlink. Finally, choose Edit | Paste 

As Hyperlink from the menu bar. 

To force a carriage return in Excel 2000 chart legends:

When you enter the text, simply press [Alt][Enter] wherever you want to force a carriage return.

To open a specific workbook whenever you launch Excel (97/2000/2001/2002):

Choose Tools | Options from Excel's menu bar. When the Options dialog box appears, click on the 

General tab. Then, enter the folder path in the Alternate Startup File Location text box. 

(In Excel 2002, the text box is labeled with a more descriptive name: At Startup, Open All Files In.) 

Once you've entered the appropriate folder path, click OK.

To insert symbols and special characters in Excel 2002:

Choose Insert | Symbol from the menu bar when you need to insert a symbol or special character 

into your cell text. Excel displays the Symbol dialog box, which contains two tabs, Symbols and

Special Characters. The Symbols tab is similar to Character Map. You can select one of the fonts 

installed on your system and see its characters displayed in a grid. The Special Characters tab 

provides a static list of commonly used characters. Regardless of which tab you're using, simply 

select the appropriate character and then click the Insert button to add the character at your 

insertion point. When you've finished, click the Close button to dismiss the dialog box and 

return to regular keyboard entry.

To indent text in a cell without resorting to the [spacebar](Excel 2002):

First, select the cells that contain the data you want to indent. Then, press [Ctrl]1 to 

launch the Format Cells dialog box, click on the Alignment tab, and select Left (Indent) 

from the Horizontal dropdown list. Then, use the Indent spinner box to set the number 

of spaces you want to appear between the left edge of the selected cells and the start 

of your text data. You can set an indent of up to 15 spaces. When you've finished, click 

the OK button. To create (or remove) an indent even faster, select the cell containing your 

data and use the Decrease Indent and Increase Indent buttons on the Formatting toolbar 

to decrease/increase the size of the indent incrementally.

To reposition data point labels to make charts easier to read(Excel 97/2000/2001/2002):

Select one of the data labels. When you do, all of the related labels are automatically selected. 

Next, choose Format | Selected Data Labels from the menu bar and then click on the Alignment

tab. Select the desired location from the Label Position dropdown list and click OK. 

You can also selectively change the Label Position setting for individual labels. To do so, 

select the labels as previously described. Then, wait a moment and click on the single 

data label you want to change. Doing so selects the individual label. You can then 

change Label Position setting as you did before. If the results are still undesirable, 

you can manually drag the data labels to another location place. To do so, follow 

the previous steps to select an individual label. Then, click and drag the label's 

border to move it to the desired spot.

To sum filtered results (Excel 97/2000/2001/2002):

To create an subtotal formula, select any cell in the list you want to analyze and 

choose Data | Filter | AutoFilter from the menu bar to display dropdown arrows 

in the lists top row. Then, use any one of the dropdown arrows to filter the list for a 

particular criterion value. Then, select the cell immediately beneath the column of 

numbers you want to sum. Click the AutoSum button and you'll see that 

Excel inserts a SUBTOTAL formula into the cell. Click the AutoSum button 

again to complete the formula. You can now change the list's filter criteria and the 

SUBTOTAL formula will return the sum of whatever data is visible in the list.

To convert numbers entered as text (Excel 97/2000/2001/2002):

Select the cell or range of cells that you want to convert. When the smart 

tag button appears, click on it and choose Convert To Number from the 

action menu. On older versions of Excel, first enter the number 1 in any 

blank cell. Next, select the cell and choose Edit | Copy from the menu bar. 

Then, select all the cells containing values you want converted. Choose

 Edit | Paste Special from the menu bar. Finally, select the Multiply option 

button and click OK.

To apply pictures to Excel chart data points:

Select the data series you want to format on the chart by clicking on it. 

Then, choose Insert | Picture | From File from the menu bar.

 Locate and select the picture you want to use--Excel can work with 

most graphic file formats. Finally, click Insert and Excel applies the 

picture to each data point, resizing the image as needed. The method 

we used is easy, but other chart types require different approaches.

To hide items in a PivotTable's page field's selection list (Excel 97/2000/2001/2002):
Right-click on the page field's label ([control]-click on the Mac). Then, select Field Settings from the menu bar. Select the items you want to hide in the Hide Items list box and then click OK. By default, data associated with the hidden page field items is ignored from the PivotTable's totals.

To print a selected range without setting a print area (Excel 97/2000/2002):

Select the range that you want to print. Then, choose File | Print from the 

menu bar. When the Print dialog box appears, choose the Selection 

option in the Print What panel. Finally, click Preview to view the output 

onscreen or OK to print it.

To paste column widths along with data (Excel 2003):

Copy and paste the data as you normally would. Then, immediately click on the 

Paste Options smart tag that appears at the bottom right corner of the pasted 

selection. From the shortcut menu, select the Keep Source Column Widths 

option. The selection and the column(s) you pasted it into will then take on the 

same column width(s) as the original data.

To access financial data with the Research task pane (Excel 2003):

Select Research from the Tools menu to launch the Research task pane. In the 

Search For text box, enter the company name. Click the arrow in the dropdown

 list, and scroll through the list of research sources. Near the bottom, click Gale 

Company Profiles under the All Business And Financial Sites category. In a flash, 

you'll see the vital stats for the company. If you need more info, click on View 

Complete Profile at the bottom of the company information. If you use Internet 

Explorer 5.01 or later, your browser will launch and the Research pane will 

stay parked next to it. From the Thomson Gale Web site, you can choose

 to pay for the complete profile. To get back to your spreadsheet, just close the 

browser window. If you're on the hunt for hard performance numbers or the 

current stock price, MSN money is your up-to-the-minute resource. Keep the 

Research task pane open, type in the company's stock ticker abbreviation in the

 Search For text box and select MSN Money Stock Quotes from the dropdown 

list. To insert the current stock price into your spreadsheet's active cell, click0

Insert Price. Look for Charts under More On MSN Money and select 1 Year, 3 

Year, or 5 Year to create dynamic chart comparisons.

To change an existing Excel chart to a different type using Chart Wizard:

Select the chart object and then click the Chart Wizard button on the Standard toolbar. 

Doing so displays the first screen of the Chart Wizard, allowing you to pick a different

chart type, as well as make any other changes you normally can with the wizard. 

Select the chart type you want to use and click OK.

To change an existing Excel chart to a different type using the Menu Bar

Select the chart object and then choose Chart | Chart Type from the menu bar. 

This displays the Chart Type dialog box, which is essentially the same as the 

Chart Wizard's first screen. Selectthe chart type you want to use and click OK.

To change an existing Excel chart to a different type using the Chart Toolbar):

Select the chart object. If the Chart toolbar doesn't automatically appear, choose 

View | Toolbars | Chart from the menu bar. This toolbar contains a Chart Type toolbar 

button, which has a small dropdown arrow associated with it. Click on the arrow to 

reveal a palette of 18 commonly used chart types. Simply select the chart type you 

want to use and the current chart is automatically reconfigured.

To check a range selection's dimensions (Excel 97/2000/2001/2002):

When you select a range, the Name box next to the Formula bar displays its 

dimensions as long as you have the mouse button pressed.

To give column labels vertical orientation to fit them all on one page (Excel 2003):

First, click the column label cell. Then, select Format | Cells from the menu bar.

(Alternatively, right-click the cell you want to change and select Format Cells from the

shortcut menu.) Select the Alignment tab in the Format Cells dialog box and take a 

look at the Orientation section. To change your label so it reads from the top of the 

cell down, click on the bottom diamond in the Orientation semi-circle. If you want 

the text to read from the bottom up, click the top diamond. If you want your text to 

read with the letters stacked on top of each other so the reader won't have to turn 

the paper or his head to read the labels, click the Text bar on the left side of the 

Orientation section. You can choose to display your column labels at any angle 

by selecting a point on the semi-circle or choosing a negative or positive value 

in the Degrees spin box. When you're satisfied with the orientation, click OK to 

take a look. Note that you may need to change your row height to incorporate 

the new vertical label.

To create diagrams quickly in Word, Excel, and PowerPoint:

To browse diagram types, first display the Drawing toolbar. Do this by choosing 

View | Toolbars | Drawing from the menu bar or right-clicking on any toolbar and 

choosing Drawing from the dropdown list. Click the Insert Diagram Or Organization 

Chart button. In the Diagram Gallery dialog box, click each type of chart and read its 

description. Once you've decided on a diagram, double click on it to insert the 

empty diagram into your file. You can add labels to the diagram by clicking on the 

text placeholders and entering your text. If you want to change the overall 

appearance of the diagram, click the AutoFormat button on the Diagram 

toolbar, choose a style, and click OK. Note that once you've applied a style, 

you can't change the shape or color of the individual objects in your diagram. 

Explore the Diagram toolbar to control the size, position, and shape of your diagram.

If you're not satisfied with the diagram you've chosen, just click the Change To 

button to see how your data looks in another diagram type.

To access Excel 2002's special paste options:
Copy some data. Then, examine the standard Paste toolbar button. You'll see that in Excel 2002 it             now has a dropdown arrow attached to it. When you click the arrow, Excel displays a menu of             often-used Paste Special settings that you can apply with a single click.

To change the default colors Excel assigns to chart points):

Choose Tools | Options from the menu bar while the workbook that will 

contain your charts is open. Then, click on the Colors tab. The sample 

squares next to the Chart Fills and Chart Lines labels indicate the colors 

that Excel sequentially assigns to chart items. To change a particular

color, select the appropriate square in the Chart Fills or Chart Lines sequence. 

Then, click the Modify button. Pick one of the standard colors from the 

color wheel or click on the Custom tabto create a new color. Finally, 

click OK. If you ever want to restore Excel's defaults, click the Reset 

button to restore all of the color items to their original settings. Finally, 

click OK to save the color modifications.

To clarify data on line and area chart with drop lines:

To display drop lines, select a data series on the chart and then 

choose Format | Selected Data Series from the menu bar. Then, 

click on the Options tab. Select the Drop Lines check box and then 

click OK. Vertical lines now extend from each data point to the 

category axis, eliminating confusion and guesswork. Note that 

this formatting option can also be applied to 2-D area andline charts.

To size columns and rows to fit your data (Excel 97/2000/2001/2002):

Double-click on the heading separator line. When you do so on a 

column separator, Excel automatically resizes the column so it's 

wide enough to display the widest item in the column. Likewise, 

double-clicking on a row heading separator changes the row height 

to acccomodate the tallest row entry. You can apply this trick to 

multiple columns and rows at once. To do so, select the multiple 

row or column headings that you want to resize. Then, double-click 

on a separator line associated with any one of the selected headings.

To accelerate data entry with AutoComplete (Excel 97/2000/2001/2002):

Press [Alt] and the [Down Arrow] key when you begin your new entry. 

For example, select cell A5and press [Alt][Down Arrow].  Excel displays a 

dropdown list of the column's previous entries. Use your mouse or 

keyboard's directional arrows to select an item and press [Enter] to insert

it into the cell. You can also display the item list by right-clicking on a 

cell ([control]-clicking on the Mac) and choosing Pick From List. When 

working with a long list of column entries, enter the first

few letters of the word you're looking for prior to displaying the dropdown 

list so you don't have to scroll through a lot of entries. 

To link data to your document using Paste Link(Word/Excel 2003):
To use the Paste Link feature, open your Excel workbook and select the cell(s) you'd like to copy. Choose Edit | Copy from the menu bar, and then switch to your Word document. Place the insertion point where  you'd like the Excel data to appear, and then choose Edit | Paste. After you paste the data in Word, click the Paste Options button and choose Match Destination Table Style And Link To Excel from the resulting dropdown menu. Word changes the pasted data to an embedded, dynamically linked replica of the Excel  data you copied. When you make changes to the data in the linked worksheet, the linked replica in the Word document updates automatically.

To prevent error values from printing (Excel 2003):
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
Choosing Displayed prints the original error value, while the other choices replace the errors with blanks, dashes, or #N/A placeholders. Click OK when you've finished. Although this simple action won't cure your formula errors or affect your data, it gives you more control of how your spreadsheets appear in print.

To get help entering functions with Excel 2000's Formula Palette:

Click the equal sign (=) in the Formula Bar and select the function you need to work with from the dropdown list to the left of it. You can then use it to define the arguments for the function. If the Formula Palette obscures the range of cells you're working with, drag it with your mouse to a section of your worksheet that's out of the way of your data.

To prevent startup Excel macros from running (97/2000/2001/2002):

Hold down the [Shift] key when you open the file. Note that you'll need to 

keep the [Shift] key pressed throughout any warning dialog boxes that may 

appear during the process.

To adjust margins within Print Preview in Excel 2000:

Click the Margins button. This reveals the header, footer, and page 

margins, which appear as dotted lines that can be moved with your mouse. 

In addition, you'll notice several small black handles at the top of the page. 

These correspond to your worksheet's column borders, and you can drag 

the handles to resize your columns as needed.

To use an Outlook task to keep track of work in Excel:

Save your workbook and then display the Reviewing toolbar by right-clicking 

on any toolbar and selecting Reviewing from the shortcut menu. Make sure 

Outlook is open, and then click the Create Microsoft Outlook Task button on the 

Reviewing toolbar. A new task form is displayed with a shortcut to the open workbook 

inserted into the form. In the Subject text box, type the name of

the task. You can add more descriptive text beside the workbook shortcut if desired. 

Next, select the Reminder check box and set the appropriate time and date. 

Finally, click Save And Close.  When the reminder time and date arrive, you'll see a 

Reminder box appear (as long as Outlook is running at the time). In the Reminder 

box, click the Open Item button to open the task item. Then, double-click on the 

workbook shortcut to open the workbook. You can also open the Outlook task 

item from Outlook to access the workbook shortcut.

To freeze Excel columns and rows for precise scrolling control:

To freeze a row, your header row for example, select the row that's 

immediately beneath it. Then, select Window | Freeze Panes from the 

menu bar. Excel inserts a thin line on the bottom border

of the frozen pane. To freeze a column, select the column that is 

immediately to the right of it and select Window | Freeze Panes. You 

can also freeze particular rows and columns simultaneously.

Just click on the cell that's in the upper-left corner of the spreadsheet 

area you want scrollable and turn on Freeze Panes. When you save 

your worksheet, you'll also save your Freeze Panes

settings. To remove the panes, select Window | Unfreeze Panes. 

To preview how an Excel 2000 spreadsheet will look as a Web page:

 Choose File | Web Page Preview from the menu bar, and an HTML 

version of your worksheet is opened in your browser.