[PDF]Microsoft Excel Complete

Microsoft


EXCEL 2013


COMPLETE


Microsoft


Steven M. Freund
Mali B. Jones
Joy L. Starks


Indiana University Purdue University Indianapolis


s e CENGAGE
«© Learning


EXCEL 2013


Contributing Author
Raymond E. Enger


SS SHELLY
S CASHMAN
SERIES.


Australia e Brazil e Japan • Korea » Mexico • Singapore * Spain » United Kingdom • United States


s e CENGAGE
1> Learning


Microsoft® Excel® 2013: Complete © 2014 Cengage Learning


Steven M. Freund, Mali B. Jones, Joy L. Starks ALL RIGHTS RESERVED. No part of this work covered by the copyright herein


Executive Editor: Kathleen McMahon may be reproduced, transmitted, stored or used in any form or by any means
graphic, electronic, or mechanical, including but not limited to photocopying,
recording, scanning, digitizing, taping, Web distribution, information networks,
Associate Product Manager: Crystal Parenteau or information storage and retrieval systems, except as permitted under
Editorial Assistant: Sarah Ryan Section 107 or 108 of the 1976 United States Copyright Act, without the prior
written permission of the publisher.


Senior Product Manager: Emma F. Newsom


Print Buyer: Julio Esperas

Director of Production: Patty Stephan
. ; For product information and technology assistance, contact us at
Content Project Manager: Matthew Hutchinson Cengage Learning Customer & Sales Support, 1-800-354-9706


Development Editor: Lyn Markowicz For permission to use material from this text or product,
submit all requests online at cengage.com/permissions
Further permissions questions can be emailed to
permissionrequest@cengage.com


Senior Brand Manager: Elinor Gregory
Market Development Manager: Kristie Clark


Market Development Manager:


Gretchen Swann Library of Congress Control Number: 2013941391


Marketing Coordinator: Amy McGregor
, ; ISBN-13: 978-1-285-16844-9
QA Manuscript Reviewers: Jeffrey Schwartz, ISBN-10: 1-285-16844-5


John Freitas, Serge Palladino, Susan Pedicini,


Danielle Shaw, Susan Whalen A
Cengage Learning


Art Director: GEX Publishing Services, Inc. 20 Channel Center Street


Text Design: Joel Sadagursky Boston, MA 02210


Cover Design: Lisa Kuhn, Curio Press, LLC USA


Cover Photo: Tom Kates Photography Cengage Learning is a leading provider of customized learning solutions with


Compositor: PreMediaGlobal office locations around the globe, including Singapore, the United Kingdom,
Copyeditor: Harold Johnson Australia, Mexico, Brazil, and Japan. Locate your local office at:


Proofreader: Karen Annett international.cengage.com/region


Indexer: hich Carson Cengage Learning products are represented in Canada by Nelson Education, Ltd.


To learn more about Cengage Learning, visit www.cengage.com


Purchase any of our products at your local college bookstore or at our
Microsoft and the Office logo are either preferred online store at www.cengagebrain.com

registered trademarks or trademarks of
Microsoft Corporation in the United States
and/or other countries. Cengage Learning is
an independent entity from the Microsoft
Corporation, and not affiliated with Microsoft
in any manner.


Printed in the United States of America
1234567171615 14 13


Microsoft


EXCEL 2013


COMPLETE


Preface ix


To Expand a Folder, Scroll through Folder
Microsoft Office 2013 and Windows 8 a iie
To Switch from One App to Another OFF 29
á n To Save a File in a Folder OFF 29
Office aa and slan Navigating in Dialog Boxes OFF 32
Essentia Concepts and Skills To Minimize and Restore a Window OFF 32
Objectives OFF To Save a File on SkyDrive OFF 33
Roadmap ; ; OFF 2 Screen Resolution OFF 36
Introduction to the Windows 8 Operating To Change the Screen Resolution OFF 37
System OFF 2 To Exit an App with One Document Open OFF 38
Using:a Touch Screen and a Mouse OFF 3 To Copy a Folder to a USB Flash Drive OFF 39
Scrolling OFF 5 Additional Common Features of Office Apps OFF 40
Keyboard Shortcuts OFF 6 To Run an App Using the Search Box OFF 41
Starting Windows OFF 6 To Open an Existing File OFF 42
To Sign In to an Account OFF 7 To Create a New Workbook
The Windows Start Screen OFF 8 from the Backstage View OFF 43
Introduction to Microsoft Office 2013 OFF 9 To Close a File Using the Backstage View OFF 45
Microsoft Office 2013 Apps OFF 9 To Open a Recent File Using the
Microsoft Office 2013 Suites OFF 9 Backstage View OFF 45
Running and Using an App OFF 10 To Create a New Blank Workbook
Excel OFF 10 from File Explorer OFF 46
To Run an App from the Start Screen OFF 11 To Run an App from File Explorer
Windows Desktop OFF 13 and Open a File OFF 47
To Switch between an App To Save an Existing File with
and the Start Screen OFF 13 the Same File Name OFF 48
To Maximize a Window ; Oe Renaming, Moving, and Deleting Files OFF 50
Excel Workbook Window, Ribbon, and To Rename a File OFF 50
Elements Common to Office Apps OFF 14 To Move a File OFF 51
To Display a Different Tab on the Ribbon OFF 19 To Delete a File OFF 52
To Collapse and Expand the Ribbon Microsoft Office and Windows Help OFF 53
and Use Full Screen Mode OFF 19 To Open the Help Window in an
To Use a Shortcut Menu to Relocate Office App OFF 53
the Quick Access Toolbar OFF 21 Moving and Resizing Windows OFF 53
To Customize the Quick Access Toolbar OFF 22 To Move a Window by Dragging OFF 53
To Enter a Worksheet Title OFF 22 To Resize a Window by Dragging OFF 54
Saving and Organizing Files OFF 23 Using Office Help OFF 55
Organizing Files and Folders ree To Obtain Help Using the ‘Search online
To Create a Folder OFF 25 help’ Text Box OFF 55
Folder Windows OFF 26 To Obtain Help Using Help Links OFF 56


To Create a Folder within a Folder OFF 27


iv Contents Microsoft Excel 2013 Complete


Obtaining Help while Working in an
Office App
Using Windows Help and Support
To Use Windows Help and Support
Chapter Summary
Apply Your Knowledge
Extend Your Knowledge
Analyze, Correct, Improve
In the Labs
Consider This: Your Turn
Learn Online


Microsoft Office 3 65


Office 365 Essentials
Objectives
Introduction to Office 365
Cloud Computing
What Is Office 365?
Office 2013 and Office 365 Features
Comparison
Subscription-Based Office 365 Plans
First look at Office 365
Productivity Tools
Email and Calendars
Online Meetings
File Sharing
Website Creation
Synchronization
Multiple Device Access to Office 365
Teams Using Office 365 in Business
Email Communication Using Exchange
Collaboration Using SharePoint
Website Design Using SharePoint
Real-Time Communications Using Lync
Chapter Summary
Consider This: Your Turn


Microsoft Excel 201 3


CHAPTER ONE
Creating a Worksheet and a Chart


Objectives
Introduction
Project — Worksheet and a Chart
Roadmap
Selecting a Cell
Entering Text
To Enter the Worksheet Titles
AutoCorrect
To Enter Column Titles
To Enter Row Titles
Entering Numbers
To Enter Numbers
Calculating a Sum
To Sum a Column of Numbers


OFF 56
OFF 57
OFF 57
OFF 58
OFF 60
OFF 61
OFF 61
OFF 62
OFF 63
OFF 64


CLD 1
CLD 2
CLD 2
CLD 3


CLD 4
CLD 5
CLD 6
CLD 6
CLD 7
CLD 7
CLD 7
CLD 8
CLD 8
CLD 8
CLD 9
CLD 10
CLD 10
CLD 11
CLD 11
CLD 15
CLD 15


EX 1
EX 2
EX 2
EX 4
EX 6
EX 6
EX 7
EX 9
EX 9
EX 11
EX 12
EX 12
EX 14
EX 14


Using the Fill Handle to Copy a
Cell to Adjacent Cells


To Copy a Cell to Adjacent Cells in a Row


To Determine Multiple Totals
at the Same Time


To Enter a Formula Using the Keyboard


Formatting the Worksheet
Font Style, Size, and Color
To Change a Cell Style
To Change the Font
To Bold a Cell


To Increase the Font Size of a Cell Entry
To Change the Font Color of a Cell Entry


To Center Cell Entries across Columns
by Merging Cells
To Format Rows Using Cell Styles
To Format Numbers in the Worksheet
To Adjust the Column Width
To Use the Name Box to Select a Cell
Other Ways to Select Cells
Adding a Pie Chart to the Worksheet
To Add a 3-D Pie Chart
To Apply a Style to a Chart
Changing the Sheet Tab Names
To Move a Chart to a New Sheet
To Change the Worksheet Tab Name
Document Properties
Printing a Worksheet
To Preview and Print a Worksheet
in Landscape Orientation
AutoCalculate
To Use the AutoCalculate Area to
Determine a Maximum
Correcting Errors
Correcting Errors while Entering Data
into a Cell
Correcting Errors after Entering Data
into a Cell
Undoing the Last Cell Entry
Clearing a Cell or Range of Cells
Clearing the Entire Worksheet
Chapter Summary
Apply Your Knowledge
Extend Your Knowledge
Analyze, Correct, Improve
In the Labs
Consider This: Your Turn
Learn Online


CHAPTER TWO


Formulas, Functions, and Formatting


Objectives
Introduction
Project — Worksheet with Formulas
and Functions
Roadmap
Entering the Titles and Numbers into
the Worksheet


EX 15
EX 16


EX 18
EX 20
EX 22
EX 23
EX 24
EX 25
EX 26
EX 27
EX 28


EX 29
EX 30
EX 32
EX 34
EX 35
EX 36
EX 37
EX 38
EX 40
EX 41
EX 41
EX 42
EX 44
EX 45


EX 46
EX 48


EX 48
EX 49


EX 50


EX 50
EX 51
EX 52
EX 52
EX 54
EX 55
EX 57
EX 58
EX 59
EX 63
EX 64


EX 65
EX 66


EX 66
EX 68


EX 70


Entering Formulas
To Enter a Formula Using the Keyboard
Arithmetic Operations
Order of Operations
To Enter Formulas Using Point Mode
Option Buttons
Using the AVERAGE, MAX, and MIN Functions
To Determine the Highest Number in
a Range of Numbers Using the
Insert Function Box
To Determine the Lowest Number
in a Range of Numbers Using
the Sum Menu
To Determine the Average of a Range of
Numbers Using the Keyboard
Verifying Formulas Using Range Finder
To Verify a Formula Using Range Finder
Formatting the Worksheet
To Change the Workbook Theme
To Change the Background Color
and Apply a Box Border to
the Worksheet Title and Subtitle
To Format Dates and Center
Data in Cells
Formatting Numbers Using the Ribbon
To Apply an Accounting Number Format
and Comma Style Format Using
the Ribbon
To Apply a Currency Style Format with
a Floating Dollar Sign Using the
Format Cells Dialog Box
To Apply a Percent Style Format and
Use the Increase Decimal Button
Conditional Formatting
To Apply Conditional Formatting
Conditional Formatting Operators
Changing Column Width and
Row Height
To Change Column Width
To Change Row Height
Checking Spelling
To Check Spelling on the Worksheet
Additional Spell Checker Considerations
Printing the Worksheet
To Change the Worksheet's Margins,
Header, and Orientation in Page
Layout View
To Print a Section of the Worksheet
Displaying and Printing the Formulas Version
of the Worksheet
To Display the Formulas in the
Worksheet and Fit the Printout
on One Page
Chapter Summary
Apply Your Knowledge
Extend Your Knowledge
Analyze, Correct, Improve
In the Labs
Consider This: Your Turn
Learn Online


EX 73
EX 73
EX 75
EX 75
EX 76
EX 79
EX 81


EX 82


EX 84


EX 86
EX 89
EX 89
EX 89
EX 91


EX 93


EX 96
EX 97


EX 97


EX 98


EX 99
EX 100
EX 100
EX 103


EX 103
EX 103
EX 106
EX 107
EX 108
EX 109
EX 110


EX 110
EX 114


EX 115


EX 115
EX 118
EX 119
EX 121
EX 122
EX 123
EX 127
EX 128


Microsoft Excel 2013 Complete Contents v


CHAPTER THREE


Working with Large Worksheets,
Charting, and What-If Analysis
Objectives
Introduction
Project — Financial Projection Worksheet
with What-If Analysis and Chart
Roadmap
Rotating Text and Using the Fill Handle
to Create a Series
To Rotate Text in a Cell
To Use the Fill Handle to Create
a Series of Month Names
Using the Auto Fill Options Menu
To Increase Column Widths
To Enter and Indent Row Titles
Copying a Range of Cells to a Nonadjacent
Destination Area
To Copy a Range of Cells to a
Nonadjacent Destination Area
Using the Paste Options Menu
Using Drag and Drop to Move or
Copy Cells
Using Cut and Paste to Move Cells
Inserting and Deleting Cells in a Worksheet
To Insert a Row
Inserting Columns
Inserting Single Cells or a Range of Cells
Deleting Columns and Rows
To Enter Numbers with Format Symbols
To Enter and Format the System Date
Absolute Versus Relative Addressing
To Enter a Formula Containing
Absolute Cell References
Making Decisions — The IF Function
To Enter an IF Function
To Copy Formulas with Absolute Cell
References Using the Fill Handle
Nested Forms of the IF Function
Adding and Formatting Sparkline Charts
To Add a Sparkline Chart to
the Worksheet
To Change the Sparkline Style
and Copy the Sparkline Chart
To Change the Sparkline Type
Formatting the Worksheet
To Assign Formats to Nonadjacent Ranges
To Copy a Cell’s Format Using
the Format Painter Button
Adding a Clustered Column Chart
to the Workbook
To Draw a Clustered Column Chart on
a Separate Chart Sheet Using
the Recommended Charts Feature
To Insert a Chart Title
To Add Data Labels
To Apply Chart Filters
To Add an Axis Title to the Chart


EX 129
EX 130


EX 130
EX 134


EX 135
EX 135


EX 137
EX 138
EX 139
EX 140


EX 141


EX 141
EX 142


EX 143
EX 144
EX 144
EX 144
EX 145
EX 145
EX 146
EX 147
EX 148
EX 151


EX 153
EX 154
EX 156


EX 158
EX 159
EX 160


EX 160


EX 162
EX 162
EX 163
EX 164


EX 168


EX 170


EX 171
EX 172
EX 173
EX 174
EX 174


vi Contents Microsoft Excel 2013 Complete


To Change the Chart Style
To Modify the Chart Axis Number Format
Organizing the Workbook
To Reorder the Sheet Tabs
Changing the View of the Worksheet
To Shrink and Magnify the View
of a Worksheet or Chart
To Split a Window into Panes
To Freeze Worksheet Columns and Rows
What-If Analysis
To Analyze Data in a Worksheet
by Changing Values
To Goal Seek
Goal Seeking
Chapter Summary
Apply Your Knowledge
Extend Your Knowledge
Analyze, Correct, Improve
In the Labs
Consider This: Your Turn
Learn Online


CHAPTER FOUR


Financial Functions, Data Tables, and
Amortization Schedules
Objectives
Introduction
Project — Mortgage Payment Calculator
with Data Table and Amortization
Schedule
Roadmap
Creating Cell Names
To Create Names Based on Row Titles
To Enter the Loan Amount
Formula Using Names
The PMT Function
To Enter the PMT Function
Other Financial Functions
Using a Data Table to Analyze
Worksheet Data
To Create a Percent Series
Using the Fill Handle
To Define a Range
as a Data Table
More about Data Tables
Creating an Amortization Schedule
Formulas in the Amortization Schedule
To Enter the Formulas in
the Amortization Schedule
To Copy the Formulas to Fill
the Amortization Schedule
Formatting the Worksheet
To Add Custom Borders to a Range
Adding a Pointer to the Data Table
Using Conditional Formatting
To Add a Pointer to the Data Table
Using Conditional Formatting
Printing Sections of the Worksheet
To Set Up a Worksheet to Print


EX 176
EX 177
EX 179
EX 180
EX 182


EX 182
EX 184
EX 185
EX 187


EX 187
EX 188
EX 190
EX 192
EX 193
EX 195
EX 196
EX 198
EX 204
EX 207


EX 209
EX 210


EX 210
EX 213
EX 216
EX 218


EX 220
EX 221
EX 221
EX 222


EX 224
EX 226
EX 228
EX 230
EX 230
EX 233
EX 234
EX 236
EX 238
EX 239
EX 243
EX 243


EX 247
EX 247


More about Print Options EX 248
To Set the Print Area EX 248
To Name and Print Sections
of a Worksheet EX 249
Protecting and Hiding Worksheets
and Workbooks EX 252
To Protect a Worksheet EX 252
More about Worksheet Protection EX 255
To Hide and Unhide a Sheet EX 255
To Hide and Unhide a Workbook EX 257
Formula Checking EX 258
To Enable Background
Formula Checking EX 258
More about Background Formula
Checking EX 259
Chapter Summary EX 261
Apply Your Knowledge EX 262
Extend Your Knowledge EX 263
Analyze, Correct, Improve EX 265
In the Labs EX 266
Consider This: Your Turn EX 271
Learn Online EX 272


CHAPTER FIVE


Working with Multiple Worksheets and
Workbooks


Objectives EX 273
Introduction EX 274
Project — Consolidated Expenses Worksheet EX 274

Roadmap EX 277
Creating the Consolidated Worksheet EX 278


To Enter Sample Data in the
Consolidated Worksheet


Using the Fill Handle EX 280
The ROUND Function and Entering
Formulas in the Template EX 282


To Enter Formulas Using
the ROUND Function and
Determine Totals in the


Consolidated Worksheet EX 282
Formatting the Consolidated Worksheet EX 286
Format Codes EX 289


To Create and Assign a Custom
Format Code and a Comma


Style Format EX 290

Cell Styles EX 291

To Create a New Style EX 292

To Apply a New Style EX 295
Working with Multiple Worksheets EX 296
To Add a Worksheet to a Workbook EX 297


To Copy the Contents of
One Worksheet to Another


Worksheet in a Workbook EX 297
To Add a Copy of a

Worksheet to a Workbook EX 299
To Drill an Entry through Worksheets EX 300
>>>