excel 2000 level 2 vicon

Microsoft Excel 2000 Level 2

Vicon Learning Systems Page i

ABOUT VICON Vicon Learning Systems (VLS), the parent company of JaxWorks.com, provides computer training products and services with a focus on helping its customers improve productivity through the successful implementation of technology. The training materials are for the students' use both during the self-administered course and as a reference guide. For more information and a list of other available products, please visit us at our web site at www.jaxworks.com.

COPYRIGHT & TRADEMARKS Copyright 2004 by Vicon Learning Systems. All rights reserved. Information in this document is subject to change without notice and does not represent a commitment on the part of Vicon Learning Systems. Trademarked names appear throughout this book. Rather than list the names and entities that own the trademarks or insert a trademark symbol with each mention of the trademark name, Vicon Learning Systems states that it is using the names for editorial purposes and to the benefit of the trademark owner with no intention of infringing upon the trademark.

DISCLAIMER Vicon Learning Systems made every effort to ensure the accuracy of this manual. If you should discover any discrepancies, please notify us immediately at: www.jaxworks.com/feedback

Vicon Learning Systems Page iii

EXCEL 2000 LEVEL 2

ABOUT VICON.......................................................................................................... I

COPYRIGHT & TRADEMARKS............................................................................ I

DISCLAIMER ............................................................................................................ I

LESSON 1 - USING AUTOMATIC FORMATTING AND STYLES..................1 Using Automatic Formatting Features .....................................................................2 Applying an AutoFormat..........................................................................................2 Changing AutoFormat Options ................................................................................4 Extending List Formats and Formulas......................................................................5 Creating a Style by Example ....................................................................................7 Applying a Style .......................................................................................................9 Creating a New Style..............................................................................................10 Modifying a Style ...................................................................................................12 Merging Styles .......................................................................................................14 Exercise ..................................................................................................................16

Using Automatic Formatting and Styles ............................................................16

LESSON 2 - WORKING WITH DATA SERIES .................................................19 Using the Series Command ....................................................................................20 Creating a Linear Series .........................................................................................21 Creating a Date Series ............................................................................................23 Using a Stop Value.................................................................................................24 Exercise ..................................................................................................................27

Working with Data Series ..................................................................................27

LESSON 3 - USING CONDITIONAL AND CUSTOM FORMATS..................29 Applying Conditional Formats ...............................................................................30 Changing a Conditional Format .............................................................................32 Adding a Conditional Format .................................................................................33 Deleting a Conditional Format ...............................................................................35 Creating a Custom Format......................................................................................36 Exercise ..................................................................................................................40

Using Conditional and Custom Formats ............................................................40

Page iv Vicon Learning Systems

LESSON 4 - USING LARGE WORKSHEETS ....................................................41 Increasing the Magnification ..................................................................................42 Decreasing the Magnification.................................................................................43 Changing the Magnification of a Range.................................................................45 Switching to Full Screen View...............................................................................46 Splitting the Window..............................................................................................48 Removing Split Windows.......................................................................................50 Freezing the Panes..................................................................................................51 Unfreezing the Panes ..............................................................................................52 Exercise ..................................................................................................................54

Using Large Worksheets ....................................................................................54

LESSON 5 - WORKING WITH MULTIPLE WORKSHEETS.........................57 Using Multiple Worksheets ....................................................................................58 Navigating between Worksheets ............................................................................59 Selecting Worksheets .............................................................................................60 Renaming Worksheets............................................................................................61 Selecting Multiple Worksheets...............................................................................62 Inserting Worksheets ..............................................................................................63 Deleting Worksheets ..............................................................................................65 Printing Selected Worksheets.................................................................................66 Exercise ..................................................................................................................68

Working with Multiple Worksheets ...................................................................68

LESSON 6 - MANAGING WORKSHEETS.........................................................69 Copying Worksheets ..............................................................................................70 Moving Worksheets................................................................................................71 Using Grouped Worksheets....................................................................................72 Moving Data between Worksheets.........................................................................73 Copying Data between Worksheets........................................................................74 Creating 3-D Formulas ...........................................................................................76 Using Functions in Worksheets..............................................................................78 Exercise ..................................................................................................................80

Managing Worksheets........................................................................................80

LESSON 7 - WORKING WITH OUTLINES.......................................................83 Applying an Outline ...............................................................................................84

Vicon Learning Systems Page v

Expanding/Collapsing an Outline...........................................................................86 Modifying Outline Settings ....................................................................................87 Clearing an Outline.................................................................................................88 Using Auto Outline.................................................................................................89 Exercise ..................................................................................................................91

Working with Outlines .......................................................................................91

LESSON 8 - USING PASTE SPECIAL.................................................................93 Working with Paste Special....................................................................................94 Copying Formats between Worksheets ..................................................................95 Copying Values between Worksheets ....................................................................96 Copying Formulas between Worksheets ................................................................97 Performing Mathematical Operations ....................................................................99 Exercise ................................................................................................................101

Using Paste Special ..........................................................................................101

LESSON 9 - WORKING WITH LABELS IN FORMULAS.............................103 Using Labels in Formulas.....................................................................................104 Using Labels to Define a Range...........................................................................104 Using Multiple Stacked Headings ........................................................................106 Referring to Individual Cells ................................................................................108 Exercise ................................................................................................................111

Working with Labels in Formulas....................................................................111

LESSON 10 - MANAGING DATA ......................................................................113 Sorting Lists .........................................................................................................114 Sorting in Ascending/Descending Order..............................................................114 Finding Data .........................................................................................................116 Replacing Data .....................................................................................................118 Exercise ................................................................................................................121

Managing Data .................................................................................................121

LESSON 11 - MANAGING FILES......................................................................123 Changing Workbook Properties ...........................................................................124 Selecting File Views.............................................................................................125 Finding Files based on Criteria.............................................................................127 Clearing Search Criteria .......................................................................................130

Page vi Vicon Learning Systems

Searching by File Name .......................................................................................131 Sorting Excel Files ...............................................................................................132 Copying Excel Files to a Floppy Diskette ............................................................134 Exercise ................................................................................................................136

Managing Files .................................................................................................136

LESSON 12 - USING MULTIPLE WORKBOOKS ..........................................137 Opening Multiple Workbook Windows ...............................................................138 Cascading Open Workbook Windows .................................................................139 Activating Cascaded Workbook Windows...........................................................141 Viewing Multiple Workbook Windows ...............................................................142 Activating Tiled Workbook Windows .................................................................144 Copying Data between Workbooks......................................................................144 Saving a Workspace .............................................................................................146 Closing All Open Workbooks ..............................................................................147 Opening a Workspace...........................................................................................149 Linking Workbooks..............................................................................................150 Opening Linked Workbooks ................................................................................152 Exercise ................................................................................................................153

Using Multiple Workbooks ..............................................................................153

INDEX......................................................................................................................155

LESSON 1 - USING AUTOMATIC FORMATTING AND STYLES

In this lesson, you will learn how to:

Use automatic formatting features Apply an AutoFormat Change AutoFormat options Extend list formats and formulas Create a style by example Apply a style Create a new style Modify a style Merge styles

Lesson 1 - Using Automatic Formatting and Styles Microsoft Excel Level 2

Page 2 Vicon Learning Systems

USING AUTOMATIC FORMATTING FEATURES

Discussion While formatting the information in a worksheet can give it a professional appearance, it can also be a time-consuming process. Excel includes many automatic formatting features that you can use to save time formatting data. One such feature is AutoFormat, which allows you to select from a list of preset formats and apply the formatting to your worksheet. Another feature, which uses the Extend list formats and formulas option, automatically repeats the existing formatting pattern when new data is entered. In addition, styles can be created to apply a combination of formats to a cell at the same time. If you always format a Totals row with a currency format, no decimal places, a 12 point Times New Roman bold font, a gray fill color, and a border line above and below the cell, you can save this format combination as a style. When you apply the style, all the formats are applied to the selected cells. Styles are saved with the current workbook only, but you can merge styles created in another workbook to the workbook on which you are currently working.

APPLYING AN AUTOFORMAT

Discussion You can use the AutoFormat feature to assign preset formats to a range of cells. AutoFormats create attractive, professional-looking table designs in a worksheet. AutoFormats include border styles, number formats, shading, font changes, and changes in column width and row height. AutoFormats are designed to format worksheet data that contains certain features, such as column and row headings, total rows, and detailed data. AutoFormats may not work properly with other types of worksheet layouts. When applying an AutoFormat, you can either select a range of cells or a single cell in a range.

Microsoft Excel Level 2 Lesson 1 - Using Automatic Formatting and Styles

Vicon Learning Systems Page 3

Applying an AutoFormat

If you do not like an applied AutoFormat, you can remove it using the Undo button.

Step-by-Step From the Student Data directory, open AUTOFMT.XLS. Apply an AutoFormat to a range.

Steps Practice Data 1. Drag to select the range you want to

format. The range is selected.

Drag A2:I7

2. Select the Format menu. The Format menu appears.

Click Format

3. Select the AutoFormat command. The AutoFormat dialog box opens.

Click AutoFormat...

4. Select the desired format from one of the displayed AutoFormats samples. An AutoFormat sample is selected.

Click Classic 1



Steps Practice Data 5. Select OK.

The AutoFormat dialog box closes and the AutoFormat is applied to the selected range.

Click OK

Click any cell to deselect the range.

CHANGING AUTOFORMAT OPTIONS

Discussion If you do not want to apply all the formatting in an AutoFormat sample, you can select which options you want to disable. By default, all the options are enabled. These options include Number, Border, Font, Patterns, Alignment, and Width/Height. If you disable an option, any formatting associated with that option is not applied to the selected range when you apply the AutoFormat sample.

Changing AutoFormat options



Step-by-Step Change AutoFormat options.

Steps Practice Data 1. Drag to select the range you want to

format. The range is selected.

Drag A2:I7


Click Format

3. Select the AutoFormat command. The AutoFormat dialog box opens.

Click AutoFormat...

4. Select the desired format from one of the displayed AutoFormat samples. An AutoFormat sample is selected.

Click Accounting 2

5. Select Options. A list of available options appears at the bottom of the AutoFormat dialog box.

Click Options...

6. Deselect the check box of the option you do not want to apply. The check box is deselected.

Click Number to deselect it

7. Deselect the check box of any other options you do not want to apply. The check boxes are deselected.

Click Width/Height to deselect it

8. Select OK. The AutoFormat dialog box closes and the AutoFormat is applied to the selected range without the deselected options.

Click OK

Click any cell to deselect the range.

EXTENDING LIST FORMATS AND FORMULAS

Discussion When you add new data to the next row in a list, Excel automatically applies the formatting and formulas from the previous row. Enabled by default, this feature can



be disabled using the Extend list formats and formulas option on the Edit page in the Options dialog box. To extend the formats and formulas to a new row in a list, the formats and formulas must exist in at least three of the preceding five rows of the list. Therefore, to take advantage of this time-saving feature, you must enter the first three rows of data along with the necessary formulas, and then format the list. As you enter the next new row of data, Excel will automatically extend the formatting and formulas as you type.

If you are using different formatting on alternate rows of a list, you must repeat one of the formats on at least three rows for Excel to extend the formats and formulas to a new row.

When typing data across a row, use the [Tab] key instead of the [Enter] key to move to the next cell. The [Tab] key moves the active cell to the right instead of down. When you have finished entering a row of data, press the [Enter] key to move the active cell to the first column of the next row in the list. You can also use the [Tab] key to move horizontally in a selected range.

Step-by-Step Use extended formats and formulas. Display the Sheet2 worksheet.

Steps Practice Data 1. Select the Tools menu.

The Tools menu appears. Click Tools

2. Select the Options command. The Options dialog box opens.

Click Options...

3. Select the Edit tab. The Edit page appears.

Click the Edit tab

4. Select the Extend list formats and formulas option, if necessary. The Extend list formats and formulas option is selected.

Click Extend list formats and formulas, if necessary




The Options dialog box closes and the Extend list formats and formulas option is enabled.

Click OK

6. Select the first cell in the next row of a list in which formatting and formulas exist on at least three of the preceding five rows. The cell is selected.

Click cell A11

7. Type the new list information. The formatting and formulas in the list are extended to the new row of data.

Follow the instructions shown below the table to complete this step

Type the following data into row 11, starting with the word Chicago. Press [Tab] after each entry to move to the next column in the row. Do not type anything into cell E11, just press [Tab] to move to cell F11.

A B C D E F

Office Jan Feb Mar Total Sales Expenses

Chicago 2145 1934 2790 1355

Notice that Excel automatically repeats the formatting of the list in the new row. The formulas in cells E11, G11, and H11 are automatically extended. Type Paris into cell A12 to see if the alternating fill pattern repeats.

CREATING A STYLE BY EXAMPLE

Discussion A style is a group of formats that are applied to cells. Styles are useful because they allow you to apply more than one format to cells at one time. For example, if you are formatting a title in a worksheet using a variety of cell formats such as color, pattern, alignment, font, and numeric formatting, you can use a style to apply them all at once rather than having to apply each format individually. A style can include any combination of number formats, font size and style, text alignment, text color, background color, borders, and protection. In addition, styles ensure consistency throughout a worksheet. The easiest way to create a style is to use style by example. Before creating a style by example, the desired formats must be applied to at least one cell in the worksheet. These formats will be used to create a new style. For example, if one cell in a



worksheet is formatted with the desired numeric and font formats, you can use the attributes applied to that cell to create a style by example.

Creating a style by example

You can change the formats contained in a style at a later time, if desired.

Step-by-Step Create a style by example using the formats applied to a cell. Display the Sheet3 worksheet.

Steps Practice Data 1. Select the cell containing the formats

you want to include in the style. The active cell moves accordingly.

Click cell E1


Click Format

3. Select the Style command. The Style dialog box opens with the text in the Style name text box selected.

Click Style...



Steps Practice Data 4. Type a name for the style in the Style

name text box. The name appears in the Style name text box and the attributes of the style appear under Style Includes (By Example).

Type Title

5. Select OK. The Style dialog box closes and the style by example is created.

Click OK

APPLYING A STYLE

Discussion Once a style has been created, the style name appears in the Style name list in the Style dialog box. You can apply the style to any selected cell or range of cells on any worksheet in a workbook. For example, in a sales worksheet, you can apply the same style to any cell where sales were higher than projected.

Selecting a style to apply

When a style is selected in the Style dialog box, a list of the formats included with that style appears under Style includes.



Step-by-Step Apply a style to cells. If necessary, display the Sheet3 worksheet.

Steps Practice Data 1. Select the range to which you want to

apply the style. The range is selected.

Drag A3:H3


Click Format

3. Select the Style command. The Style dialog box opens.

Click Style...

4. Select the Style name list. A list of style names appears.

Click Style name

5. Select the name of the style you want to apply. The style name appears in the Style name text box and the attributes of the style appear under Style includes.

Click Title

6. Select OK. The Style dialog box closes and the range is formatted with the selected style.

Click OK

Click in a blank area of the worksheet to deselect the range. Widen columns F and H, as necessary, to adjust for the new style.

CREATING A NEW STYLE

Discussion You can create a new style using the Style dialog box. When creating a new style, Excel opens the Format Cells dialog box. You can select any of the cell format attributes. For example, you can create a style for the title rows in a worksheet using numeric formatting, fonts, patterns, borders, and protection attributes. When you close the Style dialog box, the active cell is formatted with the newly created style.



You can deselect any group of attributes under Style includes in the Style dialog box.

Step-by-Step Create a new style. If necessary, display the Sheet3 worksheet.

Steps Practice Data 1. Select the cell to which you want to

apply a newly created style. The active cell moves accordingly.

Click cell A9


Click Format

3. Select the Style command. The Style dialog box opens with the text in the Style name text box selected.

Click Style...

4. Type a name for the style in the Style name text box. The name appears in the Style name text box.

Type Total Row

5. Select Modify. The Format Cells dialog box opens.

Click Modify...

6. Select the Number tab. The Number page appears.

Click the Number tab, if necessary

7. Select the desired number format. The number format is selected.

Click Currency

8. Select additional options, as desired. The desired options are selected. Click Decimal places to 2, if necessary

9. Select the Alignment tab. The Alignment page appears.

Click the Alignment tab

10. Select the desired alignment list under Text alignment. A list of alignments appears.

Click Horizontal

11. Select the desired alignment option. The desired alignment option is selected.

Click Center



Steps Practice Data 12. Select the Font tab.

The Font page appears. Click the Font tab

13. Select the desired font options. The desired font options are selected.

Click Italic in the Font style list box

14. Select the Border tab. The Border page appears.

Click the Border tab

15. Select the desired border options. The desired border options are selected.

Click under Border (third down from the top)

16. Select the Patterns tab. The Patterns page appears.

Click the Patterns tab

17. Select the desired pattern options. The desired pattern options are selected.

Click the light yellow color (upper palette, fifth row, third column)

18. Select the Protection tab. The Protection page appears.

Click the Protection tab

19. Select the desired protection options. The desired protection options are selected.

Click Locked, if necessary

20. Select OK. The Format Cells dialog box closes and the attributes of the newly created style appear under Style Includes (By Example) in the Style dialog box.

Click OK

21. Select OK. The Style dialog box closes and the selected cell is formatted with the newly created style.

Click OK

Practice the Concept: Apply the Total Row style to the range B9:H9. Widen columns B, C, and D, as necessary, to adjust for the new style.

MODIFYING A STYLE

Discussion You can modify an existing style. After you modify a style, all the cells formatted with that style are updated to reflect the modifications. For example, if a style is



applied to all the column headings in a worksheet, you can modify the style in one cell and all the column headings will update.

Step-by-Step Modify a style. If necessary, display the Sheet3 worksheet.

Steps Practice Data 1. Select the cell that contains the style

you want to modify. The active cell moves accordingly.

Click cell A3


Click Format


Click Style...

4. Select Modify. The Format Cells dialog box opens.

Click Modify...

5. Select the tab that contains the attributes you want to change. The selected page appears.

Click the Border tab

6. Change options as desired. The desired options are changed. Click under Border

(third down from the top)

7. Select OK. The Format Cells dialog box closes and the new attributes appear under Style includes.

Click OK

8. Select OK. The Style dialog box closes and all cells formatted with the modified style are updated.

Click OK

Close AUTOFMT.XLS.



MERGING STYLES

Discussion Styles are only saved with the current workbook. You can, however, merge styles from another workbook into your present workbook. This option saves time when you need to recreate the same styles over and over. For example, if you create a sales worksheet for each month, you can merge the styles created in one monthly worksheet into another. In order to merge styles, both workbooks must be open. If the workbook into which you are merging contains styles with the same names as styles in the workbook you are merging from, you can choose to replace the existing styles in the workbook into which you are merging.

The Merge Styles dialog box

You cannot selectively replace styles. You must replace all the styles with the same name.



Step-by-Step From the Student Data directory, open AUTOSTYL.XLS and AUTOFMT2.XLS. Merge styles from another workbook. Display the Autostyl workbook.

Steps Practice Data 1. Select the Format menu.

The Format menu appears. Click Format


Click Style...

3. Select Merge. The Merge Styles dialog box opens.

Click Merge...

4. Select the workbook from which you want to merge styles. The workbook is selected.

Click Autofmt2.xls

5. Select OK. The Merge Styles dialog box closes and, if applicable, a message box opens, asking if you want to merge styles with the same name.

Click OK

6. If the workbooks contain styles with the same name, select Yes to replace the styles in the current workbook with the merged styles or No to merge styles without replacing. The message box closes, and the styles in the current workbook are replaced with the merged styles or the styles are merged without replacing.

Click Yes, if necessary

7. Select OK. The Style dialog box closes and the styles are merged.

Click OK

Row 9 was previously formatted with the Total Row style. Notice that the formatting in row 9 is replaced with the new Total Row style. Widen columns B, C, and D, as necessary, to adjust for the new style.

Practice the Concept: Select the range A3:H3 and cell E1 and apply the Title style. Close AUTOSTYL.XLS and AUTOFMT2.XLS.



EXERCISE

USING AUTOMATIC FORMATTING AND STYLES

Task Use the automatic formatting features and styles to format a worksheet.

1. Open Region10.

2. Format the range A1:I7 with the Accounting 1 AutoFormat.

3. Reformat the range. Select the List 1 AutoFormat and apply it without the Font and Border options.

4. Add the following data for row 8. A8: Mexico B8: 14545 C8: 18404 D8: 21300 E8: 24500 G8 22300

5. Display Sheet2.

6. Use the formats in cell A1 to create a style by example. Name the style Heading.

7. Apply the Heading style to the ranges A2:A3 and A7:G7.

8. Create a style named Totals for the totals in row 12. The style should include a currency format with no decimals; a bold, 11 pt. font; a border on the top of the cell (first option from the top under Border); and a light yellow background color (upper palette, third column, fifth row). (Hint: Make sure that you select a cell to which you want to apply the newly created style.)

9. Apply the Totals format to the range A12:G12.

10. Modify the Heading style to make the font bold and italic and the font color red (upper palette, first column, third row). (Hint: Make sure that you select a cell that contains the style you want to modify.)



11. Open Ceq1.

12. Merge the styles from Region10 to Ceq1. Select Yes to merge styles that have the same names.

13. Apply the Totals style to the range A12:G12.

14. Close both workbooks without saving them.

LESSON 2 - WORKING WITH DATA SERIES


Use the Series command Create a linear series Create a date series Use a stop value

Lesson 2 - Working with Data Series Microsoft Excel Level 2


USING THE SERIES COMMAND

Discussion If you need to enter a sequence of values on a worksheet, you can use the Series command. Although you can create simple series using the fill handle on a cell, the Series command opens a dialog box with additional options. In the Series dialog box, you can choose one of the following series types: Linear, Growth, Date, or AutoFill. You can also set a step value (the amount by which the series increases in each cell) and a stop value (the value at which the series should end). You can use the Series command in two ways. If you select a range before you open the Series dialog box, the Series in option automatically reflects the direction of the selection and you do not need to enter a stop value. The series ends when the range is filled. If you do not select a range, the Series in option defaults to Rows and you must indicate the direction for the fill as well as enter a stop value. The following table displays how Excel completes a data series, based on the initial entry or entries.

Type of Series Initial Entry or

Entries Extended Series

Linear 1

1,4

1000, 975

2, 3, 4

7, 10, 13

950, 925, 900

Growth 1, 2

1, 4

1000

4, 8, 16 (The step value is 2)

16, 64, 256 (The step value is 4)

1100, 1210, 1331 (The step value is 1.1)

Date 1 Jan

1/1/99

1/1/99, 2/1/99

2 Jan, 3 Jan, 4 Jan

1/2/99, 1/3/99, 1/4/99

3/1/99, 4/1/99, 5/1/99

Microsoft Excel Level 2 Lesson 2 - Working with Data Series


Type of Series Initial Entry or Entries

Extended Series

AutoFill Qtr 1

1st Area

Sample 1, of the Lot

Qtr 2, Qtr 3, Qtr 4

2nd Area, 3rd Area, 4th Area

Sample 2, of the Lot Sample 3, of the Lot Sample 4, of the Lot

In the last example for the AutoFill series type, the comma between Sample 1 and of the Lot indicates that the items are located in adjacent cells.

CREATING A LINEAR SERIES

Discussion When you create a linear series, the values are increased or decreased by a constant value. For example, if you enter the number 1 in the first cell of a range, Excel enters 2, 3, 4, etc., in the remaining cells of the range. If you enter the number 10 in the first cell and use a step value of 10, Excel fills the subsequent cells with 20, 30, 40, etc.

Creating a linear series



Step-by-Step From the Student Data directory, open SALES71.XLS. Create a linear series using the Series dialog box.

Steps Practice Data 1. Select the cell for the first value in the

linear series. The cell is selected.

Click cell A4, if necessary

2. Type the first value in the series. The text appears in the cell and on the formula bar.

Type 1

3. Press [Enter]. The text is entered in the cell.

Press [Enter]

4. Select the range you want to fill. The range is selected.

Drag A4:A15

5. Select the Edit menu. The Edit menu appears.

Click Edit

6. Point to the Fill command. The Fill submenu appears.

Point to Fill

7. Select the Series command. The Series dialog box opens.

Click Series...

8. Under Type, select the Linear option.The Linear option is selected and the text in the Step value text box is selected.

Click Linear, if necessary

9. Enter the desired step value in the Step value text box. The text appears in the Step value text box.

Type 1, if necessary

10. Select OK. The Series dialog box closes and the range is filled with the linear series.

Click OK

Click anywhere in the worksheet to deselect the range.



CREATING A DATE SERIES

Discussion When you create a date series, the values are incremented by date units. For example, if you enter Jan-99 in the first cell of a range, Excel fills the remaining cells of the range with Feb-99, Mar-99, etc. In the Series dialog box, you can choose from four date units: Day, Weekday, Month, or Year. In addition, you are not restricted to entering dates in a date format. For example, if you enter Jan 1 as the starting value, Excel recognizes it as a date and creates the series accordingly.

Filling cells with a date series



Step-by-Step Create a date series using the Series dialog box.

Steps Practice Data 1. Select the cell for the first date in the

series. The cell is selected.

Click cell B4

2. Type the first date in the series. The text appears in the cell and on the formula bar.

Type 1/31/99


Press [Enter]

4. Select the range you want to fill. The range is selected.

Drag B4:B15


Click Edit

6. Point to the Fill command. The Fill submenu appears.

Point to Fill


Click Series...

8. Under Date unit, select the desired date unit. The desired date unit option is selected.

Click Month

9. Select OK. The Series dialog box closes and the range is filled with the date series.

Click OK

Click anywhere in the worksheet to deselect the range.

USING A STOP VALUE

Discussion If you know the value at which you want a series to stop, you do not have to first select a range on the worksheet. For example, you may want a series of cells to display the dates 1980 to 2010. The step value determines the value by which each



value in the series is incremented and the stop value provides the means for entering the ending value in the series. Excel ends the series when the increments reach the stop value.

A date series created with a stop value

Step-by-Step Use a stop value to create a series.

Steps Practice Data 1. Select the cell for the first value in the

series. The cell is selected.

Click cell E4

2. Type the first value in the series. The text appears in the cell and on the formula bar.

Type 1980


Press [Enter]

4. Select the cell that starts the series. The cell is selected.

Click cell E4


Click Edit



Steps Practice Data 6. Point to the Fill command.

The Fill submenu appears. Point to Fill


Click Series...

8. Under Series in, select the Rows or Columns option as desired. The desired option is selected.

Click Columns

9. Select the Step value text box. The text in the Step value text box is selected.

Double-click the Step value text box

10. Type the desired step value. The text appears in the Step value text box.

Type 5

11. Select the Stop value text box. The insertion point appears in the Stop value text box.

Press [Tab]

12. Type the desired stop value. The text appears in the Stop value text box.

Type 2015

13. Select OK. The Series dialog box closes and the range is filled with the series until Excel reaches the stop value.

Click OK

Click anywhere in the worksheet to deselect the range. Close SALES71.XLS.



EXERCISE

WORKING WITH DATA SERIES

Task Use data series to create a worksheet.

1. Open Offer.

2. Enter the date 11/1/99 into cell A5.

3. Create a date series with an increment of seven days and a stop value of 11/29/99. Create the series down column A in the range A5:A9.

4. Type the number 1 in cell A13, select the range A13:A18, and create a linear series.

5. Type Nov 99 in cell B13, select the range B13:B18, and create a date series that uses monthly units.

6. Close the workbook without saving it.

LESSON 3 - USING CONDITIONAL AND CUSTOM FORMATS


Apply conditional formats Change a conditional format Add a conditional format Delete a conditional format Create a custom format

Lesson 4 - Using Large Worksheets Microsoft Excel Level 2


APPLYING CONDITIONAL FORMATS

Discussion You can use the Conditional Formatting feature to emphasize data that meets certain conditions in cells or formulas. You can set up the Conditional Formatting feature so that all sales that are greater than or equal to a certain value will appear in a different color. You can change the formatting option that is used to emphasize data which meets a condition. The Conditional Formatting feature has two options, the Cell Value Is and Formula Is. The Cell Value Is option allows you to compare the values of selected cells to conditions, such as greater than or less than. The Formula Is option allows you to compare the results of a logical formula to a selected range of cells, where the formula produces either a true or false result indicated by the selected formatting.

Applying conditional formats

You can use the Format Painter button to copy conditional formatting from one range to another.

Formats applied with the AutoFormat feature do not override conditional formatting.

Step-by-Step From the Student Data directory, open CONDFMT.XLS. Apply conditional formats using the Conditional Formatting feature. If necessary, display the Qtr1 worksheet.

Microsoft Excel Level 2 Lesson 4 - Using Large Worksheets


Steps Practice Data 1. Drag to select the range to which you

want to apply conditional formatting. The range is selected.

Drag B3:D6


Click Format

3. Select the Conditional Formatting command. The Conditional Formatting dialog box opens.

Click Conditional Formatting...

4. Select the first list under Condition 1 to choose the criteria on which you want to apply the conditional formatting. A list containing Cell Value Is and Formula Is appears.

Click the first

5. Select the desired option. The desired option is selected.

Click Cell Value Is

6. Select the second list under Condition 1 to choose the desired condition. A list of conditions appears.

Click the second

7. Select the desired condition. The condition is selected.

Click greater than

8. Select the text box under Condition 1.The insertion point appears in the text box.

Click in the text box

9. Type the value you want to use as the formatting criteria. The value appears in the text box.

Type 2000

10. Select Format. The Format Cells dialog box opens.

Click Format...

11. Select the Color list. A color palette appears.

Click Color

12. Select the color you want to apply to the values that meet the specified condition. The color is selected.

Click red (third row, first color)

13. Select OK. The Format Cells dialog box closes and the format appears in the preview under Condition 1.

Click OK




The Conditional Formatting dialog box closes and the conditional formatting is applied to those cells that meet the specified condition.

Click OK

Click any cell to deselect the range. Notice that the data in cells with values greater than 2000 appears in red. Change the number in cell B6 to 2105. The number appears in red because it is greater than 2000.

CHANGING A CONDITIONAL FORMAT

Discussion You can change a conditional format. For example, you may have a worksheet that displays cells with values less than $4000 in red. You can change the condition to recognize a different value.

Step-by-Step Change the conditional format of a range. If necessary, display the Qtr1 worksheet.

Steps Practice Data 1. Drag to select the range containing the

conditional format you want to change. The range is selected.

Drag B3:D6


Click Format





Steps Practice Data 4. Select the second list under Condition

1 to choose the desired condition. A list of conditions appears.

Click the second


Click less than

6. Double-click the text box under Condition 1. The text box is selected.

Double-click in the text box

7. Type the value you want to use as the new formatting criteria. The value appears in the text box.

Type 1800

8. Select OK. The Conditional Formatting dialog box closes and the changed conditional format is applied to those cells that meet the specified condition.

Click OK

Click any cell to deselect the range. Notice that the cells with values less than 1800 display in red.

ADDING A CONDITIONAL FORMAT

Discussion You can have more than one conditional format for a range of cells. You can have a worksheet that displays cells with values less than $4000 in red and cells with values greater than $8000 in blue.



Step-by-Step Add a second conditional format to a range. If necessary, display the Qtr1 worksheet.


conditional format to which you want to add another condition. The range is selected.

Drag B3:D6


Click Format



4. Select Add. The Conditional Formatting dialog box expands to display a section for Condition 2.

Click Add

5. Select the second list under Condition 2 to choose the desired condition. A list of conditions appears.

Click the second under Condition 2


Click greater than

7. Select the text box under Condition 2.The insertion point appears in the text box.

Click the text box under Condition 2

8. Type the value you want to use as the new formatting criteria. The value appears in the text box.

Type 2000

9. Select Format under Condition 2. The Format Cells dialog box opens.

Click Format... under Condition 2

10. Select the Color list. A color palette appears.

Click Color



Steps Practice Data 11. Select the color you want to apply to

the values that meet the specified condition. The color is selected.

Click blue (second row, sixth color)

12. Select OK. The Format Cells dialog box closes and the format appears in the preview under Condition 2.

Click OK

13. Select OK. The Conditional Formatting dialog box closes and the Condition 2 formatting is applied to those cells that meet the specified condition.

Click OK

Click any cell to deselect the range. Notice that the cells with values greater than 2000 display in blue and cells with values less than 1800 display in red.

DELETING A CONDITIONAL FORMAT

Discussion You can delete one or more conditions from a conditionally formatted range. For example, you may have a worksheet that displays cells with values less than $4000 in red and cells with values greater than $8000 in blue. You may want to delete the condition which displays values in blue so that only one condition is emphasized.

Step-by-Step Delete the conditional format from a range. If necessary, display the Qtr1 worksheet.


conditional format you want to delete. The range is selected.

Drag B3:D6


Click Format



Steps Practice Data 3. Select the Conditional Formatting

command. The Conditional Formatting dialog box opens.


4. Select Delete. The Delete Conditional Format dialog box opens.

Click Delete...

5. Select the check box of the condition you want to delete. The appropriate check box is selected.

Click Condition 2

6. Select OK. The Delete Conditional Format dialog box closes and the Condition 2 section is removed from the Conditional Formatting dialog box.

Click OK

7. Select OK. The Conditional Formatting dialog box closes and the Condition 2 formatting is removed from the cells in the range.

Click OK

Click any cell to deselect the range. Notice that the cells with values greater than 2000 no longer appear in blue.

CREATING A CUSTOM FORMAT

Discussion If you want to format a number using a format that does not exist in Excel, you can create a custom number format. Custom number formats can contain text, hyphens, and symbols. For example, in a sales worksheet, you can create a custom number format that will display price per pound with the text per lb appearing after the numbers. When you create a custom number format, you can use an existing format as the basis for the new format. Custom number formats use the following conventions:

1. A number sign (#) indicates a placeholder and is used when you need

to indicate at what position to place a comma.



2. A zero (0) is used when a number should always be displayed. For example, if there is no number for the position, Excel will display a 0, such as in 0.5.

3. The format can contain four sections separated by semi-colons. The first section controls the appearance of positive numbers. The second section controls the appearance of negative numbers. The third section controls the appearance of zero values. The fourth section controls the appearance of text. If only two sections exist, the first controls positive numbers and zero values and the second controls negative numbers.

4. Each section can be displayed in a color. You place the color name in brackets at the beginning of the section, i.e., [RED].

5. Text in number formats must be surrounded by quotes ( ).

The following number format [CYAN] #,##0.0 "per lb";[RED](#,##0.0)"per lb";"N/A" displays positive numbers in a cyan color with 1 decimal place followed by the text per lb, negative numbers in red surrounded by parentheses with 1 decimal place followed by the text per lb, and zeros as the text N/A. After you create a custom number format, it is available in the Type list box when Custom is selected in the Category list box on the Number page in the Format Cells dialog box.

The Format Cells dialog box



The Format Cells dialog box also allows you to format a range of cells with a specific category, such as Date, Fraction, or Scientific.

Step-by-Step Create a custom format. Display the Bonus worksheet.

Steps Practice Data 1. Select the cell to which you want to

apply a custom format. The active cell moves accordingly.

Click cell C4


Click Format

3. Select the Cells command. The Format Cells dialog box opens.

Click Cells...

4. Select the Number tab. The Number page appears.

Click the Number tab, if necessary

5. Select Custom in the Category list box. A list of custom formats appears in the Type list box.

Click Custom

6. Select the format that most closely resembles the format you want to create in the Type list box. The format is selected and appears in the Type text box.

Scroll as necessary and click $#,##0_);($#,##0) (tenth format from the top)

7. Place the insertion point in the desired location in the Type text box. The insertion point appears in the desired location in the Type text box.

Click at the end of the format in the Type text box

8. Make the desired changes to the format. The changes appear in the Type text box.

Type ;"N/A"



Steps Practice Data 9. When you have finished making

changes, select OK. The Format Cells dialog box closes and the custom format is applied to the selected cell.

Click OK

Practice the Concept: Apply the newly created custom number format to the range C5:C7. The custom number format appears at the bottom of the Type list box when Custom is selected in the Category list box. Close CONDFMT.XLS.



EXERCISE

USING CONDITIONAL AND CUSTOM FORMATS

Task Use conditional and custom formatting features to format a worksheet.

1. Open Region25.

2. Apply conditional formatting to the range B5:E9 so that the cells with values greater than $45000 display in red (third row, first color).

3. Change the conditional formatting of the range B5:E9 so that the cells with values less than $45000 display in red.

4. Add a conditional format to the range B5:E9 so that, in addition to displaying cells with values less than $45000 in red, you also display cells with values greater than $50000 in green (second row, fourth color).

5. Delete the conditional formatting from the range B5:E9 that displays cells with values less that $45000 in red.

6. Create a custom number format for cell H5 that displays positive numbers in blue with the currency format and no decimal places; and negative numbers in red with parentheses, the currency format, and no decimal places. To save time, you can use an existing format similar to the one you are creating. (Hint: Make sure that you select a cell to which you want to apply a custom number format.)

7. Apply the custom format to the range H6:H9.




LESSON 4 - USING LARGE WORKSHEETS


Increase the magnification Decrease the magnification Change the magnification of a range Switch to Full Screen view Split the window Remove split windows Freeze the panes Unfreeze the panes



INCREASING THE MAGNIFICATION

Discussion You can increase the magnification of cells. Magnifying the display is similar to using a magnifying glass; it makes the cells and their contents appear larger. This option is useful when you want to view a small portion of the worksheet in greater detail. For example, with a worksheet containing annual sales, you may want to view only sales for the current quarter. The default magnification is 100%. The larger the percentage, the larger the cells appear. For example, with a magnification of 200%, the cells appear twice as large as with a magnification of 100%.

A worksheet zoomed to 200%

Changing the magnification affects the display only. It does not affect printing.

You can also use the Zoom list on the Standard toolbar to change the magnification.



Step-by-Step From the Student Data directory, open COMM09.XLS. Increase the magnification of a worksheet.

Steps Practice Data 1. Select the View menu.

The View menu appears. Click View

2. Select the Zoom command. The Zoom dialog box opens.

Click Zoom...

3. Under Magnification, select the desired option. The desired Magnification option is selected.

Click 200%

4. Select OK. The Zoom dialog box closes and the magnification of the worksheet increases accordingly.

Click OK

Practice the Concept: Use the Zoom list on the Standard toolbar to change the magnification back to 100%.

DECREASING THE MAGNIFICATION

Discussion You can decrease the magnification of cells. Decreasing the magnification makes the cells appear smaller and allows more cells to appear in the window. This option is useful when you want to view a larger portion of the worksheet. For example, with a worksheet containing annual sales, you may want to view the sales for the entire year, or you may want to review the formatting or layout of the entire worksheet. The default magnification is 100%. The smaller the magnification, the smaller the cells appear. For example, with a magnification of 50%, the cells appear half as large as with a magnification of 100%.



A worksheet zoomed to 75%

Changing the magnification affects the display only. It does not affect printing.

You can also use the Zoom list on the Standard toolbar to change the magnification.

Step-by-Step Decrease the magnification of a worksheet.




Click Zoom...

3. Under Magnification, select the desired option. The desired Magnification option is selected.

Click 75%




The Zoom dialog box closes and the magnification of the worksheet decreases accordingly.

Click OK

Practice the Concept: Use the Zoom list on the Standard toolbar to change the magnification back to 100%.

CHANGING THE MAGNIFICATION OF A RANGE

Discussion You can magnify a selected range so that its size adjusts as needed to fit the worksheet window. It is useful to zoom selections when you need to view all the cells in a range at the same time. For example, with a worksheet containing annual sales figures, you may want to zoom in on the numbers that make up the annual sales.

A range zoomed to fit the screen



Step-by-Step Change the magnification of a range to fit the screen.

Steps Practice Data 1. Drag to select the range for which you

want to change the magnification. The range is selected.

Drag A1:E7

2. Select the View menu. The View menu appears.

Click View


Click Zoom...

4. Select the Fit Selection option. The Fit Selection option is selected.

Click Fit Selection

5. Select OK. The Zoom dialog box closes and the magnification of the range changes to fit the screen.

Click OK

Practice the Concept: Use the Zoom list on the Standard toolbar to change the magnification back to 100%. Deselect the range.

SWITCHING TO FULL SCREEN VIEW

Discussion You can view a worksheet without viewing screen elements such as toolbars and title bars using Full Screen view. This option allows you to display a large portion of a large worksheet. For example, you can use Full Screen view to display as much of an annual worksheet as possible without changing the magnification.



Full Screen view

When viewing the worksheet in Full Screen view, a Full Screen toolbar appears. If you close the Full Screen toolbar, you must select the Full Screen command from the View menu to return to the Normal view.

Step-by-Step Switch to Full Screen view to view more of a worksheet.



2. Select the Full Screen command. The worksheet appears in Full Screen view.

Click Full Screen

3. To return to Normal view, click the Close Full Screen button on the Full Screen toolbar. The worksheet appears in Normal view.

Click Close Full Screen



SPLITTING THE WINDOW

Discussion If you need to view two or more areas of a large worksheet at the same time, you can split the workbook window into panes. Panes display different areas of the same worksheet. You can use panes to view different areas of the workbook that do not normally appear on the screen at the same time. For example, in a large worksheet containing sales for many regions, you can view the totals of each region in a separate pane. You can split the workbook window into two or four panes. With two panes, you can have either horizontal or vertical panes. With four panes, the display is divided into four sections. To split the window, you use the horizontal and vertical split boxes. The horizontal split box is located at the top of the vertical scroll bar. The vertical split box is located at the right end of the horizontal scroll bar. When you drag the split boxes, a line appears in the worksheet indicating where the split is located. You can drag the line to readjust the size of the panes. When the window is split into panes, you can use the scroll bars to view different areas of the same worksheet. Horizontal panes have separate vertical scroll bars and share the same horizontal scroll bar. As a result, horizontal panes can scroll up and down independently but they scroll left and right simultaneously. Vertical panes have separate horizontal scroll bars and share the same vertical scroll bar. As a result, vertical panes can scroll right and left independently but they scroll up and down simultaneously. When you split the window into four panes, the vertical panes share a vertical scroll bar and the horizontal panes share a horizontal scroll bar.



A window split into four panes

Double-clicking the horizontal split bar splits the window above the active cell. Double-clicking the vertical split bar splits the window to the left of the active cell.

Step-by-Step Split the window into four panes to view different areas of the worksheet.

Steps Practice Data 1. To split the workbook window into

horizontal panes, drag the horizontal split box to the desired row position. The worksheet window is split horizontally.

Drag the horizontal split box to between rows 8 and 9

2. To view different areas of the worksheet in the horizontal panes, click either vertical scroll bar. The horizontal panes display different areas of the worksheet.

Click in the lower pane until the third quarter data appears



Steps Practice Data 3. To split the workbook window into

vertical panes, drag the vertical split box to the desired column position. The worksheet window is split vertically.

Drag the vertical split box to between columns D and E

4. To view different areas of the worksheet in the vertical panes, click either horizontal scroll bar. The vertical panes display different areas of the worksheet.

Click in the right pane three times

REMOVING SPLIT WINDOWS

Discussion You can remove the panes from a workbook window by double-clicking the horizontal or vertical split bar. You can remove the panes when you no longer need to view distant areas of the worksheet. For example, after you have viewed the regional totals in a large sales worksheet, you may want to view only the figures for one region.

Step-by-Step Remove the panes from a workbook window.

Steps Practice Data 1. To remove horizontal panes, double-

click the horizontal split bar. The horizontal panes are removed.

Double-click the horizontal split bar

2. To remove vertical panes, double-click the vertical split bar. The vertical panes are removed.

Double-click the vertical split bar



FREEZING THE PANES

Discussion Occasionally a worksheet is so large you cannot view the column or row headings and all the data at the same time. When this happens, it is difficult to view the headings for the data in the worksheet. For example, in a worksheet containing sales figures for several hundred sales representatives, you cannot view the column headings and the representatives at the bottom of the list at the same time. To solve this problem, you can freeze worksheet titles in panes. Freezing panes prevents the row and column headings from scrolling out of view as you navigate the worksheet. Frozen panes are indicated by a line below a row and a line to the right of a column.

Frozen row and column headings



Step-by-Step Freeze the panes in a worksheet in order to view both the column and row headings at the same time.

Steps Practice Data 1. To freeze both row and column

headings, place the active cell in the cell directly below the column headings you want to freeze and to the right of the row headings you want to freeze. The cell is selected.

Click cell B3

2. Select the Window menu. The Window menu appears.

Click Window

3. Select the Freeze Panes command. The rows above and the columns to the left of the active cell are frozen.

Click Freeze Panes

Move to cell I24. Notice that rows 1 and 2 and column A do not scroll.

UNFREEZING THE PANES

Discussion After you have frozen headings in a large worksheet, you can unfreeze the panes. Unfreezing removes the panes so that title rows or columns are no longer frozen on the screen.



Step-by-Step Unfreeze the panes in a worksheet so that the row and column headings are no longer frozen.

Steps Practice Data 1. Select the Window menu.

The Window menu appears. Click Window

2. Select the Unfreeze Panes command. The headings are no longer frozen.

Click Unfreeze Panes

Move to cell I24. Notice that the row and column headings are no longer frozen. Close COMM09.XLS.



EXERCISE

USING LARGE WORKSHEETS

Task Use features for working with a large worksheet.

1. Open Region11.

2. Zoom the worksheet to 75% so that you can view more of it on the screen.

3. Zoom the range A1:E11 to fit the selection.

4. Return the view to 100%.

5. Display the document in Full Screen view.

6. Switch to Normal view.

7. Split the screen into two vertical panes so that you can view both the Total Sales and the Percent of Total columns.

8. Remove the panes.

9. Freeze the row headings in column A and the column headings in rows 1 through 4.

10. Scroll to display the Avg. Sales and Percent of Total columns.



11. Unfreeze the panes.


LESSON 5 - WORKING WITH MULTIPLE WORKSHEETS


Use multiple worksheets Navigate between worksheets Select worksheets Rename worksheets Select multiple worksheets Insert worksheets Delete worksheets Print selected worksheets

Lesson 5 - Working with Multiple Worksheets Microsoft Excel Level 2


USING MULTIPLE WORKSHEETS

Discussion Workbook files can contain multiple worksheets. Using multiple worksheets is a convenient way to manage related data in the same workbook. For example, you can enter sales data for individual months, quarters, or regions in separate worksheets. You also can create summary worksheets that add numbers from each of the worksheets in a workbook. In addition, you can group worksheets to apply consistent formatting, as well as to print all the worksheets as a group. By default, a new workbook contains three worksheets. The name of each worksheet appears on a tab above the status bar. The default name is Sheet followed by a number. You can change the name to indicate the type of information on the worksheet. For example, if your worksheet contained your weekly expenses, you could rename the default worksheet Expenses. A workbook can contain up to 255 worksheets. Worksheets can be moved and copied within the current workbook.

A workbook with multiple worksheets

To change the number of default worksheets, select the General page in the Options dialog box.

Microsoft Excel Level 2 Lesson 5 - Working with Multiple Worksheets


NAVIGATING BETWEEN WORKSHEETS

Discussion The active worksheet is the worksheet that is currently displayed. You can display a worksheet by clicking its tab; however, by default, only six worksheet tabs appear in the workbook window. If you have more than six worksheets, you cannot see all the worksheet tabs at one time. For example, in a workbook that contains worksheets for every month of the year, the tabs for the last few months of the year would be hidden, depending on how the months are named. If the worksheet tab you want to view is not visible, you can use the tab scrolling buttons to display hidden tabs.

Button Function

Displays the next worksheet tab to the right.

Displays the previous worksheet tab to the left.

Displays the last worksheet tab in the workbook.

Displays the first worksheet tab in the workbook.

You can drag the tab split box located to the left of the horizontal scroll bar as desired to display more or fewer tabs. You can double-click the tab split box to return the tab display to the default number of tabs.



Step-by-Step From the Student Data directory, open MONTH1.XLS. Navigate between worksheets.

Steps Practice Data 1. To view the next tab to the right, click

the Next Tab button. The next worksheet tab to the right appears.

Click

2. To view the next tab to the left, click the Previous Tab button. The next worksheet tab to the left appears.

Click

3. To view the last worksheet tab, click the Last Tab button. The last worksheet tab appears.

Click

4. To view the first worksheet tab, click the First Tab button. The first worksheet tab appears.

Click

5. To view the contents of a worksheet, click the desired worksheet tab. The worksheet appears.

Click the February tab

SELECTING WORKSHEETS

Discussion You can select a worksheet at any time by displaying the sheet list. The sheet list contains the name of all the worksheets in a workbook. It is a convenient tool when using a workbook with a large number of worksheets. For example, in an annual workbook containing monthly worksheets, you can use the sheet list to quickly select and view the third month in each quarter, one at a time.



The sheet list

Step-by-Step Select a worksheet using the sheet list.

Steps Practice Data 1. Click the right mouse button on any

tab scrolling button. The sheet list appears.

Click the right mouse button on

2. Select the desired worksheet. The worksheet appears.

Click Sheet11

RENAMING WORKSHEETS

Discussion You can replace the default worksheet names with descriptive names. For example, a worksheet containing January sales figures can be named January. Worksheet names can be up to 31 characters long, but cannot include colons (:), slash marks (/),



backslashes (\), question marks (?), or asterisks (*). In addition, the name cannot be enclosed in square brackets ([]). Each worksheet name in a workbook must be unique.

Step-by-Step Rename a worksheet. If necessary, go to Sheet 11.

Steps Practice Data 1. Double-click the worksheet tab you

want to rename. The worksheet name is selected.

Double-click the Sheet11 tab

2. Type the desired worksheet name. The worksheet name appears on the tab.

Type November

3. Press [Enter]. The worksheet name changes.

Press [Enter]

Practice the Concept: Rename Sheet 12 to December.

SELECTING MULTIPLE WORKSHEETS

Discussion Before you can apply a command to a worksheet, you must select the worksheet. If you select multiple worksheets, you can apply a command to all the worksheets at the same time. For example, you can copy, move, delete and print all the worksheets in a selected group at the same time. In addition, when you insert new sheets, the number of sheets you select determines the number of sheets inserted.

To deselect a selected worksheet without deselecting the group, hold the [Ctrl] key and click the worksheet tab, and release the [Ctrl] key.

When multiple worksheets are selected, the text [Group] appears next to the title of the workbook.

To deselect worksheet tabs, you click an unselected worksheet tab.



Step-by-Step Select multiple worksheets.

Steps Practice Data 1. Click the tab of the first worksheet you

want to select. The worksheet appears and the tab is selected.

Scroll as necessary and click the January tab

2. Hold [Shift] and click the tab of the last adjacent worksheet you want to select. The worksheet tabs are selected.

Hold [Shift] and click the March tab

3. To add non-adjacent worksheets to the group, hold [Ctrl] and click the tab of the worksheet you want to add. The worksheet tab is selected.

Hold [Ctrl] and click the June tab

Deselect the worksheet tabs by clicking the unselected April tab.

INSERTING WORKSHEETS

Discussion You can insert new worksheets into a workbook. For example, in a workbook containing worksheets for each month of the year, you can add worksheets for each quarter of the year. New worksheets are inserted to the left of the active worksheet. Excel gives new worksheets a default worksheet name, which you can change, if desired.



Inserting worksheets

If you select multiple adjacent worksheets, multiple worksheets are inserted. You cannot insert non-adjacent worksheets.

Step-by-Step Insert a worksheet before another worksheet.

Steps Practice Data 1. Select the worksheet to the left of

which you want to insert a new worksheet. The worksheet is selected.

Click the April tab

2. Select the Insert menu. The Insert menu appears.

Click Insert

3. Select the Worksheet command. The inserted worksheet appears to the left of the active worksheet.

Click Worksheet

Rename the new worksheet Qtr 1.



DELETING WORKSHEETS

Discussion You can delete unwanted worksheets. For example, you can delete a worksheet used for temporary calculations. When you delete a worksheet, the entire worksheet and the data it holds are permanently removed from the workbook.

Deleting worksheets

If you select multiple worksheets, multiple worksheets are deleted.

Step-by-Step Delete a worksheet. Scroll to display the last worksheet in the workbook.

Steps Practice Data 1. Click the right mouse button on the tab

of the worksheet you want to delete. A shortcut menu appears.

Click the right mouse button on the Annual tab

2. Select the Delete command. A Microsoft Excel message box opens.

Click Delete

3. Select OK. The Microsoft Excel message box closes and the worksheet is deleted.

Click OK



PRINTING SELECTED WORKSHEETS

Discussion You can print some or all the worksheets in a workbook. For example, in an annual workbook containing monthly worksheets, you may want to print only the worksheets for the most recent months. When printing one or more worksheets instead of the entire workbook, you must select the worksheets you want to print prior to opening the Print dialog box.

Printing selected worksheets

You can print and preview the entire workbook by selecting the Entire Workbook option in the Print dialog box.

After selecting the desired worksheets, you can preview how they will print by selecting the Print Preview button on the Standard toolbar.



Step-by-Step Print selected worksheets.

Steps Practice Data 1. Select the first worksheet you want to

print. The worksheet is selected.

Scroll as necessary and click the January tab

2. Hold [Shift] and click the tab of the last adjacent worksheet you want to print. The worksheets are selected.

Hold [Shift] and click the March tab

3. Select the File menu. The File menu appears.

Click File

4. Select the Print command. The Print dialog box opens.

Click Print...

5. Select the Active sheet(s) option, if necessary. The Active sheet(s) option is selected.

Click Active sheet(s), if necessary

6. Select OK. The Print dialog box closes and Excel prints the selected worksheets.

Click OK

Practice the Concept: Select the April, May, and June worksheets and use the Print Preview button to view the printouts. Close the Preview window. Close MONTH1.XLS.



EXERCISE

WORKING WITH MULTIPLE WORKSHEETS

Task Work with multiple worksheets in a workbook.

1. Open Region12.

2. Display the Totals worksheet.

3. Select the Totals and By Week worksheets.

4. Keeping both sheets selected, insert two new worksheets.

5. Rename the first inserted worksheet Northwest.

6. Rename the second inserted worksheet Southwest.

7. Delete the By Week worksheet.

8. Print the Northeast and Southeast worksheets.


LESSON 6 - MANAGING WORKSHEETS


Copy worksheets Move worksheets Use grouped worksheets Move data between worksheets Copy data between worksheets Create 3-D formulas Use functions in worksheets

Lesson 6 - Managing Worksheets Microsoft Excel Level 2


COPYING WORKSHEETS

Discussion You can copy a worksheet and its contents to a new location. This option is useful after you have designed a framework for a worksheet (e.g., monthly column headings, row headings, formatting, and formulas) and you want to use that framework for several similarly structured worksheets. When you copy a worksheet, the new copy is given the name of the original worksheet followed by a sequential number. You can also copy multiple grouped worksheets. After the worksheets have been copied, they are automatically ungrouped.

A copied worksheet

When copying multiple worksheets, you must drag the tab for the first worksheet in the group, which appears in bold type. Otherwise, if you hold the [Ctrl] key and click the tab of another worksheet in the selected group, that worksheet is deselected.

If you cannot view the destination location for the copied worksheet, drag the copy beyond the edge of the displayed worksheet tabs. The tabs scroll to display additional worksheets.

Microsoft Excel Level 2 Lesson 6 - Managing Worksheets


Step-by-Step From the Student Data directory, open MONTH2.XLS. Copy a worksheet.

Steps Practice Data 1. Select the tab of each worksheet you

want to copy. The worksheet tab(s) are selected.

Scroll as necessary and click the Qtr 3 tab

2. Hold [Ctrl] and drag the selected worksheet tab(s) to the desired location. Copies of the worksheet(s) appear in the new location.

Hold [Ctrl] and drag the Qtr 3 tab to the right of the December tab

Rename the copied worksheet Qtr 4.

MOVING WORKSHEETS

Discussion You can move a worksheet to a new location in a workbook and still have it retain the same name and contents. Moving worksheets allows you to rearrange them or to place new worksheets in a desired location in the workbook. For example, in an annual workbook containing monthly worksheets, you may want to reorder the worksheets so that the first, second, and third months in each quarter are adjacent. You can also move multiple grouped worksheets. After multiple grouped worksheets have been moved, they are automatically ungrouped.

Lesson 6 - Managing Worksheets Microsoft Excel Level 2


Step-by-Step Move a worksheet. Display the Annual worksheet tab.

Steps Practice Data 1. Select the tab of each worksheet you

want to move. The worksheet tab(s) are selected.

Scroll as necessary and click the Annual tab

2. Drag the selected worksheet tab(s) to the desired location. The worksheet(s) appear in the new location.

Drag the Annual tab to the left end of the horizontal scroll bar to the right of the Qtr 4 worksheet

USING GROUPED WORKSHEETS

Discussion When multiple worksheets are selected, the worksheets are grouped. If you type, edit, create formulas, or format entries in one of the grouped worksheets, entries in the same cell in all the grouped worksheets change. Grouping is useful when you want to create the same structure and appearance in all the worksheets in a workbook. For example, when creating monthly worksheets in a workbook, you can gro

excel 2000 level 2 vicon

Documents

vicon learning systems

vicon learning systems

copyright trademarks

trademark owner

trademark symbol

new style

automatic formatting

trademarked names