Book description
This is the Rough Cut version of the printed book.
Does your life play out in a spreadsheet? Do numbers in columns and rows make or break you in the work world? Tired of having numbers kicked in your face by other Excel power users who make your modest spreadsheets look paltry compared to their fancy charts and pivot tables? If you answered yes to any of these questions, Excel 2016 In Depth is the book that will make it all better. Learn quickly and efficiently from a true Excel master using his tried and true formula for success. Here, you'll find information that's undocumented elsewhereeven in Microsoft's own Help systems.
The world's most popular spreadsheet application, Microsoft Excel 2016, has many new features. Excel 2016 In Depth will ease the upgrade path. Bill Jelen "Mr. Excel," introduces the reader to the new interface allowing them to quickly get back uptospeed in performing their job and from there will then introduce the powerful new features available in Excel 2016.
There's a lot new (especially Business Intelligence) in Office 2016, and Jelen covers it all:
• Power Query is now a native feature of Excel 2016
• Power View works on SSAS Multidimensional (this is only going to work on the versions of SSAS Multidimensional that support DAX queries, i.e. SSAS 2014 or SSAS 2012 SP2)
• New Excel forecasting functions
• Time grouping functionality in PivotTables and more
Table of contents
 About This EBook
 Title Page
 Copyright Page
 Contents at a Glance
 Contents
 About the Author
 Dedication
 Acknowledgments
 We Want to Hear from You!
 Reader Services
 Introduction

I: The Excel Interface

1. What’s New in Excel 2016 (and 2013)
 Color Returns to the Excel Interface
 The Data Model from Excel 2013 Is the Most Important Feature in 2016
 Clean Your Data with Power Query
 Pivot Your Data on a Map with 3D Maps
 View Your Data Using Six New Chart Types
 Forecast the Future Using a Forecast Sheet
 Important Features from Excel 2013
 Oddities Added to Excel 2016

2. Using the Excel Interface
 Using the Ribbon
 Using the Quick Access Toolbar

Using the FullScreen File Menu
 Pressing the Esc Key to Close Backstage View
 Recovering Unsaved Workbooks
 Clearing the Recent Workbooks List
 Getting Information About the Current Workbook
 Marking a Workbook as Final to Prevent Editing
 Finding Hidden Content Using the Document Inspector
 Adding Whitespace Around Icons Using Touch Mode
 Previewing Paste Using the Paste Options Gallery
 Accessing the Gallery After Performing a Paste Operation
 Accessing the Paste Options Gallery from the RightClick Menu
 Using the New Sheet Icon to Add Worksheets
 Navigating Through Many Worksheets Using the Controls in the Lower Left
 Using the Mini Toolbar to Format Selected Text
 Expanding the Formula Bar
 Zooming In and Out on a Worksheet
 Using the Status Bar to Add Numbers
 Switching Between Normal View, Page Break Preview, and Page Layout View Modes
 3. Customizing Excel

4. Keyboard Shortcuts
 Using Keyboard Accelerators
 Using the Shortcut Keys

Using My Favorite Shortcut Keys
 Quickly Move Between Worksheets
 Jumping to the Bottom of Data with Ctrl+Arrow
 Selecting the Current Region with Ctrl+*
 Jumping to the Next Corner of a Selection
 Pop Open the RightClick Menu Using Shift+F10
 Crossing Tasks Off Your List with Ctrl+5
 Date Stamp or Time Stamp Using Ctrl+; or Ctrl+:
 Repeating the Last Task with F4
 Adding Dollar Signs to a Reference with F4
 Choosing Items from a Slicer
 Finding the One Thing That Takes You Too Much Time
 Using Excel 2003 Keyboard Accelerators

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

II: Calculating with Excel
 5. Understanding Formulas
 6. Controlling Formulas
 7. Understanding Functions

8. Using Everyday Functions: Math, Date and Time, and Text Functions
 Math Functions
 Date and Time Functions
 Text Functions

Examples of Math Functions
 Using SUM to Add Numbers
 Using AGGREGATE to Ignore Error Cells or Filtered Rows
 Rounding Numbers
 Using SUBTOTAL Instead of SUM with Multiple Levels of Totals
 Totaling Visible Cells Using SUBTOTAL
 Using RAND and RANDBETWEEN to Generate Random Numbers and Data
 Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits
 Using ABS() to Figure Out the Magnitude of Error
 Using GCD and LCM to Perform SeventhGrade Math
 Using MOD to Find the Remainder Portion of a Division Problem
 Using SQRT and POWER to Calculate Square Roots and Exponents
 Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data
 Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()
 Dates and Times in Excel
 Understanding Excel Date and Time Formats

Examples of Date and Time Functions
 Using NOW and TODAY to Calculate the Current Date and Time or Current Date
 Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/Time Apart
 Using DATE to Calculate a Date from Year, Month, and Day
 Using TIME to Calculate a Time
 Using DATEVALUE to Convert Text Dates to Real Dates
 Using TIMEVALUE to Convert Text Times to Real Times
 Using WEEKDAY to Group Dates by Day of the Week
 Using WEEKNUM or ISOWEEKNUM to Group Dates into Weeks
 Calculating Elapsed Time
 Using EOMONTH to Calculate the End of the Month
 Using WORKDAY or NETWORKDAYS or Their International Equivalents to Calculate Workdays
 Using International Versions of WORKDAY or NETWORKDAYS

Examples of Text Functions
 Joining Text with the Ampersand (&) Operator
 Using LOWER, UPPER, or PROPER to Convert Text Case
 Using TRIM to Remove Leading and Trailing Spaces
 Using the CHAR or UNICHAR Function to Generate Any Character
 Using the CODE or UNICODE Function to Learn the Character Number for Any Character
 Using LEFT, MID, or RIGHT to Split Text
 Using LEN to Find the Number of Characters in a Text Cell
 Using SEARCH or FIND to Locate Characters in a Particular Cell
 Using SUBSTITUTE to Replace Characters
 Using REPT to Repeat Text Multiple Times
 Using EXACT to Test Case
 Using TEXT to Format a Number as Text
 Using the T and VALUE Functions

9. Using Powerful Functions: Logical, Lookup, Web, and Database Functions
 Examples of Logical Functions
 Examples of Information Functions

Examples of Lookup and Reference Functions
 Using the CHOOSE Function for Simple Lookups
 Using VLOOKUP with TRUE to Find a Value Based on a Range
 Using the MATCH Function to Locate the Position of a Matching Value
 Using INDEX and MATCH for a Left Lookup
 Using MATCH and INDEX to Fill a Wide Table
 Performing Many Lookups with LOOKUP
 Using FORMULATEXT to Document a Worksheet
 Using Numbers with OFFSET to Describe a Range
 Using INDIRECT to Build and Evaluate Cell References On the Fly
 Using the HYPERLINK Function to Quickly Add Hyperlinks
 Using the TRANSPOSE Function to Formulaically Turn Data
 Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table
 Examples of Database Functions
 10. Other Functions

11. Connecting Worksheets and Workbooks

Connecting Two Worksheets
 Creating Links Using the Paste Options Menu
 Creating Links Using the RightDrag Menu
 Building a Link by Using the Mouse
 Links to External Workbooks Default to Absolute References
 Building a Formula by Typing
 Creating Links to Unsaved Workbooks
 Using the Links Tab on the Trust Center
 Opening Workbooks with Links to Closed Workbooks
 Dealing with Missing Linked Workbooks
 Preventing the Update Links Dialog from Appearing

Connecting Two Worksheets

12. Array Formulas and Names in Excel
 Advantages of Using Names
 Naming a Cell by Using the Name Dialog
 Using the Name Box for Quick Navigation
 Avoiding Problems by Using WorksheetLevel Scope
 Using Named Ranges to Simplify Formulas
 Retroactively Applying Names to Formulas
 Using Names to Refer to Ranges
 Adding Many Names at Once from Existing Labels and Headings
 Using Intersection to Do a TwoWay Lookup
 Using Implicit Intersection
 Using a Name to Avoid an Absolute Reference
 Using a Name to Hold a Value
 Assigning a Formula to a Name
 Using Power Formula Techniques
 Combining Multiple Formulas into One Formula

III: Data Analysis with Excel
 13. Transforming Data

14. Summarizing Data Using Subtotals or Filter
 Adding Automatic Subtotals
 Working with the Subtotals
 Subtotaling Multiple Fields

Filtering Records
 Using a Filter
 Selecting One or Multiple Items from the Filter DropDown
 Identifying Which Columns Have Filters Applied
 Combining Filters
 Clearing Filters
 Refreshing Filters
 Resizing the Filter DropDown
 Filtering by Selection—Hard Way
 Filtering by Selection—Easy Way
 Filtering by Color or Icon
 Handling Date Filters
 Using Special Filters for Dates, Text, and Numbers
 Totaling Filtered Results
 Formatting and Copying Filtered Results
 Using the Advanced Filter Command
 Using Remove Duplicates to Find Unique Values
 Combining Duplicates and Adding Values

15. Using Pivot Tables to Analyze Data
 Creating Your First Pivot Table
 Dealing with the Compact Layout
 Rearranging a Pivot Table
 Finishing Touches: Numeric Formatting and Removing Blanks
 Four Things You Have to Know When Using Pivot Tables
 Calculating and Rollups with Pivot Tables
 Formatting a Pivot Table
 Finding More Information on Pivot Tables
 16. Using Slicers and Filtering a Pivot Table
 17. Mashing Up Data with PowerPivot
 18. Using WhatIf, Scenario Manager, Goal Seek, and Solver

19. Automating Repetitive Functions Using VBA Macros
 Checking Security Settings Before Using Macros
 Recording a Macro
 Case Study: Macro for Formatting for a Mail Merge
 EverydayUse Macro Example: Formatting an Invoice Register
 Understanding VBA Code—An Analogy
 Using Simple Variables and Object Variables
 Customizing the EverydayUse Macro Example: GETOPENFILENAME and GETSAVEASFILENAME
 FromScratch Macro Example: Loops, Flow Control, and Referring to Ranges
 Combination Macro Example: Creating a Report for Each Customer
 20. More Tips and Tricks for Excel 2016

IV: Excel Visuals

21. Formatting Worksheets
 Why Format Worksheets?

Using Traditional Formatting
 Changing Numeric Formats by Using the Home Tab
 Changing Numeric Formats by Using Builtin Formats in the Format Cells Dialog
 Using Numeric Formatting with Thousands Separators
 Displaying Currency
 Displaying Dates and Times
 Displaying Fractions
 Displaying ZIP Codes, Telephone Numbers, and Social Security Numbers
 Changing Numeric Formats Using Custom Formats
 Using the Four Zones of a Custom Number Format
 Controlling Text and Spacing in a Custom Number Format
 Controlling Decimal Places in a Custom Number Format
 Using Conditions and Color in a Custom Number Format
 Using Dates and Times in a Custom Number Format
 Displaying Scientific Notation in Custom Number Formats
 Aligning Cells
 Changing Font Size
 Changing Font Typeface
 Applying Bold, Italic, and Underline
 Using Borders
 Coloring Cells
 Adjusting Column Widths and Row Heights
 Using Merge and Center
 Rotating Text
 Formatting with Styles
 Understanding Themes
 Other Formatting Techniques
 Copying Formats

22. Using Data Visualizations and Conditional Formatting
 Using Data Bars to Create InCell Bar Charts
 Using Color Scales to Highlight Extremes
 Using Icon Sets to Segregate Data
 Using the Top/Bottom Rules
 Using the Highlight Cells Rules

Tweaking Rules with Advanced Formatting
 Using a Formula for Rules
 Getting to the Formula Box
 Working with the Formula Box
 Finding Cells Within Three Days of Today
 Finding Cells Containing Data from the Past 30 Days
 Highlighting Data from Specific Days of the Week
 Highlighting an Entire Row
 Highlighting Every Other Row Without Using a Table
 Combining Rules
 Extending the Reach of Conditional Formats
 Special Considerations for Pivot Tables
 23. Graphing Data Using Excel Charts
 24. Using 3D Maps
 25. Using Sparklines
 26. Decorating Spreadsheets

27. Printing
 Printing in One Click
 Finding Print Settings

Previewing the Printed Report
 Using the Print Preview on the Print Panel
 Using Full Screen Print Preview
 Making the Report Fit on the Page
 Setting Worksheet Paper Size
 Adjusting Worksheet Orientation
 Adjusting Worksheet Margins
 Repeating the Headings on Each Page
 Excluding Part of Your Worksheet from the Print Range
 Forcing More Data to Fit on a Page
 Working with Page Breaks
 Adding Headers or Footers to the Printed Report
 Printing from the File Menu
 Choosing What to Print
 Using Page Layout View
 Exploring Other Page Setup Options
 28. Excel Online

21. Formatting Worksheets
 Index
 Code Snippets
Product information
 Title: Excel 2016 In Depth
 Author(s):
 Release date: December 2015
 Publisher(s): Que
 ISBN: 9780134386898
You might also like
book
Microsoft Excel 2019 Inside Out
Conquer Microsoft Excel 2019–from the inside out! Dive into Microsoft Excel 2019–and really put your spreadsheet …
book
Microsoft Excel 2019 VBA and Macros, First Edition
Renowned Excel experts Bill Jelen (MrExcel) and Tracy Syrstad explain how to build more powerful, reliable, …
book
Excel 2016 VBA and Macros
This is the Rough Cut version of the printed book. SAVE TIME AND SUPERCHARGE EXCEL 2016 …
book
Microsoft Excel 2019 Formulas and Functions, First Edition
Expert Paul McFedries helps you master key Excel 2019 and Office 365 tools for building more …