Excel 2016 In Depth is the fastest, smartest way to master Excel 2016's full power and updated interface. Students will discover how to leverage Excel's new tools for charting, business analysis, data visualization, forecasting, and more.

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  

• 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