Home > Store

Excel 2016 In Depth (includes Content Update Program)

Register your product to gain access to bonus material or receive a coupon.

Excel 2016 In Depth (includes Content Update Program)

Best Value Purchase

Book + eBook + Web Edition Bundle

  • Your Price: $43.19
  • List Price: $71.98
  • Includes EPUB, MOBI, and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    MOBI MOBI The eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

  • About this Web Edition
  • A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.

    Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:

    • Windows XP (Firefox only)
    • Windows 7, 8, or 10 (IE10, IE11, Chrome, or Firefox)
    • Mac OS X (Safari, Firefox, or Chrome)
    • Linux (Chrome or Firefox)
    • iOS 6, 7, or 8 (Mobile Safari)

More Purchase Options

Book + Web Edition

  • Your Price: $31.99
  • List Price: $39.99
  • About this Web Edition
  • A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.

    Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:

    • Windows XP (Firefox only)
    • Windows 7, 8, or 10 (IE10, IE11, Chrome, or Firefox)
    • Mac OS X (Safari, Firefox, or Chrome)
    • Linux (Chrome or Firefox)
    • iOS 6, 7, or 8 (Mobile Safari)

eBook (Watermarked) + Web Edition

  • Your Price: $25.59
  • List Price: $31.99
  • Includes EPUB, MOBI, and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    MOBI MOBI The eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.

  • About this Web Edition
  • A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.

    Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:

    • Windows XP (Firefox only)
    • Windows 7, 8, or 10 (IE10, IE11, Chrome, or Firefox)
    • Mac OS X (Safari, Firefox, or Chrome)
    • Linux (Chrome or Firefox)
    • iOS 6, 7, or 8 (Mobile Safari)

Web Edition

  • Your Price: $31.99
  • List Price: $39.99
  • About this Web Edition
  • A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.

    Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:

    • Windows XP (Firefox only)
    • Windows 7, 8, or 10 (IE10, IE11, Chrome, or Firefox)
    • Mac OS X (Safari, Firefox, or Chrome)
    • Linux (Chrome or Firefox)
    • iOS 6, 7, or 8 (Mobile Safari)

About

Features

• Beyond-the-basics, beneath-the-surface guide to Microsoft Excel 2016: comprehensive coverage, real solutions!
• Master Excel 2016s most powerful new features
• Breakthrough techniques, exclusive shortcuts, expert troubleshooting help, and amazingly realistic examples
• By top Excel expert, trainer, and consultant Bill Jelen, whose MrExcel.com site attracts 10,000,000+ page views every year
• Updates will be delivered via a FREE Web Edition of this book, which can be accessed with any Internet connection

Description

  • Copyright 2016
  • Dimensions: 7" x 9-1/8"
  • Pages: 656
  • Edition: 1st
  • Book
  • ISBN-10: 0-7897-5584-X
  • ISBN-13: 978-0-7897-5584-1

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.

Downloads

Source Code

Code samples are available for this book. Register your book to get access to all the samples and worksheets.

Extras

Author's Site

Please visit the author's site here

Sample Content

Online Sample Chapter

Customizing Excel 2016

Sample Pages

Download the sample pages (includes Chapter 3 and Index)

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

Updates

Updates & Corrections

This book is part of Que's Content Update Program. As Microsoft updates features of Office 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a free Web Edition of this book, which can be accessed with any Internet connection from your account on quepublishing.com. For more information, visit quepublishing.com/CUP.

Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020