MS Excel 2003 Training 101 - Freeze and Split, Filter and Sort

Freeze panes

Keep column names in sight as you scroll through worksheets. To freeze names, make a selection in the worksheet, and then click Freeze Panes on the Windows menu.

To freeze names, do not select the names themselves. To freeze:

Column names   Select the first row below the names.
Row names   Select the first column to the right of the names.
Both column and row names   Click the cell that is both just below the column names and just to the right of the row names.

Tip   You can freeze panes anywhere, not just below the first row or to the right of the first column. For example, if you wanted the information in the first three rows to stay in sight as you scroll, you would select the fourth row and then freeze panes.

Split panes

You split panes by making a selection in the worksheet, and then clicking Split on the Window menu.

You can split panes into:

Two panes above and below each other   Select the row below where you want the split to appear.
Two side-by-side panes   Select the column to the right of where you want the split to appear.
Four panes   Click the cell below and to the right of where you want the split to appear.
To remove the split, click Remove Split on the Window menu. Or double-click the split bar to remove the split.

Note that you cannot split a worksheet and freeze panes at the same time.

Name cells to return to

To name cells that you frequently return to:

1. Select a cell or range of cells.
2. Enter the name in the Name Box
to the left of the Formula Bar near the top of the worksheet.
3. When you need to return to the cell or range of cells, click the arrow to the right of the Name Box, and then click the name.

To change or delete a name:

1. On the Insert menu, point to Name, and then click Define.
2. In the Names in workbook list, click a name that you can change.
3. Type the new name in the box above the Names in workbook list, and then click Add. Select the old name, and then click Delete.
If you just want to delete a name, select the name in the list and then click Delete.

Find All

To use Find All to find all instances of the same thing entered in cells throughout a worksheet:

1. On the Edit menu, click Find. Type what you want to find in the Find what box.
2. Click Find All.
All instances of what you are looking for will appear in a list in the Find and Replace dialog box. Click a specific occurrence in the list and the insertion point goes right to the specific cell in the worksheet.

Tip   You can apply special formatting to cells to make all the instances you've selected stand out and easy to spot. Click the Options button in the Find and Replace dialog box. Click the Format button, click the Font tab, and then select formatting options.

Copy and move worksheets

To copy a worksheet, do one of the following:

Hold down CTRL while you drag the sheet along the row of sheet tabs. When you get to the location where you want to add the copied worksheet, release the mouse button and then the CTRL key. Or,
Right-click a worksheet tab, and then click Move or Copy on the shortcut menu. Click the sheet that you want to copy in the Before sheet list. Then select the Create a copy check box and click OK. Or,
On the Edit menu, click Move or Copy Sheet. Click the sheet that you want to copy in the Before sheet list. Then select the Create a copy check box and click OK.
To move a worksheet, do one of the following:

Drag the worksheet tab of the sheet that you want to move to its new position. Or,
Right-click the worksheet tab that you want to move, and then click Move or copy on the shortcut menu. Click the position that you want to move the sheet to in the Before sheet list, and then click OK. Or,
Click the worksheet tab of the sheet that you want to move. On the Edit menu, click Move or Copy Sheet. Click the position that you want to move the sheet to in the Before sheet list, and then click OK.

Create multiple worksheets with common data or formatting at the same time

1. Group the worksheets on which you want the common data or formatting. To group:
Two or more adjacent sheets   Click the tab for the first sheet, and then hold down SHIFT and click the tab for the last sheet.
Two or more nonadjacent sheets   Click the tab for the first sheet, and then hold down CTRL and click the the tabs for the other sheets.
All sheets in a workbook   Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.
[Group] will appear in the title bar at the top of the worksheet.

2. On the first worksheet, enter all the common data or common formatting that you want.
3. Ungroup the worksheets by clicking any unselected worksheet tab. If no unselected worksheet tab is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut menu.
[Group] will disappear from the title bar to indicate that the worksheets are no longer grouped. The information that you entered on the first worksheet will be on the subsequent worksheets.

Tip   If you've already created your worksheets and you want to duplicate existing data or formatting from one worksheet to multiple worksheets, group the worksheets that you want to duplicate the data from and onto, and then select the cells that contain the content you want to copy. Then on the Edit menu, point to Fill, and click Across Worksheets. This command is only available if you first group worksheets.

Filter and sort data

Data that is in rows and columns can be filtered and sorted. On the Data menu, point to Filter, and then click AutoFilter. An arrow appears at the top of the column in which you've selected data to filter. Click the arrow and select what you want to see. When you filter data, the other data is hidden from view.

You can also sort data differently by clicking the AutoFilter arrow and then clicking either Sort Ascending or Sort Descending in the list.

Print options

To set page breaks:

On the View menu, click Page Break Preview. You can adjust the page breaks by dragging the dotted page break lines.
To include column names on every printed page:

On the File menu, click Page Setup, and then click the Sheet tab. Under Print titles, in the Rows to repeat at top box, enter the row with the column names that you want to repeat at the top of each printed page.
To print just a portion of a worksheet:

On the View menu, click Page Break Preview. Select the area you want to print. On the File menu, point to Print Area, and then click Set Print Area. When you're ready to print, on the File menu, click Print. To clear the print area, on the File menu, point to Print Area, and then click Clear Print Area.
If you want to print a selected area of a worksheet without defining a print area, there's another method. Select the area you want to print. Then, on the File menu, click Print. Under Print what, click Selection. This selection is not saved when you save the file.
To print multiple worksheets at the same time:

Hold down CTRL and click each worksheet tab that you want to print. On the File menu, click Print. In the Print dialog box, under Print what, click Active sheets(s).