Excel 2016 In Depth

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.

• Introduction 1Part I The Excel InterfaceChapter 1 What’s New in Excel 2016 (and 2013) 5Color Returns to the Excel Interface 5The Data Model from Excel 2013 Is the Most Important Feature in 2016 6Clean Your Data with Power Query 7Pivot Your Data on a Map with 3D Maps 8View Your Data Using Six New Chart Types 8Forecast the Future Using a Forecast Sheet 9Important Features from Excel 2013 10Oddities Added to Excel 2016 11Chapter 2 Using the Excel Interface 15Using the Ribbon 15Using the Quick Access Toolbar 20Using the Full-Screen File Menu 23Using the New Sheet Icon to Add Worksheets 29Navigating Through Many Worksheets Using the Controls in the Lower Left 29Using the Mini Toolbar to Format Selected Text 29Expanding the Formula Bar 30Zooming In and Out on a Worksheet 31Using the Status Bar to Add Numbers 31Switching Between Normal View, Page Break Preview, and Page Layout View Modes 32Chapter 3 Customizing Excel 33Performing a Simple Ribbon Modification 33Adding a New Ribbon Tab 35Sharing Customizations with Others 36Questions About Ribbon Customization 36Introducing the Excel Options Dialog 37Options to Consider 40Five Excel Oddities 41Chapter 4 Keyboard Shortcuts 43Using Keyboard Accelerators 43Using the Shortcut Keys 47Using My Favorite Shortcut Keys 56Using Excel 2003 Keyboard Accelerators 58Part II Calculating with ExcelChapter 5 Understanding Formulas 69Getting the Most from This Chapter 69Introduction to Formulas 70Entering Your First Formula 71Three Methods of Entering Formulas 77Entering the Same Formula in Many Cells 80Use the Table Tool to Copy a Formula 82Chapter 6 Controlling Formulas 85Formula Operators 85Understanding Error Messages in Formulas 88Using Formulas to Join Text 90Copying Versus Cutting a Formula 91Automatically Formatting Formula Cells 92Using Date Math 92Troubleshooting Formulas 93Chapter 7 Understanding Functions 99Working with Functions 99Getting Help with Excel Functions 102Using AutoSum 105Chapter 8 Using Everyday Functions: Math, Date and Time, and Text Functions 111Math Functions 111Date and Time Functions 114Text Functions 116Examples of Math Functions 119Examples of Date and Time Functions 142Examples of Text Functions 157Using the T and VALUE Functions 175Chapter 9 Using Powerful Functions: Logical, Lookup, Web, and Database Functions 177Examples of Logical Functions 183Examples of Information Functions 188Examples of Lookup and Reference Functions 192Examples of Database Functions 213Chapter 10 Other Functions 223Web Functions 223Financial Functions 223Statistical Functions 228Trigonometry Functions 240Matrix Functions 241Engineering Functions 242Chapter 11 Connecting Worksheets and Workbooks 245Connecting Two Worksheets 245Chapter 12 Array Formulas and Names in Excel 255Advantages of Using Names 255Naming a Cell by Using the Name Dialog 257Using the Name Box for Quick Navigation 258Avoiding Problems by Using Worksheet-Level Scope 259Using Named Ranges to Simplify Formulas 260Retroactively Applying Names to Formulas 261Using Names to Refer to Ranges 261Adding Many Names at Once from Existing Labels and Headings 262Using Intersection to Do a Two-Way Lookup 263Using Implicit Intersection 264Using a Name to Avoid an Absolute Reference 265Using a Name to Hold a Value 266Assigning a Formula to a Name 266Using Power Formula Techniques 267Combining Multiple Formulas into One Formula 270Part III Data Analysis with ExcelChapter 13 Transforming Data 279Using Power Query 279Cleaning Data with Flash Fill 288Sorting Data 289Discovering Interesting Things in Your Data Using the Quick Analysis 293Chapter 14 Summarizing Data Using Subtotals or Filter 295Adding Automatic Subtotals 296Working with the Subtotals 297Subtotaling Multiple Fields 302Filtering Records 303Using the Advanced Filter Command 314Using Remove Duplicates to Find Unique Values 317Combining Duplicates and Adding Values 318Chapter 15 Using Pivot Tables to Analyze Data 321Creating Your First Pivot Table 322Dealing with the Compact Layout 328Rearranging a Pivot Table 329Finishing Touches: Numeric Formatting and Removing Blanks 330Four Things You Have to Know When Using Pivot Tables 332Calculating and Roll-ups with Pivot Tables 333Formatting a Pivot Table 342Finding More Information on Pivot Tables 343Chapter 16 Using Slicers and Filtering a Pivot Table 345Filtering Using the Row Label Filter 345Filtering Using Slicers 351Filtering Dates 353Filtering Oddities 354Sorting a Pivot Table 356Chapter 17 Mashing Up Data with PowerPivot 357Joining Multiple Tables Using the Data Model 357Benefits of Moving to PowerPivot 361Interactive Dashboards with Power View 365Chapter 18 Using What-If, Scenario Manager, Goal Seek, and Solver 367Using What-If 367Using Scenario Manager 372Using Goal Seek 375Using Solver 377Chapter 19 Automating Repetitive Functions Using VBA Macros 381Checking Security Settings Before Using Macros 381Recording a Macro 382Case Study: Macro for Formatting for a Mail Merge 383Everyday-Use Macro Example: Formatting an Invoice Register 389Understanding VBA Code–An Analogy 392Using Simple Variables and Object Variables 397Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME 400From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges 401Combination Macro Example: Creating a Report for Each Customer 409Chapter 20 More Tips and Tricks for Excel 2016 417Watching the Results of a Distant Cell 417Comparing Documents Side by Side withSynchronous Scrolling 418Calculating a Formula in Slow Motion 419Inserting a Symbol in a Cell 420Editing an Equation 420Protecting a Worksheet 421Separating Text Based on a Delimiter 421Auditing Worksheets Using Inquire 422Part IV Excel VisualsChapter 21 Formatting Worksheets 425Why Format Worksheets? 425Using Traditional Formatting 427Formatting with Styles 447Understanding Themes 450Other Formatting Techniques 453Copying Formats 459Chapter 22 Using Data Visualizations and Conditional Formatting 463Using Data Bars to Create In-Cell Bar Charts 464Using Color Scales to Highlight Extremes 468Using Icon Sets to Segregate Data 470Using the Top/Bottom Rules 474Using the Highlight Cells Rules 475Tweaking Rules with Advanced Formatting 481Combining Rules 486Extending the Reach of Conditional Formats 488Special Considerations for Pivot Tables 489Chapter 23 Graphing Data Using Excel Charts 491Choosing from Recommended Charts 492Easy Combo Charts 495Using the New Hierarchy Charts 496Creating a Frequency Distribution with a Histogram Chart 497Describe the Statistics of a Data Set with a Box and Whisker Chart 499Showing Financial Data with a Waterfall Chart 500Saving Time with Charting Tricks 500Chapter 24 Using 3D Maps 503Examples of 3D Maps 503Getting Your Data into 3D Map 5123D Map Techniques 514Building a Tour and Creating a Video 518Using an Alternate Map 519Chapter 25 Using Sparklines 523Fitting a Chart into the Size of a Cell with Sparklines 523Understanding How Excel Maps Data to Sparklines 524Chapter 26 Decorating Spreadsheets 537Using SmartArt 538Using Shapes to Display Cell Contents 543Working with Shapes 545Using WordArt for Interesting Titles and Headlines 545Using Pictures and Clip Art 547Adjusting the Picture Using the Ribbon Tab 550Inserting Screen Clippings 557Selecting and Arranging Pictures 558Chapter 27 Printing 561Printing in One Click 561Finding Print Settings 562Previewing the Printed Report 565Working with Page Breaks 569Adding Headers or Footers to the Printed Report 571Printing from the File Menu 574Choosing What to Print 575Using Page Layout View 576Exploring Other Page Setup Options 577Chapter 28 Excel Online 579Accessing Your OneDrive Workbooks from Anywhere 580Designing a Workbook as an Interactive Web Page 584Collecting Survey Data in Excel Online 586Creating a PDF from a Worksheet 589  9780789755841    TOC   10/8/2015