MS Excel 2003 Training 101 - Working with Worksheets

Save time. Take advantage of the ways Excel can make your work easier in very big worksheets, and in smaller ones too.

Copy worksheets instead of re-creating the same data by hand on another worksheet. Create multiple worksheets with common data at one time when you know you'll need the same worksheets each week or each month. Filter data by using the AutoFilter arrows so that you see only the information you need. Learn how to print just a certain area of a worksheet, and how to print multiple worksheets at once.


Copy and move worksheets
Perhaps you have worksheet data that you'd like to copy from one worksheet to another blank worksheet. An easy way to do so is to click the worksheet tab of the sheet that you want to copy, hold down CTRL, and then drag the selected sheet along the row of sheet tabs. As you drag, you'll see a small worksheet symbol with a plus (+) sign on it, indicating that you are copying a worksheet. A small downward pointing arrow will follow along.

When you get to the location where you want to add the copied worksheet, indicated by the downward pointing arrow, release the mouse button, and then release the CTRL key.

There's another way to copy a worksheet: You can right-click the worksheet tab, and then click Move or Copy on the shortcut menu. There's one more step to this method, which you'll see in the practice at the end of the lesson.

Or you might need to move a worksheet to change the order in which you've organized a set of worksheets, or to change the order of a newly inserted worksheet.

To move a worksheet, you can drag the selected sheet along the row of sheet tabs. Click the tab of the sheet you want to move. When you hold down the mouse button, you'll see a small worksheet symbol (this time without a plus (+) sign). Drag the sheet tab to a new position, and a small downward pointing arrow will follow along. When the arrow reaches the position where you want to place the sheet, release the mouse button.

Or you can right-click the tab on the sheet that you want to move, and then click Move or Copy on the shortcut menu.

Note   Take care when you move or copy sheets if you have formulas in the worksheets. Calculations or charts based on worksheet data might become inaccurate if you move the worksheets. If you insert a worksheet between sheets that are referred to by a 3-D formula reference, data on that worksheet might be included in the calculation.

Create multiple worksheets with common data at the same time
Group worksheets to save time when you type common data or apply common formatting.
 [Group] in the title bar indicates worksheets are grouped.
 When the worksheets are grouped, enter common data or apply common formatting on the first sheet.
 White worksheet tabs also indicate that worksheets are grouped.

If you create the same report or budget worksheet each week or each month and type the same data each time, such as department, employee, or product names, you can save time by typing common data just once in one worksheet, instead of retyping the same data over and over again in multiple worksheets.

You do this by first grouping together as many worksheets as you want to create. How you group the worksheets depends on how many sheets you want to group together. 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 to let you know that you have grouped worksheets.

Then enter all the data on the first sheet that will be common to all the sheets. While the sheets are grouped, you can also apply all the formatting that will be the same; insert the same number of cells, rows, columns, and worksheets; or set up the same headers and footers.

When you are through doing all the work that is the same, ungroup the sheets by clicking any unselected sheet. If no unselected sheet 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. From there you can go on to make any individual changes that you need to make to each worksheet.

Filter and sort data
To filter and sort data, on the Data menu, point to Filter, and then click AutoFilter.
 AutoFilter command.
 AutoFilter arrow.

Data that's in rows and columns can be filtered. You might filter data to show only sales by one salesperson out of many, instead of reading through row after row of all salespersons' data to review the sales for that one salesperson. When you filter data, the other data is hidden from view.

In the picture, you might use the AutoFilter arrows to see only the sales made by Leverling.

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. You click the arrow and select what you want to see.

You can also sort data differently by clicking either Sort Ascending or Sort Descending in the list on the AutoFilter arrow. You might do this with dates, order amounts, names, or any kind of data that has to do with the alphabet or numbers.

Adjust page breaks and set print options
To print just a portion of a worksheet, on the File menu, point to Print Area, and then click Set Print Area.

When it's time to print a really big worksheet, you may need help to figure out what is printing where.

Print just a portion of a worksheet

If you expect that you'll frequently print a particular area of a worksheet instead of everything on the sheet, it's a good idea to define a print area:

On the View menu, click Page Break Preview. Select the area you want to print. Next, on the File menu, point to Print Area, and then click Set Print Area. When you save the workbook, your defined print area is also saved. You can save only one defined print area at a time on a worksheet.

When you're ready to print, on the File menu, click Print. Only the defined print area will be printed.

To clear the print area definition, 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.

Tip    If you click Selection under Print what in the Print dialog box, Excel will print your selected area, even if you have defined and saved another different print area. The defined print area will still be saved.

Page breaks

Before you print, you can see exactly what data will go on each page and adjust the page breaks that determine what will print on each page.

For example, if there's a column that you want to print beside another column, but it will print on the next page, you might be able to adjust the page break so that both columns print on the same page. On the View menu, click Page Break Preview. You can adjust the page breaks by dragging the page break lines.

Column names

You'll help readers of a printed worksheet, especially big worksheets, by including column names on every page so that they don't have to flip back to the first page to see the names.

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.

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).