Modern Data Analytics in Excel

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

Part I. Data Cleaning and Transformation with Power Query

Chapter 1. Tables: The Portal to Modern Excel

Chapter 2. First Steps in Excel Power Query

Chapter 3. Transforming Rows in Power Query

Chapter 4. Transforming Columns in Power Query

Chapter 5. Merging and Appending Data in Power Query

Part II. Data Modeling and Analysis with Power Pivot

Chapter 6. First Steps in Power Pivot

Chapter 7. Creating Relational Models in Power Pivot

Chapter 8. Creating Measures and KPls in Power Pivot

Chapter 9. Intermediate DAX for Power Pivot

Part III. The Excel Data Analytics Toolkit

Chapter 10. Introducing Dynamic Array Functions

Chapter 11. Augmented Analytics and the Future of Excel

Chapter 12. Python with Excel

Chapter 13. Conclusion and Next Steps

About the Author

George Mount is the founder of Stringfest Analytics, a consulting firm specializing in analytics professional development. He has worked with leading bootcamps, learning platforms and practice organizations to help individuals excel at analytics.

George is the recipient of Microsoft’s Most Valuable Professional (MVP) award for exceptional technical expertise and community advocacy in Excel, as well as the author of Advancing into Analytics: From Excel to Python and R (O’Reilly Media, 2021). He resides in Cleveland, Ohio.