Home Page Icon
Home Page
Table of Contents for
Cover Page
Close
Cover Page
by Bill Jelen
Microsoft Excel 2019 Inside Out, First Edition
Cover Page
Title Page
Copyright Page
Dedication Page
Contents at a Glance
Table of Contents
About the Author
Introduction
Who This Book Is For
How This Book Is Organized
About the Companion Content
Acknowledgments
Support and Feedback
Part I The Excel Interface
Chapter 1 What’s New in Excel 2019
Office 365 Is the Future
Forward-Looking Features in Excel 2019
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
Improvements to PivotTables
New Calculation Functions in Excel 2019
Two New Charts in 2019
Inserting Icons and 3D Models
Using the Inking Tools in Excel 2019
Suggesting Ideas to the Excel Team
Accessibility Improvements Across Office
Changes to the Ribbon and Home Screen
Collecting Survey Data in Excel Using Office 365
Future Features Coming to Office 365
Dynamic Array Functions in Office 365
Chapter 2 Using the Excel Interface
Using the Ribbon
Using the Quick Access Toolbar
Using the Full-Screen File 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
Chapter 3 Customizing Excel
Performing a Simple Ribbon Modification
Adding a New Ribbon Tab
Sharing Customizations with Others
Questions About Ribbon Customization
Using the Excel Options Dialog Box
Options to Consider
Five Excel Oddities
Chapter 4 Keyboard Shortcuts
Using Keyboard Accelerators
Using the Shortcut Keys
Using My Favorite Shortcut Keys
Using Excel 2003 Keyboard Accelerators
Part II Calculating with Excel
Chapter 5 Understanding Formulas
Getting the Most from This Chapter
Introduction to Formulas
Entering Your First Formula
Three Methods of Entering Formulas
Entering the Same Formula in Many Cells
Use the Table Tool to Copy a Formula
Chapter 6 Controlling Formulas
Formula Operators
Understanding Error Messages in Formulas
Using Formulas to Join Text
Copying Versus Cutting a Formula
Automatically Formatting Formula Cells
Using Date Math
Troubleshooting Formulas
Chapter 7 Understanding Functions
Working with Functions
Getting Help with Excel Functions
Using AutoSum
Chapter 8 Using Everyday Functions: Math, Date and Time, and Text Functions
Math Functions
Date and Time Functions
Text Functions
Examples of Math Functions
Examples of Date and Time Functions
Examples of Text Functions
Chapter 9 Using Powerful Functions: Logical, Lookup, and Database Functions
Examples of Logical Functions
Examples of Information Functions
Examples of Lookup and Reference Functions
Examples of Database Functions
Examples of Linked Data Types
Chapter 10 Other Functions
Web Functions
Financial Functions
Statistical Functions
Trigonometry Functions
Matrix Functions
Engineering Functions
Chapter 11 Connecting Worksheets and Workbooks
Connecting Two Worksheets
Excel in Practice: Seeing Two Worksheets of the Same Workbook Side by Side
Chapter 12 Array Formulas and Names in Excel
Advantages of Using Names
Naming a Cell by Using the Name Dialog Box
Using the Name Box for Quick Navigation
Avoiding Problems by Using Worksheet-Level 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 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
Combining Multiple Formulas into One Formula
Part III Data Analysis with Excel
Chapter 13 Transforming Data
Using Power Query
Appending Worksheets from One Workbook
Splitting Each Delimiter to a New Row
Appending One Worksheet from Every Workbook in a Folder
Cleaning Data with Flash Fill
Sorting Data
Chapter 14 Summarizing Data Using Subtotals or Filter
Adding Automatic Subtotals
Working with the Subtotals
Subtotaling Multiple Fields
Subtotaling Daily Dates by Month
Filtering Records
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
Dealing with the Compact Layout
Rearranging a Pivot Table
Finishing Touches: Numeric Formatting and Removing Blanks
Three Things You Must Know When Using Pivot Tables
Calculating and Roll-Ups with Pivot Tables
Formatting 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 Slicers
Filtering Dates
Filtering Oddities
Sorting a Pivot Table
Chapter 17 Mashing Up Data with Power Pivot
Joining Multiple Tables Using the Data Model
Unlocking Hidden Features with the Data Model
Overcoming Limitations of the Data Model
Chapter 18 Using What-If, Scenario Manager, Goal Seek, and Solver
Using What-If
Using Scenario Manager
Using Goal Seek
Using Solver
Chapter 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
Everyday-Use Macro Example: Formatting an Invoice Register
Understanding VBA Code—An Analogy
Using Simple Variables and Object Variables
Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName
From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
Combination Macro Example: Creating a Report for Each Customer
Chapter 20 More Tips and Tricks for Excel 2019
Watching the Results of a Distant Cell
Calculating a Formula in Slow Motion
Inserting a Symbol in a Cell
Editing an Equation
Protecting a Worksheet
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
Part IV Excel Visuals
Chapter 21 Formatting Worksheets
Why Format Worksheets?
Using Traditional Formatting
Formatting with Styles
Understanding Themes
Other Formatting Techniques
Copying Formats
Chapter 22 Using Data Visualizations and Conditional Formatting
Using Data Bars to Create In-Cell 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
Combining Rules
Extending the Reach of Conditional Formats
Special Considerations for Pivot Tables
Chapter 23 Graphing Data Using Excel Charts
Choosing from Recommended Charts
Easy Combo Charts
Creating a Frequency Distribution with a Histogram Chart
Showing Financial Data with a Waterfall Chart
Saving Time with Charting Tricks
Chapter 24 Using 3D Maps
Examples of 3D Maps
Getting Your Data into 3D Map
3D Map Techniques
Building a Tour and Creating a Video
Using an Alternate Map
Chapter 25 Using Sparklines
Fitting a Chart into the Size of a Cell with Sparklines
Understanding How Excel Maps Data to Sparklines
Chapter 26 Formatting Spreadsheets for Presentation
Using SmartArt
Using Shapes to Display Cell Contents
Working with Shapes
Using WordArt for Interesting Titles and Headlines
Using Pictures and Clip Art
Adjusting the Picture Using the Ribbon Tab
Inserting Screen Clippings
Selecting and Arranging Pictures
Inserting Icons
Examining 3D Models
Chapter 27 Printing
Printing in One Click
Finding Print Settings
Previewing the Printed Report
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
Chapter 28 Sharing Dashboards with Power BI
Getting Started with Power BI Desktop
Building an Interactive Report with Power BI Desktop
Publishing to Power BI
Appendixes
Appendix A DAX Functions
Excel Functions and DAX Equivalents
Date and Time Functions in DAX
Time Intelligence Functions in DAX
Filter Functions in DAX
Information Functions in DAX
Logical Functions in DAX
Math and Trig Functions in DAX
Other Functions in DAX
Parent and Child Functions in DAX
Statistical Functions in DAX
Text 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 Functions
Logical Functions in Power Query M
Number Functions
Record Functions
Replacer Functions in Power Query M
Splitter Functions in Power Query M
Table Functions
Text Functions
Time Functions in Power Query M
Type Functions in Power Query M
URI Functions in Power Query M
Value Functions in Power Query M
Index
Code Snippets
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title Page
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset