Modern Data Analytics in Excel

Modern Data Analytics in Excel

by George Mount
Released April 2024
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098148829

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

If you haven't modernized your data cleaning and reporting processes in Microsoft Excel, you're missing out on big productivity gains. And if you're looking to conduct rigorous data analysis, more can be done in Excel than you think. This practical book serves as an introduction to the modern Excel suite of features along with other powerful tools for analytics.

George Mount of Stringfest Analytics shows business analysts, data analysts, and business intelligence specialists how to make bigger gains right from your spreadsheets by using Excel's latest features. You'll learn how to build repeatable data cleaning workflows with Power Query, and design relational data models straight from your workbook with Power Pivot. You'll also explore other exciting new features for analytics, such as dynamic array functions, AI-powered insights, and Python integration.

Learn how to build reports and analyses that were previously difficult or impossible to do in Excel. This book shows you how to:

  • Build repeatable data cleaning processes for Excel with Power Query
  • Create relational data models and analysis measures with Power Pivot
  • Pull data quickly with dynamic arrays
  • Use AI to uncover patterns and trends from inside Excel
  • Integrate Python functionality with Excel for automated analysis and reporting

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Learning Objective
    2. Prerequisites
      1. Technical Requirements
      2. Technological Requirements
    3. How I Got Here
    4. What Is “Modern Analytics”? Why Excel?
    5. Book Overview
      1. Part I, Data Cleaning and Transformation with Power Query
      2. Part II, Data Modeling and Analysis with Power Pivot
      3. Part III, The Excel Data Analytics Toolkit
    6. End-of-Chapter Exercises
    7. This Is Not a Laundry List
    8. Conventions Used in This Book
    9. Using Code Examples
    10. O’Reilly Online Learning
    11. How to Contact Us
    12. Acknowledgments
  2. I. Data Cleaning and Transformation with Power Query
  3. 1. Tables: The Portal to Modern Excel
    1. Creating and Referring to Table Headers
    2. Viewing the Table Footers
    3. Naming Excel Tables
    4. Formatting Excel Tables
    5. Updating Table Ranges
    6. Organizing Data for Analytics
    7. Conclusion
    8. Exercises
  4. 2. First Steps in Excel Power Query
    1. What Is Power Query?
    2. Power Query as Excel Myth Buster
      1. “Excel Is Not Reproducible”
      2. “Excel Does Not Have a True null”
      3. “Excel Can’t Process More Than 1,048,576 Rows”
    3. Power Query as Excel’s ETL Tool
      1. Extract
      2. Transform
      3. Load
    4. A Tour of the Power Query Editor
      1. The Ribbon Menu
      2. Queries
      3. The Imported Data
      4. Exiting the Power Query Editor
      5. Returning to the Power Query Editor
    5. Data Profiling in Power Query
      1. What Is Data Profiling?
      2. Exploring the Data Preview Options
      3. Overriding the Thousand-Row Limit
      4. Closing Out of Data Profiling
    6. Conclusion
    7. Exercises
  5. 3. Transforming Rows in Power Query
    1. Removing the Missing Values
    2. Refreshing the Query
    3. Splitting Data into Rows
    4. Filling in Headers and Cell Values
      1. Replacing Column Headers
      2. Filling Down Blank Rows
    5. Conclusion
    6. Exercises
  6. 4. Transforming Columns in Power Query
    1. Changing Column Case
    2. Delimiting by Column
    3. Changing Data Types
    4. Deleting Columns
    5. Working with Dates
    6. Creating Custom Columns
      1. Loading & Inspecting the Data
      2. Calculated Columns Versus Measures
    7. Reshaping Data
    8. Conclusion
    9. Exercises
  7. 5. Merging and Appending Data in Power Query
    1. Appending Multiple Sources
      1. Connecting to External Excel Workbooks
      2. Appending the Queries
    2. Understanding Relational Joins
      1. Left Outer Join: Think VLOOKUP()
      2. Inner Join: Only the Matches
    3. Managing Your Queries
      1. Grouping Your Queries
      2. Viewing Query Dependencies
    4. Conclusion
    5. Exercises
  8. II. Data Modeling and Analysis with Power Pivot
  9. 6. First Steps in Power Pivot
    1. What Is Power Pivot?
    2. Why Power Pivot?
    3. Power Pivot and the Data Model
    4. Loading the Power Pivot Add-in
    5. A Brief Tour of the Power Pivot Add-In
      1. Data Model
      2. Calculations
      3. Tables
      4. Relationships
      5. Settings
    6. Conclusion
    7. Exercises
  10. 7. Creating Relational Models in Power Pivot
    1. Connecting Data to Power Pivot
    2. Creating Relationships
    3. Identifying Fact and Dimension Tables
      1. Arranging the Diagram View
      2. Editing the Relationships
    4. Loading the Results to Excel
    5. Understanding Cardinality
      1. One-to-One Cardinality
      2. One-to-Many Relationships
      3. Many-to-Many Relationships
      4. Why Does Cardinality Matter?
    6. Understanding Filter Direction
      1. Filtering orders with users
      2. Filtering users with orders
      3. Filter Direction and Cardinality
      4. From Design to Practice in Power Pivot
    7. Creating Columns in Power Pivot
      1. Calculating in Power Query Versus Power Pivot
      2. Example: Calculating Profit Margin
      3. Recoding Column Values with SWITCH()
    8. Creating and Managing Hierarchies
      1. Creating a Hierarchy in Power Pivot
      2. Using Hierarchies in the PivotTable
    9. Loading the Data Model to Power BI
      1. Power BI as the Third Piece of “Modern Excel”
      2. Importing the Data Model to Power BI
      3. Viewing the Data in Power BI
    10. Conclusion
    11. Exercises
  11. 8. Creating Measures and KPIs in Power Pivot
    1. Creating DAX Measures
      1. Creating Implicit Measures
      2. Creating Explicit Measures
    2. Creating KPIs
      1. Adjusting Icon Styles
      2. Adding the KPI to the PivotTable
    3. Conclusion
    4. Exercises
  12. 9. Intermediate DAX for Power Pivot
    1. CALCULATE() and the Importance of Filter Context
    2. CALCULATE() with One Criterion
    3. CALCULATE() with Multiple Criteria
      1. AND Conditions
      2. OR Conditions
    4. CALCULATE() with ALL()
    5. Time Intelligence Functions
      1. Adding a Calendar Table
      2. Creating Basic Time Intelligence Measures
    6. Conclusion
    7. Exercises
  13. III. The Excel Data Analytics Toolkit
  14. 10. Introducing Dynamic Array Functions
    1. Dynamic Array Functions Explained
      1. What Is an Array in Excel?
      2. Array References
      3. Array Formulas
    2. An Overview of Dynamic Array Functions
      1. Finding Distinct and Unique Values with UNIQUE()
      2. Finding Unique Versus Distinct Values
      3. Using the Spill Operator
    3. Filtering Records with FILTER()
      1. Adding a Header Column
      2. Filtering by Multiple Criteria
    4. Sorting Records with SORTBY()
      1. Sorting by Multiple Criteria
      2. Sorting by Another Column Without Printing It
    5. Creating Modern Lookups with XLOOKUP()
      1. XLOOKUP() Versus VLOOKUP()
      2. A Basic XLOOKUP()
      3. XLOOKUP() and Error Handling
      4. XLOOKUP() and Looking Up to the Left
    6. Other Dynamic Array Functions
    7. Dynamic Arrays and Modern Excel
    8. Conclusion
    9. Exercises
  15. 11. Augmented Analytics and the Future of Excel
    1. The Growing Complexity of Data and Analytics
    2. Excel and the Legacy of Self-Service BI
    3. Excel for Augmented Analytics
    4. Using Analyze Data for AI Powered Insights
    5. Building Statistical Models with XLMiner
    6. Reading Data from an Image
    7. Sentiment Analysis with Azure Machine Learning
    8. Conclusion
    9. Exercises
  16. 12. Python with Excel
    1. Reader Prerequisites
    2. The Role of Python in Modern Excel
      1. A Growing Stack Requires Glue
      2. Network Effects Mean Faster Development Time
      3. Bring Modern Development to Excel
      4. Using Python and Excel Together with pandas and openpyxl
      5. Other Python Packages for Excel
    3. Demonstration of Excel Automation with pandas and openpyxl
      1. Cleaning Up the Data in pandas
      2. Summarizing Findings with openpyxl
      3. Adding a Styled Data Source
    4. Conclusion
    5. Exercises
  17. 13. Conclusion and Next Steps
    1. Exploring Excel’s Other Features
      1. LET() and LAMBDA()
      2. Power Automate, Office Scripts, and Excel Online
    2. Continued Exploration of Power Query and Power Pivot
      1. Power Query and M
      2. Power Pivot and DAX
      3. Power BI for Dashboards and Reports
    3. Azure and Cloud Computing
    4. Python Programming
    5. Large Language Models and Prompt Engineering
    6. Parting Words
  18. Index
  19. About the Author

Product information

  • Title: Modern Data Analytics in Excel
  • Author(s): George Mount
  • Release date: April 2024
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098148829