Friday, July 17, 2009

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.

No comments:

Post a Comment