Excel 2016 In Depth / Edition 1

by Bill Jelen

Overview

Excel 2016 In Depth

Full Color: Figures and code appear as they do in Excel 2016

Beyond the Basics…Beneath the Surface...In Depth

Do more in less time!

Experienced with Excel? Don’t let Excel 2016 make you feel like a beginner again! This new full-color edition of the bestselling book has been completely overhauled. Gone is unnecessary and rarely used content; emphasis is on the most-used and new aspects of Excel 2016. The result is a focused book where every topic is relevant and worth learning. Excel 2016 In Depth is the fastest, smartest way to master Excel 2016’s full power and updated interface. You’ll discover how to leverage Excel’s new tools for charting, business analysis, data visualization, forecasting, and more.

• Quickly clean your data with Excel 2016’s powerful Get & Transform tools

• Discover Excel 2016’s newest charts: waterfall, histogram, Pareto, sunburst, TreeMap, and Box and Whisker

• Use Forecast Sheets to forecast the future, including seasonal adjustments

• Pivot data on maps with 3D Maps, and animate your maps over time

• Create formulas, charts, subtotals, and pivot tables faster than ever

• Create amazing PowerPivot data mashups that integrate information from anywhere

• Automate repetitive functions using Excel macros

• Solve real-world business intelligence analysis problems

• Use PowerPivot Data Model to create pivot tables from multiple data sets without VLOOKUP

• Share workbooks on the Web and social networks

• Leverage Excel to create highly interactive web pages and online surveys

• Quickly apply attractive, consistent formats

This book is part of Que’s Content Update Program. As Microsoft updates features of Excel, sections of this book will be updated or new sections will be added to match the updates to the software. See inside for details.

Product Details

ISBN-13:	9780789755841
Publisher:	Pearson Education
Publication date:	10/28/2015
Series:	In Depth
Pages:	656
Sales rank:	697,101
Product dimensions:	6.90(w) x 9.10(h) x 1.30(d)

About the Author

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. He loves performing his half-day Power Excel seminar around the world. He has produced more than 1,900 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 45 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches–working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives in Merritt Island, Florida, with his wife, Mary Ellen.

Table of Contents

Introduction 1

Part I The Excel Interface

Chapter 1 What’s New in Excel 2016 (and 2013) 5

Color Returns to the Excel Interface 5

The Data Model from Excel 2013 Is the Most Important Feature in 2016 6

Clean Your Data with Power Query 7

Pivot Your Data on a Map with 3D Maps 8

View Your Data Using Six New Chart Types 8

Forecast the Future Using a Forecast Sheet 9

Important Features from Excel 2013 10

Oddities Added to Excel 2016 11

Chapter 2 Using the Excel Interface 15

Using the Ribbon 15

Using the Quick Access Toolbar 20

Using the Full-Screen File Menu 23

Using the New Sheet Icon to Add Worksheets 29

Navigating Through Many Worksheets Using the Controls in the Lower Left 29

Using the Mini Toolbar to Format Selected Text 29

Expanding the Formula Bar 30

Zooming In and Out on a Worksheet 31

Using the Status Bar to Add Numbers 31

Switching Between Normal View, Page Break Preview, and Page Layout View Modes 32

Chapter 3 Customizing Excel 33

Performing a Simple Ribbon Modification 33

Adding a New Ribbon Tab 35

Sharing Customizations with Others 36

Questions About Ribbon Customization 36

Introducing the Excel Options Dialog 37

Options to Consider 40

Five Excel Oddities 41

Chapter 4 Keyboard Shortcuts 43

Using Keyboard Accelerators 43

Using the Shortcut Keys 47

Using My Favorite Shortcut Keys 56

Using Excel 2003 Keyboard Accelerators 58

Part II Calculating with Excel

Chapter 5 Understanding Formulas 69

Getting the Most from This Chapter 69

Introduction to Formulas 70

Entering Your First Formula 71

Three Methods of Entering Formulas 77

Entering the Same Formula in Many Cells 80

Use the Table Tool to Copy a Formula 82

Chapter 6 Controlling Formulas 85

Formula Operators 85

Understanding Error Messages in Formulas 88

Using Formulas to Join Text 90

Copying Versus Cutting a Formula 91

Automatically Formatting Formula Cells 92

Using Date Math 92

Troubleshooting Formulas 93

Chapter 7 Understanding Functions 99

Working with Functions 99

Getting Help with Excel Functions 102

Using AutoSum 105

Chapter 8 Using Everyday Functions: Math, Date and Time, and Text Functions 111

Math Functions 111

Date and Time Functions 114

Text Functions 116

Examples of Math Functions 119

Examples of Date and Time Functions 142

Examples of Text Functions 157

Using the T and VALUE Functions 175

Chapter 9 Using Powerful Functions: Logical, Lookup, Web, and Database Functions 177

Examples of Logical Functions 183

Examples of Information Functions 188

Examples of Lookup and Reference Functions 192

Examples of Database Functions 213

Chapter 10 Other Functions 223

Web Functions 223

Financial Functions 223

Statistical Functions 228

Trigonometry Functions 240

Matrix Functions 241

Engineering Functions 242

Chapter 11 Connecting Worksheets and Workbooks 245

Connecting Two Worksheets 245

Chapter 12 Array Formulas and Names in Excel 255

Advantages of Using Names 255

Naming a Cell by Using the Name Dialog 257

Using the Name Box for Quick Navigation 258

Avoiding Problems by Using Worksheet-Level Scope 259

Using Named Ranges to Simplify Formulas 260

Retroactively Applying Names to Formulas 261

Using Names to Refer to Ranges 261

Adding Many Names at Once from Existing Labels and Headings 262

Using Intersection to Do a Two-Way Lookup 263

Using Implicit Intersection 264

Using a Name to Avoid an Absolute Reference 265

Using a Name to Hold a Value 266

Assigning a Formula to a Name 266

Using Power Formula Techniques 267

Combining Multiple Formulas into One Formula 270

Part III Data Analysis with Excel

Chapter 13 Transforming Data 279

Using Power Query 279

Cleaning Data with Flash Fill 288

Sorting Data 289

Discovering Interesting Things in Your Data Using the Quick Analysis 293

Chapter 14 Summarizing Data Using Subtotals or Filter 295

Adding Automatic Subtotals 296

Working with the Subtotals 297

Subtotaling Multiple Fields 302

Filtering Records 303

Using the Advanced Filter Command 314

Using Remove Duplicates to Find Unique Values 317

Combining Duplicates and Adding Values 318

Chapter 15 Using Pivot Tables to Analyze Data 321

Creating Your First Pivot Table 322

Dealing with the Compact Layout 328

Rearranging a Pivot Table 329

Finishing Touches: Numeric Formatting and Removing Blanks 330

Four Things You Have to Know When Using Pivot Tables 332

Calculating and Roll-ups with Pivot Tables 333

Formatting a Pivot Table 342

Finding More Information on Pivot Tables 343

Chapter 16 Using Slicers and Filtering a Pivot Table 345

Filtering Using the Row Label Filter 345

Filtering Using Slicers 351

Filtering Dates 353

Filtering Oddities 354

Sorting a Pivot Table 356

Chapter 17 Mashing Up Data with PowerPivot 357

Joining Multiple Tables Using the Data Model 357

Benefits of Moving to PowerPivot 361

Interactive Dashboards with Power View 365

Chapter 18 Using What-If, Scenario Manager, Goal Seek, and Solver 367

Using What-If 367

Using Scenario Manager 372

Using Goal Seek 375

Using Solver 377

Chapter 19 Automating Repetitive Functions Using VBA Macros 381

Checking Security Settings Before Using Macros 381

Recording a Macro 382

Case Study: Macro for Formatting for a Mail Merge 383

Everyday-Use Macro Example: Formatting an Invoice Register 389

Understanding VBA Code–An Analogy 392

Using Simple Variables and Object Variables 397

Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME 400

From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges 401

Combination Macro Example: Creating a Report for Each Customer 409

Chapter 20 More Tips and Tricks for Excel 2016 417

Watching the Results of a Distant Cell 417

Comparing Documents Side by Side with

Synchronous Scrolling 418

Calculating a Formula in Slow Motion 419

Inserting a Symbol in a Cell 420

Editing an Equation 420

Protecting a Worksheet 421

Separating Text Based on a Delimiter 421

Auditing Worksheets Using Inquire 422

Part IV Excel Visuals

Chapter 21 Formatting Worksheets 425

Why Format Worksheets? 425

Using Traditional Formatting 427

Formatting with Styles 447

Understanding Themes 450

Other Formatting Techniques 453

Copying Formats 459

Chapter 22 Using Data Visualizations and Conditional Formatting 463

Using Data Bars to Create In-Cell Bar Charts 464

Using Color Scales to Highlight Extremes 468

Using Icon Sets to Segregate Data 470

Using the Top/Bottom Rules 474

Using the Highlight Cells Rules 475

Tweaking Rules with Advanced Formatting 481

Combining Rules 486

Extending the Reach of Conditional Formats 488

Special Considerations for Pivot Tables 489

Chapter 23 Graphing Data Using Excel Charts 491

Choosing from Recommended Charts 492

Easy Combo Charts 495

Using the New Hierarchy Charts 496

Creating a Frequency Distribution with a Histogram Chart 497

Describe the Statistics of a Data Set with a Box and Whisker Chart 499

Showing Financial Data with a Waterfall Chart 500

Saving Time with Charting Tricks 500

Chapter 24 Using 3D Maps 503

Examples of 3D Maps 503

Getting Your Data into 3D Map 512

3D Map Techniques 514

Building a Tour and Creating a Video 518

Using an Alternate Map 519

Chapter 25 Using Sparklines 523

Fitting a Chart into the Size of a Cell with Sparklines 523

Understanding How Excel Maps Data to Sparklines 524

Chapter 26 Decorating Spreadsheets 537

Using SmartArt 538

Using Shapes to Display Cell Contents 543

Working with Shapes 545

Using WordArt for Interesting Titles and Headlines 545

Using Pictures and Clip Art 547

Adjusting the Picture Using the Ribbon Tab 550

Inserting Screen Clippings 557

Selecting and Arranging Pictures 558

Chapter 27 Printing 561

Printing in One Click 561

Finding Print Settings 562

Previewing the Printed Report 565

Working with Page Breaks 569

Adding Headers or Footers to the Printed Report 571

Printing from the File Menu 574

Choosing What to Print 575

Using Page Layout View 576

Exploring Other Page Setup Options 577

Chapter 28 Excel Online 579

Accessing Your OneDrive Workbooks from Anywhere 580

Designing a Workbook as an Interactive Web Page 584

Collecting Survey Data in Excel Online 586

Creating a PDF from a Worksheet 589

9780789755841 TOC 10/8/2015