Excel 2016 In Depth
Published on 12. November 2015
Book
Paperback/Softback
656 pages
978-0-7897-5584-1 (ISBN)
Description
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.
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.
More details
Other editions
Additional editions
Previous edition
Person
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.
Content
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
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