Exploring Microsoft Excel 2013, Comprehensive

735 Pages·2013·105.031 MB·English

by  Poatsy, Mary Anne;Mulbery, Keith;Davidson, Jason;Grauer, Robert T

S e r i e S
(ex ploring)
•
1. Investigating in a systematic way: examining. 2. Searching into
or ranging over for the purpose of discovery.
Microsoft®
Excel 2013
CoMprEhEnsivE
Mary Anne Poatsy
Series Editor
Mulbery | Davidson
Series Created by Dr. Robert T. Grauer
Boston Columbus Indianapolis New York San Francisco Upper Saddle River
Amsterdam Cape Town Dubai London Madrid Milan Munich Paris Montréal Toronto
Delhi Mexico City São Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo
Editor in Chief: Michael Payne
Senior Editor: Samantha McAfee Lewis
Editorial Project Manager: Keri Rand
Product Development Manager: Laura Burgess
Development Editor: Barbara Stover
Editorial Assistant: Laura Karahalis
Director of Marketing: Maggie Moylan Leen
Marketing Manager: Brad Forrester
Marketing Coordinator: Susan Osterlitz
Marketing Assistant: Darshika Vyas
Managing Editor: Camille Trentacoste
Production Project Manager: Ilene Kahn
Senior Operations Specialist: Maura Zaldivar
Senior Art Director: Jonathan Boylan
Interior Design: Studio Montage
Cover Design: Studio Montage
Cover Photos: Supri Suharjoto/Shutterstock, wavebreakmedia/Shutterstock, Terry Chan/Shutterstock, Csaba Peterdi/Shutterstock
Associate Director of Design: Blair Brown
Digital Media Editor: Eric Hakanson
Director of Media Development: Taylor Ragan
Media Project Manager, Production: Renata Butera
Full Service Project Management: Andrea Stefanowicz/PreMediaGlobal
Composition: PreMediaGlobal
Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page within text.
Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and related graphics
published as part of the services for any purpose. All such documents and related graphics are provided “as is” without warranty of any kind. Microsoft and/or
its respective suppliers hereby disclaim all warranties and conditions with regard to this information, including all warranties and conditions of merchantability,
whether express, implied or statutory, fitness for a particular purpose, title and non-infringement. In no event shall Microsoft and/or its respective suppliers be
liable for any special, indirect or consequential damages or any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract,
negligence or other tortious action, arising out of or in connection with the use or performance of information available from the services.
The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically added to the
information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the program(s) described herein at
any time. Partial screen shots may be viewed in full within the software version specified.
Microsoft® and Windows® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This book is not sponsored or endorsed by or
affiliated with the Microsoft Corporation.
Copyright © 2014 by Pearson Education, Inc. All rights reserved. Manufactured in the United States of America. This publication is protected by Copyright, and
permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means,
electronic, mechanical, photocopying, recording, or likewise. To obtain permission(s) to use material from this work, please submit a written request to Pearson
Education, Inc., Permissions Department, One Lake Street, Upper Saddle River, New Jersey 07458, or you may fax your request to 201-236-3290.
Many of the designations by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and
the publisher was aware of a trademark claim, the designations have been printed in initial caps or all caps.
10 9 8 7 6 5 4 3 2 1
ISBN 10: 0-13-341218-0
ISBN 13: 978-0-13-341218-5
Dedications
For my husband, Ted, who unselfishly continues to take on more than his share to support me
throughout the process; and for my children, Laura, Carolyn, and Teddy, whose encouragement and
love have been inspiring.
Mary Anne Poatsy
I dedicate this book in memory to Grandpa Herman Hort, who dedicated his life to his family and to
the education field as a teacher and administrator. He inspired a daughter and several grandchildren to
become passionate educators and provide quality curriculum to students.
Keith Mulbery
I dedicate this book in loving memory of my grandmother Earline B. Leggett (Nanny). Your kindness,
wisdom, and support have been and will always be an inspiration. I couldn’t have made it without you.
Jason Davidson
About the Authors
Mary Anne Poatsy, Series Editor
Mary Anne is a senior faculty member at Montgomery County Community College, teaching various
computer application and concepts courses in face-to-face and online environments. She holds a B.A. in
Psychology and Education from Mount Holyoke College and an M.B.A. in Finance from Northwestern
University’s Kellogg Graduate School of Management.
Mary Anne has more than 12 years of educational experience. She is currently adjunct faculty at Gwynedd-
Mercy College and Montgomery County Community College. She has also taught at Bucks County
Community College and Muhlenberg College, as well as conducted personal training. Before teaching, she
was Vice President at Shearson Lehman in the Municipal Bond Investment Banking Department.
Dr. Keith Mulbery, Excel Author
Dr. Keith Mulbery is the Department Chair and a Professor in the Information Systems and Technology
Department at Utah Valley University (UVU), where he currently teaches systems analysis and design,
and global and ethical issues in information systems and technology. He has also taught computer
applications, C# programming, and management information systems. Keith served as Interim Associate
Dean, School of Computing, in the College of Technology and Computing at UVU.
Keith received the Faculty Senate Faculty Excellence Award for the College of Technology and Computing
in 2013, the Utah Valley State College Board of Trustees Award of Excellence in 2001, School of Technology
and Computing Scholar Award in 2007, and School of Technology and Computing Teaching Award in
2008. He has authored more than 17 textbooks, served as Series Editor for the Exploring Office 2007 series,
and served as developmental editor on two textbooks for the Essentials Office 2000 series. He is frequently
asked to give presentations and workshops on Microsoft Office Excel at various education conferences.
Keith received his B.S. and M.Ed. in Business Education from Southwestern Oklahoma State University and
earned his Ph.D. in Education with an emphasis in Business Information Systems at Utah State University.
His dissertation topic was computer-assisted instruction using Prentice Hall’s Train and Assess IT program
(the predecessor to MyITLab) to supplement traditional instruction in basic computer proficiency courses.
Jason Davidson, Excel Author
Jason Davidson is a faculty member in the College of Business at Butler University, where he teaches
Advanced Web Design, Data Networks, Data Analysis and Business Modeling, and introductory MIS courses.
With a background in media development, prior to joining the faculty at Butler, he worked in the technical
publishing industry. Along with teaching, he currently serves as an IT consultant for regional businesses
in the Indianapolis area. He holds a B.A. in telecommunication arts from Butler University and an M.B.A.
from Morehead State University. He lives in Indianapolis, Indiana, and in his free time, enjoys road biking,
photography, and spending time with his family.
Rebecca Lawson, Office Fundamentals Author
Rebecca Lawson is a professor in the Computer Information Technologies program at Lansing Community
College. She coordinates the curriculum, develops the instructional materials, and teaches for the E-Business
curriculum. She also serves as the Online Faculty Coordinator at the Center for Teaching Excellence at LCC.
In that role, she develops and facilitates online workshops for faculty learning to teach online. Her major areas
of interest include online curriculum quality assurance, the review and development of printed and online
instructional materials, the assessment of computer and Internet literacy skill levels to facilitate student retention,
and the use of social networking tools to support learning in blended and online learning environments.
Dr. Robert T. Grauer, Creator of the Exploring Series
Bob Grauer is an Associate Professor in the Department of Computer Information Systems at the University
of Miami, where he is a multiple winner of the Outstanding Teaching Award in the School of Business, most
recently in 2009. He has written numerous COBOL texts and is the vision behind the Exploring Office series,
with more than three million books in print. His work has been translated into three foreign languages and
is used in all aspects of higher education at both national and international levels. Bob Grauer has consulted
for several major corporations including IBM and American Express. He received his Ph.D. in Operations
Research in 1972 from the Polytechnic Institute of Brooklyn.
iv About the Authors
Brief Contents

Office Chapter 1 Office Fundamentals and File Management 1

excel Chapter 1 Introduction to excel 83
Chapter 2 Formulas and Functions 157
Chapter 3 Charts 215
Chapter 4 Datasets and tables 265
Chapter 5 Subtotals, pivottables, and pivotCharts 327
Chapter 6 What-If analysis 377
Chapter 7 Specialized Functions 421
Chapter 8 Statistical Functions 465
Chapter 9 Multiple-Sheet Workbook Management 505
Chapter 10 Imports, Web Queries, and XML 553
Chapter 11 Collaboration and Workbook Distribution 593
Chapter 12 templates, Styles, and Macros 643
application Introductory (Chapters 1–4) 693
Capstone Comprehensive (Chapters 5–12) 695
exercises
GLOSSary 699
InDeX 707
Brief Contents v
Contents
Microsoft Office 2013
■ ChApter One Office Fundamentals and File Management: taking
the First Step 1
ObjeCtIveS 1 Hands-On ExErcisE 4:
CaSe StuDy: SpOtteD beGOnIa art GaLLery 1 the backstage view tasks 41
WInDOWS 8 Startup 2 hOMe tab taSkS 45
Logging In with Your Microsoft Account 2 Selecting and editing text 45
Identifying the Start Screen Components 3 Using the Clipboard Group Commands 49
Interacting with the Start Screen 4 Using the editing Group Commands 52
Accessing the Desktop 4
Hands-On ExErcisE 5:
Hands-On ExErcisE 1: home tab tasks 55
Windows 8 Startup 6
InSert tab taSkS 60
FILeS anD FOLDerS 10 Inserting Objects 60
Using File explorer 10 revIeW tab taSkS 62
Working with Folders and Files 13 reviewing a File 62
Selecting, Copying, and Moving Multiple Files and Folders 15 paGe LayOut tab taSkS 65
Hands-On ExErcisE 2: Using the page Setup Dialog Box 66
Files and Folders 17 Hands-On ExErcisE 6:
MICrOSOFt OFFICe SOFtWare 22 Insert tab tasks, page Layout tab tasks, and
Identifying Common Interface Components 22 review tab tasks 68
Getting Office help 28 Chapter ObjeCtIveS revIeW 72
Hands-On ExErcisE 3: key terMS MatChInG 73
Microsoft Office Software 31 MuLtIpLe ChOICe 74
praCtICe eXerCISeS 75
the baCkStaGe vIeW taSkS 36
MID-LeveL eXerCISeS 79
Opening a File 36
beyOnD the CLaSSrOOM 81
printing a File 38
CapStOne eXerCISe 82
Closing a File and Application 39
Microsoft Office excel 2013
■ ChApter One Introduction to excel: What Is a Spreadsheet? 83
ObjeCtIveS 83 Hands-On ExErcisE 3:
CaSe StuDy: Ok OFFICe SySteMS 83 Workbook and Worksheet Management 111
IntrODuCtIOn tO SpreaDSheetS 84 CLIpbOarD taSkS 116
exploring the excel Window 84 Selecting, Moving, Copying, and pasting Data 116
entering and editing Cell Data 87
Hands-On ExErcisE 4:
Hands-On ExErcisE 1: Clipboard tasks 121
Introduction to Spreadsheets 91
FOrMattInG 124
MatheMatICS anD FOrMuLaS 94 Applying Alignment and Font Options 124
Creating Formulas 94 Applying number Formats 126
Using Auto Fill 96 Hands-On ExErcisE 5:
Displaying Cell Formulas 97 Formatting 129
Hands-On ExErcisE 2: paGe Setup anD prIntInG 133
Mathematics and Formulas 99 Selecting page Setup Options 133
WOrkbOOk anD WOrkSheet ManaGeMent 104 previewing and printing a Worksheet 137
Managing Worksheets 104 Hands-On ExErcisE 6:
Managing Columns and rows 107 page Setup and printing 138
vi Contents
Chapter ObjeCtIveS revIeW 142 MID-LeveL eXerCISeS 151
key terMS MatChInG 144 beyOnD the CLaSSrOOM 154
MuLtIpLe ChOICe 145 CapStOne eXerCISe 155
praCtICe eXerCISeS 146
■ ChApter tWO Formulas and Functions: performing Quantitative Analysis 157
ObjeCtIveS 157 Hands-On ExErcisE 3:
CaSe StuDy: tOWnSenD MOrtGaGe COMpany 157 Logical, Lookup, and Financial Functions 188
FOrMuLa baSICS 158 ranGe naMeS 192
Using relative, Absolute, and Mixed Cell Creating and Maintaining range names 192
references in Formulas 158 Using range names in Formulas 194
Correcting Circular references 160
Hands-On ExErcisE 4:
Hands-On ExErcisE 1: range names 195
Formula basics 162
Chapter ObjeCtIveS revIeW 198
FunCtIOn baSICS 166 key terMS MatChInG 199
Inserting a Function 166 MuLtIpLe ChOICe 200
Inserting Basic Math and Statistics Functions 168 praCtICe eXerCISeS 201
Using Date Functions 173 MID-LeveL eXerCISeS 206
Hands-On ExErcisE 2: beyOnD the CLaSSrOOM 211
Function basics 176 CapStOne eXerCISe 213
LOGICaL, LOOkup, anD FInanCIaL FunCtIOnS 181
Determining results with the IF Function 181
Using Lookup Functions 184
Calculating payments with the pMt Function 187
■ ChApter three Charts: Depicting Data Visually 215
ObjeCtIveS 215 Chart DeSIGn anD SparkLIneS 247
CaSe StuDy: COMputer jOb OutLOOk 215 Applying a Chart Style and Colors 247
Chart CreatIOn baSICS 216 Modifying the Data Source 248
Selecting the Data Source 216 Creating and Customizing Sparklines 249
Choosing a Chart type 217 Hands-On ExErcisE 3:
Moving, Sizing, and printing a Chart 227 Chart Design and Sparklines 251
Hands-On ExErcisE 1: Chapter ObjeCtIveS revIeW 254
Chart Creation basics 230 key terMS MatChInG 255
Chart eLeMentS 235 MuLtIpLe ChOICe 256
Adding Chart elements 235 praCtICe eXerCISeS 257
Formatting Chart elements 238 MID-LeveL eXerCISeS 260
Hands-On ExErcisE 2: beyOnD the CLaSSrOOM 263
Chart elements 243 CapStOne eXerCISe 264
■ ChApter FOUr Datasets and tables: Managing Large Volumes of Data 265
ObjeCtIveS 265 tabLe ManIpuLatIOn 286
CaSe StuDy: reID FurnIture StOre 265 Sorting Data 286
LarGe DataSetS 266 Filtering Data 288
Freezing rows and Columns 267 Hands-On ExErcisE 3:
printing Large Datasets 267 table Manipulation 292
Hands-On ExErcisE 1: tabLe aGGreGatIOn 297
Large Datasets 271 Using Structured references and a total row 297
eXCeL tabLeS 275 Hands-On ExErcisE 4:
Designing and Creating tables 275 table aggregation 300
Applying a table Style 279
COnDItIOnaL FOrMattInG 304
Hands-On ExErcisE 2: Applying Conditional Formatting 304
excel tables 281 Creating a new rule 308
Contents vii
Hands-On ExErcisE 5: praCtICe eXerCISeS 318
Conditional Formatting 311 MID-LeveL eXerCISeS 322
Chapter ObjeCtIveS revIeW 315 beyOnD the CLaSSrOOM 324
key terMS MatChInG 316 CapStOne eXerCISe 326
MuLtIpLe ChOICe 317
■ ChApter FIVe Subtotals, pivottables, and pivotCharts:
Summarizing and Analyzing Data 327
ObjeCtIveS 327 Hands-On ExErcisE 3:
CaSe StuDy: IvOry haLLS pubLIShInG COMpany 327 pivottable Options 354
SubtOtaLS anD OutLIneS 328 pIvOttabLe DeSIGn anD pIvOtChartS 359
Subtotaling Data 328 Formatting a pivottable 359
Grouping and Ungrouping Data 331 Using powerpivot Functionality 360
Hands-On ExErcisE 1: Creating a pivotChart 362
Subtotals and Outlines 332 Hands-On ExErcisE 4:
pIvOttabLe baSICS 336 pivottable Design and pivotCharts 364
Creating a pivottable 336 Chapter ObjeCtIveS revIeW 366
Modifying a pivottable 339 key terMS MatChInG 367
Hands-On ExErcisE 2: MuLtIpLe ChOICe 368
pivottable basics 344 praCtICe eXerCISeS 369
pIvOttabLe OptIOnS 349 MID-LeveL eXerCISeS 373
Filtering and Slicing a pivottable 349 beyOnD the CLaSSrOOM 375
Creating a Calculated Field 352 CapStOne eXerCISe 376
■ ChApter SIX What-If analysis: Using Decision-Making tools 377
ObjeCtIveS 377 SOLver 398
CaSe StuDy: perSOnaL FInanCe: buyInG yOur FIrSt hOMe 377 Loading the Solver Add-In 398
One- anD tWO-varIabLe Data tabLeS 378 Optimizing results with Solver 399
Creating a One-Variable Data table 378 Hands-On ExErcisE 3:
Creating a two-Variable Data table 382 Solver 405
Hands-On ExErcisE 1: Chapter ObjeCtIveS revIeW 409
One- and two-variable Data tables 384 key terMS MatChInG 410
GOaL Seek anD SCenarIO ManaGer 389 MuLtIpLe ChOICe 411
Identifying an Input Value with Goal Seek 389 praCtICe eXerCISeS 412
Using Scenario Manager 390 MID-LeveL eXerCISeS 415
Generating Scenario Summary reports 392 beyOnD the CLaSSrOOM 417
Hands-On ExErcisE 2: CapStOne eXerCISe 419
Goal Seek and Scenario Manager 394
■ ChApter SeVen Specialized Functions: Logical, Lookup,
Databases, and Finances 421
ObjeCtIveS 421 FInanCIaL FunCtIOnS 442
CaSe StuDy: tranSpayne FILtratIOn 421 Creating a Loan Amortization table 442
LOGICaL anD LOOkup FunCtIOnS 422 performing Other Financial Calculations 444
Creating a nested Logical Function 422 Hands-On ExErcisE 3:
Using MAtCh and InDeX Lookup Functions 426 Financial Functions 447
Hands-On ExErcisE 1: Chapter ObjeCtIveS revIeW 451
Logical and Lookup Functions 429 key terMS MatChInG 452
DatabaSe FILterInG anD FunCtIOnS 433 MuLtIpLe ChOICe 453
Using Advanced Filtering 433 praCtICe eXerCISeS 454
Manipulating Data with Database Functions 435 MID-LeveL eXerCISeS 459
Hands-On ExErcisE 2: beyOnD the CLaSSrOOM 462
Database Filtering and Functions 438 CapStOne eXerCISe 463
viii Contents
■ ChApter eIGht Statistical Functions: Analyzing Statistics 465
ObjeCtIveS 465 performing Analysis Using the Analysis toolpak 486
CaSe StuDy: eDuCatIOn evaLuatIOn 465 performing Analysis of Variance (AnOVA) 486
Math anD StatIStICaL FunCtIOnS 466 Calculating COVArIAnCe 488
Using Conditional Math and Statistical Functions 466 Creating a histogram 488
Calculating relative Standing with Statistical Functions 469 Hands-On ExErcisE 3:
Hands-On ExErcisE 1: Inferential Statistics 490
Math and Statistical Functions 474 Chapter ObjeCtIveS revIeW 494
DeSCrIptIve StatIStICaL FunCtIOnS 479 key terMS MatChInG 495
Measuring Central tendency 479 MuLtIpLe ChOICe 496
Hands-On ExErcisE 2: praCtICe eXerCISeS 497
Descriptive Statistical Functions 483 MID-LeveL eXerCISeS 500
beyOnD the CLaSSrOOM 502
InFerentIaL StatIStICS 486
CapStOne eXerCISe 503
Loading the Analysis toolpak 486
■ ChApter nIne Multiple-Sheet Workbook Management:
ensuring Quality Control 505
ObjeCtIveS 505 FOrMuLa auDItS anD Data vaLIDatIOn 528
CaSe StuDy: CIrCLe CIty SpOrtInG GOODS 505 Auditing Formulas 529
MuLtIpLe WOrkSheetS 506 Setting Up a Watch Window 531
Working with Grouped Worksheets 506 Validating Data 532
Managing Windows and Workspaces 509 Hands-On ExErcisE 3:
Inserting hyperlinks 511 Formula audits and Data validation 535
Hands-On ExErcisE 1: Chapter ObjeCtIveS revIeW 540
Multiple Worksheets 514 key terMS MatChInG 541
3-D FOrMuLaS anD LInkeD WOrkbOOkS 519 MuLtIpLe ChOICe 542
Inserting a 3-D Formula 519 praCtICe eXerCISeS 543
Linking Workbooks 521 MID-LeveL eXerCISeS 546
Hands-On ExErcisE 2: beyOnD the CLaSSrOOM 549
3-D Formulas and Linked Workbooks 524 CapStOne eXerCISe 551
■ ChApter ten Imports, Web Queries, and XML: Managing Data 553
ObjeCtIveS 553 XML 575
CaSe StuDy: StOCk anaLySIS 553 Understanding XML Syntax 575
eXternaL Data 554 Importing XML Data into excel 576
Importing Data from external Sources 554 Hands-On ExErcisE 3:
Creating a Web Query 558 XML 579
Managing Connections 560
Chapter ObjeCtIveS revIeW 582
Hands-On ExErcisE 1: key terMS MatChInG 583
external Data 562 MuLtIpLe ChOICe 584
teXt ManIpuLatIOn 568 praCtICe eXerCISeS 585
Converting text to Columns 568 MID-LeveL eXerCISeS 588
Manipulating text with Functions 569 beyOnD the CLaSSrOOM 590
Using Flash Fill 571 CapStOne eXerCISe 591
Hands-On ExErcisE 2:
text Manipulation 572
Contents ix