Tips (ML/EC/CB/SA/EG/DC)

Because there's more than one spreadsheet package out there, we've done our best to come up with a few generic tips you can use with any software package. We've also rounded up some application-specific tips that will come in handy for Excel and AppleWorks users.

General Spreadsheet Tips

Use arrow keys to change the active cell, but use the scroll bar to change the view of the spreadsheet.

Although you can use the scroll bars to change your view of a spreadsheet, using the scroll bars does not change the active cell.

Pay attention to the mouse pointer.

Spreadsheets change the mouse pointer to provide visual clues about things you can do.

• The mouse pointer appears as a white cross (or fat plus sign) when it's in the spreadsheet area—click to select a cell, row, or column; drag to select multiple cells; or click to add a cell to a formula if the formula or entry bar is active.

• The mouse pointer appears as a standard I-beam insertion point when it's in the formula or entry bar—click to edit the contents of the formula or entry bar.

• The mouse pointer appears as a thick black bar with arrows when it's between column or row headings—drag to change the width of the column or height of the row.

• The mouse pointer appears as a standard arrow pointer when you move it out of the spreadsheet window—use it to pull down a menu, scroll with a scroll bar, move or resize a window, or switch to another open window or application.

Turn a formula into a text value.

If you're having trouble entering a formula correctly and your spreadsheet keeps beeping at you each time you try to move to another cell, delete the equal sign from the beginning of the formula and press . The spreadsheet accepts what remains as mere text, allowing you to move on to other things. Later (when you're older and wiser), you can come back to fix the thing up. Why not just delete the formula and start from scratch later? Because often you'll get close to getting a formula right—to throw it all away is to waste the time and effort you've already put into it.

Use -click to select cells.

Select the first cell of a range, then hold down and click in the last cell of the range or use the arrow keys to extend the selection.

Select all cells with your mouse.

Click in the empty box at the top-left corner of the worksheet window (to the left of column A and above row 1). This selects every cell in the spreadsheet.

Create a data-entry area.

Select the range of cells in which you want to enter data. Then, when you complete an entry by pressing or , you'll automatically move to the next cell in the selection. This is quicker than activating cells one at a time.

Understand the difference between relative and absolute references.

You indicate that all or part of a cell reference is absolute by putting a dollar sign ($) before it. To remember what that symbol means in a cell reference, think of the word always. So, for example, you can think of $D$5 as always D, always 5—or always D5.

Make a column the best width.

To make a column just wide enough to display the longest item in the column, double-click the line to the right of the column letter in the column-heading area.

Use drawing tools to annotate spreadsheets and charts.

You can draw attention to spreadsheet results by drawing circles and arrows right on the spreadsheet. Use a text-box tool, if available, to add notes.

Use contextual menus.

Hold down the key and click a selection. A pop-up menu with commands you can use on the selection appears.

Transpose rows and columns.

You organized your spreadsheet with months in columns and categories in rows, and then you decide that you really wanted categories in columns and dates in rows. What do you do? Transpose them. Select the cells you want to transpose, and use the Copy command to copy them. Then choose the Paste Special command, and turn on the Transpose check box. Click OK to transpose the rows and columns.

Split the screen so headings stay put when you scroll.

Drag the little black bar at the very top of the vertical scroll bar down as far as you wish to split the screen—you can then scroll either the top or the bottom half of the window. Horizontal splitting works the same way—look for the split bar to the left of the horizontal scroll bar.

Hide columns or rows.

Hiding a column or row is a good way to get something out of your way temporarily. To hide a column or row, set its column width or row height to 0. It disappears! Your only clue to the fact that there are hidden rows or columns in the spreadsheet is the missing letters or numbers in the headers. Displaying a hidden column or row is a little trickier—you can't easily select a column or row if you can't see it. One way is to use the Go To command to select a single cell in the column or row and then set the column width or row height to something other than 0.

Excel Tips

Edit directly in the cell.

If you want to change the contents of a cell without having to use the formula bar, double-click the cell.

Make noncontiguous selections.

You can select cells or blocks of cells that aren't next to each other (to apply formatting, say) by selecting the first cell or block and then holding down while you select subsequent cells.

Move a selection block.

If you've selected a block of five cells in a row to apply some formatting and then want to select another five cells two rows down to apply some more formatting, don't reach for the mouse. Instead, move the selection block: moves the block to the right and moves it to the left, and and move it up and down, respectively.

Select referenced cells.

If the current cell has a formula in it, you can select all the cells to which the formula directly refers by pressing . Pressing instead will select all cells to which the formula refers, even indirectly.

Move cells with drag and drop.

You can drag a cell or a range of cells by its border to move it to a new position. Hold down while dragging to insert it between other cells. Hold down while dragging to copy it to the new location.

Use AutoFill to enter data into adjacent cells.

Enter a value or formula in the first cell, press to complete the entry, and then drag the fill handle (the little box in the bottom-right corner of the cell) to extend a box around the other cells you want to contain the same value or formula. If the original cell contains a day, month, or other component of a familiar series, Excel completes the series for you.

Enter the current time or date quickly.

To enter the current date in an Excel cell, press . To enter the current time, press .

Use AutoSum to add columns or rows.

Select the cell at the bottom of a column or the right side of a row you want to total. Then double-click the AutoSum button, the one with the sigma (Σ) on it. Excel guesses which cells you want to total and writes a formula complete with the SUM function and references to the cells. You can use this feature in a variety of ways—even to total more than one column or row at a time.

Use Natural Language Formulas.

This Excel feature enables you to write formulas using column and row headings to refer to cells. For example, in this little spreadsheet, the formulas in cells B10, C10, and D10 are =SUM(FY00), =SUM(FY01), and =SUM(FY02), respectively (Figure 10.10). Neat, huh?

Name cells.

You can also give cells or ranges custom names, which make it easier to write formulas and use the Go To command.

Use the Formula palette to write formulas using functions.

The Formula palette not only provides online help to help you understand functions but takes you every step of the way through the creation of a formula with one or more functions.

Change relative references to absolute references with a keystroke.

If you've already entered a cell reference in a formula and want to change the reference type, select the cell reference and press until Excel places the absolute reference dollar sign(s) where you want them.

Float a toolbar.

Position the mouse pointer anywhere on a toolbar other than on a button and drag the toolbar down from the top of the screen. You'll get a floating toolbar that you can put anywhere you like. Drag it back up (or down or to the side) to dock it again.

Tear-off toolbar menus.

If a toolbar button's drop-down menu has a move handle—a gray bar along its top or side edge—you can tear the menu off the toolbar. Simply drag the menu away from the button to display the menu as a floating toolbar.

Rename sheets.

You can change the name of a worksheet in an Excel workbook file by double-clicking the sheet tab for the sheet, entering a new name, and pressing .

Move or copy sheets by dragging.

You can change the order of sheets in a workbook by simply dragging tabs to new positions. You can copy a worksheet by holding down as you drag the tab. You can move (or copy) a sheet to another workbook by dragging (or -dragging) its tab from one workbook file to another.

Experiment with the macro feature.

It enables you to perform repetitive tasks quickly or create custom functions. To get started, let Excel's macro recorder write the macro for you. This is a nice—although limited—use of macros that can familiarize you with the macro language.

Apply basic formatting from the keyboard.

You can apply all the basic number-formatting choices to a cell by using keyboard commands:

General
Currency
Percentage
Exponential
Date
Time
Two decimal places, 1000 separator, and minus sign (–) for negatives

Use the Format Painter.

A cell's format includes text formatting (font, size, and style), alignment (left or centered), and number formats (dollar signs and the number of decimals). You can use the Format Painter to copy formatting and apply it to other cells. Start by selecting the cell that contains the formatting you want to copy. Click the Format Painter button in the Standard toolbar. Then select the cells to which you want to apply the formatting. To apply formatting to multiple cell selections, double-click the Format Painter button; press when you want to stop painting.

Apply conditional formatting.

Suppose you want all values over a certain amount to appear in green or bold or with a yellow background? Use the Conditional Formatting dialog box to define the conditions and related formatting (Figure 10.11). Excel does the rest based on spreadsheet contents.

Use the Range Finder feature.

While you're editing a formula that refers to one or more cell ranges, the range names are color-coded in the formula bar to match Range Finder frames around the ranges in the spreadsheet. To change a range in the formula, grab the appropriate Range Finder handle and drag to surround more or fewer cells—the formula changes to reference the new range. This works with charts, too; when a chart series is selected, adjustable Range Finder frames appear around associated spreadsheet ranges. Just drag the frame's handle to change the chart.

Switch worksheets—or workbooks—without using the mouse.

Use these shortcuts instead:

Next worksheet
Previous worksheet
Next workbook
Previous workbook

Copy a selection as a graphic.

You can copy a selected section of the spreadsheet to the Clipboard as a PICT graphic (which will include gridlines and row and column headings) and then paste it into any program as a graphic. Just hold down and choose Copy Picture from the Edit menu.

Select multiple worksheets.

You can select more than one worksheet at a time to perform certain global operations such as deleting the worksheets, running a spelling check, or turning off all the gridlines. Start by clicking the tab of the first worksheet to include in the selection. Then:

• To select multiple contiguous worksheets, hold down and click the tab of the last worksheet. This selects all worksheets in between.

• To select noncontiguous worksheets, hold down and click their tabs.

• To select all worksheets, hold down , click any worksheet tab, and choose Select All Sheets from the contextual menu that appears.

Enter the same data in the same cells of multiple worksheets.

First select the tabs of the worksheets in which you want to enter the data. Then enter the data in the top worksheet. It appears in all selected worksheets.

AppleWorks Tips

Put some life in your spreadsheets.

Select a range of cells to color, and then use the Accents window options to add background colors, patterns, and other formatting effects (Figure 10.12). Can't see black text against your colored background? Pick a text color for selected cells with the Text Color command under the Format menu.

Customize the button bar.

Use options in the Customize Button Bar dialog box to specify which buttons appear on the button bar and in what order they appear.

Zoom out to see more of your work at once.

Click the small-mountains button at the bottom left of a spreadsheet window to zoom out; click the big- mountains button to zoom in. Besides displaying your current magnification level, the number in the bottom-left corner of the window is a pop-up menu that lets you zoom as far as you want in a single move.

Select all data with your mouse (SA/ML).

Hold down while clicking in the empty box at the intersection of the column and row headings. This selects everything from cell A1 to the farthest cell containing data. (Holding down also changes the Edit menu's Select All command to Select All Data.)

Use the Fill Special command to type stuff for you.

Need to enter a series of months into adjacent cells? Click in a cell that contains the first month of the series, and drag to the right or down as far as you want to go. Then choose Fill Special from the Calculate menu. This works for all kinds of series.

Copy and paste formatting.

Select a cell with formatting you want to copy to other cells. Then choose Copy Format from the Edit menu. Now select the cells to which you want to copy the formatting, and choose Paste Format from the Edit menu. This copies the formatting of the first cell to the other cells. It's much easier than choosing formatting options such as font, size, color, or style by hand, and it always works perfectly.

Use the Lock Title Position command to keep column titles in sight (DC).

Not only does this prevent column titles from scrolling out of the window but it ensures that column titles print on each page. It's also a handy way to identify a range of cells.

Set the arrow-key action.

By default, the arrow keys move you around in the entry bar for editing cell contents. But you can change that through the Edit menu's Preferences command and make the arrow keys move you from one cell to another instead. (While you're in there, you can change the action of the key, too.)

Reverse the arrow-key action.

Whichever way you've set the preferences for the arrow keys, pressing reverses it temporarily. So, if an arrow key normally lets you edit in the edit bar, pressing an arrow key with will let you move from cell to cell, and vice versa.

Auto-enter absolute references.

If you're clicking or dragging in cells to enter their names into a formula, you can make the references absolute by holding down and while clicking or dragging in the cells.

Activate the entry bar.

If you're working from the keyboard and have selected a cell, don't click in the entry bar to activate it so you can edit the cell contents. Instead, press to put the insertion point at the end of the entry bar or to put the insertion point at the beginning of the entry bar.

Move a selection without dragging and dropping.

You can move a selected cell or a block of cells by holding down and while clicking where you want the selection moved. As with a drag-and-drop move, this doesn't change any of the cell references in formulas you may be moving.

Use AutoSum to add columns or rows.

Select the range of cells you want to total, as well as the empty cell where you want the total to appear. Then double-click the AutoSum button (the one with the sigma [Σ] on it). AppleWorks writes a formula with the SUM function and references to the cells.

Reverse the axes in a chart.

When your chart's axes are reversed—the x-axis is where you want the y-axis and the y-axis is where you want the x-axis—you can switch them. Use the General panel of the Chart Options dialog box to change the Series in option.