Tip # 37 Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

• Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
• Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula. (See Tip #1 for an alternate suggestion.)
• Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
• Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.

Tip # 36 Identify printed sheets

Printing a sheet is a common task. Some users find it useful to print the name of the workbook in the header or footer. In Excel 2003, you can accomplish this by choosing Page Setup from the File menu and clicking the Header/Footer tab. Then, choose the appropriate item from the Header control's drop-down list. Versions prior to 2003 can use the following VBA procedure to print the full file's pathname:

Sub FormatHeader() 
 With ThisWorkbook   
  ThisWorkbook.Worksheets(sheetname)PageSetup.LeftHeader = .FullName 
 End With 
End Sub

where sheetname is the sheet's name as a string value. To make the procedure more dynamic, use ActiveSheet.Name instead. That way you can run it against any sheet in the workbook.

Tip # 35 View formulas, or not, quickly

You probably know that you can view all the formulas in a sheet by choosing Options from the Tools menu and selecting Formulas on the View tab. Doing so displays formulas instead of their evaluated results. But there's a quicker way. Press Ctrl+~ (the tilde character to the left of the number 1 on your keyboard). The keyboard combination toggles between formulas and normal view. When you're finished viewing the formulas, simply press Ctrl+~ again to return to normal view.

Tip # 34 Hide everything but the working area

You usually hide a column or row to conceal or protect data and formulas. You can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area.

To hide unused rows, select the row beneath the sheet's last row. (Select the row header to select the entire row.) Next, press Ctrl+Shift+Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl+Shift+Right Arrow and then choose Column from the Format menu instead of Row.

Before you hide anything, make sure you don't inadvertently hide an obscure area by pressing Ctrl+End to find the last cell in the sheet's used range. Unhide the rows and columns by selecting the entire sheet. Then, select Row or Column from the Format menu, and choose Unhide.

Tip # 33 Customize movement

By default, the cell pointer moves down when you press Enter. Selecting the cell immediately below the current one won't always be what you need. For instance, some people enter data from column to column. You could press the Right Arrow key instead of Enter, but out of habit, most of us reach for Enter. Even if you can retrain yourself (or users) to use the arrow keys, they're far enough away from the main keys to slow down data entry.

Fortunately, you can change the cell pointer's default direction. Chose Options from the Tools menu and then click the Edit tab. Select the Move Selection After Enter check box (if necessary) and then choose a direction from the option's drop-down list. For instance, to move from column to column, you might choose Right instead of Down.

While entering data, you can temporarily force the cell pointer to move in the opposite direction by holding down the Shift key while you press Enter.

Tip # 32 Create custom lists

Most of us work with sets of data that seem to repeat themselves throughout our projects. That means we can enter the same values in numerous spots. If you frequently enter the same dataset, consider creating a custom list. To do so, choose Options from the Tools menu and then click the Custom Lists tab. In the List Entries control, enter each item in the list, one entry per line, in the order in which you want it to appear. When you've completed the list, click Add. Excel will copy the list to the Custom Lists control. Click OK to close the Options dialog. To enter the list, select a cell and enter any name in the list. Then use the fill handle to complete the list.

If you want a partial list, enter the item you want to begin with and then pull down the fill handle. Excel will fill in the remaining names.

If the list already exists in the sheet, you don't have to retype it to create a custom list. Simply select the list before choosing Options from the Tools menu. Then, click Import on the Custom Lists tab.

Tip # 31 Enter repetitive data quickly

Tip # 29 show you how to copy existing data into noncontiguous cells. You can also use this technique to enter data into a series of noncontiguous cells. Hold down the Ctrl key and click all the cells into which you want to enter data. Then, type the text you want to enter and press Ctrl+Enter. Excel will enter the typed text into all of the cells in the noncontiguous selection.