microsoft office excel 2007 level 3 by Han Nguyen Dinh

Microsoft Business Certification Series ®

Microsoft Office Excel 2007 ®

Level 3 – Advanced Skills

Courseware #: 6265-2

Revised February 2009 Microsoft Business Certification Series © CCI Learning Solutions Inc.

Preface

About This Courseware

Microsoft® Office Excel 2007 – Level 3 Developer: Kenny Lee Editors: Sue Wong, Cristina Goodwin Publisher: Kelly Hegedus This courseware is one in a series prepared by CCI Learning Solutions Inc. for use by students and instructors in courses on computer software applications. CCI designed these materials to assist students and instructors in making the learning process both effective and enjoyable. This training manual is copyrighted and all rights are reserved by CCI Learning Solutions, Inc. No part of this publication may be reproduced, transmitted, stored in a retrieval system, modified, or translated into any language or computer language, in any form or by any means, electronic, mechanical, magnetic, optical, chemical, manual or otherwise without written permission of CCI Learning Solutions, Canada: 1-800-668-1669. The information in this courseware is distributed on an “as is” basis, without warranty. While every precaution has been taken in the preparation of this courseware, neither the author nor CCI Learning Solutions Inc. shall have any liability to any person or entity with respect to any liability, loss, or damage caused or alleged to be caused directly or indirectly by the instructions contained in this courseware or by the computer software and hardware products described therein. CCI Learning Solutions Inc. would like to acknowledge the financial support of the Government of Canada through the Book Publishing Industry Development Program for our publishing activities. © 2007 CCI Learning Solutions Inc.

ISBN: 978-1-55332-155-2

Working With the Data Files The exercises in this courseware require you to use the data files provided for the book. Follow the instructions shown to download the data files for this courseware. 1

Launch your browser and navigate to the CCI Web site location http://www.ccilearning.com/data.

Enter 6265 in the Courseware # box and click

Select the 6265-2-student-data.exe file then click Run. Click Run again in the Internet Explorer – Security Warning window, if necessary.

In the WinZip Self-Extractor dialog box, use the Browse button to specify the Windows Desktop as the location to unzip the file and then click Unzip.

The 6265-2 Student folder containing the required student work files has now been downloaded to your desktop. It is recommended that you rename the folder using your own name before starting the exercises in this courseware. You can reinstall and use the work files as many times as you like.

All student data files have been checked for viruses at the time of development. CCI and its developers are not responsible for any virus infection that may occur as a result of customer or distribution channel manipulation. Please ensure your anti-virus software is updated with the latest virus pattern to prevent new viruses from infecting your system.

6265-2 v1.00 © CCI Learning Solutions Inc.

Table of Contents

Preface

Table of Contents About This Courseware Course Description ...................................................................................................................................................................... v Course Series ............................................................................................................................................................... v Course Length .............................................................................................................................................................. v Course Prerequisites .................................................................................................................................................... v Classroom Setup .......................................................................................................................................................... v Course Design .............................................................................................................................................................................vi Course Objectives ......................................................................................................................................................................vii Conventions and Graphics ........................................................................................................................................................ viii

Lesson 1: Using Data Tools Lesson Objectives ....................................................................................................................................................................... 1 Lesson 1 Buttons .......................................................................................................................................................... 1 Using AutoFill ............................................................................................................................................................................... 2 Finding and Replacing Data ........................................................................................................................................................ 7 Finding Data ................................................................................................................................................................. 7 Replacing Data ........................................................................................................................................................... 10 Using Paste Special................................................................................................................................................................... 11 Consolidating Data .................................................................................................................................................................... 15 Using the Subtotal Function ....................................................................................................................................... 18 Setting Up Automatic Subtotals ................................................................................................................................................ 22 Grouping and Ungrouping Data ................................................................................................................................................ 29 Converting Text to Columns ...................................................................................................................................................... 38 Lesson Summary ....................................................................................................................................................................... 41 Review Questions ...................................................................................................................................................................... 41

Lesson 2: Preparing Online Documents Lesson Objectives ..................................................................................................................................................................... 43 Lesson 2 Buttons ........................................................................................................................................................ 43 Validating Data........................................................................................................................................................................... 44 Using Pick Lists ......................................................................................................................................................................... 49 Removing Duplicate Rows ....................................................................................................................................................... 51 Circling Invalid Data ................................................................................................................................................................... 54 Working with Templates ............................................................................................................................................................ 55 Creating a Template .................................................................................................................................................. 57 Using User-Defined Templates .................................................................................................................................. 59 Editing Templates ....................................................................................................................................................... 60 Deleting Templates ..................................................................................................................................................... 62 Using Hyperlinks ........................................................................................................................................................................ 65 Inserting Hyperlinks .................................................................................................................................................... 65 Modifying and Deleting Hyperlinks ............................................................................................................................. 68 Saving Worksheets as HTML..................................................................................................................................................... 70 Saving a Range of Cells as HTML .............................................................................................................................. 73 Saving as a PDF File .................................................................................................................................................. 76 Lesson Summary ....................................................................................................................................................................... 77 Review Questions ...................................................................................................................................................................... 77

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

iii

Preface

Table of Contents

Lesson 3: Collaborating with Others Lesson Objectives ..................................................................................................................................................................... 79 Lesson 3 Buttons ........................................................................................................................................................ 79 Using Comments ........................................................................................................................................................ 80 Looking at Workgroup Functions .............................................................................................................................................. 83 Creating a Shared Workbook ..................................................................................................................................... 83 Resolving Shared Workbook Conflicts ....................................................................................................................... 85 Tracking Changes ...................................................................................................................................................... 87 Showing the History of Changes ................................................................................................................................ 89 Removing Shared Use of Workbooks ........................................................................................................................ 91 Protecting Your Workbook ......................................................................................................................................................... 91 Using Passwords ........................................................................................................................................................ 91 Protecting the Worksheet ........................................................................................................................................... 92 Workbook Protection .................................................................................................................................................. 96 Protecting Files ........................................................................................................................................................... 98 Preparing for Distribution ......................................................................................................................................................... 100 Changing Workbook Properties ............................................................................................................................... 100 Using the Document Inspector................................................................................................................................. 103 Using Digital Signatures ........................................................................................................................................... 106 Marking Workbooks as Final .................................................................................................................................... 115 Using Digital Rights Management ............................................................................................................................ 116 Saving a Macro-Enabled Workbook ......................................................................................................................... 118 Compatibility With Previous Versions of Excel ......................................................................................................... 122 Lesson Summary ..................................................................................................................................................................... 124 Review Questions .................................................................................................................................................................... 125

Lesson 4: Using Analysis Tools Lesson Objectives ................................................................................................................................................................... 127 Lesson 4 Buttons ...................................................................................................................................................... 127 What-If Analysis ....................................................................................................................................................................... 128 Using the Goal Seeking Tool ................................................................................................................................................... 129 Using the Solver....................................................................................................................................................................... 132 Using a Data Table .................................................................................................................................................................. 137 Working with Single Variable Data Tables ................................................................................................................ 137 Working with Two-variable Data Table ..................................................................................................................... 140 Multiple Formulas in a Data Table ............................................................................................................................ 142 Using PivotTables .................................................................................................................................................................... 144 Creating a PivotTable ............................................................................................................................................... 144 Creating a PivotChart................................................................................................................................................ 148 Modifying Field Positions .......................................................................................................................................... 152 Modifying Data Field Options ................................................................................................................................... 154 Lesson Summary ..................................................................................................................................................................... 156 Review Questions .................................................................................................................................................................... 157

Appendices Appendix A: Prerequisites ........................................................................................................................................................A 2 Appendix B: Answers to Review Questions .............................................................................................................................A 3 Appendix C: Microsoft Office Excel 2007 Exam Objectives .....................................................................................................A 7 Appendix D: Glossary of Terms................................................................................................................................................A 8 Appendix E: Index ...................................................................................................................................................................A 11

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

About This Courseware

Preface

Course Description Microsoft® Office Excel 2007 – Level 3 teaches students how to use some advanced features to work with different types of reports. Students will look at some productivity tools to increase data entry, features to enable collaboration with others, and use various commands to analyze large numerical reports. Students who complete this course can be on their way to preparing for a Microsoft Certified Application Specialist exam. By completing the other levels available for this application students will have reviewed all of the exam objectives.

Course Series Microsoft Office Excel 2007 – Level 3 is one of the courses in the Microsoft Business Certification Series. Other courses available in the series include: •

Microsoft Office Excel 2007– Level 1, Level 2

•

Microsoft Office Word 2007 – Level 1, Level 2, Level 3

•

Microsoft Office Access 2007 – Level 1, Level 2

•

Microsoft Office PowerPoint 2007 – Level 1, Level 2

•

Microsoft Office Outlook 2007 – Level 1, Level 2

•

Microsoft Windows Vista – Level 1, Level 2

Course Length The Microsoft Business Certification Series can adapt to meet various course length requirements. There are two types of exercises in this book, Learn the Skill and Practice the Skill (refer to the Conventions and Graphics section to identify the different icons used for each type). For the shortest course lengths, students can perform just the Learn the Skill exercises. For longer course lengths, students can complete both the Learn the Skill and Practice the Skill exercises. Instructors should refer to the Instructor Resources for this book to learn more about how the material can be adapted.

Course Prerequisites This course is a continuation of the Microsoft Office Excel 2007 – Level 2 courseware, in which you were introduced to intermediate features to manipulate specific types of data in worksheets. Students who wish to become proficient using the features of Microsoft Office Excel 2007 will benefit from taking this course.

Classroom Setup If you are upgrading from Microsoft Office 2003 to the 2007 release, additional hardware should not be required, although an operating system upgrade may be required. If your are upgrading from Microsoft Office 2000 or Office XP to the 2007 release, ensure that the hardware and operating system meet the minimum 2007 Microsoft Office system requirements (as published by Microsoft). Instructors should evaluate classroom computers before installing 2007 Microsoft Office system products to verify that the student computers meet or exceed the minimum system requirements. It is likely your instructor set up the classroom computers based on the system requirements to run the software for this course. Most software configurations on your computer are identical to those on your instructor’s computer. However, your instructor may use additional software to demonstrate network interaction or related technologies.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preface

Microsoft® Office Excel 2007 – Level 3

Course Design This course book was developed for instructor-led training and will assist you during class. Together with comprehensive instructional text and objectives checklists, this course book provides easy-to-follow hands-on lab exercises and a glossary of course-specific terms. This course book is organized in the following manner:

 Microsoft Office Excel 2007  Table of Contents  Lessons  Lesson Objectives  Narrative Text  Graphics  Tips and Tricks  Tech Notes

 Exercises  Graphics  Tips and Tricks  Tech Notes

 Lesson Summary  Lesson Review  Appendices  Prerequisites  Answers to Review Questions  Courseware Mapping  Glossary of Terms  Index When you return to your home or office, you will find this course book to be a valuable resource for reviewing exercises and applying the skills you have learned. Each lesson concludes with questions that review the material. Lesson review questions are provided as a study resource only and in no way guarantee a passing score on a certification exam. Appendixes in the back of this course book provide additional information, resources, and answers to review questions.

6265-2 v1.00 © CCI Learning Solutions Inc.

About This Courseware

Preface

Course Objectives The main objective of this courseware is to introduce you to the advanced features of Excel. The purpose of this book is to demonstrate features that can increase productivity in using many of the built-in features to format, analyze, calculate, or protect your worksheets. You will also look at how to set up worksheet data on web pages as well as how to use data from other programs within Excel. On successful completion of this courseware, you will be able to perform the following:

 use AutoFill to copy or fill data  find and replace specific types of data  consolidate or summarize data  group or outline data  convert text to columns  use AutoFill to set up a series of data  find and replace data in a worksheet  use Paste Special to create different types of data  create groups and subtotals from the data in the worksheet  convert text to columnar formats  validate data and remove duplicate rows of data  use pick lists  working with existing templates, or create your own template  create, modify or remove a hyperlink  save a worksheet or a range of cells in a HTML format  publish a worksheet to the Internet  work with shared workbooks including adding protection or tracking changes  changing workbook properties and using the Document Inspector appropriately  set up a what-if scenario with a worksheet  use the Goal Seeking and Solver tool  work with a single or two variable data table  create and modify PivotTables or PivotCharts

6265-2 v1.00 © CCI Learning Solutions Inc.

vii

Preface

Microsoft® Office Excel 2007 – Level 3

Conventions and Graphics The following conventions are used in CCI learning materials. File Names or Database Field Names

File names or database field names are indicated in italic font style.

Exercise Text

Content to be entered by the student during an exercise appears in Courier New font.

Procedures

Procedures and commands for completing a task are indicated in bold font style.

Menu Options and Features

Menu options and features are listed in the left hand column and corresponding descriptions are in the right hand column.

The following graphics are used in CCI learning materials. Specific Keyboard Graphics provide easy-to-identify graphics to identify the key on the keyboard to press.

Technical Notes point out exceptions or special circumstances that you may find when working with a particular procedure, or may indicate there is another method to complete the task.

Learn the Skill Learn the Skill graphics signal the start of step-by-step hands-on exercises or other activities.

Practice the Skill Practice the Skill exercises follow Learn the Skill exercises and provide additional practice and reinforcement or may present an alternative method of completing a task. Indicates the numbered objective from the Microsoft Certified Application Specialist exam that is being covered in this topic. Refer to the Appendix for a complete listing of exam objectives. 6.1

viii

6265-2 v1.00 © CCI Learning Solutions Inc.

Microsoft Business Certification Series ®

Lesson 1: Using Data Tools Lesson Objectives In the lesson, you will learn about different tools you can use to manipulate data to display specific information you may want in your reports, including finding or replacing text, pasting special items into a worksheet, grouping or outlining data, summarizing data, and converting text to columns. On successful completion of this lesson, you should be able to: 

use AutoFill to set up a series of data



find and replace data in a worksheet



use Paste Special to create different types of data



create groups from the data in the worksheet



create subtotals for specific groups of data in the worksheet



summarize data



convert text to columnar formats

Lesson 1 Buttons The following command buttons are covered in this lesson: Home tab

Data tab

Miscellaneous Items Show Detail Hide Detail Smart Tag Action

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Using AutoFill 1.1

The AutoFill feature is a very useful tool when you want to copy data or formulas to one or more consecutive adjacent cells. You need only to select the cells to be copied, and then drag the AutoFill handle (the small black square at the bottom right corner of the cell pointer) across target cells. When you release the mouse, an AutoFill icon appears. A menu appears when you click the icon.

The contents of this menu vary depending on the type of data being copied or filled, with some of the more common options including: Copy Cells

Text, values or formulas are copied with their formatting; does not create a series.

Fill Series

Creates a simple linear series and copies the formatting of the source cell. A series is a sequentially incrementing or decrementing set of values, such as 1 2 3 4, E F G H, Sun Mon Tues, -5 -10 -15 -20, Jun Jul Aug Sep, etc.

Fill Formatting Only

Copies formatting from the source cell but leaves the contents of the target cells unchanged.

Fill Without Formatting

Creates a simple linear series without copying the formatting of the source cell.

Depending on the type of data being copied, AutoFill selects the Copy Cells or Fill Series option for you. In most cases, it correctly guesses which option you want in a particular situation without requiring you to use the AutoFill icon. Usually when Excel cannot determine what the series is, this can be resolved by inserting another cell in the source to help identify the series such as where 1 3 6 identifies the pattern of increasing by 2 first, then by three. If the 6 was not entered, Excel determines the pattern would be to increase by 2 for every subsequent cell.

Learn the Skill This exercise demonstrates how the AutoFill feature works. 1

Open the Sports Fundraising workbook and save as Sports Fundraising - Student.

First, enter the initial values of the series. 2

Select cell B3, and type: Jan.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools 3

Select cell B7, and type: =ROUND(B$4*$O7,0). Notice that this formula calculates the percentage of the monthâ&#x20AC;&#x2122;s fundraising revenue to the first cell. Also notice the use of absolute references in preparation for the formula to be copied to other cells. Now copy the formula in cell B7 to the rest of the cells in the column.

Select cell B7 and position the mouse pointer on the AutoFill handle at the bottom right corner of the cell.

The mouse cursor will change to a +. 5

Select and drag the mouse down to cell B17.

Click Auto Fill Options to look at which options are selected.

Click elsewhere in the worksheet to collapse the Auto Fill Options menu.

Now enter the SUM function in cell B18, and format it to make it stand out. 8

Select cell B18, and on the Home tab, in the Editing group, click Sum and then press

Select cell B18 again, and on the Home tab, in the Font group, click Bold and click Italic to turn them on.

On the Home tab, in the Paragraph group, click the arrow for Border and click Top and Thick Bottom Border.

Enter a SUM function in cell N7, and copy it to cell N18. 11

Select cell N7, and on the Home tab, in the Editing group, click Sum. Verify the cell range for the sum is . B7:M7, then press

Position the mouse pointer on the AutoFill handle at the bottom right corner of cell N7.

Click and drag the mouse down to cell N18.

Now copy the formulas in the cell range B7:B18 across the rest of the columns. 14

Select cells B7:B18. Position the mouse pointer on the AutoFill handle at the bottom right corner of cell B18.

Click and drag the mouse across to column M.

Click Auto Fill Options to look at which option is selected.

Click elsewhere in the worksheet to collapse the Auto Fill Options menu.

Now copy the formatting from 18

Select cells N7 and N18. Note the formula and the current value in each of these cells: Cell Value Formula N7 _______ ___________ N18 _______ ___________

Select cells M7:M18, then drag the AutoFill handle across to column N. Do not click on any other cells in the worksheet yet. Compare the new values in cells N7 and N18 to what you wrote previously. Why do you think they have changed?

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Click Auto Fill Options, and click Fill Formatting Only.

Select cells N7 and N18 again, and verify the formula and displayed value are correct.

Now try using the AutoFill with a month value. 22

Select cell B3.

Use the AutoFill handle to fill across to cell M3.

The default fill option used by Excel was Fill Series. Try the Copy Cells option and see what happens. 24

Click Auto Fill Options, and click Copy Cells.

Now try the other fill options available. 25

Click Auto Fill Options, and click Fill Without Formatting.

Click Auto Fill Options, and click Fill Months. The worksheet should appear similar to the following:

Save and close the workbook.

Practice the Skill This exercise demonstrates how the AutoFill feature works with a variety of values. 1

Create a new workbook, save as AutoFill - Student and enter the following values: Cell Value B2 January Monday B3 B4 Qtr1 B5 Region 1 Type 4 B6 B7 June B8 James C B9 B10 First B11 Product A Qtr1/2005 B12 B13 33 B14 March 2008 B15 March 1, 2008

First, use the Copy feature. 2

Select cell B2.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools 3

On the Home tab, in the Clipboard group, click Copy.

Select cells C2:J2.

On the Home tab, in the Clipboard group, click Paste.

Lesson 1

Notice the cells all contain the same value. Now try the AutoFill handle. 6

Select cell B2 again.

On the Home tab, in the Font group, click Bold and Italic to change its formatting.

Position the mouse cursor over the AutoFill handle in the bottom right hand corner of cell B2.

Press and hold the mouse button and drag the AutoFill handle to the right to cell J2. This time, notice how as you drag, you don’t see the same month being filled and Excel shows you tips for the months, with “September” showing for cell J2 just before you released the mouse button. When using the AutoFill to extend cell values (but not formulas), Excel will show a tip window indicating what entry will be placed in the cell you are passing over. This facility allows you to easily determine what entry you have reached and thus control when to stop the dragging of the handle.

Change the Auto Fill Option to copy the original cell content, including the formatting. 10

Click Auto Fill Options and click Copy Cells.

Change the Auto Fill Option back to the default of filling the series, including the formatting. 11

Click Auto Fill Options and click Fill Series.

Change the Auto Fill Option to fill in with only the formatting. The data in the cells came from the copy and paste done previously. 12

Click Auto Fill Options and click Fill Formatting Only.

Change the Auto Fill Option to fill the series but leave the formatting unchanged. 13

Click Auto Fill Options and click Fill Without Formatting. Excel recognizes the cell contents as a month value.

The Auto Fill Option has a new option of filling the series as month names. 14

Click Auto Fill Options and click Fill Months.

Use the AutoFill handle from cell B3 across to the cell range C3:J3.

Click Auto Fill Options and select each of the following Auto Fill Options: Fill Weekdays Fill Days

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

17 Repeat steps 15 and 16 for the remaining cells: From To Cells Auto Fill Options Notes B4 C4 to J4 Copy Cells Fill Series Series is only from Qtr1 to Qtr4, then repeats. B5 B6 B7 B8 B9 B10 B11 B12

C5 to J5 C5 to J5 C5 to J5 C5 to J5 C5 to J5 C5 to J5 C5 to J5 C5 to J5

Copy Cells Fill Series

Series increments from Region 1 onwards.

Copy Cells Fill Series

Series can start at any number, not just 1.

Copy Cells Fill Months

Series can start at any month, not just January.

Copy Cells Fill Without Formatting

No series pattern recognized here.

Copy Cells Fill Without Formatting

No series pattern recognized here.

Copy Cells Fill Without Formatting

No series pattern recognized here.

Copy Cells Fill Without Formatting

No series pattern recognized here.

Copy Cells Fill Series

Only one series can be incremented at one time.

B13

C5 to J5

Copy Cells Fill Series

Excel tries to make best guess whether to use Copy Cells or Fill Series option.

B14

C14 to J14

Fill Days Fill Weekdays Fill Months Fill Years

Dates have additional fill options.

B15

C15 to J15

Fill Days Fill Weekdays Fill Months Fill Years

The variety of data shown in this worksheet shows examples of what Excel recognizes as incremental values, and others that are not.

You can also force Excel to increment by a value other than 1 by using two cells adjacent to each other that will demonstrate the series. Now try to force Excel to increment by more than one or force it to try to AutoFill certain types of labels.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools 18

Enter the following values: Cell Value B17 C C17 D Qtr1/2009 B18 C18 Qtr2/2009 B19 Product A Product B C19 B20 Product 1 C20 Product 3 3-Mar B21 C21 4-Mar

Select cells B17:C21.

AutoFill to column J. The worksheet should appear similar to the following:

Save and close the workbook.

Finding and Replacing Data Finding Data You can search a worksheet for every occurrence of a value (text label or number), function name, or cell reference. In the event you need to find every cell that contains this search item, the Find feature is invaluable. To activate the Find feature, use one of the following methods: •

On the Home tab, in the Editing group, click Find & Select, and then click Find; or

•

press

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Find what

The value to search.

Within

Search only within the current worksheet or the entire workbook.

Indicates which direction to search by: By Rows: Search across from left to right across each row in the worksheet or workbook starting from the top row. By Columns. Search down each column starting from the leftmost column.

Look in

Specifies whether to look only at the value, the underlying formula, or any comment in each cell. The Formulas option is generally more flexible even if the cell contains just a value.

Match case

Specifies whether or not to match the upper and lower case of alphabetic characters in the cell.

Match entire cell contents

Specifies whether or not the Find what value must form the entire contents of the searched cell.

Learn the Skill This exercise demonstrates how to find all cells that contain a specific data value. 1

Open the Pullemout Dental Suppliers workbook and save as Pullemout Dental Suppliers - Student.

First try looking for a word. 2

On the Home tab, in the Editing group, click Find & Select, and then click Find.

In the Find what text box, type: expense and click Find Next.

Click Find Next several more times, and observe which cells are selected.

Now try different find options. 5

In the Find and Replace dialog box, click Options to display all find options.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Click Match case to turn it on, and then click Find Next. A message box displays with the message “Microsoft Office Excel cannot find the data you’re searching for.” Even though Excel had no problem looking for the word expense earlier, none of the cells contains the word expense in all lower case characters.

Click OK to close the message box.

Click Find what, and change the value to search for to: Expense (be sure to use upper case for the first letter).

Click Find Next several times.

Click Match entire cell contents to turn it on, and then click Find Next. A message box with the message “Microsoft Office Excel cannot find the data you’re searching for” displays again. Excel now cannot find any cell containing only the word Expense in it. The cells found earlier all have other words in them as well.

Click OK to close the message box.

Click Find what, and change the search value to: Expenses.

Click Match entire cell contents to turn it off.

Click Find Next several times.

Click Match case to turn it off, and then click Options to suppress the options view.

Now look for all cells that refer to the cell B5. 16

In Find what, delete the current contents, type: B5 and click Find All. A window drops down from the dialog box to display details of each occurrence.

Click Close.

Leave the workbook open.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Replacing Data You can also replace the search item with a new value, on an individual basis by using Replace, or for all cells that contain that value by using Replace All. The Replace function simplifies and speeds up the job of replacing a word or value with another. As the process is automated, it virtually eliminates any typing errors you would have made if you had done it manually. To activate the Replace feature, use one of the following methods: •

On the Home tab, in the Editing group, click Find & Select, and click Replace; or

•

press

•

if the Find dialog box is displayed, click the Replace tab.

; or

You may want to use Replace to control and verify that each replacement should be made. An alternative is to use the Find tab before using the replace function to search through the worksheet and verify that your search value and options have been set correctly. Once you are certain that your search value and options are correct, you can proceed with using Replace All.

Learn the Skill This exercise demonstrates how to use the replace feature. 1

Make sure the Pullemout Dental Suppliers - Student workbook is the active workbook.

Replace all instances of the word Expenses with the word Costs. 2

On the Home tab, in the Editing group, click Find & Select, and click Replace.

In Find what, type: Expenses.

In Replace with, type: Costs and click Find Next.

Instead of using Find Next, you could click Replace All to replace all occurrences at one time. 5

Click Replace All to replace each instance of the word Expenses. Excel has completed replacing all specific text occurrences when a message box appears with “Excel has completed its search and has made 8 replacements.”

Click OK to close the message box.

Click Close.

Save and close the workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools

Using Paste Special The standard Paste command will paste the entire contents of the clipboard into the target cell(s), including data, formatting, and any comments. In some cases, you may not want to do this. The Paste Special command enables you to do special types of pasting. To activate the Paste Special command, on the Home tab, in the Clipboard group, click the arrow for Paste, then click Paste Special.

Paste

Specifies what components of the data in the Clipboard are pasted to the target cells. For example, if formulas are specified, then only the formulas are selected.

Operation

Specifies how any data in the target cells will be treated. For example, if Multiply is selected then any existing data will be multiplied by the data pasted into the target cells.

Skip blanks

If selected, will avoid overwriting any existing data in the target cells if there is no data from the clipboard to be put into that cell.

Transpose

If specified, will rotate the data. Therefore data in the clipboard listed down a column will now be listed across a row in the target cells. Similarly, data listed across cells in a row become listed down cells in a column.

Four of the options available in the Paste Special dialog box are directly accessible on the Home tab, in the Clipboard group by clicking the arrow for Paste. These four are commonly used options for Paste Special.

Learn the Skill This exercise demonstrates how to use the Paste Special command. 1

Open Amalgamated Consolidated Income Statement.

Suppose you are planning to create a draft budget for next year’s revenues and expenses, with a comparison to this year’s totals. 2

Select cells A1:G14, and then on the Home tab, in the Clipboard group, click Copy.

Press Paste.

Make appropriate changes to column widths.

Save the new workbook as Amalgamated Student.

to create a new workbook quickly, and then on the Home tab, in the Clipboard group, click

6265-2 v1.00 © CCI Learning Solutions Inc.

Consolidated

Income

Statement (estimate)

1.3

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Select cells B5:G14 and press

Click the column B heading and then on the Home tab, in the Cells group, click Insert.

In cell B4, type: Total (LY) as the title and press

Switch to Amalgamated Consolidated Income Statement and select cells B5:B14. Then on the Home tab, in the Clipboard group, click Copy.

Switch to Amalgamated Consolidated Income Statement (estimate) - Student and select cell B5.

On the Home tab, in the Clipboard group, click the arrow for Paste and then click Paste Special.

Click Values and then click OK.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Notice how the totals from last yearâ&#x20AC;&#x2122;s budget are now inserted into this worksheet as values only. This removes any links from the other worksheet and gives you the opportunity to view these numbers and build your budget accordingly. 13

Make any formatting changes as desired.

Save and close the worksheet.

Close all other worksheets without saving.

Practice the Skill This exercise gives more practice using the Paste Special command. 1

Open the Paste Special workbook and save as Paste Special - Student.

First, try pasting just the values to a set of target cells. 2

Select cells A2:B6, and on the Home tab, in the Clipboard group, click Copy.

Select cell H3. On the Home tab, in the Clipboard group, click the arrow for Paste, and then click Paste Special.

In the Paste Special dialog box, click Values and click OK. Alternatively, you can also select the Paste Values option in the Paste options list. This is provided as this is commonly used.

Now compare the contents of each of these two sets of cells. Because you had selected Values, all formulas were evaluated, and the results were pasted into the target cells. This option had no impact on the cells containing text or numeric values. Note that none of the cell formatting was pasted into the target cells. 5

Repeat steps 3 to 4, except in the Paste Special dialog box, click Formats.

Now see what happens when you paste just the formulas to the target cells. 6

Select cells B2:B6, and on the Home tab, in the Clipboard group, click Copy.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1 7

Microsoft® Office Excel 2007 – Level 3

Select cell F2. On the Home tab, in the Clipboard group, click the arrow for Paste, click Paste Special, click Formulas and then click OK.

Alternatively, you can also select the Formulas option directly from the Paste options list. The Formulas option is commonly used so it was made readily accessible. Now try the Transpose option. 8

Select cells A2:B6, and on the Home tab, in the Clipboard group, click Copy.

Select cell B9. On the Home tab, in the Clipboard group, click the arrow for Paste, click Paste Special, and then click Transpose. Click OK. Alternatively, you can also select Transpose from the Paste Special dialog box.

You can also use a matrix add function with data. 10

Select cells A2:A6, and on the Home tab, in the Clipboard group, click Copy.

Select cell H3. On the Home tab, in the Clipboard group, click the arrow for Paste, and then click Paste Special.

In the Paste Special dialog box, click Values. Then in the Operation area, click Add and click OK. The completed worksheet should now look similar to the following:

Save and close the workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools

Consolidating Data Consolidating data refers to the process of summarizing large amounts of data into a single table. You can consolidate data from different areas within a worksheet, separate worksheets or across other workbooks.

Function

Selects the type of function (e.g., sum, count, average, max, min, etc.) to apply to the data being consolidated together. For example, if Sum is selected (the default), then the consolidated data will be added together.

Reference

Selects the cell range from the worksheet/workbook where the source data is located. When you click Add, this cell range selection is added to the All references list.

All references

Lists all source cell ranges that have been selected. When OK is selected, the data from all these source cell ranges will be consolidated together using the selection function.

Use labels in

Designates whether any label row and/or column is to be used to consolidate the data together. If this is not selected (the default), then the data is consolidated by position.

Create links to source data

Will automatically update your consolidated data whenever the source data is changed.

When consolidating by position, the source ranges must be the same layout as the destination range. Excel will process cell for cell the source ranges to the destination range using the selected function. The data in each of the source worksheets must therefore all be in the same positions; e.g., the data in cell A1 from each of the source worksheets will be consolidated together and placed in cell A1 of the destination worksheet. A more flexible method is to use the labels to consolidate data. Excel will use the column and row labels to match the source ranges to the destination range, the order does not matter. When using labels to perform consolidation the row and column labels must match exactly, the match is not case sensitive. Although consolidating information is relatively easy, there are some points you should consider when you are using the consolidation process: •

Only numeric values are consolidated; any text in the source areas display as a blank cell.

•

Each consolidation area must be separate from other consolidation areas. For example, there must be a set area for the Eastern region sales, another area for Western region sales, and so on.

•

Excel uses the same numeric format in the consolidated area as it found in the first source area.

•

Excel consolidates data values only; formulas are not carried into the consolidated worksheet.

•

To link different worksheets while consolidating, choose Create links to source data. Creating links results in a summary table with the details linked and summation formulas to total each group.

•

You can specify a maximum of 255 source areas.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

•

When referencing other workbooks the workbooks do not need to be opened at the time of specification.

•

You can use category or range names in the source and destination worksheets. This makes it easier to find specific information, especially if you do not know exactly where the cells are.

Learn the Skill This exercise will demonstrate how to consolidate data by category from two workbooks into a new summary workbook. 1

Open the Store1 and Store2 workbooks.

Create a new blank workbook and save as Store Total - Student.

On the View tab, in the Window group, click Arrange All. Click Tiled and click OK.

If necessary, select cell A1 of the new workbook.

On the Data tab, in the Data Tools group, click Consolidate.

In the Reference text box, select cells A1:B3 from the Store1 workbook. The referenced workbook does not need to be open, as is in this exercise, for you to reference it. If it is not open, use Browse to find the file. Edit the reference so that the name of the workbook is in square brackets followed by the worksheet name all in single quotes. Follow this with a bang character (!) and then the cell range from the source worksheet. As the file is open, a faster method to select the cells to reference is to click in that worksheet and select the appropriate cells. Alternatively, the cell references can also be typed directly into the Reference text box.

In the Use labels in area, click Left column to turn it on. Then click Add.

Note that this cell range now appears in the All references list box. Now select the cells from the second workbook. 8

In the Store2 workbook, select cells A1:B3. Click Add.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Click OK to complete the consolidation. If the row labels do not appear in the consolidated worksheet, use Undo and be sure to turn on the Left column check box in step 7. The worksheet should now appear similar to the following:

Save the new workbook, then close all workbooks.

Practice the Skill In this exercise you will consolidate the workbooks from four different stores into one summary workbook. The cells in the store workbooks will be selected directly using the mouse. 1

Open the Computer Super Warehouse Store 4 workbook.

Now open the inventory workbooks for the other three stores. All four workbooks are identical in format except for the quantity of each inventory item at each store. 2

Open the (in this specified sequence) Computer Super Warehouse Store 3, Computer Super Warehouse Store 2, and Computer Super Warehouse Store 1 workbooks.

Create a new workbook to merge together the values from all of the store workbooks. 3

Create a new blank workbook, and save it as Computer Super Warehouse Store Consolidated Total - Student.

On the View tab, in the Window group, click Arrange All. Click Tiled and click OK.

On the Data tab, in the Data Tools group, click Consolidate to display the Consolidate dialog box.

In the Use labels in area, click Left column to turn it on.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Move the Consolidate dialog box aside so that the Computer Super Warehouse Store 1 workbook is not obstructed.

With the cursor in the Reference text box, click on the Computer Super Warehouse Store 1 workbook to select it, then select cells A1:B19 from the Computer Super Warehouse Store 1 workbook and click Add.

Repeat steps 7 and 8 for each of the other three store workbooks.

Click OK to complete the consolidation.

Select column A in the Computer Super Warehouse Store Total - Student workbook. On the Home tab, in the Cells group, click Format and select Column Width. Set the column width to 25 and click OK.

Save and close the Computer Super Warehouse Store Total - Student workbook.

Close the four store workbooks and discard any changes that may have been made to them.

Using the Subtotal Function 3.3 4.5

The SUM function calculates an arithmetic sum total of the selected cell range. In most situations, it is the appropriate function for automatically adding together the value in multiple cells. In complex data tables, it may be easier to use the SUBTOTAL function instead. The SUBTOTAL function can perform a variety of statistical operations, depending on which function number is used. The general format of the SUBTOTAL function is: =SUBTOTAL(Function #, Cell Range)

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

The most commonly used function numbers are: Function # Function 1 AVERAGE — mean average of the cell range 2 COUNT — number of cells containing numbers 3 COUNTA — number of non-blank cells 4 MAX — highest value in the cell range 5 MIN — lowest value in the cell range 9 SUM — sum total of the cell range Other function numbers that are also available for SUBTOTAL are: Function # Function 6 PRODUCT 7 STDEV 8 STDEVP 10 VAR 11 VARP The primary advantage of SUBTOTAL is that it will not include any other SUBTOTAL calculations included in its range. As a result, you can insert subtotal, total, and grand total calculations into a table by simply including the entire range of cells without any risk of accidentally double-counting a subtotal. To insert the SUBTOTAL function, use one of the following methods: •

Click Insert Function and then in the Or select a category field, click Math & Trig; or

•

on the Formulas tab, in the Function Library group, click Math & Trig.

Learn the Skill This exercise demonstrates the use of the different SUBTOTAL function numbers. 1

Open the Video Rentals Database workbook and save it as Video Rentals Database (function) Student.

Insert a new row with subtotals for all video rentals on October 1. 2

Select row 10 and then on the Home tab, in the Cells group, click Insert.

Enter the following values or functions: Cell Value or Function B10 Subtotal C10 =SUBTOTAL(3,C2:C9) D10 =SUBTOTAL(9,D2:D9)

Select row 10 and turn on bolding for the entire row.

Now insert another row with subtotals for all video rentals on October 2. 5

Select row 25 and then on the Home tab, in the Cells group, click Insert.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1 6

Enter the following values or functions: Cell Value or Function B25 Subtotal C25 =SUBTOTAL(3,C11:C24) D25 =SUBTOTAL(9,D11:D24)

Select row 25 and turn on bolding for the entire row.

Microsoft® Office Excel 2007 – Level 3

You can also copy the contents of cells B10:D10 to B25:D25, and change the cell references in the SUBTOTAL functions. More subtotals can be added for each day of October, but for the purpose of this exercise these two will be sufficient. Now enter formulas with totals for all rows. 8

Scroll down to the bottom of the data. Then enter the following values or functions: Cell Value or Function B331 Subtotal C331 =SUBTOTAL(3,C324:C330) D331 =SUBTOTAL(9,D324:D330) B332 Grand Total C332 =SUBTOTAL(3,C2:C330) D332 =SUBTOTAL(9,D2:D330) Notice that you do not need to concern yourself about how many SUBTOTAL functions were entered above this Grand Total row — you simply reference the entire column of data.

Select rows 331 and 332 and turn on bolding for both rows. The worksheet should now appear similar to the following:

Save and close the workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Practice the Skill This exercise demonstrates the difference between SUM and SUBTOTAL. 1

Open the Subtotal Function workbook and save it as Subtotal Function - Student.

First use the traditional SUM function to total column B. 2

Enter the following formulas: Cell Formula B8 =SUM(B3:B7) B18 =SUM(B10:B17) B19 =SUM(B3:B18)

Notice that cell B19 has double-counted the numbers above it. The proper way of using SUM to calculate a grand total is to find and add together only the sum values, or alternatively including only non-SUM cells in the grand total SUM function. Now enter the correct SUM formulas. 3

Enter the following formulas: Cell Formula C8 =SUM(C3:C7) C18 =SUM(C10:C17) C19 =C8+C18

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Because worksheets like this are more complex, it will be difficult to ensure the formulas are correct as more data is added to the worksheet. Now try the SUBTOTAL formula. 4

Enter the following formulas: Cell Formula D8 =SUBTOTAL(9,D3:D7) D18 =SUBTOTAL(9,D10:D17) D19 =SUBTOTAL(9,D3:D18) The worksheet should now appear similar to the following:

Save and close the workbook.

Setting Up Automatic Subtotals 4.5

Many worksheets become very large due to the sheer amount of information needed for analysis. Inevitably, summary totals are added for each major group of data, and grand totals. The end result is a worksheet that is difficult to see from one end to another. Worksheet outlining is an Excel feature that enables the user to compress the worksheet to see only the subtotals and grand totals. They can also expand a subtotal to see the details of its components. The great advantage is that worksheet outlining is easy to set up, use, and remove — all with using the mouse. To help organize the data, Excel provides an automatic subtotal tool that enables you to create subtotals based on changes in a selected field using a variety of functions. As long as the data is structured in a consistent manner, the automatic subtotal feature can be used. Once the subtotals have been generated, you can then use the Outline feature to hide the details and only display the subtotals or display all details and the subtotals. Before creating the subtotals, you must decide which column to summarize by. You must then sort the worksheet by that column to group these related rows together; otherwise the results will not make much sense. While working down the rows, Excel then automatically inserts a subtotal whenever the value in this column changes. You can also create multiple subtotals for the same columns, or on multiple layers of columns. For example, you can calculate the sum, average, and variance subtotal for as many numeric columns as you require. Each of these different types of subtotals must be added as separate rows to identify the type of subtotal.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

You can create a more complex type of multiple subtotals by nesting them. That is, you may want to have a sales report listing every sales transaction for a year. As long as you have a separate column for the month value, you can create subtotals based on the month. You can then also create another layer of subtotals based on the date of each sale, which is a different column. You can then use the outline functions to zoom in and out of parts of the worksheet of interest to you. The key difference in creating multiple subtotals is how Replace current subtotals is used. When creating simple subtotals, ensure this check box is turned on. When creating multiple subtotals, ensure this check box is turned off so that you can add more subtotal layers.

Learn the Skill This exercise demonstrates how to create automatic subtotals for one and two numeric columns. The same procedure can be used for other types of columns. 1

Open the Smal-Mart Sales workbook, and save it as Smal-Mart Sales Subtotal - Student.

To create an automatic subtotal based on the Department, you must sort the worksheet by this column first. 2

Select any cell in column C.

On the Data tab, in the Sort & Filter group, click Sort A to Z.

Now create the subtotals on the Amount column. 4

On the Data tab, in the Outline group, click Subtotal.

Click the arrow for At each change in and click Department.

In the Use function list box, verify Sum is selected.

In the Add subtotal to list, verify that only Amount is turned on and all other check boxes in this list are turned off.

If necessary, click Replace current subtotals and Summary below data to turn them on and clear Page break between groups.

Click OK. Then scroll down the worksheet to see how the data now appears.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

The worksheet should appear similar to the following:

Notice that the worksheet now has three (or more if there are additional subtotal rows) outline levels added at will compress the the left side. Each of these numbers correspond to a different level of detail; clicking the will also compress the data rows but the data rows so that only the grand total is displayed; clicking the subtotal rows and the grand total will be displayed; and clicking the will show all data rows for that level, the subtotals, and the grand total. Now practice with the outline features. 10

Click the

at the top of the outline section, then scroll up to the top of the worksheet.

The and buttons work the same way as in the Windows Explorer: clicking the will collapse the details. show the data in the next level, and clicking the

will open the level and

Click the

to the left of the Grand Total line.

Click the

to the left of the Men’s wear Total, then scroll up the worksheet for a few rows.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools 13

Click the

at the top of the outline section, then scroll up to the top of the worksheet.

Click the

to display all details.

Now try removing the subtotals from the worksheet. 15

Ensure that the cell pointer is located within the data area and on the Data tab, in the Outline group, click Subtotal.

Click Remove All button to clear the subtotals. When removing the summary report, all summary functions are removed. You cannot remove part of the summary.

Now calculate subtotals for two columns in the worksheet at the same time. 17

On the Data tab, in the Outline group, click Subtotal again.

If necessary, click the arrow for At each change in and click Department. Verify that Sum is selected for the Amount column.

If necessary, click Replace current subtotals and Summary below data to turn them on and clear the Page break between groups check box. Click OK.

On the Data tab, in the Outline group, click Subtotal.

Verify that Department is still selected for At each change in.

Click the drop-down list button for the Use function list box, and select Count.

Clear the Amount check box in the Add subtotal to list box, then turn on the Trans # check box. Note that when you are adding another subtotal, you have to clear Replace current subtotals.

Turn off Replace current subtotals, and click OK.

Scroll down the data and see how it now appears.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

The worksheet now appears as follows:

Save and close the workbook.

Practice the Skill In this exercise you will practice automatically generating nested subtotals in a worksheet. 1

Open the Video Rentals Database workbook, and save it as Video Rentals Database (subtotals) Student.

Create the first subtotal on the Rental Type column. 2

Select any cell in the Rental Type column.

On the Data tab, in the Sort & Filter group, click Sort.

In the Sort dialog box, click the arrow for Sort by and click Rental Type.

Click Add Level. In the Then by list box, select Video Title, and click OK.

On the Data tab, in the Outline group, click Subtotal.

Click the arrow for At each change in and click Rental Type.

Verify that Sum is selected for the Use function.

In the Add subtotal to list, click Price to turn it on and clear any other check boxes in this list that may be on.

If necessary, click Replace current subtotals and Summary below data to turn them on and clear Page break between groups. Click OK.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Now add a second subtotal row consisting of the number of rows on the Rental Type column. 11

On the Data tab, in the Outline group, click Subtotal. Choose the following options and then click OK. At each change in Rental Type Use function Count Add subtotal to Price Replace current subtotals off

Now add another subtotal, based on the Video Title column. 12

On the Data tab, in the Outline group, click Subtotal. Choose the following options and then click OK. At each change in Video Title Use function Sum Add subtotal to Price Replace current subtotals off

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

With the subtotals created, try expanding and collapsing the detailed rows of data. First suppress the display of all detailed data. 13

Click the

in the outline section.

Notice how these subtotals quickly give you a statistical review of the data. The worksheet now appears as follows:

Save and close the workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Grouping and Ungrouping Data Creating an outline of an Excel worksheet using automatic subtotals is a very useful feature, but only if the data is structured consistently. However, not all worksheets have the data designed as a database where there is a clear break from one group of rows or columns to the next group. For some worksheets, you will need to insert the summary functions manually where they are needed instead of having Excel automatically choose where they are placed. To create an outline of a worksheet using this manual grouping method, your worksheet must adhere to these guidelines: •

The worksheet must contain summation formulas, including SUM, SUBTOTAL, or simple addition (+) operators.

•

All summary formulas must be in the same direction. For example, all =SUM formulas must add together cells above or to its left in a single worksheet. If one or more add together other cells to the right or below as well, Excel will not be able to create the outline for you.

•

While Excel assumes the direction of the summary formulas is above or to the left, you can override these assumptions by changing the settings. On the Data tab, in the Outline group, click the Dialog box launcher to display and change the following dialog box:

•

You can only have one outline for each worksheet.

If any of these conditions is not met, Excel may not complete the task and you will have to create the outline manually.

Learn the Skill This exercise demonstrates the use of outlining. 1

Open the Smal-Mart Sales workbook and save as Smal-Mart Sales Grouping - Student.

Before this worksheet can be outlined, summary totals must be added for each month of data. Insert a new row between the last April row and the first May row. 2

Scroll down and select row 67. On the Home tab, in the Cells group, click Insert.

Enter the following: Cell Value or Formula A67 Subtotal B67 =SUBTOTAL(2,B4:B66) F67 =SUBTOTAL(9,F4:F66)

Now insert a new row between May and June, and at the end of June. 4

Repeat step 2 to insert a new row at row 137, and enter the following: Cell Value or Formula A137 Subtotal B137 =SUBTOTAL(2,B68:B136) F137 =SUBTOTAL(9,F68:F136)

6265-2 v1.00 © CCI Learning Solutions Inc.

4.5

Lesson 1 5

Microsoft® Office Excel 2007 – Level 3

Enter the following at the bottom of the data: Cell Value or Formula A210 Subtotal A211 Total B210 =SUBTOTAL(2,B138:B209) B211 =SUBTOTAL(2,B4:B209) F210 =SUBTOTAL(9,F138:F209) F211 =SUBTOTAL(9,F4:F209)

Now have Excel automatically create the outline for the worksheet. 6

Select a cell inside the data range, such as C9.

On the Data tab, in the Outline group, click the Dialog box launcher to display the Settings dialog box. Alternatively, on the Data tab, in the Outline group, click the arrow for Group and then click Auto Outline.

With the top two check boxes turned on, click Create .

Collapse and expand the worksheet using the outline buttons. 9

Click the

to the left of the column header row.

The outline buttons are only available using the mouse.

The worksheet has collapsed to show only the single grand total. Now display the subtotals and the grand total. 10

Click the

at the left of the outline section.

Now show the details of the first and third subtotals. 11

Click the

to the left of the row 67 header.

Click the

to the left of the row 210 header. Scroll up and down the worksheet to see the data displayed.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools The completed worksheet should look similar to the following:

These details can be collapsed back as well. 13

Click

at the left of the row 67 header.

The entire worksheet can be expanded back to full size as well. 14

Click the

Save the workbook.

On the Data tab, in the Outline group, click the arrow for Ungroup, and click Clear Outline.

at the top left side of the outline section.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

The groups have now been removed. 17

Close the workbook and discard the changes.

Practice the Skill In this exercise, you will create and use a unique signature for replies and forwarded messages. 1

Open the Tires R Us workbook, and save as Tires R Us - Student.

First, insert the required sum formulas. 2

Select cell B9, and on the Home tab, in the Editing group, click AutoSum. Verify the range is B6:B8 and press .

Repeat step 2 for the cells B13, B17, and B21.

Copy the SUM formulas created in steps 2 and 3 and paste each of them across the remaining cells in the Quarter 1, Quarter 2, Quarter 3, and Quarter 4 rows. The worksheet should similar to the following:

Select cell E6, and on the Home tab, in the Editing group, click AutoSum. Verify the range is B6:D6 and press .

Repeat step 2 for the cells I6 and L6.

Copy the SUM formulas created in steps 5 and 6 and paste them down their respective columns to row 21.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Select cell M6, and on the Home tab, in the Editing group, click AutoSum. Verify the cells included are E6, I6, and D6 and press .

Copy the SUM formula and paste it to the cell range M7:M21.

Select cell B22, and on the Home tab, in the Editing group, click AutoSum. Copy and paste this formula across the cell range C22:M22. The worksheet should now appear as follows:

Some cells have a green triangle at the top left of the cells to indicate the formula is different here than in other areas of the worksheet. When you click one of these cells, it shows a Smart Tag Action icon. They can be suppressed because the formulas are correct.

Select the cell range E9:I21.

Click Smart Tag Action appearing to the left of cell E9, and click Ignore Error.

Now create outlines for each Quarter (three rows of quarterly data). 13

Select cells B6 to B8. Then on the Data tab, in the Outline group, click Group. In the Group dialog box, verify Rows is selected and click OK.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

If you select a range of cells to group, Excel asks if you intend to group by Row or Column. If you select entire rows or columns, Excel will not need to ask. 14

Repeat step 13 for the rows 10 to 12, 14 to 16, and 18 to 20 The first level of groups has now been created at the left side of the worksheet.

Repeat step 13 for rows 6 to 21.

Create outlines for each type of tire, across the columns at the top of the worksheet. 16

Select cells B16:D16, then on the Data tab, in the Outline group, click Group.

In the Group dialog box, click Columns and click OK.

Repeat steps 16 and 17 for columns F to H, and columns J to K.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools

Repeat steps 16 and 17 for columns B to L.

Click both

in the outline section.

Now display the first level of sum totals for each type of product. 21

Click the

at the top of the outline section.

Now collapse these subtotals back to the grand total, and display the sum totals for each quarter of the year. 22

Click the

at the top of the outline section.

Click the

at the left of the outline section.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Now show both sets of subtotals. 24

Click the

at the top of the outline section.

The worksheet now appears as follows:

Now suppose you want to see the sales for each month of Quarter 2 only. 25

Click the

to the left of the Quarter 2 line.

The same steps can be followed for the columns as well. 26

Click the

above the Industrial Total column to show the details for these columns.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools

The entire worksheet can be expanded back to full size as well. 27

Click both

in the outline section.

Groups can also be removed. 28

Select cells F7 to H7, then on the Data tab, in the Outline group, click Ungroup. In the Ungroup dialog box, select Columns and click OK.

Select rows 14 to 16, and on the Data tab, in the Outline group, click Ungroup.

Collapse the worksheet to only show the subtotals (for the cells that are still grouped).

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

Click the

at the top and left side of the outline section.

Save and close the workbook.

Converting Text to Columns 2.3 3.7

Excel has a powerful data importing capability to convert data created by external programs into the internal Excel data file format. Excel has extended this capability even further with a feature that enables you to separate text apart into its component words, provided there is a marker that can be used to identify the end of one word from the start of the next word. This marker is referred to as a delimiter. The most commonly used delimiters are commas, spaces, and the tab character. The end-of-line and paragraph markers are rarely used as delimiters because they usually indicate the end of the row. Excel enables you to select any of these delimiters individually or combined together. A common use to convert text into separate columns is to separate people’s names into their first and last names. Note that you would not use this feature to separate a date value into its component month, day, and/or year values. To accomplish this, you should use the built-in date functions MONTH(), DAY(), and YEAR().

Learn the Skill This exercise will demonstrate the use of converting text into individual columns. 1

Open Famous People 20C, and save as Famous People 20C - Student.

Select cells A2:A20.

On the Data tab, in the Data Tools group, and click Text to Columns. The Convert Text to Columns Wizard window now displays. In the first step, you need to identify whether delimiter characters are used between the words, or the words begin at the same (fixed) position for each row.

6265-2 v1.00 © CCI Learning Solutions Inc.

Using Data Tools

Lesson 1

Confirm that the Delimited option is selected, and click Next. In this exercise, the first name is separated from the last name by a comma, and the years are separated from the name by one space. The Delimiters option must be set to look for these characters in the text.

Click Comma and Space to turn them on. Click any other delimiters to turn them off.

If necessary, click Treat consecutive delimiters as one to turn it on.

Click Next.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

In the last step, you can further refine each column to identify them as text or date values, or other types of data that you will let Excel decide is the best fit. 8

Select the third column, click the Text button in the Column data format area, and then click Finish. The results now display. There are some inconsistencies in the columns because some of the names have middle names, while others do not. In practice, you would need to insert a new column to accommodate this data.

Save and close the workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Using Data Tools

Lesson Summary In the lesson you looked at different tools to manipulate data to display specific information, including finding or replacing text, pasting special items into a worksheet, grouping or outlining data, summarizing data, and converting text to columns. You should be able to: 

use AutoFill to set up a series of data



find and replace data in a worksheet



use Paste Special to create different types of data



create groups from the data in the worksheet



create subtotals for specific groups of data in the worksheet



summarize data



convert text to columnar formats

Review Questions 1.

Explain the difference between using the Copy and AutoFill features.

How would you set up the values if the AutoFill series is not a common trend such as weekdays, months, or years?

What items can Excel search for in a worksheet?

Give an example of when you would use Replace versus Replace All in a worksheet.

Give at least two examples of when you might use Paste Special.

What process does consolidating data refer to?

What process would you need to perform before creating subtotals?

What does creating multiple subtotals refer to?

When might you use the Outlining feature?

10. How does the Sum function differ from the Subtotal function? 11. Give an example of when you might want to convert text to columns.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 1

Microsoft® Office Excel 2007 – Level 3

6265-2 v1.00 © CCI Learning Solutions Inc.

Microsoft Business Certification Series ®

Lesson 2: Preparing Online Documents Lesson Objectives In this lesson, you will look at how to work with data in worksheets for distribution to an intranet or the Internet using features such as validating data, hyperlinks, or templates. On successful completion of this lesson, you should be familiar with: 

how to validate data



what a hyperlink is



removing duplicate rows of data



how to create, modify or remove a hyperlink



using pick lists



how to save a worksheet in a HTML format



working with existing templates



how to save a range of cells in a HTML format



creating and editing your own template



how to publish a worksheet to the Internet



saving your template in different locations

Lesson 2 Buttons The following command buttons are covered in this lesson: Data tab

Insert tab

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Validating Data 1.2

Electronic spreadsheets first appeared as very crude blank sheets. You could enter any type of value in any cell, and the spreadsheet would attempt to cope with it. For example, you may expect a numeric value in a cell, but Excel would accept a text string in it. If that cell is subsequently used in a calculation, Excel treated that cell as if it contained the value 0 (zero). This flexibility (or described another way, willingness of the spreadsheet to accept anything anywhere without many complaints) made spreadsheets very popular with novice computer users. However, this flexibility often results in undesirable side effects when wrong values are entered into certain cells. For example, the upper case letter “O” can be mistakenly entered instead of the number “0”, and the lower case “l” can be mistaken for the number “1”. Excel has the ability to prevent this with the Data Validation feature. You can display a helpful message whenever the user selects that cell. You can also display an error message if an incorrect value is entered. You can choose from different data types as a validation check: Any value

The default setting for all cells, which deactivates any data validation. However, you can use it to display an input message without validating any data entry.

Whole number

Only allow numeric values without any decimal digits.

Decimal

Allow any numeric value.

List

Allow selection from a defined list of values in a cell range. This is also known as a pick list.

Date

Only allow date values. The lowest date value is January 1, 1900.

Time

Only allow valid time values.

Text length

Allow only the specified number of text characters. This validation will not permit any numbers, dates, or time values to be entered.

Custom

Enter a formula to validate the data being entered into the cell. The result of the formula must be a True or False value.

Learn the Skill This exercise will demonstrate how to use the Data Validation feature. 1

Open the Metric Conversion workbook and save as Metric Conversion - Student.

Select cell B2. On the Data tab, in the Data Tools group, click Data Validation.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Preparing Online Documents 3

Click the arrow for Allow, and click Decimal.

Click the arrow for Data and click greater than or equal to.

In the Minimum text box, type: 0 (zero) and click OK.

Now try entering an invalid loan amount and observe the results. 6

Select cell B2, type: -2 and press

Click Cancel.

Use a maximum value data validation for the next conversion row. 8

Select cell B3. On the Data tab, in the Data Tools group, click Data Validation.

In the Allow list box, click the drop-down list and select Decimal.

In the Data list box, click the drop-down list and select less than or equal to.

In the Maximum text box, type: 1000 and click OK.

Select cell B3, and type: 1200 and press

Click Cancel.

Now enter a different data validation for the temperature conversion cell. 14

Select cell B4. On the Data tab, in the Data Tools group, click Data Validation.

Click the arrow for the Allow list box, and click Decimal.

Click the arrow for the Data list box, and click between.

In the Minimum text box, type: 10.

In the Maximum text box, type: 100 and click OK.

Now test out the validation check. 19

In cell B4, type: 0 and press

Click Retry. In cell B4, type: 99 and press

Save and close the workbook.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

. .

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Practice the Skill This exercise will give you more practice in using the Excel data validation feature. 1

Create a new worksheet, save as Data Validation - Student and enter the following values:

First set up the cell for a whole number validation, with default messages. 2

Select cell B3. On the Data tab, in the Data Tools group, click Data Validation.

In the Settings tab, click the arrow for Allow. Click Whole number.

If necessary, click the arrow for Data option list, and select between.

In the Minimum text box, type: 10

In the Maximum text box, type: 20

Click OK.

Try entering the following values into cell B3: 25, 0, -10, abc, June 1 and 12.0. Click Retry to continue trying other input values.

If necessary, click Cancel or press

to remove any remaining invalid entry in the cell.

Now set up another cell for a decimal number validation, with a customized error message. 10

Select cell B4. Then on the Data tab, in the Data Tools group, click Data Validation.

Click the arrow for Allow and click Decimal.

Click the arrow for Data and then click less than or equal to.

In the Maximum text box, type: 20. If the user attempts to enter a value that does not meet these validation criteria, Excel displays a message box with a generalized error message. If you prefer, you can customize this message to help the user understand the reason for the error.

Click the Error Alert tab.

In the Title text box, type: Wrong value entered!

In the Error message text box, type: The value must be a number less than or equal to 20.0.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Preparing Online Documents

Click OK.

Try entering the following values into cell B4: 25, 20.00001, 12.0, -100005, abc, June 1. Click Retry to continue trying other numbers.

Now try changing how errors are handled for cell B4. 19

Select cell B4. On the Data tab, in the Data Tools group, click Data Validation. If necessary, click the Error Alert tab. The error type is handled as an error, warning, or information. If you want Excel to treat erroneous data as errors, it will refuse to accept any values until a correct one is entered. The other two options, however, will allow the user to retain the invalid data in the cell.

Click the arrow for Style, and click Warning. Click OK.

In cell B4, type: abc and press

Click Yes.

Notice that Excel will now accept erroneous data with the Yes button, and the other two buttons will reject the new entry. Now modify the data validation check to report the error as an information item. That is, Excel will not request the value be changed. 23

Select cell B4. On the Data tab, in the Data Tools group, click Data Validation. If necessary, click the Error Alert tab.

Click the arrow for Style, click Information and then click OK.

In cell B4, type: def and press

Click OK.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Another way of helping the user is to display an input message on the worksheet whenever this cell is selected. Like the Error Alert settings, these settings are optional. 27

Select cell B5. On the Data tab, in the Data Tools group, click Data Validation. Click the Settings tab.

Click the arrow for Allow and click Date. Click the arrow for Data, and click not between.

In the Start date text box, type: June 1, 2009.

In the End date text box, type: October 31, 2009.

Click the Input Message tab.

In the Title text box, type: Date Field and in the Input message text box, type: Date value must not be between June 1 and October 31, 2009.

Click OK.

Notice the message that appears whenever this cell is selected. 34

In cell B5, try entering the following values: June 1, 2009 October 23, 2009 November 1, 2009 January 1, 2008

Now try the text length validation, using parameter values from other cells. 35

Select cell B6. On the Data tab, in the Data Tools group, click Data Validation. Click the Settings tab.

Click the arrow for Allow and click Text length. If necessary, click the arrow for Data, and click between.

Click Minimum, and then click Collapse. Select cell D6 on the worksheet and click Restore. An alternative to using Restore is to press

Click Maximum, click Collapse, select cell E6 on the worksheet and click Restore. Click OK.

In cell B6, try entering the following values: Word Sentence 12 123 12345

Select cell D6, type: 3 and in E6 type: 4.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents 41

Lesson 2

In cell B6, try entering the following values again: Word Sentence 12 123 12345 The completed worksheet will appear similar to the following:

Save and close the workbook.

Using Pick Lists A type of data validation is the pick list for a worksheet cell. Like the other types of data validation, the pick list can ensure only valid information is entered by allowing the user to select only from a predefined list of items. The user cannot select any other value. It is ideally suited to text data because of the extreme difficulty to apply arithmetic comparison operators (such as greater than) for text. You cannot use pick lists consisting of numbers and dates.

Learn the Skill This exercise will demonstrate how to use a pick list. 1

Open the Country Pick List workbook and save as Country Pick List - Student.

Select cell B19, and type: =VLOOKUP(A19,A1:B16,2).

Select cell A19. On the Data tab, in the Data Tools group, click Data Validation. If necessary, click the Settings tab.

Click the arrow for Allow and click List.

In Source, click Collapse and then select cells A1:A16 in the worksheet. Click Restore. The cell range that acts as the source for the data validation must be on the same worksheet.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2 6

Microsoft® Office Excel 2007 – Level 3

Click OK. An arrow now appears next to cell A19.

Click the arrow and select a country abbreviation. The completed worksheet should appear similar to the following:

Save and close the workbook.

Practice the Skill This exercise will provide more practice in creating and using a pick list. 1

Open the Electronic Waybill workbook, and save it as Electronic Waybill - Student.

Set up the data validation for the Ship Via cell. 2

Select cell E4. On the Data tab, in the Data Tools group, click Data Validation. If necessary, click the Settings tab.

Click the arrow for Allow and click List.

In Source, click Collapse, and then select cells I3:I6 in the worksheet. Click Restore.

Click the Input Message tab.

In Title, type: Ship Via. In Input message, type: Valid options are truck, rail, air freight, ocean transport.

Click the Error Alert tab.

In Title, type: Ship Via. In Error message, type: Valid options are truck, rail, air freight, ocean transport. Click OK.

Now set up the data validation for the Size cell. 9

Select cell E6 and repeat steps 2 to 8 using the appropriate values for steps 6 and 8. Arrows now appear next to cells E4 and E6 when they are selected.

Select cell E4, type: Taxi and press

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents 11

Click Cancel, then click the arrow to select an option of your choice.

Select cell E6, and attempt to enter invalid data.

Click Cancel, then click the arrow to select an option of your choice.

Lesson 2

The completed worksheet should appear similar to the following:

Save and close the workbook.

Removing Duplicate Rows

1.2

The advanced filter feature has the ability to select and display only unique data rows from a data table. Excel now also has the ability to remove duplicate data rows from a table, to help with finding errors in the data when it was being entered. This feature is another way of validating the quality of the data in the worksheet. This feature can also be used as a quick way of filtering data, as long as you remember to undo the removal and do not save the workbook.

The rows to be deleted can be selected not only on all columns, but also on selected columns. For example, a data table containing customer numbers and names can be scanned to find any rows where the customer number is listed two or more times. If no rows are removed in the process, then you can be sure that these numbers are unique.

Learn the Skill This exercise will demonstrate how to use the remove duplicates feature. 1

Open the Address List workbook and save as Address List (duplicates) - Student.

Scan through the data to see if all rows are unique. 2

Select any cell in the cell range A1:G15.

On the Data tab, in the Data Tools group, click Remove Duplicates.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2 4

Microsoft® Office Excel 2007 – Level 3

Click OK. A message box is displayed with the message: “No duplicate values found.”

Click OK to close the message box.

Now remove any duplicate data rows so that the combination of city and province/state appears only once. 6

On the Data tab, in the Data Tools group, click Remove Duplicates.

Click Unselect All.

Click the check boxes for the City and Prov/State columns to turn them on, and click OK. A message box is displayed with the message: “2 duplicate values found and removed; 12 unique values remain.”

Click OK to close the message box.

Save and close the workbook.

Practice the Skill This exercise will give you more practice in removing the duplicate data rows based on selected columns. 1

Open the Video Rentals Database workbook and save as Video Rentals Database (duplicates) Student.

Select any cell in the data range.

First check the data to see if there are any duplicate data rows — this is one test to see if there is any erroneous data. 3

On the Data tab, in the Data Tools group click Remove Duplicates.

Click OK.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents 5

Lesson 2

Click OK to close the message box.

From when a video title is first available for sale, the title will progress through several stages starting at short rentals at the highest price down to the weekly rental rate. 6

On the Data tab, in the Data Tools group, click Remove Duplicates.

Click Unselect All.

Click the check boxes for the Rental Type, Video Title, and Price columns to turn them on, and click OK.

Click OK to close the message box.

Another search can be done by undoing the duplicates removal. Now get a list of all customers that have rented a video during this time period. 10

On the Quick Access Toolbar, click Undo.

Repeat steps 6 to 9 on the Customer No column. Note that the date, rental type, video title, and price are not relevant in this list. That is, you cannot tell from this list whether each of the customers listed here had rented only once or more than once. Further, if they did rent more than once, you cannot assume the date, rental type, video title and price are for the first or last row, or one somewhere in the middle.

Now do another search to find all unique rental types and the rental prices charged. 12

On the Quick Access Toolbar, click Undo.

Repeat steps 6 to 9 on the Rental Type and Price columns.

Save and close the workbook.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Circling Invalid Data Earlier in this lesson, you learned how to use data validations to help ensure that only correct data is entered into cells. You need to be aware the validation check only works when data is entered directly into a cell. That is, Excel will not validate any data that is currently in a cell at the time of entering the validation check. Also, if the cell value is calculated using a formula, the data validation check will not detect an incorrect value. You can use an auditing feature in Excel to quickly find all cells that fail any validation checks that have been set up for those cells.

Learn the Skill This exercise will demonstrate how to use the circle invalid data feature. 1

Open the Union Ballot workbook and save it as Union Ballot - Student. This worksheet is used to record nominations of union members for a board position. One rule is that the member must be a member in good standing for 10 or more years.

Enter the following values: Cell Value A4 1 A5 3 5 A6 A7 7 A8 9 You will notice that you encountered no errors or problems entering this data.

Now try entering a seemingly valid number into one of the cells. 3

Select cell C4, type: 8 and press

The data validation error message “Member must have 10 or more years of service to qualify” appears. Yet the previous value of 7 is also clearly in violation of this validation check. Perhaps trying another cell may resolve this problem. 4

Click Cancel.

Select cell C6, type: 7 and press

The data validation error message appears again. Rather than trying each cell to determine whether the data is correct or not, you can use the circle invalid data feature in Excel. 6

Click Cancel.

On the Data tab, in the Data Tools group, click the arrow for Data Validation and then click Circle Invalid Data. The worksheet should now appear with circles around the cells containing invalid data:

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

On the Data tab, in the Data Tools group, click the arrow for Data Validation and click Clear Validation Circles.

Save and close the workbook.

Working with Templates When you create a new workbook in Excel, you choose which workbook template to use as a model. By using a template, you can save a considerable amount of time in creating commonly used workbooks. For example, Excel includes sample templates for an expense statement, invoice, and purchase order. When you create a workbook without selecting a template, Excel will use the blank workbook template by default. Excel template files are stored in a separate folder with an Excel template (.xltx) extension. When you enter data into a template and attempt to save it, Excel will not overwrite the template file but will display the Save As dialog box and expect you to save it as a normal (.xlsx) workbook file.

Learn the Skill This is a review exercise on how to use Excel templates. You must have access to the Internet before proceeding. 1

Click the Office Button and then click New.

In the Microsoft Office Online list, click Invoices. Then click Sales invoice (Blue Gradient design) and click Download.

When prompted with a message about templates being available to customers running genuine Microsoft Office software, click Continue. Excel now displays a blank sales invoice form.

You will now enter some information for a new sale and save the invoice. 4

In the Name field type: Cash Sale.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

5.4

Lesson 2

Microsoft® Office Excel 2007 – Level 3

In the first row of the main body of the invoice, in Qty type: 3, in Description type: Widgets, and in Unit Price type: 5.

Save as Sales Invoice - Student and then close it. The data you added does not alter the template file but was created in a new workbook using the styles of this template.

Practice the Skill In this exercise, you will create a workbook using a built-in template. 1

Click the Office Button, and click New to display the New Workbook dialog box.

In the Templates list, select Installed Templates.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents 3

Lesson 2

Select the Expense Report template and click Create.

You will now enter some information for a new sale and save the invoice. 4

Enter the following data: Purpose: Sales trip to meet with client Name: <your name> Position: Account Manager Department: Sales & Marketing Manager: John Brown Date: press + Description: Travel Hotel: 120.00 Fuel: 45.00 Meals: 50.00 The completed expense report should look similar to the following:

Save the workbook as Expense Report1 - Student and close it.

Creating a Template The advantages of using templates becomes more apparent when you create a template of your own, complete with labels, formulas, charts and other Excel functions and objects. Once you have created a template, you will be able to open a new workbook with only the data missing. Using templates can make regular reporting a simple task.

Learn the Skill In this exercise, you will create and use a workbook template. 1

Create a new blank workbook and enter the following data:

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

5.4

Lesson 2

Microsoft® Office Excel 2007 – Level 3

In cell B8, type: =SUM(B4:B7).

In cell E4, type: =SUM(B4:D4).

Copy the formula in cell B8 across to cells C8:E8. Copy the formula in cell E4 down to cells E5:E7.

On the Quick Access Toolbar, click Save. In the File name field, type: Sales Report Template and make sure the.xlsx has been removed.

Click the arrow for Save as type and then click Excel Template (*.xltx). The Save As dialog box automatically sets the Save in location to the Templates folder. This is a standard folder that all Microsoft Office programs share.

Click Save and then close the file.

Practice the Skill In this exercise, you will create a workbook template. 1

Open the Loan Payment Calculator workbook.

Click the Office Button and then click Save As.

Click the arrow for Save as type and click Excel Template (*.xltx). Then click Save.

Close the Loan Payment Calculator template workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

Using User-Defined Templates Once you create a user-defined template, you can reuse it as many times as you want. Note that Excel stores the templates that you create under the Windows logon ID that was used to create it. Therefore only that person (or whoever uses that ID to log on) will be able to access that template workbook. If you want to share it with others (assuming that they log onto their computers with their own individual IDâ&#x20AC;&#x2122;s), you will have to move or copy it into the central templates folder where Excel stores its own template files.

Learn the Skill This exercise demonstrates how to use a template to create a workbook. 1

Click the Office Button and then click New.

In the Templates list, click My templates.

In the New dialog box, double-click Sales Report Template. Notice the default workbook name is Sales Report Template1.

Fill in the range B4:D7 with values of your choosing.

Save the workbook as Sales Report from Template - Student and close the workbook.

Practice the Skill In this exercise, you will use a workbook template you created in an earlier exercise. 1

Click the Office Button and then click New. In the Templates list, click My templates.

In the New dialog box, click Loan Payment Calculator and then click OK. A new workbook with the title Loan Payment Calculator1 now appears.

Enter the following values: Cell Value B3 190,000 .05 B4 B5 25

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

The completed worksheet should appear as follows:

Save as Loan Payment Calculator v1 - Student and close the file.

Editing Templates You can change your user-defined templates at any time as you would with a workbook. There is only one problem: these files are stored in a hidden folder under the user ID you used to log into Windows. By default, these files are stored in the following location: C:\Documents and Settings\<user ID>\Application Data\Microsoft\Templates If you are using Windows Vista as the operating system, the location for templates is C:\Users\<user ID>\AppData\Roaming\Microsoft\Templates. The <user ID> is the ID you used to log into Windows. You may want to check with your system administrator if you are on a network to determine where the templates may be saved if you do not find them in this location, or to find out where templates that are shared by other people in your office are stored. One way of finding the location of where your template files are stored is to look in the Save As dialog box when saving your template workbook; click the Save in drop-down and make a note of the folder hierarchy.

Another way to find this location is to trick Excel into finding this hidden folder for you. Alternatively, if you had recently created or modified the template file, you can also find your template file when you click on the My Recent Files link at the left side of the Open dialog box.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

Excel does not store its own built-in templates in your hidden templates folderâ&#x20AC;&#x201D;it uses a normal folder (but still cryptically named) where all users of that computer can access: C:\Program Files\Microsoft Office\Templates\1033

Learn the Skill This exercise demonstrates how to edit a template file. 1

Click the Office Button, and click Open.

Click My Recent Documents in the location bar area.

The number of files or folders displayed in this screen will vary depending on what was recently accessed on the system you are using. 3

Select the Sales Report Template.xltx template and click Open.

Now make a change to the template. 4

Select cells A1:E1. On the Home tab, in the Alignment group, click Merge & Center.

Select cell B4 to make it the active cell. After you have completed your changes to the template, you will save it again as a template file in the Templates folder.

Save and close the Sales Report Template template workbook.

Now test out the modified template. 7

Click the Office Button, and click New.

With the Blank and recent option selected in the New Workbook dialog box, select the Sales Report Template.xltx and click Create. A new workbook is created with this modified template.

Close the Sales Report Template1 workbook without saving it.

Practice the Skill This exercise will give you more practice in editing a template workbook, using My Recent Documents to locate the template. 1

Exit from Excel.

Start up Excel again.

Click the Office Button, and click Save As.

Now to trick Excel into displaying the location where the templates workbooks are stored.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2 4

Microsoft® Office Excel 2007 – Level 3

Click the Files of type drop-down, and click Excel Template (*.xltx). With the Templates folder now displayed, you can open the template workbook to make changes to it.

Click Cancel to close the Save As dialog box.

Click the Office Button, and click Open.

Select the Loan Payment Calculator.xltx template and click Open.

Now make changes to the formatting of the cells. 8

In cell A12, type: Total Annual Payments.

In cell B12, type: =B8*12.

Select cell B12 again. On the Home tab, in the Number group, click the Number Format drop-down and select Currency.

Select cell B3 to position the cursor there.

Save and close the template workbook.

Now test out the modified template. 13

Click the Office Button, and click New.

Select the Loan Payment Calculator template in the New dialog box and click Create.

Enter the following values: Cell Value B3 90,000 B4 .065 B5 15 The workbook should now appear similar to the following:

On the Quick Access Toolbar, click Save. Because you had just used the My Recent Documents link, the Open dialog box will still be set to display the shortcuts in that folder. You will have to select the correct folder to store your Excel document.

Select a different folder as directed by your instructor.

Save as Loan Payment Calculator 2 - Student and close the workbook.

Deleting Templates Templates are just like Excel documents — they are simply files. As described above, the only difficulty is that user-defined templates are stored in a hidden folder. You can use Excel to open the folder for you or use Windows Explorer to find and delete the template file.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

Learn the Skill This exercise demonstrates how to delete a template workbook. As with the previous exercise, you will trick Excel into displaying the Templates folder for you. 1

Open the Sales Report from Template - Student workbook.

Click the Office Button and then click Save As. Click the arrow for Save as type and click Excel Template (.xltx).

Select the Sales Report Template template.

In the Save As dialog box, click Delete. A message box appears with the question: “Are you sure you want to send ‘Sales Report Template.xlt’ to the Recycle Bin?”

In the Confirm File Delete message box, click Yes.

In the Save As dialog box, click Cancel.

Close the Sales Report from Template - Student workbook without saving it.

Now check to see if this template still appears in New dialog box. 8

Click the Office Button and then click New. In the Templates list, click My templates. Notice how your new template no longer appears in the My templates section of the New dialog box. The reference for it still appears in the Recently Used Templates area although it is no longer accessible.

In the New dialog box, click Cancel.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Practice the Skill This exercise demonstrates how to delete an Excel template using the Windows Explorer. 1

Start up Windows Explorer. Then select the Local Disk (C:) icon in the Folders list. If Windows Explorer displays the message, “These files are hidden. This folder contains files that keep your system working properly. You should not modify its contents.”, ignore it and continue the exercise.

Click Search in the Explorer toolbar.

Under the question “What do you want to search for?”, select All files and folders.

If necessary, click the drop-down for More advanced options. If necessary, click Search hidden files and folders and Search subfolders to turn them on.

Select All or part of the file name, and type: *.xltx.

Click Search.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

After a short wait, the Windows Explorer will display the search results. Your computer may show more or fewer listings of this file name, depending on the number of copies made on your computer.

Right-click the Loan Payment Calculator.xltx file located in the Templates folder. Click Delete. Ensure you delete the Microsoft Office Excel Template file, not just the shortcut.

In the Confirm File Delete message box, click Yes.

Close the Windows Explorer.

Using Hyperlinks 2.3

Information can now be easily accessed in the Internet and various intranets set up within organizations, even though different types of computers are being used. The information is stored in simple documents containing text, graphics, and hyperlinks to other documents. A hyperlink can be text or picture on a page that jumps or links you from one location to another. This location may be on the same document, another document, a document or page on another Web site, or to an e-mail address.

Inserting Hyperlinks By clicking a hyperlink, you can display the referenced document. These hyperlinks are stored in the form of a Uniform Resource Locator (URL), which is the unique address for this document on the Internet or the intranet. These URLs can be stored in an Excel worksheet or workbook, and used as hyperlinks to jump to other documents in the Internet or intranet, other Excel workbooks, or other Microsoft Office documents. To insert a hyperlink, use one of the following methods: •

On the Insert tab, in the Links group, click Hyperlink; or.

•

press

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Learn the Skill This exercise demonstrates how to create and use hyperlinks to link to an Excel workbook. 1

Open the Store Chain Summary workbook and save it as Store Chain Summary – Student.

Insert the first hyperlink in cell B6. 2

Ensure cell B6 is selected and then on the Insert tab, in the Links group, click Hyperlink.

Scroll down and select the Northern Store workbook. If necessary, select a different folder in the Look in list where the data files are located, as directed by the instructor.

Click in Text to display and change the contents to: Northern Store.

Click OK.

Now insert a hyperlink to another workbook using the copy and paste method this time. 6

Open the Southern Store workbook.

Select cell A4. On the Home tab, in the Clipboard group, click Copy.

Select the Store Chain Summary - Student workbook as the active window.

Select cell C6. Then on the Home tab, in the Clipboard group, click the arrow for Paste and click Paste as Hyperlink.

Select the Southern Store workbook, and close it.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

The worksheet should appear similar to the following:

Position the mouse cursor over the hyperlink in cell B6 â&#x20AC;&#x201D; the cursor changes to a white pointing hand. Notice that a screen tip will appear with the URL for the Northern Store workbook.

Click the Northern Store hyperlink. The Northern Store workbook is now open on top of the Store Chain Summary workbook.

Now open the Southern Store workbook as well. 13

Select the Store Chain Summary - Student workbook again.

Click the Southern Store hyperlink in cell C6.

Close the Southern Store workbook. Close the Northern Store workbook.

Save the Store Chain Summary - Student workbook, and leave it open.

Practice the Skill This exercise gives you more practice in creating and using hyperlinks in an Excel workbook. 1

Open the Romantic Cruise Lines workbook.

Now create a new workbook that will contain the hyperlinks to the other workbooks. 2

Create a new blank Excel workbook. Save the workbook as Romantic Cruise Lines Hyperlink Student.

In cell A2, type: Sales revenue projections:.

In cell A6, type: 2007 monthly sales:.

Click the Romantic Cruise Lines workbook from the Windows taskbar to make this worksheet active.

Select any cell in the range of A1 to G17, and on the Home tab, in the Clipboard group, click Copy.

Click Romantic Cruise Lines Sales Hyperlink - Student to make this workbook active. Alternatively, on the View tab, in the Window group, click Switch Windows, and then click the required workbook from the list.

Select cell D2. Then on the Home tab and in the Clipboard group, click the arrow for Paste, and click Paste as Hyperlink.

Select and close the Romantic Cruise Lines workbook.

Insert another hyperlink using the Ribbon. 10

Select cell D6. Then on the Insert tab, in the Links group, click Hyperlink.

Scroll down and select the Romantic Cruise Lines Monthly Revenues.xlsx workbook. If necessary, select a different folder in the Look in list where the data files are located, as directed by the instructor. Click OK.

Now manually change the text for the two hyperlinks.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2 12

Microsoft® Office Excel 2007 – Level 3

Move your mouse to cell D2, hold the left mouse button until the mouse pointer changes from a hand to a white cross, then release the left mouse button. Type: Sales projections and press . Alternatively, you can also select a nearby cell that does not contain a hyperlink, then use the cursor keys on the keyboard to select the cell.

Press

to move to cell D6. Type: Monthly revenues and press

The worksheet should appear similar to the following:

Now open the two referenced workbooks. 14

Click the Sales projections hyperlink in cell D2 to open the Romantic Cruise Lines workbook.

Select the Romantic Cruise Lines Hyperlink workbook again.

Click the Monthly revenues hyperlink in cell D6 to open the Romantic Cruise Lines Monthly Revenues workbook.

Close the Romantic Cruise Lines and Romantic Cruise Lines Monthly Revenues workbooks without saving any changes to them.

Save and close the Romantic Cruise Lines Hyperlink - Student workbook.

Modifying and Deleting Hyperlinks Hyperlink addresses are unique, specific locations to a document, whether it is stored on the Internet, the local intranet, or on the local computer. If the referenced document is moved or renamed, then you must change the hyperlink. You may also want to change the text displayed in the worksheet for the hyperlink, or add a custom ScreenTip. When you no longer need the hyperlink, you can remove the reference. The text displayed for the hyperlink remains in the cell. To modify a hyperlink in the worksheet, use one of the following methods: •

Right-click the hyperlink and then click Edit Hyperlink; or

•

move the cell pointer to the cell containing the link and then on the Insert tab, in the Links group, click Hyperlink to display the Edit Hyperlink dialog box. Make the necessary changes and close the dialog box.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

To delete a hyperlink in the worksheet, use one of the following methods: •

Right-click the link and then click Remove Hyperlink; or

•

move the cell pointer to the cell containing the link and then on the Insert tab, in the Links group, click Hyperlink to display the Edit Hyperlink dialog box. Click Remove Link.

Learn the Skill This exercise demonstrates how to modify hyperlinks to Excel workbooks. Set up by making a copy of one of the workbooks. 1

Open the Northern Store workbook, save it as Northern Store - Student and then close it.

Select Store Chain Summary - Student as the active workbook.

Change the text description for the other hyperlink. 3

Right-click the Southern Store hyperlink in cell C6, and then click Edit Hyperlink.

In the Edit Hyperlink dialog box, click Text to display, and change the contents to: Southern Store.

Click OK. The worksheet should now appear similar to the following:

In step 1 above, you created a new Excel workbook containing the Northern Store data. Change the hyperlink to this new document. 6

Right-click the hyperlink in cell B6 and click Edit Hyperlink.

In the Edit Hyperlink dialog box, select the Northern Store - Student file.

Now enter a custom ScreenTip for one of the hyperlinks. 8

In the Edit Hyperlink dialog box, click ScreenTip.

In the Set Hyperlink ScreenTip dialog box, type: This is the link to the Northern Store data file. and click OK.

In the Edit Hyperlink dialog box, click OK.

Position the cursor over the hyperlink in cell B6 to view the ScreenTip.

Test the change to the Northern Store data hyperlink to make sure that it is working properly. 12

Click the hyperlink in cell B6.

Close the Northern - Student workbook.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Now delete one of the hyperlinks. 14

Right-click the hyperlink in cell C6 and click Remove Hyperlink.

Save and close the Store Chain Summary - Student workbook.

Saving Worksheets as HTML You can save your Excel worksheet as a Web page, which can then be published on the Internet or your organization’s intranet. By doing so, you can then share your information with many more people. A Web page is not much more than a simple document containing text and graphics. You can use Microsoft Office applications such as Word and Excel to create very basic Web pages, and link them together with hyperlinks. You can then use Internet browser software like Microsoft Internet Explorer or Mozilla Firefox to display these pages. To create a Web page from your worksheet, you simply save your workbook as a Web Page (*.htm, *.html) file type. Excel automatically makes the necessary conversions to create a page that can be displayed by a Web browser. You can save an entire worksheet as a Web page, or just a selected range of cells.

Learn the Skill This exercise demonstrates how to save an Excel worksheet as a web page. 1

Open the Humongous Holdings workbook.

Click the Office Button, point at Save As, then click Other Formats. Click the arrow for Save as type and click Web Page (*.htm, *.html). Alternatively, you can also initially select any type of workbook in the Save As menu, then change the Save as type to Web Page. Notice that the familiar Save As dialog box has changed due to the additional options for Web page publishing.

6265-2 v1.00 © CCI Learning Solutions Inc.

5.4

Preparing Online Documents

Lesson 2

You should only create a Web page from one worksheet at a time. If you need to convert the entire workbook into one Web page, you can click Save in this dialog box. To proceed with converting the currently active worksheet, click Publish. 3

Click Publish. In this dialog box, you can select a worksheet or a cell range to convert to a Web page.

If necessary, click the arrow for Choose, and click Items on Sheet1.

In the Choose list, click All contents of Sheet1.

Click Browse.

In the Publish As dialog box, select a different folder where the Web page will be stored as determined by your instructor if necessary.

Verify the File name text box contains: Humongous Holdings and click OK.

In the Publish as Web Page dialog box, click Open published web page in browser to turn it on, if necessary.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

If your computer does not have a Web browser installed, Open published web page in browser will be disabled. 10

Click Publish.

Close the Internet Explorer. You should always test the Web page on other Web browsers before publishing it to the Internet. The Web page may appear slightly different on other browsers.

Leave the Humongous Holdings workbook open.

Practice the Skill This exercise gives you more practice in creating a web page from an Excel workbook. 1

Open the Potato Chipper workbook.

Click the Office Button, point at Save As, and then click Excel Workbook. Click the arrow for Save as type and click Web Page (*.htm, *.html).

Click Publish.

If necessary, click the arrow for Choose, and click Items on Sheet1.

If necessary, click Browse and select a different folder as directed by your instructor, and click OK.

If necessary, click Open published web page in browser to turn it on.

Click Change.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents 8

In the Set Title dialog box, enter: The Potato Chipper as the Title and click OK.

In the Publish as Web Page dialog box, click Publish.

Lesson 2

Internet Explorer now starts up and displays the completed Web page.

Scroll down to view the rest of the web page.

Close the Internet Explorer.

Close the Potato Chipper workbook and discard any changes made to it.

Saving a Range of Cells as HTML Instead of an entire worksheet, you can save just a selected range of cells as a Web page.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Learn the Skill This exercise demonstrates how to create a Web page for a range of cells from an Excel worksheet. 1

Select the Humongous Holdings workbook again.

Now save only a selected range of cells from the worksheet as a Web page. 2

Click the Office Button, point at Save As, and click Excel Workbook. Click the arrow for Save as type and then click Web Page (*.htm, *.html).

Click Publish in the Save As dialog box.

In the Publish as Web Page dialog box, click the arrow for Choose and click Range of cells.

In the Choose list, click cells A4 to B26. The selected cell range must be a single contiguous block of cells.

Click Change, enter: Humongous Holdings International as the Title, and click OK.

If necessary, click Browse and select a different target folder as directed by your instructor.

Change the File name to Humongous Holdings - Current Year.

In the Publish as Web Page dialog box, click Publish.

Close the Internet Explorer.

Close the Humongous Holdings workbook and discard any changes made to it.

6265-2 v1.00 © CCI Learning Solutions Inc.

Preparing Online Documents

Lesson 2

Practice the Skill This exercise gives you more practice with creating a web page using selected cells from an Excel worksheet. For this exercise, an Excel chart will be used. 1

Open the Potato Chipper workbook.

Click the Office Button, point at Save As, and click Excel Workbook. Click the arrow for Save as type and click Web Page (*.htm, *.html).

Click Publish.

In the Publish as Web Page dialog box, click the arrow for Choose and click Range of cells.

In the Choose list, click cells A17:H37 directly from the worksheet.

Click Change, type: The Potato Chipper and click OK.

Click Browse.

Select a different folder as determined by your instructor.

Select the File name text box, change it to: Potato Chipper - Chart.htm and click OK.

In the Publish as Web Page dialog box, click Open published web page in browser to turn it on, if necessary.

Click Publish. Internet Explorer now starts up and displays the completed Web page.

Close Internet Explorer.

Close the Potato Chipper workbook and discard any changes made to it.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

Saving as a PDF File PDF refers to Portable Document Format. This file format is very popular for sending or distributing information that you want others to view but not be able to change. It can also reduce the size of a large file to a size that is manageable for an intranet or on the Internet. Excel provides the option to create simple PDF files using an add-in product that can be downloaded from the Microsoft Web site. This add-in creates very simple PDFs that can then be sent to others for review. In order to edit or lock out multiple options for a file, you must use a program that can work with PDF files or create PDF files from the original file formats within the PDF program. Take note that when you save a file in a PDF format, it is created using the same options as for printing. For example, if the worksheet is wider than the orientation chosen for the report and you have not adjusted the size accordingly, the PDF file is created for everything that fits on each page as denoted by the page breaks in the workbook. The following exercise should only be performed if you have the Save as PDF or XPS add-in downloaded and installed for Microsoft Office 2007.

Optional Exercise This exercise will demonstrate how to save a workbook into PDF format for distribution. 1

Open the Potato Chipper workbook.

On the Page Layout tab, in the Scale to Fit group, change the Scale to 90%.

Click the Office button and then point at Save As. Click PDF or XPS. If the PDF or XPS option is not listed in the Save As menu, then you must download the Save as PDF or XPS add-in from the Microsoft (or another software vendor) Web site and install it.

Leave the name and options the same, check that the file type is PDF, click Open file after publishing, and then click Publish.

6265-2 v1.00 © CCI Learning Solutions Inc.

5.4

Lesson 2

Preparing Online Documents

Excel opens the PDF file after publishing it in this format using the PDF reader installed on your system. Notice how you cannot change anything in the file other than the view options. 5

Close the reader and then close the file without saving.

Lesson Summary In this lesson, you looked at how to work with data in worksheets for distribution to an intranet or the Internet using features such as validating data, hyperlinks, or templates. You should now be familiar with:  how to validate data

 what a hyperlink is

 removing duplicate rows of data

 how to create, modify or remove a hyperlink

 using pick lists

 how to save a worksheet in a HTML format

 working with existing templates

 how to save a range of cells in a HTML format

 creating and editing your own template

 how to publish a worksheet to the Internet

 saving your template in different locations

Review Questions 1.

Explain how you can use data validation.

How can you prevent duplicate rows of data?

Explain what a pick list is.

Give an example of why you might want to circle specific data.

Give an example of why you might want to create a template of your own.

Provide an example of why you might want to delete a template versus editing it.

Give an examples of when you might insert a hyperlink into a worksheet.

Explain why you might want to save a worksheet in HTML format.

Explain why you might want to save a range of cells only as HTML.

10. How can you turn an Excel worksheet into a PDF file?

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 2

Microsoft® Office Excel 2007 – Level 3

6265-2 v1.00 © CCI Learning Solutions Inc.

Microsoft Business Certification Series ®

Lesson 3: Collaborating with Others Lesson Objectives In this lesson, you will look at different ways you can share data with others to collaborate on changes. You will also look at how to add protection on a worksheet or the entire workbook, as well as how to remove personal information and mark workbooks as final. On successful completion of this lesson, you will be familiar with the following: 

creating a shared workbook



protecting files using digital signatures



resolving shared workbook conflicts



changing workbook properties



working with protected shared workbooks



tracking changes on shared workbooks

using the Document Inspector before distributing workbooks



reviewing comments and changes



understanding compatibility issues



removing shared use on workbooks



marking workbooks as final



protecting your worksheet and the workbook

Lesson 3 Buttons The following command buttons are covered in this lesson: Review tab

Developer tab

Miscellaneous Items Comment Symbol

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Using Comments 5.1

Comments are similar to placing “sticky” notes in your document. Excel automatically adds the current user’s name (as shown in the Excel Options dialog box) at the top of the comment text box. Multiple users sharing a workbook can then annotate worksheets with their various comments. The final reviewer can then act on each comment and follow up with the originator if necessary. In non-workgroup situations, comments are useful as reminders or to provide detailed information about the formulas used. Spreadsheets typically contain large volumes of numbers, titles and formulas to produce the desired results. Comments will often assist with explanations. The traditional way of adding comments (which still works) is to allocate space on the worksheet to type the comments as free-form text into cells. Embedded comments, however, are better because they can be used when worksheets have a large volume of data tightly packed together. To insert a comment into a worksheet, use one of the following methods: •

Select the cell where the comment will be inserted, then on the Review tab, in the Comments group, click New Comment; or

•

press

•

right-click the cell where the comment will be inserted and then click Insert Comment.

; or

To temporarily display the contents of an individual comment, position the cursor over a cell containing the (Comment Symbol). When you move the cursor away, only the Comment Symbol displays. To force a comment to remain displayed, use one of the following methods: •

Select the cell containing the comment, then on the Review tab, in the Comments group, click Show/Hide Comment; or

•

right-click the cell containing the comment and click Show/Hide Comment.

To display the contents of all comments in a worksheet, on the Review tab, in the Comments group, click Show All Comments. To delete a comment, use one of the following methods: •

Select the cell containing the comment, then on the Review tab, in the Comments group, click Delete; or

•

right-click the cell and then click Delete Comment; or

•

select the cell containing the comment, then on the Home tab, in the Editing group, click Clear, and then click Clear Comments.

Learn the Skill In this exercise you will insert comments into a worksheet. 1

Open the Megatron Entertainment workbook and save it as Megatron Entertainment (comments) Student.

Select cell B9.

On the Review tab, in the Comments group, click New Comment.

In the comment box, type: Bill started work on April 15.

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

The worksheet appears as follows with the comment displayed.

Click anywhere on the worksheet away from the comment. Excel displays a (Comment Symbol) in this cell to remind you that a comment has been inserted there. Otherwise, the comment will be hidden at this time until you place cursor over that cell.

With the current active cell elsewhere on the worksheet, move the cursor over cell B9. The comment box will reappear. It will continue to display until you move the cursor elsewhere.

Select cell B30, and on the Review tab, in the Comments group, click New Comment.

In the comment box, type: Sales are doing well at 16% of total sales.

Click elsewhere on the worksheet. If necessary, you can display all comments on a worksheet at one time. You may want to do this if you are seeing the worksheet for the first time, or many comments have been added since you last opened the workbook.

Select cell B30 again, then on the Review tab, in the Comments group, click Show/Hide Comment. Click elsewhere on the worksheet. The comment can then be hidden again.

Select cell B30, then on the Review tab, in the Comments group, click Show/Hide Comment again. All comments in the worksheet can be made to display at the same time.

On the Review tab, in the Comments group, click Show All Comments.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

On the Review tab, in the Comments group, click Show All Comments again to hide all comments.

You can delete a comment from a cell when you no longer need it. 14

Select cell B9. Then on the Review tab, in the Comments group, click Delete.

Save and close the workbook.

Practice the Skill This exercise will provide you with more practice in using cell comments. 1

Open the Economic Forecasts workbook, and save it as Economic Forecasts - Student.

Add comments to two cells. 2

Select cell B4.

On the Review tab, in the Comments group, click New Comment.

In the comment box, type: Only 55% probability of this.

Select cell E6, and on the Review tab, in the Comments group, click New Comment.

In the comment box, type: Unable to achieve consensus on this.

Have Excel display each of the comments in turn. 7

Select cell B4.

On the Review tab, in the Comments group, click Next.

Click the Next button two more times, until a message box is displayed.

Click Cancel to close the message box.

Observe the behavior of cell comments when you point at it.

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others 11

Lesson 3

Point at cell E6, then move the cursor somewhere else.

Now force a comment to stay on, and observe its behavior again. 12

Select cell E6. On the Review tab, in the Comments group, click Show/Hide Comment.

Select another cell away from E6. Point at cell E6, then move the cursor somewhere else.

Select cell E6. On the Review tab, in the Comments group, click Show/Hide Comment.

Save and close the workbook.

Looking at Workgroup Functions You can share your workbooks with others using two different methods: •

Post your workbook on a public folder or an intranet using a network for other users to update. The users may be allowed to share the same workbook at the same time or not. The changes can then be accumulated together for your review.

•

Send or distribute copies of your workbook by e-mail. Each person makes changes and comments to his/her copy, and returns it to you. You can then merge the changes together into the original workbook.

If the workbook is shared in a network, you still have limitations because the workbook may be set to be accessed by a single user at a time. That is, only the first person to open a workbook can update it while others who subsequently try to open the same workbook have to wait for the first person to close the workbook, or open a copy of the workbook with “read only” access and no changes can be saved to the original workbook until the first person has closed it. Excel offers multiple users the ability to open and update the same workbook at the same time. However, you should be aware that you are not permitted to perform certain functions when sharing workbooks, such as (but not limited to): •

insert or delete blocks of cells; however, you can insert or delete entire rows and columns

•

delete worksheets in the workbook

•

insert or change charts, pictures, objects, or hyperlinks

•

use any of the drawing tools

•

group or outline data

•

insert automatic subtotals or summarize data

•

set up or change data validation restrictions and messages

•

change or remove passwords; passwords added before the workbook was set to be shared will remain unchanged

You should be aware that Excel does not truly allow multiple users to update the same workbook at the same time. Each user really updates their own copy of the “shared” workbook. Excel then tries to resolve multiple changes to cells by simply identifying these conflicts. You should therefore avoid sharing workbooks with more than two other users at the same time. The only exception to this is if you are subscribed to the Excel Services, available only on a Microsoft SharePoint server.

Creating a Shared Workbook In order for you and others to work on the same workbook, it must be set up as a shared workbook. If you don’t activate this feature, your network (if applicable) will display a prompt indicating that someone else has the file open and you can either open a read-only file version, or be notified when the other person has closed the file.

6265-2 v1.00 © CCI Learning Solutions Inc.

5.2

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Having a shared workbook gives you the flexibility to then later compare or merge the two versions together rather than re-keying the data, as necessary.

Learn the Skill This exercise demonstrates how to set up a workbook for sharing. 1

Open the Rainbow Computer Sales workbook and save as Rainbow Computer Sales - Student.

For the purpose of this exercise, the workbook must be saved immediately with its new name. This has to be done now because Excel will automatically save the workbook when you convert it to being shareable. 2

On the Review tab, in the Changes group, click Share Workbook. If you see the following message, this is an indicator that the workbook cannot be shared until you turn off the feature to remove personal information from the file’s properties. You will need to follow the instructions shown in the box and then repeat step 2 before you can proceed further with the exercise.

Click Allow changes by more than one user to turn it on. Click OK. A message box appears with the question: “This action will now save the workbook. Do you want to continue?”.

Click OK to save the workbook and leave the workbook open. The title bar at the top of the workbook now has the indicator [Shared] displayed:

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Resolving Shared Workbook Conflicts The possibility of conflicting changes is inevitable with shared workbooks; two or more users may try to change the same cell with different values or formulas. Excel recognizes this situation only when the workbook copies containing the contentious cell values are saved onto the network. At that point, Excel asks you to resolve the conflicting information by displaying the Resolve Conflicts dialog box. The Resolve Conflicts dialog box identifies who had already updated a specific cell and what the change was. The last user to save the file must then resolve the difference by accepting or rejecting the change to that cell. Other cells that are not in conflict will be accepted automatically. The dialog box will appear for each cell with a conflicting value.

Learn the Skill This exercise demonstrates how to resolve conflicting changes to the same shared workbook. It will require two copies of Excel to run at the same time, both sharing the same workbook. Alternatively, you can share the workbook on two different computers running one copy of Excel each in a network, if you have the facilities to do so. To ensure the results match this exercise, be sure to follow the same sequence of instructions. 1

Make sure the Rainbow Computer Sales - Student workbook is open.

Start another copy of Excel.

Open the Rainbow Computer Sales - Student workbook in this second copy of Excel.

If you are running two copies of Excel on the same computer, resize and move the windows so that both copies of Excel and the worksheets appear adjacent to each other on the screen.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Enter the following values into the respective copies of the worksheet (but do not save the workbooks yet):

Save the workbook displayed on the right side of your screen.

Save the workbook displayed on the left side of your screen. The Resolve Conflicts dialog box now appears because cell B16 was updated on both copies of Excel.

For this exercise, assume that you will accept the first and third estimates in the worksheet on the left, and the second estimate from the worksheet on the right. 8

Click Accept Mine.

Click Accept Other.

Click Accept Mine. After the updates have been completed, the following message box appears:

Click OK. In addition, Excel will add comments to each cell changed by others sharing the workbook.

Move the mouse pointer to each cell with an update comment in the worksheet on the left.

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Save the workbook on the right side of the screen again.

Click OK.

Move the mouse pointer to each cell with an update comment in the worksheet on the right.

Lesson 3

The worksheets should now appear as follows:

Close the workbook and Excel on the right side of the screen.

Maximize the Excel on the left side of the screen, and resize the workbook to a larger size, if needed.

Tracking Changes By enabling sharing of a workbook, you may be concerned about unrestricted and uncontrolled changes to the data. Excelâ&#x20AC;&#x2122;s shared workbook feature actually helps to alleviate those concerns because all changes to the workbook (after sharing has been enabled) are recorded in a history log, which can be viewed at any time. In contrast, non-shared workbooks can still be updated by others if they are readily accessible on a shared network folder. Even password protection will allow unrestricted changes to any (hopefully authorized) individual who knows the correct password(s). You can view the changes made to a cell in one of two ways:

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

5.1

Lesson 3

Microsoft® Office Excel 2007 – Level 3

•

Display the change comment by positioning the mouse pointer over the cell. This displays the most recent change, who made the change, and the date and time of change. The comment can only display one change, previous changes will not appear.

•

Display the change history in a temporary worksheet.

The change comment is marked by the blue triangle in the upper left corner of a cell. Light blue triangles indicate changes made by other users, dark blue are changes you have made. You can control when these change comment markers appear or reset.

Learn the Skill This exercise demonstrates how to change the settings for tracking change comments. 1

Make sure the Rainbow Computer Sales - Student workbook is open.

On the Review tab, in the Changes group, click Track Changes and then click Highlight Changes.

When

Since I last saved, All, Not yet reviewed, or Since date.

Who

Everyone, Everyone but me, and a list of each user who has updated the shared worksheet.

Where

Select specific cell ranges for displaying the change comments.

The default combination is Since I last saved, Everyone, and all cells. These choices are important to ensure the history accurately records the changes that you want to be aware of. You may consider more restrictive choices to maintain a reasonably sized history. 3

Click the arrow for When, and click All. Click OK.

Move the mouse pointer over some of the cells to display the change comments there.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 3

Collaborating with Others

On the Review tab, in the Changes group, click Track Changes, and click Highlight Changes.

Click the arrow for When, and click Since I last saved. Click OK. The message box “No changes were found with the specified properties” appears.

Click OK. All of the changed comment markers should now disappear.

Now change one cell. 8

Select cell C18, type: 68 and press

Notice that cell C18 is now the only cell with a change comment marker. 9

Save the workbook. This change comment marker now disappears again.

Showing the History of Changes A full history of all changes to all cells in the workbook in the last 30 days can be displayed from the Highlight Changes dialog box. The history displays on a temporary worksheet. Each of the column headings has a list button. Clicking this button displays a list of unique values in that column. You can select any of those values to filter out any rows that do not contain that selected value. The history only shows changes that were accepted. In the case of conflicts, discarded changes are not kept in the history. Tracking of changes starts automatically with shared workbooks. By default, Excel only retains the most recent 30 days of changes. To change this setting at any time, on the Review tab, in the Changes group, click Share Workbook, and then click the Advanced tab.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Learn the Skill This exercise demonstrates how to display the change history. 1

Make sure the Rainbow Computer Sales - Student workbook is open.

On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

Click When to turn it off. If necessary, disable Who and Where as well.

Click List changes on a new sheet to turn it on. Click OK.

If necessary, widen the worksheet window to view as much of the history worksheet as possible.

Click the arrow for Time. Select a time value of your choosing.

Click the arrow for Time again, and click (Select All).

Select other values in other columns as desired.

Now turn off the history worksheet.

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

Click the arrow for When, and click Since I last saved.

Click List all changes on a new sheet to turn it off.

Click OK. Click OK once more to remove the message box.

Removing Shared Use of Workbooks Shared workbooks can be converted back to single user access at any time.

Learn the Skill This exercise demonstrates how to convert shared workbooks back to single user. 1

Make sure the Rainbow Computer Sales - Student workbook is open.

On the Review tab, in the Changes group, click Share Workbook. If necessary, click the Editing tab.

Verify that you are the only current user of the workbook. If necessary, close the workbook at other computers or on additional copies of Excel running on your computer.

Click Allow changes by more than one user to turn it off.

Click OK. The following warning message is displayed:

Click Yes. The [Shared] indicator is no longer displayed in the title bar.

Save and close the workbook.

Protecting Your Workbook Using Passwords

5.2

Eventually you may find yourself developing applications that other people will use. Once other people use the workbook, you no longer have control of what is being changed unless you protect the workbook. Excel provides a variety of levels of protection from individual cells to controlled access to the workbook files. By selecting the appropriate level of protection you can allow others access to the workbook while hiding sensitive information and protecting your formulas. When protection is enabled, Excel prompts for a password. If you are just protecting your worksheet or workbook from accidental changes, you can leave the password blank; this is equivalent to giving everyone the password. A password, however, will protect against malicious data tampering or unauthorized viewing of sensitive data. Once a worksheet, workbook or file is password protected you will not be able to remove the protection without the password.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

There are some considerations when using passwords: •

Keep a record of the your passwords in a safe place. If you forget the password even Microsoft will not be able to help you remove the protection.

•

Passwords are case sensitive. If you use a mix of upper and lower case you must remember the exact inverts the case when you are typing—be sure to check the Caps Lock pattern. Remember indicator when setting and using passwords.

•

Be careful as you type. Passwords do not display on the screen. You will be asked to retype the password to ensure that you typed it correctly the first time. Watch your hand position on the keyboard!

•

Protection is lost if someone hacks (by guessing or other methods) the password. There are numerous rules and guidelines about how best to decide on a password to use. Your choice of a password comes down to how sensitive the information in the worksheet is.

If you need a more secure password (more difficult to hack), then the following guidelines apply: •

The password must be at least six characters in length. It is not necessary to exceed ten characters.

•

It must have at least one of each of these: lower case alphabetic character (a to z), upper case (A to Z), numeric digits (0 to 9), other characters (e.g. # ! % &). An example of a secure password is seC#r3T

If you need a simple password, then you may want to follow these guidelines: •

Try to use a password that is easy to remember but not obvious. For example, if you had a worksheet that listed everyone’s salary in the company, you might select a password of “grossPAY” instead of “salary”.

•

Avoid associating the password with the job function, i.e., do not use “payroll” as a password for a payroll workbook.

•

If the password is distributed to people you want to access the workbook, then you should not to use any of your personal passwords. If you use a password you normally use to access a secure network such as a LAN, you risk others gaining access to your own secure area.

•

Avoid use of names; people trying to unlock the workbook will often try using your name, the names of people or pets close to you or associated with the workbook.

Protecting the Worksheet When you turn on the worksheet protection, all cells are locked by default, preventing anyone from making changes to them. Before you turn on the worksheet protection, you must select the range of cells that you will allow other people to enter data and make changes. Worksheet protection cannot only be used to lock others from making unwanted changes to your worksheet, you may also wish to consider using this feature to prevent yourself from making mistakes that cause the loss of formulas or core information. To “open” up some cells on a worksheet, you must first select the ranges of cells that users can edit.

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Title/Refers to cells

The ranges of cells that are unlocked. More than one range of cells can be specified for the worksheet.

Permissions

Specific individuals can be identified to have access to these ranges. Otherwise, these cells are open to anyone who is permitted to modify the workbook. For more information about protecting files, refer to this topic below. However, this Permissions feature can only be used on computers connected to a domain-based network.

A similar — but incompatible — method of unlocking cells is to use the Locked status. This feature has been the traditional way of unlocking cells in a protected worksheet. It continues to be available in Excel 2007 to be compatible with workbooks that were created using previous versions of Excel.

To access this feature, on the Home tab, in the Cells group, click Format, click Format Cells and click the Protection tab as shown in the screen above. Click Locked to turn it off. Alternatively, on the Home tab, in the Cells group, click Format and click Lock Cell to toggle it off.

Learn the Skill This exercise will demonstrate how to protect a worksheet. 1

Open the Protected Loan Calculator workbook and save it as Protected Loan Calculator - Student.

Select the cell range to allow users to enter data even though the rest of the worksheet is locked. 2

On the Review tab, in the Changes group, click Allow Users to Edit Ranges.

Click New.

In the Title text box, type: Loan data.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Select the Refers to cells text box, then select cells B3:B5 from the worksheet.

Click OK.

You have now unlocked a range of cells that users will be permitted to update once worksheet protection is turned on. Now activate the worksheet protection. 7

Click Protect Sheet in the Allow Users to Edit Ranges dialog box. Alternatively, you can also display this dialog box on the Review tab, in the Changes group, click Protect Sheet, or on the Home tab, in the Cells group, click Format, and click Protect Sheet). The Protect Sheet dialog box opens so you can select which worksheet operations or objects to protect.

Choosing the default settings will allow editing in unlocked cells only, and will prevent all operations such as inserting/deleting rows or columns or formatting cells. This level of protection does not prevent anyone from deleting the entire worksheet or the workbook!

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Notice that the password is optional. If you leave it blank, the worksheet will be protected but there will be no password; anyone can unprotect the worksheet. This is useful if you are using protection to avoid accidentally deleting formulas. When you enter a password, the â&#x2014;? character displays for every character in the password. This is to avoid someone else seeing your password displayed as you enter it. If you entered a password, Excel will prompt you to enter it again. This is to ensure you entered the password correctly. 8

Leave all check boxes at their default settings, type: 123 in the Password to unprotect sheet text box and then click OK. Do not use this password for your real workbooks! See previous commentary regarding how to select a good password to use. The Confirm Password dialog box now appears; reenter your password to ensure it is correct.

Type: 123 in the text box then click OK.

Move to cell C12 and try to make a change.

Click OK to clear the error message.

Try to insert or delete a row or column.

Enter the following information into the cells indicated: B3 50000 B4 .08 B5 10

Now remove the password protection. 14

On the Review tab, in the Changes group, click Unprotect Sheet. The Unprotect Sheet dialog box is displayed. Alternatively, you can also display this dialog box by selecting the Home tab, in the Cells group click Format, and click Unprotect Sheet.

Type: 123 in the Password text box and click OK.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Once you have unlocked the spreadsheet, it remains unlocked until you protect it again. You must reenter the password each time you turn protection back on again — be careful! 16

Move to cell C12 and type: 10000 and press

Save and close the workbook.

Practice the Skill This exercise will demonstrate how to protect a worksheet using the dialog box method. 1

Open the Age Calculator workbook, and save it as Age Calculator (protected) - Student.

On the Review tab, in the Changes group, click Allow Users to Edit Ranges. Click New.

In the Title text box, type: Age data.

Select the Refers to cells text box, then select cells B3:B4 from the worksheet.

Click OK to close the New Range dialog box.

Click OK again to close the Allow Users to Edit Ranges dialog box.

Protect the worksheet with a password. 7

Under the Home tab, in the Cells group click Format, then select Protect Sheet.

Leave the default options selected, and enter: 456 in the Password text box and click OK.

In the Confirm Password dialog box, type: 456 and click OK.

Enter your dates into cells B3 and B4.

Save the workbook.

Workbook Protection Excel also provides protection for the workbook. When workbook protection is enabled you will not be able to add, delete or move worksheets within the workbook. You can also protect the size and location of the windows of the workbook.

Learn the Skill In this exercise, you will enable workbook protection for both structure and window layout. 1

Open the Protected Monthly Salary workbook, and save as Protected Monthly Salary - Student.

Change the size of the worksheet window (e.g., increase its width) to see if Excel will allow it.

Activate the workbook protection feature. 3

On the Review tab, in the Changes group, click Protect Workbook.

If necessary, check Structure and Windows to turn both of them on.

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Type: 123 in the Password text box and then click OK.

Type: 123 in the Reenter password to proceed text box and then click OK.

Try to move and resize the worksheet window.

Lesson 3

Notice the window control buttons are no longer displayed in the upper right corner of the worksheet as well as the Insert Worksheet option is not available. 8

Click the Home tab and in the Cells group, click the arrow for Delete as well as Format to notice how the Delete Sheet and Move or Copy Sheet options are not available.

Now unprotect the workbook. 9

On the Review tab, in the Changes group, click Unprotect Workbook. If you are using the Windows Vista operating system, then on the Review tab, in the Changes group, click Protect Workbook again to unprotect it.

Type: 123 in the Password text box and click OK.

Close the workbook without saving any changes.

Practice the Skill 1

Select the Age Calculator (protected) - Student workbook.

On the Review tab, in the Changes group click Protect Workbook to display the Protect Workbook dialog box.

If necessary, check Structure to turn it on, but leave Windows off.

Type: 456 in the Password text box then click OK.

Type: 456 in the Reenter password to proceed text box then click OK.

Move and resize the worksheet window.

Click the Home tab. Notice that Insert and Delete in the Cells group are both grayed out completely.

Under the Home tab, in the Cells group click Format. Most of the buttons on this menu are grayed out, except for the Unprotect Sheet menu item.

On the Review tab, in the Changes group click Unprotect Sheet.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 3 10

Type: 456 in the Unprotect Workbook dialog box and click OK.

Save and close the workbook.

Microsoft® Office Excel 2007 – Level 3

Protecting Files Using worksheet and workbook protection, you can control changes to your Excel application. In addition to this level of protection you can also protect access to the file itself. Using file level protection you can control access to an Excel workbook. Without the password Excel will not load the file, making it impossible to see what is in the file without the password. If you ever forget your password, you will never be able to retrieve the data. No one — not even Microsoft — will be able to get around this password protection. Be sure to store your password in a secure location (but not in obvious places such as taped to your computer).

You can enter two different passwords to control the level of access for your users: •

If you enter only a Password to open, then users must enter the password to see the contents of the workbook. Once the file is open, users can make any changes they wish to the workbook and save them.

•

If you enter only a Password to modify, then users may open the workbook and see the contents without the password. However, if they make any changes to the workbook, they cannot save them to the same workbook (although they can save it as a workbook with a different name or to a different folder).

•

If you enter both a Password to open and a Password to modify, then users must enter both passwords to have full access to the workbook. If they only have the Password to open, they can only see the contents of the workbook, and not be able to save changes. If they only have the Password to modify, then will not be able to do anything with the workbook because Excel needs the Password to open first.

Learn the Skill In this exercise, you will enable file access and write protection. 1

Open the Protected Monthly Salary - Student workbook.

Click the Office Button and then click Save As.

Click Tools in the Save As dialog box, then click General Options.

Specify both passwords to set the highest level of protection for this workbook. 4

In the Password to open text box, type: 123.

In the Password to modify text box, type: 456.

Click OK.

You must now confirm your passwords: first is the Password to open, then the Password to modify. 7

In the first Confirm Password dialog box enter: 123 then click OK.

In the second Confirm Password dialog box enter: 456 then click OK.

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 3

Collaborating with Others 9

In the Save As dialog box, click Save. Because the file was previously saved with the same name, Excel is now asking you to confirm before saving the file.

Click Yes in the message box.

Close the workbook.

Open the Protected Monthly Salary - Student.xls workbook again.

Excel needs the passwords in order to grant access to you. You must enter the Password to open password in order to continue. 13

Type: 123 and click OK.

Now Excel wants to know if you only want Read Only access to the workbook, or enter the Password to modify password. 14

In the second password dialog box, click Read Only.

In cell B5, enter: 4000 and press

Click the Office Button and then click Save.

Click OK. In the Save As dialog box, click Cancel.

Press

to close the workbook without saving the changes.

Practice the Skill In this exercise, you will enable workbook protection for only the structure of the workbook. 1

Open the National Intelligence Agency workbook.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Click the Office Button and click Save As.

In the File name text box, type: National Intelligence Agency - Student.

Click Tools in the Save As dialog box, then click General Options.

In the Password to modify text box, type: your eyes only.

Click OK.

In the Confirm Password dialog box enter: your eyes only and then click OK.

In the Save As dialog box, click Save.

Close the workbook.

Open the National Intelligence Agency - Student workbook.

In the Password dialog box, enter: your eyes only and click OK. Since the workbook was saved with only one password “for modify” only, that one password is all that is needed to open it. This same password also makes the workbook open for changes as well.

Now remove the password protection and save the workbook again. 12

Click the Office Button and click Save As.

Click Tools in the Save As dialog box, then click General Options.

Clear the Password to modify text box. Click OK. You do not need to enter the password again to confirm it — you are removing the password.

Save and close the workbook. Answer Yes to replace the existing file.

Preparing for Distribution Changing Workbook Properties 5.3

Another useful tool for workgroups is the ability to display a workbook’s properties prior to opening it. This is useful when there are many workbooks stored on the network server, often with very similar names. The properties display will help distinguish each of the workbooks from each other. These same benefits apply in non-workgroup situations as well.

Learn the Skill 1

Open the Secure Computer Sales workbook and save it as Secure Computer Sales - Student.

Click the Office Button, then click Prepare, and click Properties. The Document Information panel is displayed above the workbook.

100

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Type the following: Author: <your own name> Title: Secure Computer Sales Subject: purchase order Keywords: sales, purchase order Category: sales Status: Draft

Click Document Properties and then Advanced Properties.

Click each of the tabs and you will see that entering the information in the Document Information panel is the same as with earlier versions of Excel, except that you can see the contents of the document, which enables you to enter more precise details about the document.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

101

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Click OK to close the dialog box.

Close the Document Information panel.

Save and close the workbook.

Click the Office Button and select Open.

Click the Views drop-down and click Properties.

Scroll down and select the Secure Computer Sales - Student workbook.

Click the Open button.

Practice the Skill This exercise will demonstrate how to change a workbook’s properties using the Advanced Properties method. 1

Open the Economic Forecast with Comments workbook and save it as Economic Forecast with Comments - Student.

Click the Office Button, click Prepare, and click Properties.

Click Document Properties and then Advanced Properties.

102

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others 4

Lesson 3

If necessary, click the Summary tab and then enter the following: Title: National economic forecast Subject: Key economic indicators by quarter Author: <your own name> Category: Economic indicators Keywords: GDP, unemployment rate, interest rate, CPI, value of dollar

Click OK and close the Document Information panel.

Save the workbook.

Using the Document Inspector This is a new feature to Office 2007 and gives you the option to decide what type of information is to be associated with the document being sent for distribution. Depending on to whom you are sending this file, you may want to take a closer look at data that may be hidden from normal view. The Document Inspector is designed to look for certain types of data stored in workbooks and report them to you. Some of these items include: •

Comments, revision marks, document versions, or annotations containing names of people who have made changes or made notations in the workbook.

•

Properties of the document such as statistics, e-mail headers, user name, template name, routing slips, information in the Custom tabs of the Document Properties dialog box; these may be referred to as metadata and often include personal information about the originator.

•

Information in any headers or footers, including watermarks

•

Hidden data rows, columns, and worksheets

•

Custom XML data that is not visible in the document

By knowing that these types of data exist in a workbook, you can make a decision on whether it is appropriate to leave this data in or remove it.

6265-2 v1.00 © CCI Learning Solutions Inc.

103

5.3

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Activate this feature by clicking the Office Button, click Prepare and then click Inspect Document. The first time you enter information into the Document Properties panel, Excel displays an option to turn on the AutoComplete feature to provide suggestions on entries.

Learn the Skill This exercise demonstrates how to use the Document Inspector to find facts about the workbook that is “hidden” from view. 1

Select the Secure Computer Sales - Student workbook, and save it again as Secure Computer Sales (inspected) - Student.

Click the Office Button, click Prepare, and then click Inspect Document.

This graphic shows the items in your document that Document Inspector looks for and then displays the results so that you can choose which items to keep or remove. 3

Leave all default options unchanged and click Inspect.

104

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

This dialog box shows one item that Document Inspector identified which you may choose not to share if you intend to distribute this document to others. Document Inspector provides the option to remove the items automatically, so that you do not have to remove them manually. 4

Click Remove All for Document Properties and Personal Information.

Click Reinspect and then click Inspect. Excel displays the same dialog box, now showing that there are no longer any of the listed items in your document.

Close the Document Inspector dialog box.

Save and close the workbook.

Practice the Skill This exercise will further demonstrate some of the capabilities of the Document Inspector. 1

Select the Economic Forecast with Comments - Student workbook.

Click the Office Button, click Prepare, and then click Inspect Document.

Click Inspect.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

105

Lesson 3

MicrosoftÂŽ Office Excel 2007 â&#x20AC;&#x201C; Level 3

Click Remove All for Comments and Annotations.

Click Remove All for Document Properties and Personal Information.

Click Close.

Save the workbook.

Using Digital Signatures 5.3

The use of digital signatures is a way of authenticating a document so that others can rely on its contents. Without this capability, we would have to resort to printing the document, obtaining a penned signature, and storing the document where it can be retrieved as necessary. With Office 2007, you can apply a digital signature to Word and Excel documents. This new capability is a cornerstone to supporting electronic signatures because we rely on the fact that the holder of that signature was the person who applied the signature, and that the software will prevent any tampering of the document after the signature has been applied. With a digitally signed document, you can be confident that no one has added a virus or maliciously coded macro to the workbook. To ensure that the signature is authentic, you should obtain a digital certificate from a reputable certificate issuing authority, such as VeriSign.

106

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

When setting up a workbook with a signature line that will expect a signature, you can set the following options:

Suggested signer, title, and e-mail address

Identifies who is the person expected to sign the document.

Instructions to the signer

Displays these comments and instructions to the signer.

Allow the signer to add comments

Allows the signer to write comments and instructions when they apply their signature.

Show sign date in signature line

Causes the current date to be applied at the same time as the signature.

Other facts to be aware of with digital signatures: •

You can attach more than one signature line to a document.

•

The software allows you to create your own digital signature if you do not have a certificate from a Third Party issuer.

•

A signature image (e.g., a digital picture of a person’s hand signature) can be used as a personal digital signature.

•

Once signed, Excel will prevent any changes from being made to the document. If the document was changed somehow, Excel will require it to be signed again.

Learn the Skill In this exercise you will add a signature line to an Excel workbook, and then digitally sign it. An option available is to add an image of an actual signature to the document. Microsoft Paint will be used to create a signature image. 1

Start Microsoft Paint.

Click the Text tool, click and drag a text box approximately 4 inches (10 cm) wide by 0.5 inch (1.3 cm) high at the top left corner of the canvas, and type your own name.

If necessary, display the Text Toolbar by right-clicking the text and clicking Text Toolbar.

Highlight your name and change the font to one of your choosing such as Script Bold. Increase the font size to 28 or a similar large size.

Click another tool to apply the text, then drag the bottom right corner handle of the canvas to resize it to the same size as the text.

6265-2 v1.00 © CCI Learning Solutions Inc.

107

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Select File, then click Save As. If necessary, navigate to the folder where the image file will be stored, as directed by your instructor.

Change the Save as type to: JPEG (*.JPG; *.JPEG; *.JPE; *.JFIF)

Change the Filename to: signature_image and click Save.

Close Microsoft Paint.

Now open the workbook and add a signature line to it so that the necessary individual knows that they have to approve it. 10

Switch to Excel and select the Secure Computer Sales - Student workbook.

Select cell B22 to place the signature line there.

On the Insert tab, in the Text group, and click Signature Line. If you clicked the Signature Line drop-down button in error, then select Microsoft Office Signature Line.

Click OK to close the message box. The Signature Setup dialog box displays.

Enter the following: Suggested signer: your own name Suggested signer’s title: Manager, Approvals Suggested signer’s e-mail address: your_name@securecomputersales.com

108

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Click OK. The signature line is now added to the workbook.

Save and close the workbook.

Now assume that you are the approver of this workbook. Open it and apply your digital signature to it. 17

Open the Secure Computer Sales - Student workbook. A message line appears between the Ribbon and the workbook to inform you that a signature is expected.

Click View Signatures. The Signatures task pane appears at the right side of the workbook window.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

109

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Click the signer drop-down and click Sign. When the message box appears as seen in step 12, click OK. Alternatively, you can also right-click on the signature line and select Sign. To apply a digital signature to a document, you will need a digital certificate. If you do not need a highly secure certificate or you are waiting for your certificate to arrive, you can create your own digital certificate. The following dialog box displays if your computer does not have your digital certificate loaded as of yet.

Click Create your own digital ID and click OK. Excel now prompts you for additional information to create your temporary digital certificate.

Enter the following into the Create a Digital ID dialog box: Name: <your name> E-mail address: your_name@securecomputersales.com Organization: Secure Computer Sales Inc

Click Create.

110

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

The Sign dialog box displays. This is the dialog box where you would insert your digital certificate. You can type your name in the text box next to the ‘X’, or you can import your signature image file.

Click Select Image.

In the Select Signature Image dialog box, navigate to the folder where your signature image is located. Select the signature_image.JPG file and click Select. The Sign dialog box now shows your signature image.

Click Sign.

Click OK.

6265-2 v1.00 © CCI Learning Solutions Inc.

111

Lesson 3

Microsoft® Office Excel 2007 – Level 3

The workbook is now locked with your signature. Note the [Read-Only] indicator in the title bar to indicate that changes will not be permitted. In addition, the workbook has already been saved with your signature and no one can use Save.

Try to enter new data into the workbook, or to save the workbook. Also notice the signature indicator in the status bar.

Close the workbook.

Practice the Skill This exercise will provide you with another example of how to digitally sign a workbook. 1

Select the Economic Forecast with Comments - Student workbook and save it as Economic Forecast signoff - Student.

Insert the signature line at cell B8. 2

Select cell B8 to put the signature line there.

On the Insert tab, in the Text group, and click Signature Line. If you clicked the Signature Line drop-down button in error, then select Microsoft Office Signature Line.

Click OK to close the message box.

112

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others 5

Lesson 3

Enter the following into the Signature Setup dialog box: Suggested signer: <your name> Suggested signer’s title: Manager, Approvals Suggested signer’s e-mail address: your_name@crystalballconsults.com This forecast will be sent to the Economic Council. Instructions to the signer:

Click Allow the signer to add comments in the Sign dialog and click OK. The workbook is now ready for signature.

Right-click the signature line, and click Sign.

Click OK to close the message box. The Sign dialog box is displayed. Because the computer already has a digital signature certificate (created in the exercise above), this dialog box can be immediately displayed.

This time, you will type your name into the dialog box.

6265-2 v1.00 © CCI Learning Solutions Inc.

113

Lesson 3 9

Microsoft® Office Excel 2007 – Level 3

Enter the following into the Sign dialog box: X: your own name Purpose for signing this document: The forecasts appear to be reasonable.

Click Sign. The Signature Confirmation message box displays.

Click OK to close the Signature Confirmation dialog box. The workbook is now digitally signed.

Close the workbook.

114

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Marking Workbooks as Final By marking a workbook as final, you can send it out to others and prevent them from accidentally making changes. When the final status is turned on, all typing, editing, and proofing commands are turned off and the file is set to read-only. Note that this is not meant as a security feature by preventing all changes to the workbook. The final status can be turned off. To reverse this status, simply select Mark as Final again.

Learn the Skill 1

Open the Secure Computer Sales workbook and save it as Secure Computer Sales (final) Student.

Click the Office Button, then click Prepare, and select Mark as Final.

Click OK.

Read the message and then click OK.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

115

5.3

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Notice how Excel has turned off most of the buttons on the Ribbon and Quick Access Toolbar, and the [Read-Only] indicator in the workbook title bar. Also notice the icon on the Status bar indicating that this document has been marked as final and that changes can no longer be made to it.

Try to make changes to the workbook and save the workbook.

Close the workbook.

Practice the Skill This exercise will demonstrate how to mark a workbook as Final, then reverse it, and mark it again. 1

Select the Economic Forecast with Comments - Student workbook and save it as Economic Forecast Final - Student.

Click the Office Button, then click Prepare, and select Mark as Final.

Click OK to close the message box.

Click OK to close the next message box. The workbook is now marked with the status of Final.

This status can easily be reversed. 5

Click the Office Button, then click Prepare, and select Mark as Final. The workbook no longer shows the [Read-Only] indicator in the title bar.

Now mark the workbook as final again. 6

Click the Office Button, then click Prepare, and select Mark as Final.

Click OK twice to close the next two message boxes.

Close the workbook.

Using Digital Rights Management 5.3.2

Excel offers Information Rights Management (IRM). IRM works with a Windows Rights Management Services (RMS) server to allow you to control what happens to workbooks. The purpose is to prevent the viewer from editing, copying, printing, or saving the information in sensitive workbooks. You can even set an expiration date on IRM-protected workbooks so the recipient cannot view or use them after they expire. IRM is available only with a Windows 2003 server or with a Windows Vista system. You need to set up the appropriate passport account to Microsoft before you can use this option. We used Windows Vista to show the screen for this feature. It is strongly suggested that you save your file before proceeding with this process for any workbook.

116

6265-2 v1.00 © CCI Learning Solutions Inc.

Lesson 3

Collaborating with Others

To restrict permissions, click the Office Button, click Prepare, click Restrict Permission and then click Restrict Access.

Restrict Permission To This Document

Turn this feature on or off.

Read or Change

Enter the e-mail addresses of the users you want to be able to read and/or make changes to the workbook.

More Options

Specify additional permissions, including an expiration date for the workbook, permission to print content, and permission for users with read access to copy content and allow programmatic access to the workbook. In addition, you can provide your e-mail address so that users can request additional permissions or require a connection to verify a user's permission. You can set these permissions as the default for all workbooks that have restricted permissions. You can also give different permissions to different users by clicking in the Access Level column next to a user's name and selecting Read, Change, or Full Control from the drop-down box.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

117

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Once you have set permissions, a notification will appear in the information bar at the top of the workbook specifying that access is restricted.

Optional Exercise In this exercise, you will set access restrictions on a document that contains sensitive information. Ensure you have a Windows Right Management Server or access to a Windows Vista system before you proceed with this exercise. 1

Open the Protected Monthly Salary workbook and save as Protected Monthly Salary (restricted) - Student.

Click the Office Button, click Prepare, click Restrict Permission and then click Restrict Access.

Click Restrict permission to this workbook.

Click More Options.

Click This workbook expires on and select a date three months from today’s date. As you click the drop-down arrow, Excel displays a calendar where you can click the arrow on either side of the month to move forward or backward to select a date.

Click Allow users with read access to copy content.

Click OK.

Excel now places a Restricted Access information bar above the worksheet. 8

Close the worksheet.

Saving a Macro-Enabled Workbook 5.4

The term macro typically refers to a set of actions that can be executed with a single command. The Excel macro language is a fully featured programming language in which you can write complete applications. This language is called Visual Basic for Applications (or VBA), which is now common in all Microsoft Office applications. This capability allows independent software developers to develop systems that will integrate these applications together. This courseware will only cover the basics of creating a simple macro and saving it with the workbook. Because of its powerful capabilities, macros have attracted an inordinate amount of publicity over its exposure to computer viruses (i.e., macro viruses). To protect users, Microsoft has re-architected the security components in the Office group of applications. One of the changes is that the standard Excel Workbook (*.xlsx) will not store any macros. Workbooks containing macros must be stored as an Excel Macro-Enabled Workbook (*.xlsm). When opening a .xlsm (or the earlier version .xls) workbook containing macros, Excel will normally disable them unless you then decide to enable them. You can apply digital certificates to macros to improve their trust level. These digital certificates are automatically invalidated if someone tries to make any change after the certificates are applied. Excel has a Trust Center Settings dialog box in which you can tell Excel how to treat macros when it tries to open the workbook. You should never open workbooks and automatically enable macros. The macro protection feature in Excel will only detect the presence of macros in a workbook. It cannot determine if a macro contains a virus or not. You must acquire anti-virus software to detect and remove macro viruses. As well as including viruses, macros can also contain damaging or malicious code, e.g., to delete files or erase cell ranges in other workbooks.

118

6265-2 v1.00 © CCI Learning Solutions Inc.

Collaborating with Others

Lesson 3

Be aware that even though the .xlsm format uses the OOXML format, the macros can only be executed by Excel. Other applications that are compatible with this format are not able to use these macros. Furthermore, only Excel is able to read and decode macro VBA code. It is not necessary to be familiar with the VBA programming language to create macros. Excel provides a macro recorder that can be used to create simple macros. After starting the macro recorder, it will save all commands you enter from the keyboard or click with the mouse until you turn off the recorder.

Learn the Skill 1

Open the Secure Computer Sales workbook

Look for the Developer tab, in the Ribbon. The macro commands are located in that tab. If the tab is missing, you must enable it. 2

If necessary, click the Office Button and click Excel Options. In the Popular category, click the Show Developer tab, in the Ribbon check box to turn it on, and click OK. Activating the Developer tab in Excel will activate it in the other Office programs as well, e.g., Word and PowerPoint.

Select cell A5.

On the Developer tab, in the Code group, click Record Macro.

In the Macro name text box, type: BoldItalics.

Press

Click OK.

to jump to the Shortcut key text box. Type: B (uppercase).

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

119

Lesson 3

Microsoft® Office Excel 2007 – Level 3

If you enter a lower case alphabetic character (i.e., a to z), then you only need to use the macro. If you enter an upper case letter (A to Z), then you must use both activate the macro.

to activate and to

The actions that you perform on the workbook will be recorded in the macro starting from this point. The selection of tabs in the Ribbon are not recorded in the macro. In the macro world, there is essentially no concept of Ribbon tabs — all commands are directly accessible. 8

On the Home tab, in the Font group, click Bold and then click Italic.

On the Developer tab, in the Code group, click Stop Recording.

Now try out the macro. 10

Select cell A6.

Press

Click the Office Button, click Save As.

In the Save As dialog box, change the File name to Secure Computer Sales (macro) - Student.

Change the Save as type to Excel Macro-Enabled Workbook (*.xlsm) and click Save.

on the keyboard.

If you try to save it as a normal Excel Workbook (*.xlsx), an error message will be displayed with the explanation that this format is macro-free. The Excel Macro-Enabled Workbook format is intended to store workbooks containing macros. When saving a workbook containing a macro as a .xlsm file, a Privacy Warning message appears. 15

Click OK to close the message box and then close the workbook.

Open the Secure Computer Sales (macro) - Student workbook. The following security warning displays below the Ribbon:

If this security warning is not displayed or does not allow you to click Options, then the Macro Setting may need to be changed. 17

If necessary, click the Office Button and click Excel Options. Click the Trust Center category and click Trust Center Settings. In the Trust Center dialog box, select the Macro Settings category, select Disable all macros with notification.

Click OK. Close the Secure Computer Sales (macro) - Student workbook and open it again.

120

Lesson 3

Collaborating with Others

Because you know you had created this macro for this workbook (and therefore you can trust it), you will enable the macro. 19

Click Options. The Security Alert â&#x20AC;&#x201C; Macro dialog box is displayed.

Click the Enable this content option button, and click OK.

Select cell A22, and press

Save the workbook.

If necessary, click OK to close the privacy warning and then close the workbook.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

on the keyboard.

121

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Compatibility With Previous Versions of Excel 5.4

As part of its major upgrade from previous versions, Excel 2007 uses a different format for storing its files as demonstrated by the use of new file extensions: File Type

2007 Extension

97-2003 Extension

Excel workbook

.xlsx

.xls

Excel workbook with macros

.xlsm

.xls

Excel workbook template

.xltx

.xlt

Excel add-in

.xlam

.xla

Excel 2007 will directly open files created in earlier versions of Excel, and will continue to preserve that format in the file. The workbook title bar also displays the [Compatibility Mode] indicator, as shown here:

Workbooks created in earlier versions of Excel will open automatically and display this indicator in the title bar as a reminder that it was created in a different version of this program. Excel 2007 will not make any changes to the workbook in any way, such as changing the formatting or the default font. However, you can now also choose if you want to save your workbook in its original (.xls) file format, or in the Excel 2007 (.xlsx) file format. Microsoft has provided a large number of file formats you can choose from to save your files, using Save As in the Office menu or Save as type which you will find on the Save As dialog box, beneath the file name field.

122

Collaborating with Others

Lesson 3

Excel 2007 enables users to set up a default for saving workbooks in the format of earlier versions of Excel. Unlike earlier versions that forced you to save all workbooks in the new version, Excel 2007 enables you to choose between the old and the new formats. This can be very handy for those who share workbooks with others who are using different versions of Excel. You can set the default file format for saving files to an earlier version by clicking the Office Button, clicking Excel Options, then clicking Save to access the Options dialog box where you can then set your default Save options.

Note that you will not be able to open an Excel 2007 workbook in an earlier version of Excel without the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats. This Compatibility Pack is available as a download on the Microsoft Office 2007 Web site. It is also available in Help as a link in the Create a document to be used by previous version of Excel. For instance, without the Compatibility Pack installed, you will not be able to open a cash flow report created in Excel 2007 using Excel 2003 or Excel 2002. Once the Compatibility Pack has been installed, Excel displays a message indicating it is converting the 2007 file to this version. If you are making changes, you can then save the file in its original format (.xlsx for Excel 2007), or save it in the format matching your version of Excel. A better option is to use Excel 2007 to save a workbook using the format of the previous Excel version. You can use the built-in Compatibility Checker to look for any features that are not supported in previous versions. Examples include changes to conditional formatting, larger worksheet sizes, and more sort sequences. A complete list is available in the Help documentation â&#x20AC;&#x201D; look under Compatibility Checker.

Learn the Skill 1

Open the Incompatible Formats workbook.

Click the Office Button, click Prepare, then click Run Compatibility Checker.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

123

Lesson 3

Microsoft® Office Excel 2007 – Level 3

Click Copy to New Sheet. A new worksheet is created containing the details of the incompatibilities.

Close the workbook without saving.

Lesson Summary In this lesson, you looked at different ways you can share data with others to collaborate on changes. You also looked at how to add protection on a worksheet or the entire workbook, as well as how to remove personal information and mark workbooks as final. You should now be familiar with the following: 

creating a shared workbook



protecting files using digital signatures



resolving shared workbook conflicts



changing workbook properties



working with protected shared workbooks



tracking changes on shared workbooks

using the Document Inspector before distributing workbooks



reviewing comments and changes



understanding compatibility issues



removing shared use on workbooks



marking workbooks as final



protecting your worksheet and the workbook

124

Collaborating with Others

Lesson 3

Review Questions 1.

When would you create a shared workbook?

Give some examples of types of conflicts you can experience when working with shared workbooks.

Give two examples of how you can maintain some control over shared workbooks.

When would you want to track the changes made to a worksheet?

How can you use comments in a worksheet?

Whatâ&#x20AC;&#x2122;s the difference between protecting a worksheet versus a workbook?

When would you use digital signatures on a workbook?

Give an example of why you might want to change or add properties to a workbook.

What is the Document Inspector?

10. Explain what compatibility issues may arise with working with files created or saved for earlier versions of Excel. 11. If you need to make your workbooks compatible with an earlier version of Excel, how would you set this up in Excel? 12. What does marking a workbook as final do?

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

125

Lesson 3

Microsoft® Office Excel 2007 – Level 3

126

Microsoft Business Certification Series ®

Lesson 4: Using Analysis Tools Lesson Objectives In this lesson, you will work with a variety of tools to help analyze different types of data, including setting up “what-if” scenarios, working with data tables and PivotTables. On successful completion of this lesson, you should be able to: 

set up a what-if scenario with a worksheet



use formulas in data tables



use the Goal Seeking tool



create and modify PivotTables



use the Solver tool



create and modify PivotCharts



work with a single or two variable data table

Lesson 4 Buttons The following command buttons are covered in this lesson: Data tab

PivotTable Tools – Design tab

PivotTable Tools – Options tab

127

Lesson 4

Microsoft® Office Excel 2007 – Level 3

128

Using Analysis Tools

Lesson 4

PivotChart Tools – Analyze tab

PivotChart Tools – Design tab

PivotChart Tools – Format tab

PivotChart Tools – Layout tab

What-If Analysis One of the most common applications for spreadsheets is “what-if” analysis; the ability to pursue an almost endless cycle of trial-and-error use of base numbers in formulas and therefore be able to make important decisions quickly. The traditional manual method — even with the help of handheld calculators — instantly became obsolete because spreadsheets were able to recalculate a large number of formulas simultaneously to display the end result. This capability saved a tremendous amount of manual work and virtually eliminated calculation errors.

Learn the Skill This exercise will demonstrate the use of what-if analysis to perform a simple break-even calculation. Assume that you are about to graduate from school and enter the workforce. You are trying to determine what your income needs are to support the anticipated expenses. 1

Open the Income What-If workbook and save as Income What-If - Student.

In the following cells, enter the specified formulas: Cell Formula B2 =B1*.25 B3 =B1-B2 B15 =B3-B13 You will try an optimistic target: cover expenses and still be able to save $50 per month. Calculating the correct gross pay amount is complicated by the income tax. You might start by simply adding the desired savings to the total expense amount: $50 + $2,175 = $2,225.

Select cell B1 and enter: 2225.

129

Lesson 4

Microsoft® Office Excel 2007 – Level 3

Excel has quickly calculated an end result of -$506.25, which clearly shows that you will not be earning enough to cover your expenses, let alone put aside money into savings. 4

Repeat step 3 with other gross pay values to try to achieve an end result of $50 to deposit into savings. You will notice that you only have to enter different values into one cell, while Excel recalculates the other formulas on the worksheet to show the final result in cell B15. You then only need to assess whether the final result value is acceptable or more changes are required.

Select cell B1 and enter: 2967. You can also change other cell values as well. Assume that some of your expenses do not appear to be correct.

Change cell B7 to 200 and B10 to 275.

Change the gross pay value in cell B1 to achieve a savings amount of $50 in cell B15 again. The worksheet should appear as follows:

Save and close the workbook.

Using the Goal Seeking Tool Spreadsheets are developed using formulas with the result of the calculation being placed in a particular cell. This works well when you know all the variables for the calculation. But what happens when you don’t know all the variables, yet you do know the answer you want? A very common business example is determining the break-even point for a new product. The trial and error method works, but it becomes tedious considering the amount of computing power that modern computers have. A much easier solution is to use the Goal Seek tool in Excel. The Goal Seek tool is an add-in and must be installed prior to being able to use this tool. To install it, click the Office Button, click Excel Options, and in the Add-ins category, ensure the Manage field shows Excel Add-ins and click Go. Then click Solver from the list, click OK, and then click Yes to install this feature. The Goal Seek tool actually automates the entire process changing one variable while calculating a function and trying to match the desired value. In a break-even situation the Goal Seek tool can be set to change the number of units produced while checking the profit calculation for a 0 value. Using this tool you can easily determine the impact of 10% increase in labor cost on the number of units needed to break-even.

130

Lesson 4

Using Analysis Tools The variables used to perform the goal seek are as follows:

Set cell

The cell that contains the desired result. This cell must contain a formula to calculate the result using values from other cells in the worksheet.

To value

The end result value that you want to achieve. It must be a numeric value — it cannot be a formula or reference a cell.

By changing cell

The variable cell that contains the value to be changed to achieve the desired result. This cell must not contain a formula. If you imagine that you had to perform the what-if analysis without the goal seek feature, you would have to continuously change this cell value.

When using the Goal Seek tool, keep the following in mind: •

The Goal Seek tool is not a function; it changes the value of the variable cell to achieve the desired goal. If any of the values or formulas involved with the results are changed, you must run the Goal Seek tool again.

•

The cell displaying the outcome must contain a formula that is dependent on the variable cell, either directly or indirectly.

•

Changes made using the Goal Seek tool can be reversed using the Undo feature.

Learn the Skill This exercise demonstrates how to use the Goal Seek feature to perform the what-if analysis that you did manually earlier. 1

Open the Income What-If workbook and save as Income Goal Seek - Student.

In the following cells, enter the specified formulas: Cell Formula B2 =B1*.25 B3 =B1-B2 B15 =B3-B13

Select cell B15.

On the Data tab, in the Data Tools group, click What-If Analysis, and click Goal Seek. The Goal Seek dialog box will appear with the Set cell text box referring to the answer cell.

Select the To value text box and set the value to 50.

Select the By changing cell text box and type: B1 or use the mouse to select cell B1.

Click OK to initiate the goal seek analysis.

131

Lesson 4

Microsoft® Office Excel 2007 – Level 3

The Goal Seek tool will display the result on the Goal Seek Status dialog box. If Excel was able to find a solution the Target value and Current value will be the same. The Goal Seek dialog box should appear as follows:

Click OK in the Goal Seek Status message box to accept the results of the goal seek.

Now determine what the income needs are to support a reduced amount of expenses. 9

Change cell B7 to 200 and B8 to 250.

Repeat steps 3 to 8 to determine the income with a target of saving $10 per month. The worksheet should appear as follows:

Save and close the workbook.

Practice the Skill In this exercise, you will perform another Goal Seek to calculate the break-even point for a production line. 1

Open the Goal Seek workbook and save as Goal Seek - Student.

In the following cells, enter the specified formulas: Cell Formula C5 =C1*B5 C6 =C1*B6 C8 =SUM(C4:C7) C11 =C1*B11 C12 =C11*.02 C13 =C11-C12 C15 =C13-C8 In this worksheet Excel will determine the number of units required to have the unit cost match the revenue, the break-even point.

Select cell C15.

On the Data tab, in the Data Tools group, click the What-If Analysis drop-down, and click Goal Seek.

132

Using Analysis Tools

Lesson 4

Select the To value text box and set the value to 0 (zero).

Select the By changing cell text box, type: C1 or use the mouse to select cell C1, and click OK.

Click OK. The worksheet should appear as follows:

Save and close the workbook.

Using the Solver The Goal Seek function works well when there is only one variable that can be changed to reach a solution. To find solutions with more than one variable is a much more complex task known in mathematics as Linear Programming. The Solver tool is an optional add-in that comes with Excel. If the Solver item does not appear in the Analysis group in the Data tab (or the Analysis group is not displayed at all), you must use the Excel Add-in manager to install it. Linear programming has many applications in the real world, for instance determining the mix of products to manufacture based on costs, ability to manufacture, profit and ability to sell. The Solver is a powerful and flexible tool for solving these types of problems. The linear programming model consists of three components: •

The first component is the Objective Function (called the Target Cell in the solver) that Excel will optimize. You can choose to Maximize, Minimize, or choose a Value of number for this objective function.

•

The second component, the Decision Variables (called the Changing Cells in the solver) are the variables for the Objective Function that you will modify to determine the optimal solution.

•

The Constraints make up the third component. These are the limits or conditions you impose on the model.

In some situations there may be more than one workable solution to the problem; in which case the solution that the Solver produces can be affected by the starting values chosen. You should set the variables to your best guess to produce the optimal solution you are seeking. If the Solver finds a solution different from what you expect, change the values and rerun the Solver. If the Solver cannot find a solution, carefully review your worksheet functions and constraint equations. With the number of variables involved it is easy to make an error.

133

Lesson 4

Microsoft® Office Excel 2007 – Level 3

Learn the Skill In this exercise, you will use the Solver to help you decide whether your budget is adequate to purchase a new house. The Objective Function is the calculated difference between your take-home pay and your expenses. It cannot be less than zero, but it will not likely be greater than zero because you want the best house you can possibly afford. 1

Open the Mortgage Solver workbook and save as Mortgage Solver - Student.

Enter the following formulas in the specified cells: Cell Formula B2 =B1*.4 B3 =B1-B2 B5 =E10 B8 =SUM(B5:B7) B10 =B3-B8 E6 =E4-E5 E10 =PMT(E8/12,E9*12,-E6) It appears that your current income level does not support this mortgage. You will have to change the house price and the mortgage term to find an affordable solution. Instead of trial and error you will use the Solver to find the best solution. The Goal Seek tool cannot be used to calculate the solution because there are more than one variable that needs to be changed; specifically, the price of the house (cell E4) and the length of the mortgage term (cell E9). To keep the exercise simple, it is assumed that the person’s income and their personal expenses cannot be changed. This loan payment calculation function is valid in the United States where the interest on mortgages is compounded monthly. The function is not correct in Canada where the interest on mortgages is compounded semi-annually.

On the Data tab, in the Analysis group, click Solver. If the Solver does not appear in the Ribbon, then it has to be loaded in. To load the Solver, click the Office Button and click Excel Options. In the Excel Options dialog box, select the Add-Ins category. Click the Manage drop-down button at the bottom of the dialog box, select Excel Add-ins and click Go. In the Add-Ins dialog box, click the Solver Add-in check box to turn it on and click OK. The Solver Parameters dialog box will be displayed to allow you to set up the linear programming model.

Select the Set Target Cell field in the Solver Parameters dialog box, then select cell B10 from the worksheet.

Select the Value of option and enter, if necessary, 0 (zero) in the value field.

134

Using Analysis Tools

Lesson 4

When you select By Changing Cells, the solver puts different values into these cells to achieve the desired goal that you designated in step 5. There are two variables that you can change: the price of the house (cell E4) and the amortization term (cell E9) of the mortgage. You cannot change the interest rate, and the down payment is already the maximum that you can afford, so these two cells would not be included in the solver. 6

Select the By Changing Cells field.

Select cell E4 (Price) from the worksheet.

Hold and select cell E9 (Term) from the worksheet. If necessary, move the Solver Parameters dialog box to make cell E9 visible, or click Collapse. Although constraints do not appear to be important on the surface, they are in fact crucial to the process. If not used, the solver will create meaningless solutions. You must look at each cell in the worksheet that affects the target cell and the changing cells, and determine if there are any limitations or constraints. As the solver performs its calculations, the monthly payment value (cell E10) will also be calculated. However, the monthly payment cannot exceed 30% of your Gross Pay. To prevent the solver from trying to set a monthly payment that is too high, you need to enter a constraint to the solver.

Click Add in the Subject to the Constraints section of the dialog box. The Add Constraint dialog box will appear. This dialog box is used to add one or more constraints to the Solver Parameters dialog box.

Select the Cell Reference field of the Add Constraint dialog box then select cell B5 from the worksheet.

Select the <= constraint operator.

In the Constraint field type: B1*.3.

Click OK. The Solver is now ready to run.

Click Solve.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

135

Lesson 4

Microsoft® Office Excel 2007 – Level 3

The Solver Results dialog box will open with a message indicating whether or not the Solver was able to find a solution.

If the Solver returned an error message or the results of the Solver are not satisfactory, you can restore the original values, make some adjustments and rerun the Solver program. Notice now that the mortgage period is a ridiculous number of years, which is well over the limit of 25 years used in most financial institutions. To reach a usable solution you need to add another constraint on the term to limit it to no more than 25 years. In addition the term must be expressed as a whole number. 15

Select the Restore Original Values option on the Solver Results dialog box and click OK. You can also click Cancel.

On the Data tab, in the Analysis group, click Solver. The previously entered values are still there.

Add the new constraint to the dialog box. 17

Click Add.

Set the Cell Reference to cell E9 of the worksheet.

Click the operator drop-down list box and select int then click Add to add another constraint.

Set the Cell Reference to cell E9 of the worksheet again, select the <= constraint operator, and set the Constraint value to 25. Click OK.

Click Solve.

136

Using Analysis Tools

Lesson 4

If at any time you get a #DIV/0 (division by zero) error message in the payment amount field, add the constraint: E10 >= 1 to the Solver. This time the Solver came up with a solution without any error.

Select Keep Solver Solution in the Solver Results dialog box and click OK.

Save and close the workbook.

Practice the Skill With the same workbook, change the payroll deduction from 40% to 25%. The result will be that the Solver cannot find a solution that leaves the net income at 0 (zero). 1

Open the Mortgage Solver - Student workbook, and save it as Mortgage Solver v2 - Student.

Change the formula in cell B2 to 25%: =B1*0.25.

Run the Solver with the existing values and constraints.

Click Cancel in the Solver Results dialog box.

In looking closely at the worksheet with the new Solver solution, the problem becomes apparent: one of the constraints is that the mortgage payment cannot exceed 30% of the gross income ($3,400) which is $1,020. The Solver has been told to spend all of your income (get cell B10 down to 0) so that means the mortgage payment will be the largest amount possible. However, because of the 30% limit, the Solver cannot set the mortgage payment high enough to spend all of your income and so you will have money left over even after paying all expenses. The solution to solving this error is to recognize that you will have savings left over at the end of the month. The Solver just simply needs to be adjusted to minimize this value in order to maximize the mortgage payment. First, change the description of the cell then re-run the Solver.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

137

Lesson 4 5

Select cell A10, and enter: Savings.

On the Data tab, in the Analysis group, click Solver.

In the Equal To section, select Min.

Click Solve.

Microsoft® Office Excel 2007 – Level 3

A solution is now displayed. 9

In the Solver Results dialog box, click OK.

Notice that the mortgage term only had to increase by 1 year. If you wish to find the maximum amount you can afford for a house, you can increase the mortgage term to the maximum length possible — such as 25 years. If you wish, you can change the value in cell E9 to 25 (as your new “guess”) and re-run the Solver. 10

Save and close the workbook.

Using a Data Table Working with Single Variable Data Tables When looking for solutions to problems it is often beneficial to see the results of multiple variables at the same time. The Goal Seek and Solver tools provide single solutions requiring you to change the input variable each time you want to see the results. To be able to compare the results of different values you can create a data table that displays the different results for different input values in a single table. A data table is simply a table in Excel that displays one or two input variables and the result of a calculation using the input variables. You will also look at a data table with a single input variable that can be used to determine the effect of changes in interest rates on a mortgage. Data tables can be either column-oriented or row-oriented. If it is row-oriented, then create the formula in a cell, and all of the results of the data table will be displayed in the same row starting with the next cell on the right. The input data must also be entered in the row directly above the formula. If the data table is column-oriented, then the results will be displayed in the same column as the formula starting with the next cell below, and the input data must be entered in the next column to the left of the formula.

138

Using Analysis Tools

Lesson 4

Learn the Skill In this exercise, you will create a data table to create a basic multiplication table, with various numbers multiplied by 10. 1

Open the Multiplication Tables workbook and save as Multiplication Tables - Student. Select the One Variable sheet tab.

To create a data table, you must enter the basic formula into an empty cell in the worksheet. Because this will be a row-oriented data table, there must be enough cells available to the right of that formula to show the results, and there must be a row above for the input data. For this exercise, the formula will be entered into cell B2. For this exercise, the basic formula is: Product = multiplier * 10 Row 1 is used as the input data. For example, with the input value in cell C1, the result will appear in cell C2. The input value in D1 will be used to calculate the result in cell D2, and so on. As you know, all formulas in Excel must specify the data and the actual cell references needed to perform the calculation. Data tables are different because the formula is only entered once, but the results will be calculated for an unknown number of times. The trick is that you will still enter the formula using a specific cell reference, but Excel will override that cell reference using the input data. In this exercise, cell A1 is arbitrarily chosen for the basic formula. The selected cell must not contain any data because Excel is going to use it to build the table. Excel will take the input value from row 1 of each column, calculate the formula, and place the result in row 2. 2

In cell B2, enter: =A1*10.

With the basic formula entered, the row-oriented data table is ready to be created, starting with the next cell to the right. For this exercise, you will calculate the data table using input values between 10 and 20. The data table can have as many or as few input values as you wish and they can be any value, as long as they are valid values that can be used in the formula. If this exercise was a column-oriented data table, then the input values would have to be listed down column A starting at cell A2 (below and to the immediate left of the formula). The results would then be displayed down column B below cell B2. The labels in cells B1 and A2 are optional, and are used in this exercise to help explain the purpose of the data in rows 1 and 2. 3

Select cells B1:M2.

On the Data tab, in the Data Tools group, click What-If Analysis and click Data Table.

In the Data Table dialog box enter the cell reference that is used for the basic formula. If the data table is row-oriented, then the cell reference is entered into the Row input cell text box. If the data table is columnoriented, then the cell reference is entered into the Column input cell text box.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

139

Lesson 4 5

Microsoft® Office Excel 2007 – Level 3

Select the Row input cell field, then select cell A1 on the worksheet. For the Row or Column input cell reference, you must select the same input cell as what was used in the basic formula in step 2.

Click OK to construct the data table. Excel will now create the data table, substituting the values in row 1 for the input cell value in the formula, and placing the calculated value in row 2. The completed worksheet should appear similar to the following:

Select some of the cells in the range of C2:M2, and note the contents of each cell.

Save the workbook.

Practice the Skill In this exercise, you will create a column-oriented data table to calculate the payment on a mortgage for a variety of interest rates. The resulting data table makes it easy to see how the interest rate affects your monthly mortgage payment amount. 1

Open the Mortgage Payments workbook and save it as Mortgage Payments - Student. If necessary select the One Variable worksheet. The data table will be created in the cell range A5 to B22. Because the table uses the column-oriented format, the formula (PMT) will be in the upper right corner cell B5. The input cell will be arbitrarily chosen as cell B3, with the input values taken from the left column of cells (A6 to A16). The calculated values for each input value is placed in the corresponding cell to the right in column B.

In cell B5 type: =PMT(B3/12,B2*12,-B1).

The format of the PMT formula is: =PMT(rate,term,pv) where rate is interest rate per month, term is number of months, pv is amount of mortgage or loan. 3

Select A5:B22, then on the Data tab, in the Data Tools group, click What-If Analysis and click Data Table.

Click in the Column input cell then click cell B3 on the worksheet.

Click OK.

140

Using Analysis Tools

Lesson 4

Now observe changes to the two cells that are referenced by the PMT formula, containing the mortgage amount and the term. The formula used to create the data table can refer to cells other than the input variable. Once the table has been created, changing any of the values used in the formula changes the results of the table. 6

Change the Mortgage Amount in cell B1 to 250,000.

Change the Term to 15 years.

Change the Mortgage Amount again to 150,000. The worksheet should now appear as follows:

Save the workbook.

Working with Two-variable Data Table A single variable data table is useful when you only want to see the effects of different values of one variable. You can create a similar two-variable data table to observe the effects of two variables on each other. A two-variable data table is simply a row-oriented and a column-oriented data table combined.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

141

Lesson 4

Microsoft® Office Excel 2007 – Level 3

For example, a mortgage table could use just the interest rate as the variable, with the resulting impact on the monthly payment. This is useful if you have already chosen the term of the mortgage. If you are also considering different terms for the mortgage as well, then a two-variable data table will show the impact of changes in both.

Learn the Skill In this exercise, you will create a multiplication table with two input values multiplied together. 1

Ensure that the Multiplication Tables - Student workbook is open, and select the Two Variable sheet tab.

Enter the formula for the data table. Note that any two empty cells from the worksheet can be used as input cells in the basic formula. As with the one variable data table, Excel will put the input values into these two cells to calculate the result for each cell in the two variable data table. Also, this formula must be in the upper left corner cell of a two-variable data table. 2

Select cell B2, and enter: =A1*A2.

Now select the cells for the data table. 3

Select cells B2:M13.

On the Data tab, in the Data Tools group, click What-If Analysis and click Data Table.

Select the Row and Column input cells, which must be the same cells referenced in step 2 above. 5

Select the Row input cell field, then select cell A1 on the worksheet.

Select the Column input cell field, then select cell A2 on the worksheet.

Click OK to construct the data table. The completed worksheet should appear similar to the following:

Save the workbook.

Practice the Skill In this exercise, you will create a data table using two input values for a mortgage calculation formula. 1

Open the workbook Mortgage Payments - Student and select the Two Variables worksheet.

Enter the data table formula using cells C1 and D1 from the worksheet, and select the data table range. 2

In cell A4 type: =PMT(C1/12,D1*12,-B1).

Select the range A4:F21.

On the Data tab, in the Data Tools group, click What-If Analysis and click Data Table.

Select the two input cells for the data table.

142

Using Analysis Tools

Lesson 4

Be sure to select the correct cells in the Table dialog box. The Row input cell field will substitute values in the same row as the data table formula. The Column input cell formula will substitute values in the same column as the data table formula. 5

Select the Row input cell field, then select cell D1 on the worksheet.

Select the Column input cell field, then select cell C1 on the worksheet.

Click OK to build the data table. The completed worksheet should look similar to the following:

The resulting table shows payments for different interest rates and varying numbers of years. 8.

Save the workbook.

Multiple Formulas in a Data Table A Data Table can have more than one formula, using a single set of input values, by having additional rows or columns in the table. These multiple formula data tables are created in the same manner as with a single formula. Like the one variable data table, these data tables are either row-oriented or column-oriented.

Learn the Skill In this exercise, you will extend the Multiplication Table workbook by adding in other formulas using the values in the worksheet. 1

Ensure that the Multiplications Tables - Student workbook is open, and select the Multiple Formulas sheet tab.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

143

Lesson 4 2

Microsoft® Office Excel 2007 – Level 3

Enter the following formulas: Cell Label/Formula B2 =A1*10 B3 =A1+12 B4 =A1-7 B5 =A1/3.5

Select cells B1:M5.

On the Data tab, in the Data Tools group, click the arrow for What-If Analysis and click Data Table.

Select the Row input cell field, then select cell A1 on the worksheet.

Click OK to construct the data table. The completed worksheet should appear similar to the following:

Save and close the workbook.

Practice the Skill In this exercise, you will calculate the total interest paid on the mortgage for the various interest rates. 1

Open the Mortgage Payments - Student workbook and then the Multiple Formulas worksheet.

Enter the following labels and formulas: Cell Label/Formula B5 =PMT(B3/12,B2*12,-B1) C5 =B5*B2*12-B1

Select the range A5:C22.

On the Data tab, in the Data Tools group, click What-If Analysis and click Data Table.

Select the Column input cell, then select cell B3 on the worksheet. Click OK. The completed worksheet should appear similar to the following:

Save and close the workbook.

144

Using Analysis Tools

Lesson 4

Using PivotTables The PivotTable is one of the most powerful features in Excel. The PivotTable lets you summarize or cross-tabulate large amounts of data by selecting fields for rows and columns and performing a summary function on the intersections of the row and column fields. The key difference between a PivotTable and a regular database is that the PivotTable uses repeating values in a regular database as the row and column headers. The various repeating values can be paired in various combinations to reveal underlying trends with sudden clarity.

Creating a PivotTable The PivotTable Field List task pane controls the structure of the PivotTable. You use this task pane to decide what column data (from your source data table, and now referred to as fields) to use as the column labels, row labels, PivotTable values, or report filter.

Report Filter

Fields put into this list box are used to select (or filter) the rows of data from the source data to appear in this PivotTable.

Column Labels

Fields put into this list box are displayed as columns across the top of the PivotTable.

Row Labels

Fields put into this list box are displayed as rows down the PivotTable.

Values

Fields put into this list box are summarized in the main body of the PivotTable. The default summary function is SUM, although it can be changed to other functions such as AVERAGE, MIN, and MAX.

Excel allows you to put more than one column from the source data into each list box in the PivotTable Field List.

Learn the Skill This exercise will demonstrate how to create a PivotTable. 1

Open the Farley’s Farm Supplies workbook and save as Farley’s Farm Supplies - Student.

Select a cell within the cell range A1:E22. On the Insert tab, in the Tables group, click PivotTable.

145

Lesson 4

Microsoft® Office Excel 2007 – Level 3

The Create PivotTable dialog box is displayed. In this dialog box you select the source of the data and whether to put the PivotTable on the current worksheet or a new one. PivotTables can be created from data in a single range, multiple ranges, external data sources or even other PivotTables. If you select an external data source Excel starts Microsoft Query to create a query that will retrieve the data for the PivotTable.

Click OK. The skeleton PivotTable is now ready. Notice that it has been created in a new worksheet. You can now create a summary which displays the total quantity of each item purchased by each purchaser.

Click the check box for the Purchaser, Description, and Quantity fields in the PivotTable Field List to turn them on and indicate that they are to be used in the PivotTable. Excel does not know exactly where these fields will be placed, so it will take a guess for now.

You can also drag the fields directly from the field list in the top half of the PivotTable Field List task pane to the list boxes located in the bottom half. Excel had placed the Purchaser and Quantity fields into the correct list boxes. The Purchaser names will be listed down the rows of the PivotTable, and the Quantity values will be SUMmed in the main body. However, the Description field needs to be moved to the columns.

146

Using Analysis Tools

Lesson 4

Click and drag the Description field from the Row Labels list box to the Column Labels list box.

Click a cell outside of the PivotTable to close the PivotTable Field List task pane and view all of the data.

Now remove some of the field values from the PivotTable, such as any non-seed items. 7

Click the Column Labels AutoFilter button in the PivotTable.

Click the Diesel fuel, Gasoline, and Motor oil check boxes to turn them off. Click OK.

Now reduce the width of the columns in the PivotTable to use only enough to display all of the data. 9

Select cells B4:I10. On the Home tab, in the Cells group, click Format, click AutoFit Column Width. The worksheet should now be similar to the following:

The PivotTable Field List task pane will automatically turn itself on and off by selecting a cell inside or outside the PivotTable. 10

Click any cell inside the PivotTable to display the PivotTable Field List task pane.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

147

Lesson 4

Microsoft® Office Excel 2007 – Level 3

The PivotTable Field List task pane can also be turned on or off using the Ribbon. Under the PivotTable Tools, on the Options tab, in the Show/Hide group, click Field List to toggle it on or off. 11

Save the workbook.

Practice the Skill In this exercise, you will practice creating another PivotTable. 1

Open the Video Rentals Database workbook, and save it as Video Rentals PivotTable - Student.

Select a cell within the data range.

On the Insert tab, in the Tables group, click PivotTable.

Accept the defaults and click OK to create the PivotTable. Note that this dialog box allows you to insert the field buttons directly into the PivotTable as part of the wizard function. You will create a PivotTable that summarizes the money spent by each customer for each rental type category. The date value will also be included for overall selection purposes.

Click and drag the Rental Type field to the Column Labels list box.

Click and drag the Customer No field to the Row Labels list box.

Click and drag the Date field to the Report Filter list box.

Click and drag the Price field to the Values list box.

Now change the date selection value for the PivotTable. 9

Click the Date AutoFilter in cell B1.

Select 1-Oct-2008 and click OK.

148

Using Analysis Tools

Lesson 4

Click the Date AutoFilter button again, then select 8-Oct-2008 and click OK. By selecting a different date, the PivotTable is automatically updated to show only the data for that date.

Select columns B to F.

On the Home tab, in the Cells group, click Format and click Column Width to reduce the width to 13. Click OK.

Close the PivotTable Field List. Note that this PivotTable shows several information facts at a glance: -

the grand total revenue for video rentals for the date(s) selected — in the bottom right corner

the total revenue generated from each customer for the date(s) selected, for each category — the numbers in the main body of the PivotTable

the total revenue generated from each customer for the date(s) selected, for all categories — the Grand Total column at the far right

the total revenue generated from each category, for all customers — the Grand Total row at the bottom

The worksheet should now be similar to the following:

Save the workbook.

Creating a PivotChart The data displayed in a PivotTable can be put into a chart, which is referred to as a PivotChart. The only special aspect of a PivotChart is that it also has a PivotChart Filter Pane that enables you to control the data series displayed in this chart. A PivotChart can be created from a PivotTable, or directly from the source data. If you do create the PivotChart directly, Excel will also create the PivotTable as well in the same worksheet because PivotCharts must be linked directly to a PivotTable.

149

Lesson 4

Microsoft® Office Excel 2007 – Level 3

Learn the Skill This exercise will demonstrate how to create a PivotChart from an existing PivotTable. 1

Select the Farley’s Farm Supplies - Student workbook. If necessary, select the Sheet4 tab where the PivotTable is located.

Select any cell in the PivotTable.

Under PivotTable Tools, on the Options tab, in the Tools group, click PivotChart.

Select the Stacked Column chart in the top row, second from the left of the Column section and click OK.

Move the PivotChart to a new position below the PivotTable.

The PivotChart is linked directly to the PivotTable. Any change made to the PivotTable will cause the PivotChart to change as well. 6

Click the Column Labels AutoFilter button.

Click the Bean seed check box to turn it off and click OK.

150

Using Analysis Tools

Lesson 4

The PivotChart is automatically changed to reflect the change in the structure of the PivotTable.

Save the workbook.

Practice the Skill This exercise will demonstrate how to create a PivotChart. 1

Open the Video Rentals PivotTable - Student workbook, and select the Sheet1 worksheet tab.

Select a cell within the data range.

On the Insert tab, in the Tables group, click the PivotTable drop-down button and select PivotChart. The Create PivotTable with PivotChart dialog box displays. This dialog box is essentially identical to the Create PivotTable dialog box, except that Excel will take the extra step to insert the PivotChart as well.

Accept the defaults and click OK to create the PivotTable.

Click in any cell in the PivotTable. Note the label names used for each of the list boxes in the PivotTable Field List. When the PivotChart is selected, these label names will change to correspond to what they are referred to in a chart.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

151

Lesson 4 6

Click anywhere inside the PivotChart.

Click and drag the following fields into these PivotChart list boxes: Field Place in Rental Type Legend Fields Customer No Axis Fields Video Title Report Filter Price Values

Move and resize the PivotChart so that you can clearly view it.

Microsoft® Office Excel 2007 – Level 3

The PivotChart should now look similar to the following:

Like the PivotTable, the AutoFilter feature can also be used in a PivotChart to select one or more data series for display. The other data series will be filtered out. You can use the AutoFilter directly in the PivotTable or from the PivotChart Filter Pane. Currently all Rental Type data series are displayed in the chart. Suppose you want to only show the 7 Night weekly rentals in your PivotChart. 9

Click the Report Filter Legend Fields (Series) AutoFilter button in the PivotChart Filter Pane.

Click the Select All check box to turn it off.

Click the 4-7 Night weekly rental check box to turn it on and click OK.

152

Using Analysis Tools

Lesson 4

The PivotChart now looks like this:

Save the workbook.

Modifying Field Positions Once you have created the PivotTable you can easily change the order of the fields by dragging them to different positions in the row and column header areas or delete the field from the PivotTable by dragging the field off the PivotTable area. Once you move or delete a field, Excel reorganizes and calculates the values for the PivotTable. In addition to reorganizing the fields it is possible to group field values and to hide or show the group details.

Learn the Skill This exercise demonstrates how to change the fields in a PivotTable. 1

Select the Farley’s Farm Supplies - Student workbook and select the PivotTable on Sheet4.

First clear any AutoFilters that may be turned on. 2

Click the Column Labels AutoFilter button, then click Clear Filter From “Description”. The AutoFilter symbol is no longer showing for any field in the PivotTable Field List.

Now remove the Description field from the PivotTable. 3

Click the Description check box in the PivotTable Field List to turn it off.

Click and drag the Purchaser field from the Row Labels list box to the Column Labels list box. Notice that the PivotTable now has only one column left.

Click and drag the Date field down to the Row Labels list box.

153

Lesson 4

Microsoft® Office Excel 2007 – Level 3

The completed PivotTable (and the linked PivotChart) now appears similar to the following:

Save this workbook.

Practice the Skill This exercise will give you more practice with changing the fields in a PivotTable. 1

Select the open Video Rentals PivotTable - Student workbook and select the Sheet4 worksheet tab.

Click the Date AutoFilter button in the PivotTable Field List.

Select All and click OK.

First remove the Rental Type field from the PivotTable. 4

Click the Rental Type check box in the PivotTable Field List to turn it off.

Click and drag the Date field from the Report Filter to the Column Labels list box. The completed PivotTable now appears similar to the following:

Save the workbook.

154

Using Analysis Tools

Lesson 4

Modifying Data Field Options You can change two options for the data field in a PivotTable: its format, and how it is summarized. The field formatting option simplifies the task of formatting the values in the data field because you do not have to select all of the cells in the PivotTable first. Also, the default summarizing option is to calculate the sum total of all values for the PivotTable. You can choose other summarizing options such as counting, average, maximum, minimum, and the multiplication product.

Learn the Skill This exercise demonstrates how to modify the contents of the PivotTable by adding another field. 1

Select the Farleyâ&#x20AC;&#x2122;s Farm Supplies - Student workbook.

Now try changing the format of the Quantity data. 2

Select any cell in the PivotTable and display the PivotTable Field List.

Click the Sum of Quantity drop-down in the Values list box, and select Value Field Settings.

Click Number Format.

In the Format Cells dialog box, select Number in the Category list.

Reduce the Decimal places to 0.

Click Use 1000 Separator (,) to turn it on and select the first item in the Negative numbers list. Click OK.

Select Average in the Summarize value field by list box and click OK.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

155

Lesson 4

Microsoft® Office Excel 2007 – Level 3

The PivotTable should now appear similar to the following:

Save and close the workbook.

Practice the Skill This exercise demonstrates how to modify the contents of the PivotTable by adding another field. 1

Ensure the Video Rentals PivotTable - Student workbook is open and the PivotTable on Sheet5 is displayed.

Now try changing the format of the Price data. 2

Click the Rental Type AutoFilter button, and select Clear Filter From “Rental Type”.

Click the Sum of Price drop-down in the Values list box and select Value Field Settings.

Click Number Format.

In the Format Cells dialog box, select Currency in the Category list and click OK. Click OK to close the Value Field Settings dialog box.

156

Lesson 4

Using Analysis Tools Notice the formatting of the numbers as currency for all cells in the PivotTable. Now change the summarize function to counting. 6

Click the Sum of Price drop-down in the Values list box and select Value Field Settings.

In the Summarize value field by list box, select Count.

Click Number Format.

In the Format Cells dialog box, select Number in the Category list. Click OK to close both dialog boxes. The PivotTable should now appear similar to the following:

Save and close the workbook.

Lesson Summary In this lesson you worked with a variety of tools to help analyze different types of data, including setting up “what-if” scenarios, working with data tables and PivotTables. You should now be able to:  set up a what-if scenario with a worksheet

 use formulas in data tables

 use the Goal Seeking tool

 create and modify PivotTables

 use the Solver tool

 create and modify PivotCharts

 work with a single or two variable data table

157

Lesson 4

Microsoft® Office Excel 2007 – Level 3

Review Questions 1.

What’s the difference between setting up a manual “what-if” worksheet versus using the Goal Seeking tool?

If the Solver tool is not available in the Data tab, what does this mean?

What is a data table?

Provide an example of when you might want to create a data table.

Why might you use a two variable data table instead of a single variable data table?

How can you set multiple formulas in a data table?

Explain why you might create a PivotTable.

What’s the key difference between a database and a PivotTable?

How is a PivotChart different than a regular chart?

10. What two data field options can you change in a PivotTable?

158

Microsoft Business Certification Series ®

Appendices Appendix A Prerequisites

Appendix B Answers to Review Questions

Appendix C Courseware Mapping

Appendix D Glossary of Terms

Appendix E Index

Appendix A

Microsoft® Office Excel 2007 – Level 3

Appendix A: Prerequisites The following list summarizes skills learned in Microsoft Office Excel 2007 Level 2 that students should be comfortable with before completing this courseware. Take a few minutes to review the following items to ensure you are familiar with them: •

start and exit Microsoft Office Excel 2007

•

access commands using the Ribbon, Quick Access toolbar, or Mini toolbar

•

enter numeric and text data into the worksheet

•

create, save, open, or close a worksheet

•

select cells in the worksheet by using different selection methods

•

copy and move data in the worksheet

•

format the data in the worksheet

•

enter a simple formula as well as absolute and relative formulas

•

insert and move worksheets within the workbook

•

create and save a chart from data in the worksheet

•

change print settings for a report

•

preview and print the worksheet

•

understand what a function is and the correct syntax to insert functions

•

use math and trigonometric, statistical, financial, logical, date and time, text, or information functions

•

draw and modify different types of objects

•

insert and modify graphics such as picture or SmartArt

•

customize chart elements.

•

create and customize trendline charts

•

audit a worksheet by finding and identifying errors

•

use the Format Painter to duplicate formatting

•

hide or unhide rows, columns, or workbooks

•

customize formats for numbers, dates, scientific or text data types

•

apply conditional formatting using a variety of rules

•

create and manipulate a table

•

apply cell styles and Quick Styles

•

convert a list to a table list

•

name, modify or delete a named range

•

understand basic database concepts and terminology

•

create a database and use different filter features to find specific data

•

use some of the database functions in Excel

Answers to Review Questions

Appendix B

Appendix B: Answers to Review Questions Lesson 1 1.

Explain the difference between using the Copy and AutoFill features. Copy Cells

Text, values or formulas are copied with their formatting; does not create a series.

Fill Series

How would you set up the values if the AutoFill series is not a common trend such as weekdays, months, or years? You must enter the pattern for the series for AutoFill to detect what interval to use, e.g., Apr 5 Apr 12 Apr 19 tells Excel the pattern is 7 days between the dates, etc.

What items can Excel search for in a worksheet? Excel can search for every occurrence of a value, function name, or cell reference.

Give an example of when you would use Replace versus Replace All in a worksheet. An example of using Replace could be if you have a worksheet where you want to replace the word Expense with Cost only when the account is correctly named as a cost instead of an expense. In those situations, you would click the Replace button, otherwise you would click the Find Next button. You could use Replace All to replace every occurrence of a product name in the worksheet with a new product name.

Give at least two examples of when you might use Paste Special. You want to paste only the values into a new worksheet instead of the formulas, you want to include any formatting from the original worksheet, you want to add the numbers from several worksheets to consolidate information, or you want to transpose data from columns to rows or vice versa.

What process does consolidating data refer to? Consolidating data refers to the process of summarizing large amounts of data into a single table. You can consolidate data from different areas within a worksheet, separate worksheets or across other workbooks.

What process would you need to perform before creating automatic subtotals? Before creating the automatic subtotals, you must decide which column to summarize by. You must then sort the worksheet by that column to group these related rows together, otherwise the results will not make much sense. While working down the rows, Excel then generates a subtotal whenever the value in this column changes.

What does creating multiple subtotals refer to? Creating multiple subtotals refers to the ability to use different summary calculations (e.g. sum, average, and variance) for the same or different columns in each outline group. Each different type of subtotals must be added as separate rows to identify the type of subtotal. Complex types of multiple subtotals can be created by nesting them.

When might you use the Outlining feature? Use worksheet outlining to compress the worksheet to see only the subtotals and grand totals. You can then expand a subtotal to see the individual rows that make up that outline group.

10. How does the Sum function differ from the Subtotal function? Both functions can calculate an arithmetic sum total of the selected cell range. However, a subtotal calculation will not include any other subtotal calculations included in its range. As a result, you can insert subtotal, total, and grand total calculations into a table by simply including the entire range of cells without any risk of accidentally double-counting a subtotal. 11. Give an example of when you might want to convert text to columns. You can convert text to columns when you want to separate text into its component words, provided there is a marker that can be used to identify the end of one word from the start of the next word. This marker is referred to as a delimiter.

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Appendix B

Microsoft® Office Excel 2007 – Level 3

Lesson 2 1.

Explain how you can use data validation. Use data validation to restrict the types of data that can be entered into a worksheet or to check that the type of data matches a certain criteria such as a date, numbers only, etc.

How can you prevent duplicate rows of data? Use the Remove Duplicate Rows from the Data Tools tab to prevent duplicate rows of data.

Explain what a pick list is. A pick list is a type of data validation that can ensure only valid information is entered by allowing the user to select only from a predefined list of items. The user cannot select any other value. It is ideally suited to text data because of the extreme difficulty to apply arithmetic comparison operators (such as greater than) for text.

Give an example of why you might want to circle specific data. Circle specific data when you have just added a data validation check to one or more cells, and you want to check whether the existing data will fail the new validation checks.

Give an example of why you might want to create a template of your own. You may want to create a template of your own to save yourself time whenever you are creating a new workbook. You might use a template provided by Microsoft as a starting point and then customize for your company logo or terminology.

Provide an example of why you might want to delete a template versus editing it. Delete a template when you no longer need it or if the time spent editing it is likely to be longer than creating it from scratch again.

Give an example of when you might insert a hyperlink into a worksheet. Insert a hyperlink in a worksheet to quickly open another worksheet without having to go looking for it each time you need it.

Explain why you might want to save a worksheet in HTML format. You can save a worksheet in HTML format to send to someone else or to put on your intranet for multiple people to view.

Explain why you might want to save a range of cells only as HTML. Save a range of cells only as HTML when you want to show a specific portion of the worksheet and make this available to others for viewing.

10. How can you turn an Excel worksheet into a PDF file? You need to have the Office 2007 Add-in to convert a worksheet into a PDF file and once it’s installed, click the Office Button, point at Save As and then click PDF or XPS file.

Lesson 3 1.

When would you create a shared workbook? Create a shared workbook so that multiple people can work on the same workbook and then compare the values at a later date.

Give some examples of types of conflicts you can experience when working with shared workbooks. Some examples of conflicts that can occur with shared workbooks include different formulas or values in the same cells, inserting or deleting rows and columns or formatting cells.

Give two examples of how you can maintain some control over shard workbooks. You can maintain some control by tracking changes or by setting up password protection.

When would you want to track the changes made to a workbook? You would want to track the changes made to a workbook whenever you are concerned about unrestricted and uncontrolled changes to the data. By turning on this feature, all changes to the workbook are recorded in a history log.

Answers to Review Questions 5.

Appendix B

How can you use comments in a worksheet? Use comments as notes to yourself or others regarding the data in this cell such as updates that may be needed or status notes.

What’s the difference between protecting a worksheet versus a workbook? A protected worksheet will prevent users from entering data or formulas into locked cells in that worksheet. A protected workbook will prevent users from adding, deleting, or moving worksheets within the workbook, and prevent the worksheet windows from being moved or resized.

When would you use digital signatures on a workbook? You would use your digital signature on a workbook just like manually signing a document. Once applied to the workbook, Excel will prevent anyone else from tampering with it and everyone can rely on that protection.

Give an example of why you might want to change or add properties to a workbook. The workbook properties are useful for identifying who created the workbook, and having some keywords to help categorize the workbook to differentiate it from other similar workbooks.

What is the Document Inspector? The Document Inspector enables you to remove any personal information that may be saved with the worksheet prior to distribution, as well as remove any hidden items that may exist in the worksheet.

10. Explain what compatibility issues may arise with working with files created or saved for earlier versions of Excel. Workbooks created using the Excel 2007 format can not be opened using previous versions of Excel. They may also use features that are only available in Excel 2007, such as the new rules manager for the conditional formatting. You can download the Microsoft Office Compatibility Pack to allow your earlier version of Excel to open these workbooks, but new capabilities will be lost. Excel 2007 is able to open and use workbooks created using earlier versions of Excel. 11. If you need to make your workbooks compatible with a previous version of Excel, how would you set this up in Excel? To set up Excel to save the workbook in another version, click the Office Button, click Excel Options, then change the file format in the options for the Save category. 12. What does marking a workbook as final do? When you mark a workbook as final, you are making it read-only. This status can be reversed easily.

Lesson 4 1.

What’s the difference between setting up a manual “what-if” worksheet versus using the Goal Seeking tool? With the manual worksheet, you have to keep guessing at what values would work to get the end result. With the Goal Seeking tool, you can set the parameters for the end result and it will calculate it based on the values in your worksheet.

If the Solver tool is not available in the Data tab, what does this mean? If the Solver tool isn’t there, it means it hasn’t been installed. This is an add-in tool that must be installed before it can be used. This tool is included with Microsoft Office for use in Excel.

What is a data table? A data table is simply a table in Excel that displays one or two input variables and the result of a calculation using the input variables.

Provide an example of when you might want to create a data table. To be able to compare the results of different input values that display the different results in a single table. For example, use a data table to show the different monthly payment amounts on a mortgage using different interest rates.

Why might you use a two variable data table instead of a single variable data table? You can create a two-variable data table to observe the effects of two variables on each other. A two-variable data table is simply a row-oriented and a column-oriented data table combined. For example, the mortgage payment table could use different loan amounts and different interest rates at the same time.

Appendix B 6.

Microsoft® Office Excel 2007 – Level 3

How can you set multiple formulas in a data table? Enter more than one formula using a single set of input values, by having additional rows or columns in the table. Like the one variable data table, these data tables are either row-oriented or column-oriented. For example, the mortgage payment table could show the payment amount using different interest rates and whether the payments are made monthly, semi-monthly, bi-weekly, or weekly.

Explain why you might create a PivotTable. A PivotTable lets you summarize or cross-tabulate large amounts of data by selecting fields for rows and columns and performing a summary function on the intersections of the row and column fields.

What’s the key difference between a database and a PivotTable? The key difference between a PivotTable and a regular database is that the PivotTable uses repeating values in a regular database as the row and column headers. The various repeating values can be paired in various combinations to reveal underlying trends with sudden clarity.

How is a PivotChart different than a regular chart? The special aspects of a PivotChart are that it uses a PivotTable as its source data and has a PivotChart Filter Pane that enables you to control the data series displayed in this chart.

10. What two data field options can you change in a PivotTable? The two options you can change for a data field in a PivotTable are its format and how it is summarized.

Appendix C

Courseware Mapping

Appendix C: Courseware Mapping Skills Required for the MicrosoftÂŽ Office Excel 2007 Certified Applications Specialist Exam #77-602: Objective Domain

Level

Lesson

Creating and Manipulating Data

1.1

Insert data using AutoFill

1.2

Ensure data integrity

1.3

Modify cell contents and formats

1 2

2 6

1.4

Change Worksheet Views

1.5

Manage worksheets

1 2

2 4

Formatting Data and Content

2.1

Format worksheets

2.2

Insert and modify rows and columns

1 2

2 4

2.3

Format cells and cell content

1 2 3

4 4 1, 2

2.4

Format data as a table

Creating and Modifying Formulas

3.1

Reference data in formulas

1 2

3 5

3.2

Summarize data using a formula

3.3

Summarize data using subtotals

1 3

3 2

3.4

Conditionally summarize data using a formula

2 3

6 1

3.5

Look up data using a formula.

3.6

Use conditional logic in a formula

3.7

Format or modify text using formulas

2 3

1 1

3.8

Display and print formulas

Presenting Data Visually

4.1

Create and format charts

1 2

6 3

4.2

Modify charts

4.3

Apply conditional formatting

4.4

Insert and modify illustrations

4.5

Outline data

4.6

Sort and filter data

4, 6

Collaborating and Securing Data

5.1

Manage changes to workbooks

5.2

Protect and share workbooks

5.3

Prepare workbooks for distribution

5.4

Save workbooks

2, 3

5.5

Set print options for printing data, worksheets and workbooks

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Appendix D

Microsoft® Office Excel 2007 – Level 3

Appendix D: Glossary of Terms Active cell – The cell in which you are entering information, or the current location of the cell pointer. Add-in – Special commands or functions created by Microsoft Excel to perform tasks.

Column – A vertical arrangement for text or numbers, separated from other columns by a grid line and denoted with alpha letters per column. Excel has a maximum of 256 columns, denoted from A to IV.

Alphanumeric – Including both numbers and text.

Comma delimited text file – The format of the data exported from a program such as Excel. All data in this file is of variable length. Each cell value is followed by a comma, except for the last value in each row. See also Fixed width text file.

AutoFill – A method of copying data and formulas or creating data series by dragging the lower right corner of a cell or range.

Comments – Similar to a post-it note where you can enter information for yourself or others to review.

AutoFit – A feature that will automatically adjust the width of a column or the height of a row so that the cells are just wide or high enough to display the values in all of those cells.

Consolidating – The process of summarizing large amounts of data into a single table.

Alignment – The positioning of the contents of a cell; e.g. left, right, or centered.

AutoFormat – A feature that enables you to apply many different formatting characteristics with a single command by choosing from a selection of format templates; see Cell Styles also. AutoRecover – A feature of Microsoft Office that will help you to recover the worksheet or file last worked on before a problem occurred, e.g., system hangs, software crashes, etc. AutoSum – A tool that will quickly insert a SUM function into the current cell, and determine the appropriate cell range to be used. Bold – Dark or highlighted text.

Controls – Features that allow you to create and design a form or dialog box, e.g., text box, check box, radio button, drop-down menu, etc. Copy – An editing function used to duplicate selected cells. Cut – The editing process of transferring selected cells to the Clipboard so that you can move them from one location and place them into another. Data Table – A table that displays one or two input variables and the result of a calculation using the input variables.

Borders – The feature that enables you to add lines or surrounding borders to the selected cells in the worksheet.

Data Validation – A feature that helps you set up validation checks in the cells, e.g., numbers only, list of inventory items available, etc.

Built-in Functions – Pre-programmed formulas to do specific calculations. You can either type these functions in or use the Insert Function wizard to assist.

Database – Used for compiling and sorting (typically large) lists of data.

Cell – The intersection of a spreadsheet row and a column. It holds a single value, label, or formula. It may also contain comments, formatting, and other related data. Cell address – The location description that specifies an individual cell. Cell range – A rectangular section of a worksheet containing two or more cells. Cell reference – The use of a cell address or cell range address in a formula. Cell Styles – A feature that enables you to apply many different formatting characteristics to one or more cells with a single command by choosing from a selection of format templates; see AutoFormat also. Center – To place text in the center of a cell. Character – A character can be a letter, number, or other piece of data. The program usually stores it by using one byte of memory. Chart Wizard – The automatic feature that Excel provides to help you create a chart in a step-by-step process. Chart – A pictorial representation of the data you enter in a worksheet.

Default – The standard settings that are in effect unless you change them for an individual document. You can usually adjust the defaults for most commonly used settings. Delete – Removes the information from the cells and closes in data from a selected direction. Dialog Box Launcher – The small ‘x’ at the bottom of a group of commands on a Ribbon Tab that displays a dialog box or a task pane for further options. Digital certificate – A code sequence that can be applied to any document or macro. Excel is able to read and display these certificates so that the user can assure themselves that the document is safe to use. Digital certificates may be issued by a commercial certification authority or created by individuals. Document Inspector – A tool to assist in removing any personal or hidden information you don’t want others to see when they open this file. Edit – The process of manipulating (adding, removing, replacing, etc.) characters. Error Checking – An auditing tool to assist in checking any errors that may exist in the formulas. Any errors are marked with a dark green triangle in the upper left corner of a cell.

Circular References – A type of error that occurs when one or more cells refer to each other directly or indirectly. Clear – Removes information (and/or formatting and comments) from selected cells and leaves the cells blank.

Appendix D

Glossary of Terms Exporting – The process of taking information from Excel program format and converting or exporting it out for another program format, e.g., Excel database information being exported into an Access database format. Extension – The last part of the name given to a file. It can be up to three characters and usually describes the “type” of file (e.g., .BAT for batch files; .EXE for executable program files; .XLS for Excel files; etc.). File name – The first part of a name given to a file. It can be up to 255 characters in length and usually describes the contents of a file. Filter – A feature that will suppress the display of data that do not meet the filter criteria. Fixed width text file – The format of the data exported from a program such as Excel. All data in the same column have the same length, usually the defined width of the column. Cells that have less data than the defined width will have extra blank spaces added at the right side of the cell. See also Comma delimited text file. Font – A specific typeface and point size. Footer – Text that repeats at the bottom of every page and may include automatic page numbers. Format – Instructions to Excel as to how it should display and number styles, fonts, colors, etc. Formula Bar – A field on the screen that displays the formula in the active cell. It can also be used to make entries into the worksheet. Formula – Used in a cell to calculate new result values to be displayed. Composed of values, cell references, arithmetic operators and special functions. These results may be used in other formulas located in other cells. Function – A feature designed by Excel that enables you to perform quickly a calculation or formula using a specialized function. Goal Seek – A tool that automates the process of changing one variable while calculating a function to match the desired value. Graphics – Illustrations that can be inserted into a worksheet such as pictures, clip art, charts, text boxes, shapes, etc. Header – Text that repeats at the top of every page and may include automatic page numbers. Help – A reference feature that summarizes the abilities and features of a program. HTML – Acronym for Hyper Text Markup Language. It is the underlying language for the set of instructions used by web browsers to display information on a web page. Hyperlink – A link to another document. It is usually stored in the form of a Uniform Resource Locator (URL), which is the unique address for this document on the Internet or the intranet. Importing – The process of taking information from one program format and converting or importing it into another program format, e.g., Access database information is imported into Excel. Insert Function – The feature that Excel provides to help you select the desired function to perform calculations.

Insert – An editing function that enables you to add text between other text, including entire columns or rows. Insert Worksheet Tab – The tab at the end of the worksheet tabs on the lower left corner of a workbook to assist in inserting/creating a new worksheet at the current location. Justification – The formatting function that determines how Excel will align the data within a cell or cells. Labels – Text entries that consist of alphabetic and numeric characters, plus most printable symbols. This type of data is not usually able to be used in calculations, except with text functions. Legend – A box on a chart that explains the meaning of each line in a line chart, or bar in a bar chart. Linking – The process of referencing cells or worksheets in one file to another, so that changes made on one file will automatically change in the linked file. Macro – A feature that “records” keystrokes for future use. Macros save time in operations where the same series of commands is repeated. Margin – The white space or area from the edge of the paper to the text. Name Box – This box displays the cell address of the active cell. It is located on the left below the toolbar. Office Button – Located at the top left of the Excel window, this displays a pull-down menu with commands for working with files and a list of recently used documents. Open – The function that enables you to transfer a file from a hard or floppy disk to the screen. Page Break – The division between two pages. Page Setup – The feature that determines how Excel will display and/or print the worksheet — e.g., margins, headers/footers, gridlines, etc. Paste – The editing function of placing cut or copied data into a new location. Pick List – A type of data validation that ensures only valid information is entered by allowing the user to select only from a predefined list of items. PivotChart – A chart created from the data displayed in the PivotTable. PivotTable – A feature that summarizes or cross-tabulates large amounts of data by selecting fields for rows and columns, then performing a summary function on the intersection of the row and column fields. Print – The process of sending a file to the printer, complete with printer instructions for the format and type of text. Quick Access Toolbar – Located usually next to the Office Button and contains popular commands such as Save, Undo, and Redo. This toolbar can be customized for those commands you use frequently. Range – See Cell range. Ribbon – A collection of tabs located directly below the title bar, providing quick access to commands you must use to complete a task.

Appendix D

Microsoft® Office Excel 2007 – Level 3

Ribbon Tabs – Relates to a type of activity, organizing command buttons into logical groups. The group name appears on the Ribbon tab below the group of command buttons.

Template – A pre-designed workbook that may already contain data, formulas, and other objects, thereby saving you time and effort in entering these items.

Save – When you copy information stored in the memory to a disk, you save it on the disk. If you turn the computer off without saving to a disk, you will lose all the information you have entered in the memory.

Text – Alphabetic and numeric characters, plus most printable symbols. See also Labels.

Scenarios – A tool that enables you to have as many variables as you need for data analysis. ScreenTip – A text box that displays helpful information on the purpose or function of a button or text box. Select All button – Clicking this button will select all cells in the current worksheet. It is located to the left of the column headings and above the row headings. Series – Each set of data used in a graphical chart. Shared Workbook – A feature in Excel which enables more than one user to update a workbook at the same time. If the same cell(s) are updated by different people, Excel will display the conflicts and allow one of the values to remain.

Tracing Errors – An audit tool that draws arrows to help you find or trace formula errors in cells that are precedents or dependents of the current cell. Tracking Changes – A process that displays all changes made to the worksheet, including editing actions and formatting changes. Trendline – A common method of analyzing data using charts or graphs based on the data in a worksheet. Values – Numeric, date, or time values you enter directly into a worksheet cell. This type of data is not only used to show information in a spreadsheet but is used to calculate other values on the same or other spreadsheets. Web Query – A feature that enables you to retrieve data into a worksheet from a web page.

Shortcut Keys – The commands activated by pressing the + key with another key to perform a specific task — e.g., will display the Print menu.

What-if analysis – The ability to pursue an almost endless cycle of trial-and-error use of base numbers in formulas and therefore be able to make important decisions quickly.

Shortcut Menu – The menu that appears when you click on the right mouse button.

Workbook – A single Excel file stored on disk, containing one or more worksheets.

SmartArt – A type of illustration with a variety of diagrams to show progress or flow of information.

Worksheet – In a workbook, a spreadsheet consisting of rows and columns.

Solver – A tool designed to reach a solution, by changing a number of variables.

Workspace – A file that contains the references to the workbooks, file locations, window sizes, and screen positions.

Spreadsheet – Used for entering and analyzing data (e.g., financial forecasting, cash flow, auditing, etc.). See also Worksheet.

WYSIWYG – Refers to “What You See Is What You Get,” a display mode that shows the worksheet exactly as it will appear when you print it.

Status Bar – The bar at the bottom of the Excel Document window that indicates the name and the status of keys activated.

X-axis – The horizontal edge of a chart, marking the scale used there.

Style – A combination of formatting features you can save and apply as a set.

Y-axis – The vertical edge of a chart, marking the scale used there.

Tabs – The identifiers at the bottom of each worksheet that allow you to switch from one document in the workbook to another. Serially numbered, or labeled as desired for the specific worksheet. Table Styles – A feature that enables you to apply many different formatting characteristics to a table with a single command by choosing from a selection of format templates; see AutoFormat also. Task Pane – A window displayed at the side of the screen that appears when specific commands are activated such as the Office Clipboard, PivotTable options.

A 10

Appendix E

Index

Appendix E: Index A

Analysis tools Goal seeking, 129 Solver, 132 What if analysis, 128 AutoFill, 2 Automatic subtotals, 22

Goal seeking, 129 Grouping data, 29

B Built-in templates, 55

C Changing cells, 132 Circling invalid data feature, 54 Comments, 80 Compatibility Checker, 123 Compatibility Pack, 123 Consolidating data, 15 Constraints, 132 Converting text to columns, 38 Copying and moving Paste special, 11 Copying data AutoFill, 2 Creating a template, 57

D Data table, 137 Data tables Multiple formulas, 142 Single variable, 137 Two variable, 140 Data validation Circling invalid data feature, 54 Data Validation Different types, 44 Database Automatic subtotals, 22 Grouping and ungrouping data, 29 Subtotal function, 18 Decision variables, 132 Deleting hyperlinks, 68 Deleting templates, 62 Digital Rights Management, 116 Digital signatures, 106 Document Inspector, 103 Duplicate rows, removing, 51

H Hyperlinks, 65 Deleting hyperlinks, 68 Inserting hyperlinks, 65 Modifying hyperlinks, 68

I Inserting Hyperlinks, 65 Internet Saving range of cells as HTML, 73 Saving worksheets as HTML, 70

M Macros, 118 Mark workbooks as final, 115 Modifying hyperlinks, 68

O Objective function, 132

P Passwords, 91 Files, 98 Workbook, 97 Worksheet, 95 Paste special, 11 Pick Lists, 49 PivotCharts, 148 Creating, 148 PivotTables, 144 Creating, 144 Data field options, 154 Field positions, 152 Previous Office Versions, 122 Protecting a workbook, 91 Digital signatures, 106 Passwords, 91, 92 Protecting files, 98 Workbook protection, 96 Worksheet protection, 92

Editing templates, 60

Removing shared use of workbooks, 91 Replacing data, 10 Resolving shared workbook conflicts, 85

E Files Compatibility Checker, 123 Compatibility Mode, 122 Previous Office Versions, 122 Saving a macro-enabled workbook, 118 Saving as a PDF, 76 Saving range of cells as HTML, 73 Saving worksheets as HTML, 70 Finding and Replacing Data, 7 Finding data, 7

6265-2 v1.00 ÂŠ CCI Learning Solutions Inc.

Sharing workbooks, 83 Removing shared use, 91 Resolving shared workbook conflicts, 85 Showing change history, 89 Tracking changes, 87 Showing history of workbook changes, 89 Solver, 132 Subtotal function, 18 Automatic subtotals, 22 Grouping and ungrouping data, 29

T Target cell, 132 Templates Built-in templates, 55 Creating a template, 57 Deleting templates, 62 Editing templates, 60 Using user-defined templates, 59 Tracking workbook changes, 87 Transpose data, 11

U Ungrouping data, 29 Using user-defined templates, 59

V Validating data, 44 Data validation, 44 Pick lists, 49 Visual Basic for Applications (VBA), 118

W What if analysis, 128 Workbook properties, 100 Document Inspector, 103 Workgroup functions Removing shared use of workbooks, 91 Resolving shared workbook conflicts, 85 Sharing workbooks, 83 Showing change history, 89 Tracking workbook changes, 87

Saving a file as PDF, 76 Saving a macro-enabled workbook, 118 Saving range of cells as HTML, 73 Saving worksheets as HTML, 70 Shared workbooks Creating, 83

A 11

Appendix E

Microsoft® Office Excel 2007 – Level 3

A 12