[PDF]Excel 2013 Bible

Microsoft

Excel 2013

Bible

THE COMPREHENSIVE TUTORIAL RESOURCE

MASTER EXCEL FORMULAS CREATE COMPELLING I ANALYZE AND PRESENT
FOR USEFUL WORKSHEETS CHARTS AND DIAGRAMS DATA WITH PIVOT TABLES






Excel* 2013

BIBLE





Excel’ 2013

BIBLE

John Walkenbach




WILEY

John Wiley & Sons, Inc.



Excel® 2013 Bible



Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard
Indianapolis, IN 46256

www. wiley . com

Copyright ® 2013 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada

ISBN 978-1-118-49036-5 (pbk); ISBN 978-1-118-49030-3 (ebk); ISBN 978-1-118-49170-6 (ebk);

ISBN 978-1-118-49172-0 (ebk)

Manufactured in the United States of America
10 987654321

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any
means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under
Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the
Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center,
222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for
permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., Ill River Street,
Hoboken, NJ 07030, 201-748-6011, fax 201-748-6008, or online at http : / /www. wiley . com/go /permissions.



LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO
REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE
CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED
OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED
HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING
THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL
SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL
PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR
DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN
THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT
MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR
WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE
AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED
BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.



For general information on our other products and services or to obtain technical support, please contact
our Customer Care Department within the U.S. at (877) 762-2974, outside the U.S. at (317) 572-3993 or fax
(317) 572-4002.

Library of Congress Control Number: 2012956404

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc., in
the United States and other countries, and may not be used without written permission. Microsoft and Excel are
registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners.
John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with
standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to
media such as a CD or DVD that is not included in the version you purchased, you may download this material at
http : / /booksupport .wiley . com. For more information about Wiley products, visit www.wiley.com.






Credits



Sr. Acquisitions Editor

Katie Mohr

Project Editor

Elizabeth Kuball

Technical Editor

Niek Otten

Copy Editor

Elizabeth Kuball

Editorial Manager

Jodi Jensen

Editorial Director

Mary Corder

Vice President and Executive Group
Publisher

Richard Swadley

Vice President and Publisher

Andy Cummings



Project Coordinator

Sheree Montgomery

Graphics and Production Specialists

Jennifer Creasey
Jennifer Mayberry

Quality Control Technicians

Jessica Kramer
Lauren Mandelbaum

Proofreading and Indexing

BIM Indexing & Proofreading Services

Vertical Websites Project Manager
and Producer

Rich Graves



About the Author



John Walkenbach is a bestselling Excel author who has published more than 50 spread-
sheet books. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and gila mon-
sters in Southern Arizona — but the critters are mostly scared away by his clawhammer
banjo playing. For more information, Google him.





Acknowledgments



T hanks again to everyone who bought the previous editions of this book. Your sugges-
tions have helped make this edition the best one yet.



And a special thanks to two behind-the-scenes folks who helped considerably: Elizabeth
Kuball (who made it more readable) and Niek Otten (who made it more accurate) .



IX



Contents



Acknowledgments ix

Introduction xxxvii

Is This Book for You? xxxvii

Software Versions xxxvii

Conventions Used in This Book xxxviii

Excel commands xxxviii

Filenames, named ranges, and your input xxxviii

Key names xxxviii

Functions xxxviii

Mouse conventions xxxix

How This Book Is Organized xl

How to Use This Book xl

What’s on the Website xli

Part I: Getting Started with Excel 1



Chapter 1: Introducing Excel 3

Identifying What Excel Is Good For 3

Seeing What’s New in Excel 2013 4

Understanding Workbooks and Worksheets 5

Moving Around a Worksheet 8

Navigating with your keyboard 8

Navigating with your mouse 9

Using the Ribbon 10

Ribbon tabs 10

Contextual tabs 12

Types of commands on the Ribbon 13

Accessing the Ribbon by using your keyboard 14

Using Shortcut Menus 15

Customizing Your Quick Access Toolbar 16

Working with Dialog Boxes 18

Navigating dialog boxes 19

Using tabbed dialog boxes 19

Using Task Panes 20



Contents



Creating Your First Excel Workbook 21

Getting started on your worksheet 22

Filling in the month names 22

Entering the sales data 23

Formatting the numbers 23

Making your worksheet look a bit fancier 24

Summing the values 25

Creating a chart 25

Printing your worksheet 26

Saving your workbook 26

Chapter 2: Entering and Editing Worksheet Data 29

Exploring Data Types 29

Numeric values 30

Text entries 30

Formulas 31

Entering Text and Values into Your Worksheets 32

Entering Dates and Times into Your Worksheets 33

Entering date values 33

Entering time values 34

Modifying Cell Contents 34

Deleting the contents of a cell 34

Replacing the contents of a cell 35

Editing the contents of a cell 35

Learning some handy data-entry techniques 36

Automatically moving the cell pointer after entering data 36

Using navigation keys instead of pressing Enter 37

Selecting a range of input cells before entering data 37

Using Ctrl + Enter to place information into multiple

cells simultaneously 38

Entering decimal points automatically 38

Using AutoFill to enter a series of values 38

Using AutoComplete to automate data entry 38

Forcing text to appear on a new line within a cell 40

Using AutoCorrect for shorthand data entry 40

Entering numbers with fractions 40

Using a form for data entry 41

Entering the current date or time into a cell 42

Applying Number Formatting 43

Using automatic number formatting 44

Formatting numbers by using the Ribbon 44

Using shortcut keys to format numbers 45

Formatting numbers using the Format Cells dialog box 45

Adding your own custom number formats 48



xii




Contents



Chapter 3: Essential Worksheet Operations 49

Learning the Fundamentals of Excel Worksheets 49

Working with Excel windows 49

Moving and resizing windows 50

Switching among windows 51

Closing windows 52

Activating a worksheet 52

Adding a new worksheet to your workbook 53

Deleting a worksheet you no longer need 53

Changing the name of a worksheet 54

Changing a sheet tab color 54

Rearranging your worksheets 55

Hiding and unhiding a worksheet 57

Controlling the Worksheet View 57

Zooming in or out for a better view 57

Viewing a worksheet in multiple windows 59

Comparing sheets side by side 60

Splitting the worksheet window into panes 60

Keeping the titles in view by freezing panes 61

Monitoring cells with a Watch Window 62

Working with Rows and Columns 64

Inserting rows and columns 64

Deleting rows and columns 65

Hiding rows and columns 66

Changing column widths and row heights 67

Changing column widths 67

Changing row heights 68

Chapter 4: Working with Cells and Ranges 69

Understanding Cells and Ranges 69

Selecting ranges 70

Selecting complete rows and columns 71

Selecting noncontiguous ranges 72

Selecting multisheet ranges 73

Selecting special types of cells 75

Selecting cells by searching 77

Copying or Moving Ranges 79

Copying by using Ribbon commands 80

Copying by using shortcut menu commands 81

Copying by using shortcut keys 82

Copying or moving by using drag-and-drop 83

Copying to adjacent cells 84

Copying a range to other sheets 85

Using the Office Clipboard to paste 85

Pasting in special ways 87



xiii




Contents



Using the Paste Special dialog box 88

Performing mathematical operations without formulas 90

Skipping blanks when pasting 90

Transposing a range 90

Using Names to Work with Ranges 91

Creating range names in your workbooks 92

Using the Name box 92

Using the New Name dialog box 92

Using the Create Names from Selection dialog box 93

Managing names 94

Adding Comments to Cells 95

Formatting comments 97

Changing a comment’s shape 98

Reading comments 99

Printing comments 99

Hiding and showing comments 100

Selecting comments 100

Editing comments 100

Deleting comments 100

Chapter 5: Introducing Tables 101

What Is a Table? 101

Creating a Table 104

Changing the Look of a Table 105

Working with Tables 108

Navigating in a table 108

Selecting parts of a table 108

Adding new rows or columns 108

Deleting rows or columns 109

Moving a table 109

Working with the Total Row 110

Removing duplicate rows from a table Ill

Sorting and filtering a table 112

Sorting a table 113

Filtering a table 115

Filtering a table with Sheers 116

Converting a table back to a range 118

Chapter 6: Worksheet Formatting 119

Getting to Know the Formatting Tools 119

Using the formatting tools on the Home tab 120

Using the Mini toolbar 121

Using the Format Cells dialog box 122

Using Different Fonts to Format Your Worksheet 123

Changing Text Alignment 126

Choosing horizontal alignment options 126

Choosing vertical alignment options 128

Wrapping or shrinking text to fit the cell 129



XIV




Contents



Merging worksheet cells to create additional text space 129

Displaying text at an angle 130

Controlling the text direction 131

Using Colors and Shading 131

Adding Borders and Lines 132

Adding a Background Image to a Worksheet 134

Using Named Styles for Easier Formatting 136

Applying styles 136

Modifying an existing style 137

Creating new styles 138

Merging styles from other workbooks 139

Controlling styles with templates 139

Understanding Document Themes 140

Applying a theme 141

Customizing a theme 142

Chapter 7: Understanding Excel Files 145

Creating a New Workbook 145

Opening an Existing Workbook 146

Filtering filenames 147

Choosing your file display preferences 148

Saving a Workbook 149

Using AutoRecover 150

Recovering versions of the current workbook 151

Recovering unsaved work 151

Configuring AutoRecover 151

Password-Protecting a Workbook 152

Organizing Your Files 153

Other Workbook Info Options 155

Protect Workbook options 155

Check for Issues options 156

Compatibility Mode section 156

Closing Workbooks 157

Safeguarding Your Work 157

Excel File Compatibility 158

Checking compatibility 158

Recognizing the Excel 2013 file formats 159

Saving a file for use with an older version of Excel 160

Chapter 8: Using and Creating Templates 161

Exploring Excel Templates 161

Viewing templates 161

Creating a workbook from a template 162

Modifying a template 164

Understanding Custom Excel Templates 164

Working with the default templates 165

Using the workbook template to change workbook defaults 165

Creating a worksheet template 166



XV




Contents



Editing your template 166

Resetting the default workbook 167

Creating custom templates 167

Saving your custom templates 168

Using custom templates 168

Getting ideas for creating templates 169

Chapter 9: Printing Your Work 171

Basic Printing 171

Changing Your Page View 173

Normal view 173

Page Layout view 174

Page Break Preview 175

Adjusting Common Page Setup Settings 177

Choosing your printer 178

Specifying what you want to print 178

Changing page orientation 179

Specifying paper size 180

Printing multiple copies of a report 180

Adjusting the page margins 180

Understanding page breaks 181

Inserting a page break 182

Removing manual page breaks 182

Printing row and column titles 182

Scaling printed output 183

Printing cell gridlines 184

Printing row and column headers 184

Using a background image 184

Adding a Header or Footer to Your Reports 186

Selecting a predefined header or footer 186

Understanding header and footer element codes 187

Other header and footer options 188

Copying Page Setup Settings across Sheets 189

Preventing Certain Cells from Being Printed 189

Preventing Objects from Being Printed 190

Creating Custom Views of Your Worksheet 191

Creating PDF files 192

Part II: Working with Formulas and Functions 193



Chapter 10: Introducing Formulas and Functions 195

Understanding Formula Basics 195

Using operators in formulas 196

Understanding operator precedence in formulas 197

Using functions in your formulas 199

Examples of formulas that use functions 200

Function arguments 201

More about functions 202



XVI



Contents



Entering Formulas into Your Worksheets 202

Entering formulas manually 205

Entering formulas by pointing 205

Pasting range names into formulas 206

Inserting functions into formulas 207

Function entry tips 209

Editing Formulas 210

Using Cell References in Formulas 211

Using relative, absolute, and mixed references 211

Changing the types of your references 213

Referencing cells outside the worksheet 214

Referencing cells in other worksheets 214

Referencing cells in other workbooks 214

Using Formulas in Tables 215

Summarizing data in a table 215

Using formulas within a table 217

Referencing data in a table 219

Correcting Common Formula Errors 220

Handling circular references 221

Specifying when formulas are calculated 222

Using Advanced Naming Techniques 223

Using names for constants 224

Using names for formulas 224

Using range intersections 225

Applying names to existing references 227

Working with Formulas 228

Not hard-coding values 228

Using the Formula bar as a calculator 229

Making an exact copy of a formula 229

Converting formulas to values 230

Chapter 11: Creating Formulas That Manipulate Text 231

A Few Words about Text 231

Text Functions 232

Working with character codes 233

The CODE function 234

The CHAR function 234

Determining whether two strings are identical 236

Joining two or more cells 236

Displaying formatted values as text 237

Displaying formatted currency values as text 239

Repeating a character or string 239

Creating a text histogram 239

Padding a number 240

Removing excess spaces and nonprinting characters 241

Counting characters in a string 242

Changing the case of text 242

Extracting characters from a string 243

xvii




Contents



Replacing text with other text 244

Finding and searching within a string 244

Searching and replacing within a string 245

Advanced Text Formulas 246

Counting specific characters in a cell 246

Counting the occurrences of a substring in a cell 246

Extracting the first word of a string 247

Extracting the last word of a string 247

Extracting all but the first word of a string 248

Extracting first names, middle names, and last names 248

Removing titles from names 250

Creating an ordinal number 250

Counting the number of words in a cell 250

Chapter 12: Working with Dates and Times 251

How Excel Handles Dates and Times 251

Understanding date serial numbers 251

Entering dates 252

Understanding time serial numbers 254

Entering times 255

Formatting dates and times 256

Problems with dates 257

Excel’s leap year bug 257

Pre-1900 dates 258

Inconsistent date entries 258

Date-Related Worksheet Functions 259

Displaying the current date 260

Displaying any date 261

Generating a series of dates 262

Converting a nondate string to a date 263

Calculating the number of days between two dates 264

Calculating the number of workdays between two dates 264

Offsetting a date using only workdays 266

Calculating the number of years between two dates 266

Calculating a person’s age 267

Determining the day of the year 268

Determining the day of the week 269

Determining the week of the year 269

Determining the date of the most recent Sunday 270

Determining the first day of the week after a date 270

Determining the nth occurrence of a day of the week in a month 270

Calculating dates of holidays 271

New Year’s Day 271

Martin Luther King, Jr., Day 271

Presidents’ Day 272

Easter 272



xviii




Contents



Memorial Day 272

Independence Day 272

Labor Day 272

Columbus Day 272

Veterans Day 273

Thanksgiving Day 273

Christmas Day 273

Determining the last day of a month 273

Determining whether a year is a leap year 273

Determining a date’s quarter 274

Time-Related Functions 274

Displaying the current time 275

Displaying any time 275

Calculating the difference between two times 276

Summing times that exceed 24 hours 277

Converting from military time 280

Converting decimal hours, minutes, or seconds to a time 280

Adding hours, minutes, or seconds to a time 280

Rounding time values 281

Working with non-time-of-day values 282

Chapter 13: Creating Formulas That Count and Sum 285

Counting and Summing Worksheet Cells 285

Basic Counting Formulas 287

Counting the total number of cells 288

Counting blank cells 289

Counting nonblank cells 289

Counting numeric cells 290

Counting text cells 290

Counting nontext cells 290

Counting logical values 290

Counting error values in a range 290

Advanced Counting Formulas 291

Counting cells by using the COUNTIF function 291
>>>