Excel Tips
Page 2

Clearing and Deleting Cells in Excel
Clearing cells means erasing everything within them, whereas deleting actually deletes the entire cell (or cells). This may sound like an odd distinction, but it is not really. If you compare the cells in your worksheet to paper cups, you can either remove a cup entirely (deleting the cup) or simply remove any contents from the cup (clearing the cup). When you delete a cell, the other cells around that cell move to fill where the cell used to be. To clear the contents of cells you have selected, choose Clear from the Edit menu. When you do, will see a submenu asking what you want to clear. Your choices allow you to clear any of the following:
* All. Everything related to the cell except the cell itself.
* Formats. Any formatting applied to the cell.
* Contents. The information in the cell. Any formatting remains in place.
You should make your selection based on what you want cleared and then choose the appropriate menu item. If you like, you can also use the Del key to clear a cell. When you use this shortcut key, it is the same as choosing Contents from the Clear submenu.

Deleting is done by selecting the cells you want to delete and then choosing Delete from the Edit menu. When you do this, Excel attempts to determine how the remaining cells in the worksheet should be moved to close up the hole that will be created by deleting the cells. If it can figure it out (for instance, if you are deleting an entire row or column), then the operation is completed. On the other hand, if it is not obvious how the remaining cells should be moved, Excel displays the Delete dialog box, which allows you to specify whether cells should be moved up or to the left.

Split a Worksheet's Viewing Area in Excel
Sometimes it's helpful to view different parts of a worksheet at the same time. You can easily split the view of your worksheet into scrollable panes. To create a horizontal split, drag the split box (the small rectangle that's directly above the vertical scroll bar) to the position on the worksheet where you want the split made. To create a vertical split, drag the split box that's to the right of the horizontal scroll bar. To remove a split, just double-click on the split line, or go to Window on the Menu Bar and choose Remove Split.

Determining the Least Common Multiple
Remember your junior-high math classes? The teacher would write three or four numbers on the chalkboard and ask you to determine what larger number each of the numbers on the board could be a factor of. For instance, if the numbers were 2, 3, and 4, then they are all factors of the number 12. Thus, 12 is the least common multiple of those three numbers. Things got really difficult when the teacher threw up six, seven, or ten numbers. Fortunately, Excel makes calculating the least common multiple rather easy. All you need to do is put the numbers in a range of cells, and then use a formula like this to return the correct value:

=LCM(C20:C23)

Setting Row Height in Excel
Excel normally sets the height of rows within your worksheet to reflect the size of the largest font used on the row. You can, however, adjust the height of the row to any size desired. For instance, you might want to add additional space before a row. Rather than insert a blank row, you can increase the height of the row so it has blank space before it.

The easiest method for adjusting row height is to use the mouse. All you need to do is move the mouse pointer into the row header area (the gray area at the left of the row). When you move the pointer so it is over a dividing line between rows, it changes shape to a double-headed arrow. When the mouse pointer is this shape, you can click on the left mouse button, hold it down, and drag the border to a new position. When you release the mouse button, the row is resized to the new height.

To adjust row height with the menus, follow these steps:
1. Select any cell in the row you want to adjust. If you select a cell range that includes cells in multiple rows, you can adjust the row height for more than one row at a time.
2. Choose Row from the Format menu. This displays a submenu.
3. Choose the Height option from the submenu. This displays the Row Height dialog box.
4. Adjust the value in the dialog box to reflect the desired row height.
5. Click on OK.

Close All Workbooks at Once
If you have several workbooks open and you want to close them without quitting Excel, just press the [Shift] key and open the File menu. You'll find that the usual Close command has been replaced with Close All. This also works if you have several documents open in Word.

Moving Worksheets Between Excel Workbooks (97/2000)
Occasionally you'll find that data stored in separate Excel files would be more useful if it were all stored in a single workbook. Moving sheets from one workbook to another is easy. First, open the workbooks with your data. Choose Window, then Arrange from the menu bar. Select the Horizontal option button, and click OK. Then simply click and drag a worksheet tab from one workbook to another.

Cycling Through Font Colors in Excel
Excel provides a tool you can use to cycle through the available font colors. The tool is not available by default, but must be added by following these steps:

1. Choose Customize from the Tools menu. Excel displays the Customize dialog box.
2. Make sure the Commands tab is displayed.
3. In the list of Categories, choose Format.
4. In the list of Commands, select Cycle Font Color (it has a palette for an icon).
5. Drag the Cycle Font Color icon to the place on your toolbar where you want the tool to appear.
6. Click on Close.

Now, whenever you click on the Cycle Font Color tool, the font color in the selected cells will change. Click again, and the color changes again. Keep clicking, and eventually the font colors repeat.

Quickly Clear Formatted Excel Worksheet Cells (97/2000)
If you want to delete a range of data, it's easy to select the range and press the Delete key. However, this won't remove borders and shading. To remove the borders and shading, you would need to select the whole range and then select Edit, Clear, All from the menu bar. An alternative to this is to drag and drop an adjacent range of totally blank cells over the range of formatted ones.

Highlighting the Rows of Selected Cells in Excel
Sometimes it is easy to lose track of where the selected cell is located in a worksheet. There are several ways you can locate the cell, but sometimes it would be handy to just have a way to highlight the whole row containing the selected cell.

The easiest way to do this in Excel is to press Shift+Space Bar. The entire row is highlighted, and the selected cell remains the same. If you want to move to another cell in the same row (without changing the highlight), you can use Tab to move to the right and Shift+Tab to move to the left.

Working With Excel Grid Lines
If you would like to view your worksheet with colored grid lines, run Excel and open a blank worksheet. Choose Tools, Options and, when the Options dialog box opens, click the View tab. Under Window Options, click the arrow at the right side of the Color list box and select a new color. Make sure the check box labeled Gridlines is selected and click OK to close the dialog box and apply your new color.

If you would like to print the grid lines in your new worksheet, choose File, Page Setup. When the Page Setup dialog box appears, click the Sheet tab. Now, under Print, select the Gridlines check box and click OK to close this dialog box and save your new setting. If you have a color printer, the gridlines will print in color.

Slanted Column Headings in Excel
In the days when people made spreadsheets by hand, you would often see a slanted column heading used to save space and enhance the spreadsheet's appearance. It's very easy to do the same thing in an Excel worksheet. All you have to do is select the cell or block of cells that contains the header text and choose Format, Cells. When the Format Cells dialog box opens, click the Alignment tab. Now use the Degrees spin box to set the direction and amount of slant you want for the headings. When you finish, click OK to close the dialog box and apply your new text setting.

Clearing an Excel Cell
Some Excel users get into the habit of just pressing Space to clear a selected cell. However, this is a habit you ought not get into, because this leaves a space in a cell which can cause problems. The best way to clear a cell is to select the cell and choose Edit, Clear. When the Clear submenu opens, choose All, Formats, Contents, or Comments. If you need to clear only the contents, select the cell and press Delete.

Erase Excel Cell Contents With the Mouse
This tip is handy when you have your hand on the mouse and don't want to reach over to press the Delete key to erase certain cell contents. Here's how it works: Select the cells that contain the data you want to erase. Now use the AutoFill handle to drag backward over the cells. The cells turn gray as you do this. When you release the mouse button, the cell contents disappear. This operation is exactly the opposite of dragging the handle to AutoFill cells.

Linking AutoShapes to Excel Data
AutoShapes are pre-defined drawing objects, such as starbursts, circles, and arrows, that you can add to a worksheet to improve its appearance. Most AutoShapes allow you to add text, making them useful for calling attention to particular data. You can link data from worksheet cells to the shape, so that the information reflected in the AutoShape is always current. To do so, choose View, Toolbars, Drawing from the menu bar to display the Drawing toolbar. Then, choose an appropriate shape from the AutoShapes menu and use your mouse to draw the shape on your worksheet. While the shape is still selected, click in the Formula bar and enter a formula that links to a worksheet cell, such as: =A1 to display whatever value is in cell A1. Finally, press [Enter]. You can now use the formatting options for the AutoShape to control the appearance of the linked data.

Quick AutoFill Variations
When you are working on a spreadsheet and entering text, Excel attempts to be helpful and will suggest AutoFill based on the letters you type. Another way to take advantage of this feature is to right-click on the cell where you want to enter information and choose Pick From List from the resulting Context menu. Excel displays a drop-down list that shows in alphabetical order the other items you have entered in the column, allowing you to select from the list. If you need still faster access, you can hold down the Alt key and press the Down Arrow. Excel displays the same list of items and allows you to pick the one you want. This only works with text entries.

Simple Excel Calculations
If you type some numbers into adjoining cells (either across or down) and want to quickly calculate the sum, click in the cell below or to the right of the numbers, hold down the Alt key and press = (equal); then press Enter.

Totaling Across Worksheets in Excel
There is a way to keep running totals in a column or row without worrying about the number of entries. Use the following formula which only references the column or row, but not both. For example, =SUM(B:B) to total a column or =SUM(3:3) to total a row.

Display Excel Row and Column Headings on Printouts
By default, printed Excel worksheets don't include the numeric row headings and alphabetic column headings found in the electronic version of a spreadsheet. To print these headings for a particular worksheet, choose File, Page Setup. Then, click on the Sheet tab, select the Row And Column Headings check box, and click OK. Note that the setting change applies only to the active worksheet.