We Want to Hear from You
Errata, Updates, and Book Support
Chapter 1 What’s New in Excel 2019
Office 2019 Requires Windows 10
Forward-Looking Features in Excel 2019
Artificial Intelligence in Office 365
Support for Stocks and Geography Data Types in Office 365
Power Query Is Still the Best New Feature in Excel 2019
Co-Authoring Allows Multiple People to Edit the Same Workbook at the Same Time
New Calculation Functions in Excel 2019
Faster VLOOKUP When Multiple Columns
Inserting and Exploring 3D Models
Using the Inking Tools in Excel 2019
Suggesting Ideas to the Excel Team
Keeping the Copied Cells on the Clipboard
Unselecting a Cell with Ctrl+Click
Formatting Superscripts and Subscripts
Accessibility Improvements Across Office
Changes to the Ribbon and Home Screen
New Look for the Office 365 Ribbon
Collecting Survey Data in Excel Using Office 365
Future Features Coming to Office 365
New Threaded Comments and Chat
Workbooks Statistics and Smart Lookup
Dynamic Array Functions in Office 365
Entering One Formula and Returning Many Results
Extracting Unique Values with a Formula
Generating a Sequence of Numbers
Generating an Array of Random Numbers with a Formula
Learning About New Functions and Features
Chapter 2 Using the Excel Interface
The Look of the Ribbon Is Changing
Using Flyout Menus and Galleries
Rolling Through the Ribbon Tabs
Revealing More Commands Using Dialog Box Launchers, Task Panes, and “More” Commands
Resizing Excel Changes the Ribbon
Activating Contextual Ribbon Tabs
Finding Lost Commands on the Ribbon
Using the Quick Access Toolbar
Removing Commands from the QAT
Using the Full-Screen File Menu
Pressing the Esc Key to Close Backstage View
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
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
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
Performing a Simple Ribbon Modification
Sharing Customizations with Others
Questions About Ribbon Customization
Using the Excel Options Dialog Box
Working with Protected View for Files Originating from the Internet
Working with Trusted Document Settings
Selecting Options from a Gallery
Navigating Within Drop-Down Menu Lists
Backing Up One Level Through a Menu
Dealing with Keyboard Accelerator Confusion
Selecting from Legacy Dialog Boxes
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
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
Part II Calculating with Excel
Chapter 5 Understanding Formulas
Getting the Most from This Chapter
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
Chapter 6 Controlling Formulas
Addition and Multiplication Example
Understanding Error Messages in Formulas
Copying Versus Cutting a Formula
Automatically Formatting Formula Cells
Highlighting All Formula Cells
Editing a Single Formula to Show Direct Precedents
Evaluate a Formula in Slow Motion
Chapter 7 Understanding Functions
The Formulas Tab in Excel 2019
Using Tab to AutoComplete Functions
Using the Insert Function Dialog Box to Find Functions
Getting Help with Excel Functions
Using the Function Arguments Dialog Box
Potential Problems with AutoSum
Using AutoAverage or AutoCount
Chapter 8 Using Everyday Functions: Math, Date and Time, and Text Functions
Using AGGREGATE
to Ignore Error Cells or Filtered Rows
Using SUBTOTAL
Instead of SUM
with Multiple Levels of Totals
Totaling Visible Cells Using SUBTOTAL
Using RAND
, RANDARRAY
, 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 SUMIFS
, AVERAGEIFS
, COUNTIFS
, MAXIFS
, and MINIFS
to Conditionally Calculate
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
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
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 TEXT
to Format a Number as Text
Using the T
and VALUE
Functions
Chapter 9 Using Powerful Functions: Logical, Lookup, and Database 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 Add Hyperlinks Quickly
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
Chapter 11 Connecting Worksheets and Workbooks
Excel in Practice: Seeing Two Worksheets of the Same Workbook Side by Side
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
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 Box from Appearing
Chapter 12 Array Formulas and Names in Excel
Naming a Cell by Using the Name Dialog Box
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 a Name to Avoid an Absolute Reference
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
Part III Data Analysis with Excel
Loading Data Using Power Query
Loading Data from a Single Excel Workbook
Transforming Data in Power Query
Unpivoting Data in Power Query
Loading and Refreshing the Data
Appending Worksheets from One Workbook
Splitting Each Delimiter to a New Row
Appending One Worksheet from Every Workbook in a Folder
Coaching Flash Fill with a Second Example
Flash Fill Will Not Automatically Fill in Numbers
Reordering Columns with a Left-to-Right Sort
Sorting into a Unique Sequence by Using Custom Lists
Chapter 14 Summarizing Data Using Subtotals or Filter
Showing a One-Page Summary with Only the Subtotals
Sorting the Collapsed Subtotal View with the Largest Customers at Top
Copying Only the Subtotal Rows
Subtotaling Daily Dates by Month
Selecting One or Multiple Items from the Filter Drop-Down
Identifying Which Columns Have Filters Applied
Filtering by Selection—Hard Way
Filtering by Selection—Easy Way
Using Special Filters for Dates, Text, and Numbers
Formatting and Copying Filtered Results
Using the Advanced Filter Command
Excel in Practice: Using Formulas for Advanced Filter Criteria
Using Remove Duplicates to Find Unique Values
Combining Duplicates and Adding Values
Chapter 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
Finishing Touches: Numeric Formatting and Removing Blanks
Three Things You Must Know When Using Pivot Tables
Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh!
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, Quarters, 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
Setting Defaults for Future Pivot Tables
Finding More Information on Pivot Tables
Chapter 16 Using Slicers and Filtering a Pivot Table
Filtering Using the Row Label Filter
Filtering Using the Check Boxes
Filtering Using the Label Filter Fly-Out
Filtering Using the Date Filters
Using the Slicers in Excel 2019
Replicating a Pivot Table for Every Customer
Chapter 17 Mashing Up Data with Power Pivot
Joining Multiple Tables Using the Data Model
Preparing Data for Use in the Data Model
Creating a Relationship Between Two Tables in Excel
Creating a Relationship Using Diagram View
Building a Pivot Table from the Data Model
Unlocking Hidden Features with the Data Model
Counting Distinct in a Pivot Table
Including Filtered Items in Totals
Creating Median in a Pivot Table Using DAX Measures
Overcoming Limitations of the Data Model
Enjoying Other Benefits of Power Pivot
Chapter 18 Using What-If, Scenario Manager, Goal Seek, and Solver
Creating a Two-Variable What-If Table
Modeling a Random Scenario Using a Data Table
Creating a Scenario Summary Report
Chapter 19 Automating Repetitive Functions Using VBA Macros
Checking Security Settings Before Using Macros
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
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
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
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
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
Combination Macro Example: Putting It All Together
Chapter 20 More Tips and Tricks for Excel 2019
Watching the Results of a Distant Cell
Calculating a Formula in Slow Motion
Repeat the Last Command with F4
Bring the Active Cell Back in to View with Ctrl+Backspace
Separating Text Based on a Delimiter
Auditing Worksheets Using Inquire
Chapter 21 Formatting Worksheets
Changing Numeric Formats by Using the Home Tab
Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog Box
Using Numeric Formatting with Thousands Separators
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
Applying Bold, Italic, and Underline
Adjusting Column Widths and Row Heights
Reusing Another Theme’s Effects
Using a Theme on a New Document
Formatting Individual Characters
Copying Formats to a New Worksheet
Chapter 22 Using Data Visualizations and Conditional Formatting
Using Data Bars to Create In-Cell Bar Charts
Showing Data Bars for a Subset of Cells
Using Color Scales to Highlight Extremes
Using Icon Sets to Segregate Data
Moving Numbers Closer to Icons
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
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 Every Other Row Without Using a Table
Extending the Reach of Conditional Formats
Special Considerations for Pivot Tables
Chapter 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
Creating a Frequency Distribution with a Histogram Chart
Showing Financial Data with a Waterfall Chart
Saving Time with Charting Tricks
Adding New Data to a Chart by Pasting
Saving a Favorite Chart Style as a Template
Office 365 Will Soon Support the Custom Visuals from Power BI
Adding Color Information for Categories
Tipping, Rotating, and Zooming the Map
Understanding the Time Choices
Building a Tour and Creating a Video
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
Chapter 26 Formatting Spreadsheets for Presentation
Elements Common in Most SmartArt
Changing Existing SmartArt to a New Style
Special Considerations for Organizational Charts and Hierarchical SmartArt
Using Shapes to Display Cell Contents
Using WordArt for Interesting Titles and Headlines
Using Text Boxes to Flow Long Text Passages
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
Adjusting the Brightness and Contrast
Adjusting Picture Transparency So Cell Values Show Through
Adding Interesting Effects Using the Picture Styles Gallery
Selecting and Arranging Pictures
Using the Print Preview on the Print Panel
Using Full-Screen Print Preview
Making the Report Fit on the Page
Adjusting Worksheet Orientation
Repeating the Headings on Each Page
Excluding Part of Your Worksheet from the Print Range
Forcing More Data to Fit on a Page
Manual Versus Automatic Page Breaks
Using Page Break Preview to Make Changes
Adding Headers or Footers to the Printed Report
Inserting a Picture or a Watermark in a Header
Using Different Headers and Footers in the Same Document
Changing Some of the Page Setup Settings
Exploring Other Page Setup Options
Printing Gridlines and Headings
Centering a Small Report on a Page
Replacing Error Values When Printing
Controlling the First Page Number
Chapter 28 Sharing Dashboards with Power BI
Getting Started with Power BI Desktop
Building an Interactive Report with Power BI Desktop
Building Your First Visualization
Building Your Second Visualization
Creating a Drill-Down Hierarchy
Importing a Custom Visualization
Excel Functions and DAX Equivalents
Date and Time Functions in DAX
Time Intelligence Functions in DAX
Math and Trig Functions in DAX
Parent and Child Functions in DAX
Appendix B Power Query M Functions
Excel Functions with Power Query M Equivalents
Accessing Data Functions in Power Query M
Binary Functions in Power Query M
Combiner Functions in Power Query M
Comparer Functions in Power Query M
Date Functions in Power Query M
DateTime Functions in Power Query M
DateTimeZone Functions in Power Query M
Duration Functions in Power Query M
Error Functions in Power Query M
Expression Functions in Power Query M
Function Functions in Power Query M
Lines Functions in Power Query M
List Averages Functions in Power Query M
List Generators Functions in Power Query M
List Information Functions in Power Query M
List Membership Functions in Power Query M
List Numerics Functions in Power Query M
List Ordering Functions in Power Query M
List Selection Functions in Power Query M
List Set Operations Functions in Power Query M
List Transformation Functions in Power Query M
Logical Functions in Power Query M
Number Bytes Functions in Power Query M
Number Constants Functions in Power Query M
Number Conversion and Formatting Functions in Power Query M
Number Information Functions in Power Query M
Number Operations Functions in Power Query M
Number Random Functions in Power Query M
Number Rounding Functions in Power Query M
Number Trigonometry Functions in Power Query M
Record Information Functions in Power Query M
Record Selection Functions in Power Query M
Record Serialization Functions in Power Query M
Record Transformations Functions in Power Query M
Replacer Functions in Power Query M
Splitter Functions in Power Query M
Table Column Operations Functions in Power Query M
Table Construction Functions in Power Query M
Table Conversions Functions in Power Query M
Table Information Functions in Power Query M
Table Row Operations Functions in Power Query M
Text Extraction Functions in Power Query M
Text Information Functions in Power Query M
Text Membership Functions in Power Query M
Text Modification Functions in Power Query M
Text Comparisons Functions in Power Query M
Text Transformations Functions in Power Query M
Time Functions in Power Query M
Type Functions in Power Query M
URI Functions in Power Query M