Power Point Lecture to Accompany Skills for Success

Power. Point Lecture to Accompany Skills for Success with Microsoft® Office 2010 Vol. 1 Excel Chapter 3 Manage Multiple Worksheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 1

Introduction • In Skills 1– 10, you will: – Work with Sheet Tabs, Worksheet Insertion, Grouping, Formatting – Work with Dates, Cell Contents & Formats; Move, Copy, Paste and Paste Options – Use Multiple Operators in Formulas, Create Formulas using Multiple Worksheets & Cells – Create Clustered Bar Charts Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 2

Introduction Work with Sheet Tabs, Worksheet Insertion, Grouping, Formatting Work with Dates, Cell Contents & Formats; Move, Copy, Paste and Paste Options Use multiple Operators in Formulas, Create Formulas using Multiple Worksheets & Cells Create Clustered Bar Charts Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 3

Skill 1: Work with Sheet Tabs When a workbook contains more than one worksheet, you can navigate (move) among worksheets by clicking the sheet tabs Sheet 2 tab is active Tab scrolling buttons Buttons to the left of the sheet tabs used to display Excel sheet tabs that are not in view Sheet tab scrolling buttons Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 4

Skill 1: Work with Sheet Tabs You can use up to 31 characters in a sheet tab name. Strive for a short but meaningful name Shortcut menu Rename command Sheet 1 tab is active Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Right-click the Sheet 1 sheet tab, from the shortcut menu, click Rename Skills for Success with Office 2010 Vol. 1 5

Skill 1: Work with Sheet Tabs Park 2 displays Sheet 2 tab name selected Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 6

Skill 1: Work with Sheet Tabs When a worksheet is active, the sheet tab color displays only along the lower edge of the sheet tab. When a worksheet is not active, the entire sheet tab is filled with the selected color Active sheet displays tab color at lower edge of tab Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Sheet tabs named and colored Right-click the sheet tab, and then point to Tab Color. Skills for Success with Office 2010 Vol. 1 7

Skill 2: Enter and Format Dates are stored as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1 Formula bar value is 1/31/2012 Serial number a sequential number Displayed value is 1/31 Aspen Lake sheet active Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. When you type any of the following values into cells, Excel interprets them as dates: 7/4/10, 4 -Jul-10, Jul 10. When typing in these date formats, the -(hyphen) key and the /(forward slash) key function identically Skills for Success with Office 2010 Vol. 1 8

Skill 2: Enter and Format Dates Number Format arrow Formula bar displays full date Number format gallery Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Here you can select popular date, time, and number formats, or click More Number Formats at the bottom of the list to display additional built -in number formats Skills for Success with Office 2010 Vol. 1 9

Skill 2: Enter and Format Dates Number tab selected Sample of selected format Date formats Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 10

Skill 2: Enter and Format Dates When a two-digit year between 30 and 99 is entered, a 20 th-century date is assumed The Ctrl+ ; shortcut enters the current date, obtained from your computer, into the selected cell using the default date format Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 11

Skill 3: Clear Cell Contents and Formats Cells can contain formatting, comments, and contents Contents cleared, formatting not cleared Contents underlying formulas and data. Deleting the contents of a cell does not delete the formatting of the cell Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 12

Skill 3: Clear Cell Contents and Formats Cell formats cleared, contents not cleared Clear Formats deletes the formatting from the cell but does not delete the cell contents Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 13

Skill 3: Clear Cell Contents and Formats Contents and formatting cleared Clear All deletes both the cell contents and the formatting Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 14

Skill 3: Clear Cell Contents and Formats Cell contents and formats cleared You can clear the contents of a cell, the formatting of a cell, or both. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 15

Skill 4: Move, Copy, Paste, and Paste Options Data from cells and ranges can be copied and then pasted to other cells in the same worksheet, to other worksheets, or to worksheets in another workbook Black border Gray border displays where cells will be moved Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Drag and drop is a method of moving objects in which you point to the selection and then drag it to a new location Clipboard A temporary storage area for text and graphics Screen. Tip displays the range A 16: A 17 Skills for Success with Office 2010 Vol. 1 16

Skill 4: Move, Copy, Paste, and Paste Options Columns widened, titles entered Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 17

Skill 4: Move, Copy, Paste, and Paste Options Point at the various Paste Options to preview the pasted text Paste Options gallery Preview of data Status bar message Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 18

Skill 4: Move, Copy, Paste, and Paste When pasting a range of Options cells, you need to select only the cell in the upper left corner of the paste area—the target destination for data that has been cut or copied Column widths retained Durango County Park sheet Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Column titles and formats pasted Skills for Success with Office 2010 Vol. 1 19

Skill 5: Work with Grouped Worksheets [Group] displays on title bar You can group any number of worksheets in a workbook. After the worksheets are grouped, you can edit data or format cells in all the grouped worksheets at the same time Grouped sheet tab colors display as underlines Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. An underline on a worksheet tab indicates that the worksheet is active or selected within a group Skills for Success with Office 2010 Vol. 1 20

Skill 5: Work with Grouped Worksheets Data and formats are entered on all grouped worksheets Row labels and formats display Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 21

Skill 5: Work with Grouped Worksheets Title and format entered Grouped sheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Selecting a single sheet cancels a grouping. Because the worksheets were grouped, the text and formatting was entered into all of the selected sheets. In this manner, you can make the same changes to all selected worksheets in a workbook at the same time. Skills for Success with Office 2010 Vol. 1 22

Skill 5: Work with Grouped Worksheets Title entered and formatted [Group] does not display on title bar Ungrouped sheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 23

Skill 6: Use Multiple Math Operators in a Formula Worksheet subtitle inserted Durango County Park worksheet active Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Excel follows a set of mathematical rules for performing calculations within a formula, called operator precedence. First, expressions within parentheses are calculated. Second, multiplication and division are performed before addition and subtraction Skills for Success with Office 2010 Vol. 1 24

Skill 6: Use Multiple Math Operators in a Formula Worksheet subtitle entered and formatted Worksheets grouped Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 25

Skill 6: Use Multiple Math Operators in a Formula Without the parentheses, the formula would give an incorrect result [Group] displays on title bar When a formula contains operators with the same precedence level, Excel evaluates the operators from left to right. Multiplication and division are considered to be on the same level of precedence. Addition and subtraction are considered to be on the same level of precedence Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Cells referenced in the formula are outlined Formula entered Skills for Success with Office 2010 Vol. 1 26

Skill 6: Use Multiple Math Operators in a Formula Recall that because the worksheets are grouped, the formulas have been entered on all three worksheets Durango County Park worksheet active Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Date moved (your date may differ) Skills for Success with Office 2010 Vol. 1 27

Skill 7: Format Grouped Worksheets When worksheets are grouped, any changes made to a single worksheet are made to each worksheet in the group. For example, if you change the width of a column or add a row, all the worksheets in the group are changed in the same manner [Group] displays on title bar Row 7 heading Shortcut menu Hamilton Park worksheet active Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 28

Skill 7: Format Grouped Worksheets Families data entered Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 29

Skill 7: Format Grouped Worksheets Columns summed, Total cell style applied Grouped worksheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Formula copied to cell F 7 Skills for Success with Office 2010 Vol. 1 30

Skill 7: Format Grouped Worksheets Comma [0] applied Currency [0] applied Ungrouped worksheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 31

Skill 8: Insert and Move Worksheets To organize a workbook, you can move sheet tabs into any order you desire You can add new worksheets to accommodate new information Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. New sheet tab renamed Insert Worksheet button Skills for Success with Office 2010 Vol. 1 32

Skill 8: Insert and Move Worksheets Text truncated Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 33

Skill 8: Insert and Move Worksheets Cell contents pasted Cell formatting pasted Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 34

Skill 8: Insert and Move Worksheets Point to the Summary sheet tab, hold down the left mouse button to display a small black triangle—a caret— and then notice that a small paper icon attaches to the mouse pointer Caret and paper icon indicate location of moved sheet tab Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 35

Skill 9: Construct Formulas That Refer to Cells in Other Worksheets On the Summary sheet, click cell B 5. Type = and then click the Aspen Lake sheet tab. On the Aspen Lake sheet, click cell B 9, and then press Enter Worksheet reference Summary sheet a worksheet that displays and summarizes totals from other worksheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Cell reference Detail sheet a worksheet with cells referred to by summary sheet formulas Skills for Success with Office 2010 Vol. 1 36

Skill 9: Construct Formulas That Refer to Cells in Other Worksheets Worksheet name reference Cell reference Formulas copied for all totals Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Changes made to the detail sheets that affect their totals will automatically recalculate and display on the summary sheet Skills for Success with Office 2010 Vol. 1 37

Skill 9: Construct Formulas That Refer to Cells in Other Worksheets File name in left footer area Page number in center footer area Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Sheet name in right footer area Skills for Success with Office 2010 Vol. 1 38

Skill 9: Construct Formulas That Refer to Cells in Other Worksheets Because the worksheets are grouped, all four worksheets will be previewed and the footer will display in each worksheet Durango County Park worksheet footer Page 4 of 4 Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Next Page button Skills for Success with Office 2010 Vol. 1 39

Skill 10: Create Clustered Bar Charts Switch Row/Column button Chart categories changed to row names Chart sheet Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Clustered bar chart A chart type that is useful when you want to compare values across categories. Bar charts organize categories along the vertical axis and values along the horizontal axis. Skills for Success with Office 2010 Vol. 1 40

Skill 10: Create Clustered Bar Charts Chart and axis titles display Horizontal axis values angled Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 41

Skill 10: Create Clustered Bar Charts Chart title Vertical axis title Horizontal axis title does not display Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 42

Skill 10: Create Clustered Bar Charts Legend moved to top Values display next to data points Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. On the Layout tab, in the Labels group, click the Legend button, and then click More Legend Options. In the Format Legend dialog box, under Legend Position, select the Top option button Skills for Success with Office 2010 Vol. 1 43

Skills for Success • In Skills 1– 10, you practiced the following skills : – Work with Sheet Tabs, Worksheet Insertion, Grouping, Formatting – Work with Dates, Cell Contents & Formats; Move, Copy, Paste and Paste Options – Use multiple Operators in Formulas, Create Formulas using Multiple Worksheets & Cells – Create Clustered Bar Charts Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 44

More Skills for Success • You will now practice the following additional skills: – Create Organization Charts – Create Line Charts – Set and Clear Print Areas – Insert Hyperlinks Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 45

Congratulations • You have now completed all Skills for Excel Chapter 3 Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 46

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. Skills for Success with Office 2010 Vol. 1 47