Modern Data Analytics in Excel (First Early Release)

✍ Scribed by George Mount

• Year: 2023
• Tongue: English
• Leaves: 117
• Category: Library

✦ Synopsis

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

✦ Table of Contents

1. Tables: The portal to modern analytics
   Creating and referring to table headers
   Viewing the table footers
   Naming Excel tables
   Formatting Excel tables
   Updating table ranges
   Organizing data for analytics
   Conclusion
   Exercises
2. Transforming Rows in Power Query
   Removing the missing values
   Refreshing the query
   Splitting data into rows
   Splitting Signups by column
   Stripping the Whitespace
   Filling in headers and cell values
   Replacing column headers
   Conclusion
   Exercises
   states worksheet:
   midwest_cities worksheet:
3. Transforming Columns in Power Query
   Changing column case
   Delimiting by column
   Changing data types
   Deleting columns
   Reformatting data
   Creating custom columns
   Loading & inspecting the data
   Calculated columns versus measures
   Reshaping data
   Conclusion
   Exercises
4. Introducing Dynamic Array Functions
   Dynamic array functions explained
   What is an array in Excel?
   Array references
   Static array references
   Dynamic array references
   Array formulas
   Static array formulas
   Dynamic array functions
   An overview of dynamic array functions
   Finding Distinct and Unique Values with UNIQUE()
   The UNIQUE() function parameters
   Finding unique versus distinct values
   Using the spill operator
   Filtering records with FILTER()
   Adding a header column
   Filtering by multiple criteria
   Filtering by multiple criteria
   Sorting records with SORT() and SORTBY()
   Sorting by one criterion with SORT()
   SORTBY() orders an array by another array
   Sorting by multiple criteria
   Sorting by another column without printing it
   Creating modern lookups with XLOOKUP()
   XLOOKUP() versus VLOOKUP()
   A basic XLOOKUP()
   XLOOKUP() and error handling
   XLOOKUP() and looking up to the left
   Other dynamic array functions
   Dynamic arrays and modern Excel
   Simplicity
   Familiarity
   Real-time updates
   Conclusion
   Exercises
5. Augmented Analytics and the Future of Excel
   The growing complexity of data and analytics
   Excel and the legacy of self-service BI
   Excel for augmented analytics
   Using Analyze Data for AI-powered insights
   Building statistical models with XLMiner
   Reading data from camera
   Sentiment analysis with Azure Machine Learning
   Converged Analytics and the Future of Excel
   Exercises
6. Python with Excel
   Reader prerequisites
   The Role of Python in Modern Excel
   A growing stack requires glue
   Network effects mean faster development time
   Bring modern development to Excel
   Python and the future of Excel
   Using Python and Excel together with pandas and openpyxl
   Why pandas for Excel?
   The limitations of working with pandas for Excel
   What openpyxl contributes
   How to use openpyxl with pandas
   Other Python packages for Excel
   Demonstration of Excel automation with pandas and openpyxl
   Cleaning up the data in pandas
   Summarize findings with openpyxl
   Adding a styled data source
   Conclusion
   Exercises

✦ Subjects

Modern Data Analytics in Excel, Power Query, Pover Pivot and More, Transform, Model, and Analyze Data in Spreadsheets