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
Author(s): George Mount
Year: 2023
Language: English
Commentary: Modern Data Analytics in Excel, Power Query, Pover Pivot and More, Transform, Model, and Analyze Data in Spreadsheets
Pages: 702
Tags: Modern Data Analytics in Excel, Power Query, Pover Pivot and More, Transform, Model, and Analyze Data in Spreadsheets
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
