Tips and Tricks : Excel 2000

Use AutoFill to Quickly Copy Formulas and Formatting in Excel

Would you like an easy way to extend a series of numbers in Microsoft Excel without typing each one individually? With AutoFill , you can quickly copy data, formulas, or formatting to adjacent cells. This brief tutorial will show you how:

1. Select the cells that you would like to copy.
2. Move the cursor to the bottom right corner of the highlighted cells. Your cursor will then turn into a black plus sign.
3. Click and hold down the right mouse button and drag across the cells you want to fill.
4. Release the mouse button, and when the shortcut menu appears, click Fill Series .

By following these steps, you will be able to save a lot of time creating your spreadsheets.

Show or Hide the Formulas in an Excel Spreadsheet

When you're working in an Excel worksheet, you can alternate between viewing the values in the cells and displaying the formulas. To toggle between the different views, press CTRL+` (single left quotation mark).

Editor's Note: If you're having trouble finding the single left quotation mark, it's on the same key as the "~" symbol. On most keyboards, it's the key directly to the left of the "1" key.

Don't Lose Sight of Your Column Headings in Excel

Would you like to see the column headings on your Microsoft Excel spreadsheets no matter how far down you scroll? Here is one way to keep the column headings constantly visible:

1. Select the row just below your column headings.
2. On the Window menu, click Freeze Panes .

The "frozen" column headings don't scroll, but remain visible as you move through the rest of the worksheet.

Edit Cells Quickly in Excel-Without Using Your Mouse

If you like to use your keyboard for everything, editing a lot of data quickly in an Excel spreadsheet can be difficult because you find yourself constantly reaching for the mouse when you want to make changes to a cell. But there's a shortcut you can use so that your hands never have to leave the keyboard-press F2. Here's how:

1. Use the arrow keys to select the cell you want to edit.
2. Then press F2 (or COMMAND-U, if you use a Macintosh computer) to edit the cell contents.
3. When you're finished, just press ENTER (or RETURN on a Macintosh keyboard) to enter your changes. Or press ESC to cancel the changes.

Editor's Note: This tip is especially handy for editing hyperlinks in Excel because, if you use your mouse to click on a cell with a hyperlink, it automatically opens an Internet browser window. Using the keyboard lets you edit hyperlinks with ease.

Create an Excel Chart with the Push of a Button

This is a very old Microsoft Excel trick. To quickly create a chart, using only your keyboard, select the data you want to plot and then press F11. Excel automatically creates the chart for you.

Editor's Note: G. Raghavan of Tamilnadu, India, wrote in with another way to do this trick: After you select your cells, press ALT+F1 and you'll get the same result.

Build Your Own Hover Buttons in FrontPage 2000

Have you ever wondered how to make the buttons on your Web pages change color, move, or light up when users point to them with a mouse? It's easy to do with Microsoft FrontPage® 2000.

First, you'll need to create two versions of the button you want to put on your page. One version that appears when the page first loads and another slightly different version that users see when they point to the button with a mouse. You can create your graphics using Microsoft PhotoDraw® 2000 or any drawing program you use to create graphics for the Web.

Once you've created your graphics, here's how you put them on your page:

1. On the Insert menu, click Component , and then click Hover Button .
2. In the Link to box, enter the URL of the page or file you want to display when the button is clicked.
3. In the Button text box, type the text label for the hover button. Leave this box blank if the graphic you created already contains a text label.
4. Click Custom .
5. In the Button box, enter the URL to the picture you want users to see when they first open the page.
6. In the On hover box, enter the URL to the picture you want users to see when they put a mouse over the button. Then click OK .
7. In the Width and Height boxes, type the width and height of the picture in pixels, and then click OK . The size of the hover button must match the size of the picture you use for the hover effect.

And if you don't have graphics already created, you can select one of the built-in hover effects in FrontPage 2000, such as Glow or Bevel , to quickly add interactivity to any page on your site.

Select an Entire Range of Cells in Excel

In Excel, if you want to quickly select the entire range of cells you're working on, press CTRL+SHIFT+ ASTERISK (*).

For example, if you have a list of customers in Excel, this command will select the entire list and the column headings, but not the empty cells around the list-so you get only the cells you need.

This tip is different from the Select All command, which selects every cell in the worksheet-even the ones that you are not using.

Insert Copied Cells Between Existing Cells Safely

If you want to insert a range of copied cells between other rows or columns-instead of pasting over them-there's an easy way to do it:

1. Select the cells you want to copy.
2. On the Edit menu, click Copy .
3. Select the area on the worksheet where you want to place the copied cells.
4. Press Ctrl + SHIFT + Plus Sign (+).
5. In the Insert dialog box, click the direction you want to shift the surrounding cells, and press OK .

Now, the copied cells are inserted right where you want them, and none of your existing information is lost.

Build Vertical Titles in Excel

Have you ever wondered how to create a heading for a table that runs vertically along the side of a table instead of above it?

Here's how I do it:

1. Select the cell that contains your text as well as the surrounding cells that you want your title to span.
2. On the Format menu, click Cells , and then click the Alignment tab.
3. In the degrees text box, enter 90 .
4. Select the Merge cells text box and click OK .
   

Format Excel Cells Fast

If you want quick access to the Format Cells dialog box in Microsoft Excel to change things like type style, alignment, or borders, select the cell you want to format and press CTRL+1.

Have Excel Save Your Files Automatically

Have you ever wanted Excel to automatically save your spreadsheets for you so you don't lose your work? Excel 2000 includes a feature that saves workbooks automatically at specified intervals, but it's not installed by default. Here's how you can install and use the Autosave Add-in:

First you need to load the add-in, which will add it to your Tools menu:

1. On the Tools menu, click Add-Ins .
2. In the Add-Ins available list, select the Autosave Add-in check box and then click OK .

( Editor's Note: If the Autosave Add-in is not available, you may need to install it. For more instructions, search for the phrase " Install or remove individual features of Microsoft Office or Excel " in Excel 2000 Help.)

Then, to configure and use the Autosave feature:

1. On the Tools menu, click AutoSave .
2. Select the Automatic save every check box.
3. In the Minutes box, enter how often you want Excel to save your workbooks.
4. Select any other options you want, and press OK .
   

Quickly Move Between Multiple Excel Workbooks or Worksheets

When working with several Excel workbooks or worksheets (the individual pages in workbooks) at once, you can quickly move between them using shortcut keys.

• To move between open workbooks, press CTRL+TAB.
• To move to the next sheet in a workbook, press CTRL+PAGE DOWN.
• To move to the previous sheet in a workbook, press CTRL+PAGE UP.
  

Do Fast Calculations in Excel

Have you ever needed to know the largest value in a series of cells? You can create a formula to do that, but there is a faster way.

To view the largest value in a series of cells:

1. Select the cells in which you are interested, and you will see the sum of the range displayed on the status bar, which is the horizontal area below the worksheet window.
2. If the status bar is not displayed, click Status Bar on the View menu.
3. Right-click the status bar, and then click Max . Now you can see the maximum value displayed on the status bar.

You can use the same technique to find the average of, the sum of, or the minimum value in the selected range. You can also count the cells that contain numbers (by selecting Count Nums ) or count the number of filled cells (by selecting Count ).

Completely Delete Cells in Excel Using This Shortcut

Have you ever wanted a keyboard shortcut that completely deletes a cell from your worksheet, including the formatting and comments? Just select the cells you want to delete, and then press CTRL+MINUS SIGN (-). The surrounding cells will shift to fill the space.

This is different from using the DELETE or BACKSPACE keys, which simply clears the contents of a cell without actually deleting it.

Copy the Contents of an Entire Worksheet in Excel

Here's a timesaving tip for Excel users who frequently need to copy an entire worksheet (a page within a workbook) of information-such as a list of items for a monthly inventory-from one workbook (Excel file) to another.

To copy an entire sheet to another workbook:

1. Open the workbook into which you want to paste the copied sheet.
2. Switch to the workbook that contains the sheet you want to copy.
3. Right-click the Sheet tab of the sheet you want to copy, and then click Move or Copy on the shortcut menu.
4. From the To book drop-down menu, select the workbook that will receive the sheet. (To copy the selected sheet to a new workbook, click New book on the drop-down menu.)
5. Select the Create a copy check box. (If you don't select this check box, the sheet will be moved instead of copied.)
6. Click OK .
   

Do Creative Calculations with Paste Special

I find this trick fantastically useful. If you have a block of numeric data in an Excel worksheet and you wish to change each entry to a negative value, use Paste Special with a twist.

Here's how:

1. In an empty cell, type -1.
2. Select the cell, and click Copy on the Edit menu.
3. Now select the cells containing the values you want to change.
4. On the Edit menu, click Paste Special .
5. Under Paste , click Values and under Operation , click Multiply .
6. Click OK .

All the numbers change from positive to negative, or vice versa. This method is also useful for reducing numbers by a factor of 1,000, 1,000,000, and so on.

Quickly View All Worksheet Formulas in Excel

With a quick keystroke, you can display all the formulas in your worksheet, including the serial values Excel uses to store dates.

To alternate between displaying cell values and displaying cell formulas, press CTRL+` (single left quotation mark, which usually can be found above the TAB key).

Add a Calculator to the Excel Toolbar

Did you know that you could add a calculator to your Microsoft Excel toolbar? Here's how:

1. On the View menu, click Toolbars , and then click Customize .
2. Click the Commands tab.
3. In the Categories list, click Tools , and in the Commands list, click Custom (the one with the gray calculator graphic).
4. Drag the selected command from the Commands list to a toolbar. (Lift your finger from the mouse when you see a plus sign next to your pointer.)
5. Click Close .

Now click the button you just added to run the calculator.

Paste Information from Excel as a Picture

Do you want to place an image of an Excel file into a Word document, image editing program, or other program? It's easy to do.

1. On the Excel worksheet or chart sheet, select the cells or click the chart or object you want to copy.
2. Hold down SHIFT and click Copy Picture on the Edit menu.
3. For best picture quality, make sure As shown on screen and Picture are selected, and then click OK .
4. Click the worksheet or other document where you want to paste the picture.
5. Click Paste on the Edit menu.

To make adjustments to the image after you've pasted it, use the Picture toolbar. (To open it, point to Toolbars on the View menu and click Picture .)

Editor's Note:  Cell gridlines appear in the picture if they are displayed. To omit gridlines, in Excel click Options on the Tools menu, click the View tab, and then clear the Gridlines check box.

Protect Cells Using Data Validation

Here's a creative way to protect cells in an Excel worksheet so that other users can't make changes to them:

1. Select the cells you want to protect. (It's a good idea to make a note of the cells you protect in case you need to remove that protection later.)
2. On the Data menu, click Validation , and then click the Settings tab.
3. Set the following restrictions: In the Allow box, click Text Length ; in the Data box, click between ; in the Minimum box, type 10000; and in the Maximum box, type 50000.
4. Click the Error Alert tab.
5. Make sure the Show error alert after invalid data is entered check box is selected. In the Style box, click Stop .
6. If you want a title to appear in the title bar of the message or in the Office Assistant balloon if the Office Assistant is displayed, type the text in the Title box. If you leave the Title box blank, the title defaults to Microsoft Excel .
7. If you want to display your own text for the message, type the text in the Error message box, up to 225 characters. Press ENTER to start a new line in the message. If you don't enter any text in the Error message box, the message displays the following: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Excel displays the message only when a user types data in the cell.

To remove data validation settings, select the protected cells, click Validation on the Data menu, and then click Clear All .