Contents

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

Displaying Two Workbooks on Two Monitors

Dismissing the Start Screen Permanently

Using the Subscription Model of Office 365

Using the Cloud for Storage and More

Relying on the Cloud

Oddities Added to Excel 2016

Handwriting Equations

Touchable Slicers

Search the Internet from Excel

Find a Ribbon Command if You Know the Exact Name of the Command

2 Using the Excel Interface

Using the Ribbon

Using Fly-out Menus and Galleries

Rolling Through the Ribbon Tabs

Revealing More Commands Using Dialog Launchers, Task Panes, and “More” Commands

Resizing Excel Changes the Ribbon

Activating the Developer Tab

Activating Contextual Ribbon Tabs

Finding Lost Commands on the Ribbon

Shrinking the Ribbon

Using the Quick Access Toolbar

Adding Icons to the QAT

Removing Commands from the QAT

Customizing the QAT

Using the Full-Screen 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 Right-Click 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

Performing a Simple Ribbon Modification

Adding a New Ribbon Tab

Sharing Customizations with Others

Questions About Ribbon Customization

Introducing the Excel Options Dialog

Getting Help with a Setting

New Options in Excel 2016

Using AutoRecover Options

Controlling Image Sizes

Working with Protected View for Files Originating from the Internet

Working with Trusted Document Settings

Options to Consider

Five Excel Oddities

4 Keyboard Shortcuts

Using Keyboard Accelerators

Selecting Icons on the Ribbon

Selecting Options from a Gallery

Navigating Within Drop-Down Lists

Backing Up One Level Through a Menu

Dealing with Keyboard Accelerator Confusion

Selecting from Legacy Dialog Boxes

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 Right-Click 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

Invoking an Excel 2003 Alt Shortcut

Determining Which Commands Work in Legacy Mode

II Calculating with Excel

5 Understanding Formulas

Getting the Most from This Chapter

Introduction to Formulas

Formulas Versus Values

Entering Your First Formula

Building a Formula

The Relative Nature of Formulas

Overriding Relative Behavior: Absolute Cell References

Using Mixed References to Combine Features of Relative and Absolute References

Using the F4 Key to Simplify Dollar Sign Entry

Using F4 After a Formula Is Entered

Using F4 on a Rectangular Range

Three Methods of Entering Formulas

Enter Formulas Using the Mouse Method

Entering Formulas Using the Arrow Key Method

Entering the Same Formula in Many Cells

Copying a Formula by Using Ctrl+Enter

Copying a Formula by Dragging the Fill Handle

Double-Click the Fill Handle to Copy a Formula

Use the Table Tool to Copy a Formula

6 Controlling Formulas

Formula Operators

Order of Operations

Unary Minus Example

Addition and Multiplication Example

Stacking Multiple Parentheses

Understanding Error Messages in Formulas

Using Formulas to Join Text

Joining Text and a Number

Copying Versus Cutting a Formula

Automatically Formatting Formula Cells

Using Date Math

Troubleshooting Formulas

Seeing All Formulas

Highlighting All Formula Cells

Editing a Single Formula to Show Direct Precedents

Using Formula Auditing Arrows

Tracing Dependents

Using the Watch Window

Evaluate a Formula in Slow Motion

Evaluating Part of a Formula

7 Understanding Functions

Working with Functions

The Formulas Tab in Excel 2016

Finding the Function You Need

Using Tab to AutoComplete Functions

Using the Insert Function Dialog to Find Functions

Getting Help with Excel Functions

Using On-Grid ToolTips

Using the Function Arguments Dialog

Using Excel Help

Using AutoSum

Potential Problems with AutoSum

Special Tricks with AutoSum

Using AutoAverage or AutoCount

Function Reference Chapters

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 Seventh-Grade 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

Using the IF Function to Make a Decision

Using the AND Function to Check for Two or More Conditions

Using OR to Check Whether One or More Conditions Are Met

Using the NOT Function to Simplify the Use of AND and OR

Using the IFERROR or IFNA Function to Simplify Error Checking

Examples of Information Functions

Using the ISFORMULA Function with Conditional Formatting to Mark Formula Cells

Using IS Functions to Test for Types of Values

Using the N Function to Add a Comment to a Formula

Using the NA Function to Force Charts to Not Plot Missing Data

Using the CELL Function to Return the Worksheet Name

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

Using DSUM to Conditionally Sum Records from a Database

Using the DGET Function

10 Other Functions

Web Functions

Financial Functions

Statistical Functions

Trigonometry Functions

Matrix Functions

Engineering Functions

11 Connecting Worksheets and Workbooks

Connecting Two Worksheets

Creating Links Using the Paste Options Menu

Creating Links Using the Right-Drag 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

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 Worksheet-Level Scope

Defining a Worksheet-Level Name

Referring to Worksheet-Level Names

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 Two-Way 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

Using 3D Formulas to Spear Through Many Worksheets

Referring to the Previous Worksheet

Combining Multiple Formulas into One Formula

Turning a Range of Formulas on Its Side

Coercing a Range of Dates Using an Array Formula

III Data Analysis with Excel

13 Transforming Data

Using Power Query

Specifying the Data Source

Transforming Data in Power Query

Unpivoting Data in Power Query

Correcting a Mistake in Power Query

Adding Columns in Power Query

Reviewing the Query

Loading and Refreshing the Data

Cleaning Data with Flash Fill

Coaching Flash Fill with a Second Example

Flash Fill Will Not Automatically Fill In Numbers

Using Formatting with Dates

Troubleshooting Flash Fill

Sorting Data

Sorting by Color or Icon

Factoring Case into a Sort

Reordering Columns with a Left-to-Right Sort

Sorting into a Unique Sequence by Using Custom Lists

One-Click Sorting

Fixing Sort Problems

Discovering Interesting Things in Your Data Using the Quick Analysis

14 Summarizing Data Using Subtotals or Filter

Adding Automatic Subtotals

Working with the Subtotals

Showing a One-Page Summary with Only the Subtotals

Sorting the Collapsed Subtotal View So the Largest Customers Are on Top

Copying Only the Subtotal Rows

Formatting the Subtotal Rows

Removing Subtotals

Subtotaling Multiple Fields

Filtering Records

Using a Filter

Selecting One or Multiple Items from the Filter Drop-Down

Identifying Which Columns Have Filters Applied

Combining Filters

Clearing Filters

Refreshing Filters

Resizing the Filter Drop-Down

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

Advanced Filter Criteria

Using Remove Duplicates to Find Unique Values

Combining Duplicates and Adding Values

15 Using Pivot Tables to Analyze Data

Creating Your First Pivot Table

Browsing Ten “Recommended” Pivot Tables

Starting with a Blank Pivot Table

Adding Fields to Your Pivot Table Using the Field List

Changing the Pivot Table Report by Using the Field List

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

Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh!

One Blank Cell in a Value Column Causes Excel to Count Instead of Sum

If You Click Outside the Pivot Table, All the Pivot Table Tools Disappear

You Cannot Change, Move a Part of, or Insert Cells in a Pivot Table

Calculating and Roll-ups with Pivot Tables

Grouping Daily Dates to Months and Years

Adding Calculations Outside the Pivot Table

Changing the Calculation of a Field

Showing Percentage of Total Using Show Value As Settings

Showing Running Totals and Rank

Using a Formula to Add a Field to a Pivot Table

Formatting a Pivot Table

Finding More Information on Pivot Tables

16 Using Slicers and Filtering a Pivot Table

Filtering Using the Row Label Filter

Clearing a Filter

Filtering Using the Check Boxes

Filtering Using the Label Filter Fly-Out

Filtering Using the Date Filters

Filtering to the Top 10

Filtering Using Slicers

Adding Slicers

Arranging the Slicers

Using the Slicers in Excel 2016

Filtering Dates

Filtering Oddities

AutoFiltering a Pivot Table

Replicating a Pivot Table for Every Customer

Sorting a Pivot Table

17 Mashing Up Data with PowerPivot

Joining Multiple Tables Using the Data Model

Preparing Data for Use in the Data Model

Building a Pivot Table from the Data Model

Adding the Second Table and Defining a Relationship

Understanding the Limitations of the Data Model

Benefits of Moving to PowerPivot

Count Distinct Using DAX

Date Intelligence Using DAX

Interactive Dashboards with Power View

18 Using What-If, Scenario Manager, Goal Seek, and Solver

Using What-If

Creating a Two-Variable What-If Table

Modeling a Random Scenario Using a Data Table

Using Scenario Manager

Creating a Scenario Summary Report

Adding Multiple Scenarios

Using Goal Seek

Using Solver

Installing Solver

Solving a Model Using 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

How Not to Record a Macro: The Default State of the Macro Recorder

Relative References in Macro Recording

Starting the Macro Recorder

Running a Macro

Everyday-Use Macro Example: Formatting an Invoice Register

Using the Ctrl+Down-Arrow Key to Handle a Variable Number of Rows

Making Sure You Find the Last Record

Recording the Macro in a Blank Workbook

Editing a Macro

Understanding VBA Code—An Analogy

Comparing Object.Method to Nouns and Verbs

Comparing Collections to Plural Nouns

Comparing Parameters to Adverbs

Comparing Adjectives to Properties

Using the Analogy While Examining Recorded Code

Using Simple Variables and Object Variables

Using R1C1-Style Formulas

Fixing AutoSum Errors in Macros

Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME

From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges

Finding the Last Row with Data

Looping Through All Rows

Referring to Ranges

Combining a Loop with FinalRow

Making Decisions by Using Flow Control

Putting Together the From-Scratch Example: Testing Each Record in a Loop

A Special Case: Deleting Some Records

Combination Macro Example: Creating a Report for Each Customer

Using the Advanced Filter for Unique Records

Using AutoFilter

Selecting Visible Cells Only

Combination Macro Example: Putting It All Together

20 More Tips and Tricks for Excel 2016

Watching the Results of a Distant Cell

Comparing Documents Side by Side with Synchronous Scrolling

Calculating a Formula in Slow Motion

Inserting a Symbol in a Cell

Editing an Equation

Protecting a Worksheet

Separating Text Based on a Delimiter

Auditing Worksheets Using Inquire

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 Built-in 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

Choosing a New Theme

Creating a New Theme

Reusing Another Theme’s Effects

Saving a Custom Theme

Using a Theme on a New Document

Sharing a Theme with Others

Other Formatting Techniques

Formatting Individual Characters

Changing the Default Font

Wrapping Text in a Cell

Justifying Text in a Range

Adding Cell Comments

Copying Formats

Pasting Formats

Pasting Conditional Formats

Using the Format Painter

Copying Formats to a New Worksheet

22 Using Data Visualizations and Conditional Formatting

Using Data Bars to Create In-Cell Bar Charts

Creating Data Bars

Customizing Data Bars

Showing Data Bars for a Subset of Cells

Using Color Scales to Highlight Extremes

Using Icon Sets to Segregate Data

Setting Up an Icon Set

Moving Numbers Closer to Icons

Mixing Icons or Hiding Icons

Using the Top/Bottom Rules

Using the Highlight Cells Rules

Highlighting Cells by Using Greater Than and Similar Rules

Comparing Dates by Using Conditional Formatting

Identifying Duplicate or Unique Values by Using Conditional Formatting

Using Conditional Formatting for Text Containing a Value

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

Choosing from Recommended Charts

Using the Paintbrush Icon for Styles

Deleting Extraneous Data Using the Funnel

Changing Chart Options Using the Plus Icon

Easy Combo Charts

Using the New Hierarchy Charts

Creating a Frequency Distribution with a Histogram Chart

Describe the Statistics of a Data Set with a Box and Whisker Chart

Showing Financial Data with a Waterfall Chart

Saving Time with Charting Tricks

Adding New Data to a Chart by Pasting

Dealing with Small Pie Slices

Saving a Favorite Chart Style As a Template

24 Using 3D Maps

Examples of 3D Maps

Adding Color Information for Categories

Zooming In

Animating Over Time

Going Ultra-Local

Getting Your Data into 3D Map

3D Map Techniques

Tipping, Rotating, and Zooming the Map

Adding a Photo to a Point

Combining Layers

Changing Column Size or Color

Resizing the Various Panes

Adding a Satellite Photograph

Showing the Whole Earth

Understanding the Time Choices

Animating a Line Between Two Points

Controlling Map Labels

Building a Tour and Creating a Video

Using an Alternate Map

Preparing the Store Image

Specifying a Custom Map

25 Using Sparklines

Fitting a Chart into the Size of a Cell with Sparklines

Understanding How Excel Maps Data to Sparklines

Creating a Group of Sparklines

Built-in Choices for Customizing Sparklines

Controlling Axis Values for Sparklines

Setting Up Win/Loss Sparklines

Showing Detail by Enlarging the Sparkline and Adding Labels

Other Sparkline Options

26 Decorating Spreadsheets

Using SmartArt

Elements Common in Most SmartArt

Inserting SmartArt

Changing Existing SmartArt to a New Style

Adding Images to SmartArt

Special Considerations for Organizational Charts and Hierarchical SmartArt

Using Shapes to Display Cell Contents

Working with Shapes

Using WordArt for Interesting Titles and Headlines

Using Text Boxes to Flow Long Text Passages

Using Pictures and Clip Art

Getting Your Picture into Excel

Inserting a Picture from Your Computer

Inserting Multiple Pictures at Once

Inserting a Picture or Clip Art from Online

Adjusting the Picture Using the Ribbon Tab

Resizing the Picture to Fit

Adjusting the Brightness and Contrast

Adding Interesting Effects Using the Picture Styles Gallery

Applying Artistic Effects

Removing the Background

Reducing a Picture’s File Size

Inserting Screen Clippings

Selecting and Arranging Pictures

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

Manually Adding Page Breaks

Manual Versus Automatic Page Breaks

Using Page Break Preview to Make Changes

Removing Manual Page Breaks

Adding Headers or Footers to the Printed Report

Adding an Automatic Header

Adding a Custom Header

Inserting a Picture or a Watermark in a Header

Using Different Headers and Footers in the Same Document

Scaling Headers and Footers

Printing from the File Menu

Choosing a Printer

Choosing What to Print

Changing Printer Properties

Changing Some of the Page Setup Settings

Using Page Layout View

Exploring Other Page Setup Options

Printing Gridlines and Headings

Centering a Small Report on a Page

Replacing Error Values When Printing

Printing Comments

Controlling the First Page Number

28 Excel Online

Accessing Your OneDrive Workbooks from Anywhere

Understanding the Limitations of Excel Online

Group Editing Using Excel Online

Designing a Workbook as an Interactive Web Page

Sharing a Link to Your Web Workbook

Collecting Survey Data in Excel Online

Creating a PDF from a Worksheet

Index