Microsoft Excel 2019 Inside Out

Microsoft Excel 2019 Inside Out

by Bill Jelen
Released November 2018
Publisher(s): Microsoft Press
ISBN: 9781509306015

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Conquer Microsoft Excel 2019–from the inside out!

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions. Renowned Excel expert Bill Jelen offers a complete tour of Excel 2019 and Excel in Office 365, from efficient interface customizations to advanced analysis, visualizations, and dashboards. Discover how experts tackle today’s key tasks–and challenge yourself to new levels of mastery.

  • Enter formulas more efficiently, and fully control operators and dates
  • Master the most widely-used functions in Excel
  • Integrate external data from the web and other sources
  • Easily transform complex datasets with Power Query
  • Quickly summarize millions of records with Pivot Tables
  • Perform ad hoc analyses with slicers and other filters
  • Create advanced data mashups with Power Pivot
  • Solve complex problems with What-If, Scenario Manager, Goal Seek, and Solver
  • Automate repetitive tasks by editing recorded VBA code
  • Demystify data with conditional formatting and other visualization techniques
  • Use the newest maps, charts, and data types in Excel
  • Show geographical changes over time with animated 3D maps
  • Use dynamic array functions: SORT, FILTER, UNIQUE, SORTBY and SEQUENCE
  • Find insights using Excel’s new artificial intelligence
  • Collaborate via Excel 2019’s breakthrough CoAuthoring tools
  • Publish Power BI Desktop dashboards based on Excel data

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Table of Contents
  7. About the Author
  8. Introduction
    1. Who This Book Is For
    2. How This Book Is Organized
    3. About the Companion Content
    4. Acknowledgments
    5. Support and Feedback
  9. Part I The Excel Interface
    1. Chapter 1 What’s New in Excel 2019
      1. Office 365 Is the Future
      2. Forward-Looking Features in Excel 2019
      3. Power Query Is Still the Best New Feature in Excel 2019
      4. Co-Authoring Allows Multiple People to Edit the Same Workbook at the Same Time
      5. Improvements to PivotTables
      6. New Calculation Functions in Excel 2019
      7. Two New Charts in 2019
      8. Inserting Icons and 3D Models
      9. Using the Inking Tools in Excel 2019
      10. Suggesting Ideas to the Excel Team
      11. Accessibility Improvements Across Office
      12. Changes to the Ribbon and Home Screen
      13. Collecting Survey Data in Excel Using Office 365
      14. Future Features Coming to Office 365
      15. Dynamic Array Functions in Office 365
    2. Chapter 2 Using the Excel Interface
      1. Using the Ribbon
      2. Using the Quick Access Toolbar
      3. Using the Full-Screen File Menu
      4. Using the New Sheet Icon to Add Worksheets
      5. Navigating Through Many Worksheets Using the Controls in the Lower Left
      6. Using the Mini Toolbar to Format Selected Text
      7. Expanding the Formula Bar
      8. Zooming In and Out on a Worksheet
      9. Using the Status Bar to Add Numbers
      10. Switching Between Normal View, Page Break Preview, and Page Layout View Modes
    3. Chapter 3 Customizing Excel
      1. Performing a Simple Ribbon Modification
      2. Adding a New Ribbon Tab
      3. Sharing Customizations with Others
      4. Questions About Ribbon Customization
      5. Using the Excel Options Dialog Box
      6. Options to Consider
      7. Five Excel Oddities
    4. Chapter 4 Keyboard Shortcuts
      1. Using Keyboard Accelerators
      2. Using the Shortcut Keys
      3. Using My Favorite Shortcut Keys
      4. Using Excel 2003 Keyboard Accelerators
  10. Part II Calculating with Excel
    1. Chapter 5 Understanding Formulas
      1. Getting the Most from This Chapter
      2. Introduction to Formulas
      3. Entering Your First Formula
      4. Three Methods of Entering Formulas
      5. Entering the Same Formula in Many Cells
      6. Use the Table Tool to Copy a Formula
    2. Chapter 6 Controlling Formulas
      1. Formula Operators
      2. Understanding Error Messages in Formulas
      3. Using Formulas to Join Text
      4. Copying Versus Cutting a Formula
      5. Automatically Formatting Formula Cells
      6. Using Date Math
      7. Troubleshooting Formulas
    3. Chapter 7 Understanding Functions
      1. Working with Functions
      2. Getting Help with Excel Functions
      3. Using AutoSum
    4. Chapter 8 Using Everyday Functions: Math, Date and Time, and Text Functions
      1. Math Functions
      2. Date and Time Functions
      3. Text Functions
      4. Examples of Math Functions
      5. Examples of Date and Time Functions
      6. Examples of Text Functions
    5. Chapter 9 Using Powerful Functions: Logical, Lookup, and Database Functions
      1. Examples of Logical Functions
      2. Examples of Information Functions
      3. Examples of Lookup and Reference Functions
      4. Examples of Database Functions
      5. Examples of Linked Data Types
    6. Chapter 10 Other Functions
      1. Web Functions
      2. Financial Functions
      3. Statistical Functions
      4. Trigonometry Functions
      5. Matrix Functions
      6. Engineering Functions
    7. Chapter 11 Connecting Worksheets and Workbooks
      1. Connecting Two Worksheets
      2. Excel in Practice: Seeing Two Worksheets of the Same Workbook Side by Side
    8. Chapter 12 Array Formulas and Names in Excel
      1. Advantages of Using Names
      2. Naming a Cell by Using the Name Dialog Box
      3. Using the Name Box for Quick Navigation
      4. Avoiding Problems by Using Worksheet-Level Scope
      5. Using Named Ranges to Simplify Formulas
      6. Retroactively Applying Names to Formulas
      7. Using Names to Refer to Ranges
      8. Adding Many Names at Once from Existing Labels and Headings
      9. Using Intersection to Do a Two-Way Lookup
      10. Using Implicit Intersection
      11. Using a Name to Avoid an Absolute Reference
      12. Using a Name to Hold a Value
      13. Assigning a Formula to a Name
      14. Using Power Formula Techniques
      15. Combining Multiple Formulas into One Formula
  11. Part III Data Analysis with Excel
    1. Chapter 13 Transforming Data
      1. Using Power Query
      2. Appending Worksheets from One Workbook
      3. Splitting Each Delimiter to a New Row
      4. Appending One Worksheet from Every Workbook in a Folder
      5. Cleaning Data with Flash Fill
      6. Sorting Data
    2. Chapter 14 Summarizing Data Using Subtotals or Filter
      1. Adding Automatic Subtotals
      2. Working with the Subtotals
      3. Subtotaling Multiple Fields
      4. Subtotaling Daily Dates by Month
      5. Filtering Records
      6. Using the Advanced Filter Command
      7. Excel in Practice: Using Formulas for Advanced Filter Criteria
      8. Using Remove Duplicates to Find Unique Values
      9. Combining Duplicates and Adding Values
    3. Chapter 15 Using Pivot Tables to Analyze Data
      1. Creating Your First Pivot Table
      2. Dealing with the Compact Layout
      3. Rearranging a Pivot Table
      4. Finishing Touches: Numeric Formatting and Removing Blanks
      5. Three Things You Must Know When Using Pivot Tables
      6. Calculating and Roll-Ups with Pivot Tables
      7. Formatting a Pivot Table
      8. Setting Defaults for Future Pivot Tables
      9. Finding More Information on Pivot Tables
    4. Chapter 16 Using Slicers and Filtering a Pivot Table
      1. Filtering Using the Row Label Filter
      2. Filtering Using Slicers
      3. Filtering Dates
      4. Filtering Oddities
      5. Sorting a Pivot Table
    5. Chapter 17 Mashing Up Data with Power Pivot
      1. Joining Multiple Tables Using the Data Model
      2. Unlocking Hidden Features with the Data Model
      3. Overcoming Limitations of the Data Model
    6. Chapter 18 Using What-If, Scenario Manager, Goal Seek, and Solver
      1. Using What-If
      2. Using Scenario Manager
      3. Using Goal Seek
      4. Using Solver
    7. Chapter 19 Automating Repetitive Functions Using VBA Macros
      1. Checking Security Settings Before Using Macros
      2. Recording a Macro
      3. Case Study: Macro for Formatting for a Mail Merge
      4. Everyday-Use Macro Example: Formatting an Invoice Register
      5. Understanding VBA Code—An Analogy
      6. Using Simple Variables and Object Variables
      7. Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName
      8. From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
      9. Combination Macro Example: Creating a Report for Each Customer
    8. Chapter 20 More Tips and Tricks for Excel 2019
      1. Watching the Results of a Distant Cell
      2. Calculating a Formula in Slow Motion
      3. Inserting a Symbol in a Cell
      4. Editing an Equation
      5. Protecting a Worksheet
      6. Repeat the Last Command with F4
      7. Bring the Active Cell Back in to View with Ctrl+Backspace
      8. Separating Text Based on a Delimiter
      9. Auditing Worksheets Using Inquire
  12. Part IV Excel Visuals
    1. Chapter 21 Formatting Worksheets
      1. Why Format Worksheets?
      2. Using Traditional Formatting
      3. Formatting with Styles
      4. Understanding Themes
      5. Other Formatting Techniques
      6. Copying Formats
    2. Chapter 22 Using Data Visualizations and Conditional Formatting
      1. Using Data Bars to Create In-Cell Bar Charts
      2. Using Color Scales to Highlight Extremes
      3. Using Icon Sets to Segregate Data
      4. Using the Top/Bottom Rules
      5. Using the Highlight Cells Rules
      6. Tweaking Rules with Advanced Formatting
      7. Combining Rules
      8. Extending the Reach of Conditional Formats
      9. Special Considerations for Pivot Tables
    3. Chapter 23 Graphing Data Using Excel Charts
      1. Choosing from Recommended Charts
      2. Easy Combo Charts
      3. Creating a Frequency Distribution with a Histogram Chart
      4. Showing Financial Data with a Waterfall Chart
      5. Saving Time with Charting Tricks
    4. Chapter 24 Using 3D Maps
      1. Examples of 3D Maps
      2. Getting Your Data into 3D Map
      3. 3D Map Techniques
      4. Building a Tour and Creating a Video
      5. Using an Alternate Map
    5. Chapter 25 Using Sparklines
      1. Fitting a Chart into the Size of a Cell with Sparklines
      2. Understanding How Excel Maps Data to Sparklines
    6. Chapter 26 Formatting Spreadsheets for Presentation
      1. Using SmartArt
      2. Using Shapes to Display Cell Contents
      3. Working with Shapes
      4. Using WordArt for Interesting Titles and Headlines
      5. Using Pictures and Clip Art
      6. Adjusting the Picture Using the Ribbon Tab
      7. Inserting Screen Clippings
      8. Selecting and Arranging Pictures
      9. Inserting Icons
      10. Examining 3D Models
    7. Chapter 27 Printing
      1. Printing in One Click
      2. Finding Print Settings
      3. Previewing the Printed Report
      4. Working with Page Breaks
      5. Adding Headers or Footers to the Printed Report
      6. Printing from the File Menu
      7. Choosing What to Print
      8. Using Page Layout View
      9. Exploring Other Page Setup Options
    8. Chapter 28 Sharing Dashboards with Power BI
      1. Getting Started with Power BI Desktop
      2. Building an Interactive Report with Power BI Desktop
      3. Publishing to Power BI
  13. Appendixes
    1. Appendix A DAX Functions
      1. Excel Functions and DAX Equivalents
      2. Date and Time Functions in DAX
      3. Time Intelligence Functions in DAX
      4. Filter Functions in DAX
      5. Information Functions in DAX
      6. Logical Functions in DAX
      7. Math and Trig Functions in DAX
      8. Other Functions in DAX
      9. Parent and Child Functions in DAX
      10. Statistical Functions in DAX
      11. Text Functions in DAX
    2. Appendix B Power Query M Functions
      1. Excel Functions with Power Query M Equivalents
      2. Accessing Data Functions in Power Query M
      3. Binary Functions in Power Query M
      4. Combiner Functions in Power Query M
      5. Comparer Functions in Power Query M
      6. Date Functions in Power Query M
      7. DateTime Functions in Power Query M
      8. DateTimeZone Functions in Power Query M
      9. Duration Functions in Power Query M
      10. Error Functions in Power Query M
      11. Expression Functions in Power Query M
      12. Function Functions in Power Query M
      13. Lines Functions in Power Query M
      14. List Functions
      15. Logical Functions in Power Query M
      16. Number Functions
      17. Record Functions
      18. Replacer Functions in Power Query M
      19. Splitter Functions in Power Query M
      20. Table Functions
      21. Text Functions
      22. Time Functions in Power Query M
      23. Type Functions in Power Query M
      24. URI Functions in Power Query M
      25. Value Functions in Power Query M
  14. Index
  15. Code Snippets

Product information

  • Title: Microsoft Excel 2019 Inside Out
  • Author(s): Bill Jelen
  • Release date: November 2018
  • Publisher(s): Microsoft Press
  • ISBN: 9781509306015