On this page you can learn more about my book with O’Reilly Media, Modern Data Analytics in Excel: Using Power Query, Power Pivot and More for Enhanced Data Analytics. If you are an Excel user looking to level up your analytics skills, this book is for you.
Where to buy
The book is now available in paperback and as an ebook at these and other booksellers:
- Amazon: Print, Kindle
- Bookshop.org: Print
- Barnes & Noble: Print
- Books-A-Million: Print
- eBooks.com: PDF (paid), EPUB
- O’Reilly Media Online Learning platform (subscription required): Read on your desktop or mobile device. You can also preview the book with a 10-day free subscription to the site.
Reviews, please ⭐⭐⭐⭐⭐
Whether you pick up a paperback, read on your Kindle, or head to O’Reilly Online Learning, please leave a book review. This helps tremendously with the book’s success. If you’re not sure what to include in your review, here are some ideas:
- How did you benefit from this book?
- Who else could benefit from this book and why?
- What did this book offer that other resources have not?
- What are two or three takeaways you have after reading?
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
Learning objective
By the end of this book, you should be able to use modern Excel tools for data cleaning, analysis, reporting, and advanced analytics. In particular, you’ll clean and transform data with Power Query, create relational models in Power Pivot to build sophisticated analyses, and explore the Excel analytics toolkit to further automate and enhance your work.
Prerequisites
To meet the book’s objectives, I make some technical and technological assumptions:
Technical Requirements
To make the most of this book, it is recommended that you have a Windows computer with the Office 365 version of Excel for desktop. The features covered in this book are relatively new and may not be available in older Excel versions. Please note that many of these tools are still being developed for Mac, and compatibility may vary. Due to the fast-paced nature of Excel’s development, it is difficult to provide a precise list of what’s available for each version.
Chapter 12 of the book explores the integration of Python with Excel and will guide you through the process of downloading Python for free. All other tasks and exercises in the book will be carried out exclusively within Excel, without the need for external programs.
Technological Requirements
This book is written for intermediate Excel users who wish to explore modern features that they may not be familiar with. To get the most of it, the Excel topics you should be familiar with include the following:
- Absolute, relative, and mixed cell references
- Conditional logic and conditional aggregation (
IF()statements,SUMIF()/SUMIFS(), and so forth) - Combining data sources (
VLOOKUP(),INDEX()/MATCH(), or other lookup functions) - Sorting, filtering, and aggregating data with PivotTables
- Basic plotting (bar charts, line charts, and so forth)
If you would like more practice with these topics before moving on, I suggest Excel 365 Bible by Michael Alexander and Dick Kusleika.
Table of contents
I. Power Query for Data Cleaning and ETL
1. Tables: The Portal to Modern Analytics
2. First Steps with Power Query
3. Transforming Rows in Power Query
4. Transforming Columns in Power Query
5. Merging and Appending Tables in Power Query
II. Power Pivot for Reporting and Analysis
6. From Power Query to Power Pivot
7. Creating Relational Data Models in Power Pivot
8. Data Analysis and Reporting in Power Pivot
9. Intermediate DAX for Power Pivot
III. The Excel Analytics Toolkit
10. Introducing Dynamic Array Functions
11. Predictive Analytics and AI in Excel
12. Automating Python with Excel
What people are saying
As an Excel teacher, the book helped me to gain a better knowledge in data analysis and gave me new ideas on how to teach materials. I found the explanations in the book very valuable. Not only do I know HOW to use some functionalities, but also WHEN and WHY. I loved the great explanations, I can learn much more efficiently if I truly understand the material. The book was very well-written , easy to understand and full with interesting background information.
Modern Data Analytics in Excel is a must-read for analytics professionals eager to expand their expertise with Excel’s latest features. The book covers an impressive array of techniques for cleaning and analyzing data, all within a remarkably concise format. I found the insights into Power Query and Power Pivot especially enlightening, but it was the author’s ability to weave these concepts together into a clear and accessible narrative that truly stood out. Whether you’re a seasoned analyst or just starting out, this book serves as a refreshing primer on the exciting possibilities now available in Excel. Highly recommended for anyone looking to elevate their analytics game.
This book gets to the core and is concise. All the relevant topics are covered for those wanting to learn Data Analysis in Excel. Those starting out in FP&A or looking to upskill would really benefit from reading this book.
Modern Data Analytics in Excel is a must-read for anyone looking to future-proof their data analysis skills. In an era of rapid AI advancement, this book is a timely reminder of Excel’s enduring power and relevance. It provides a roadmap for analysts to not only enhance their immediate productivity but also to develop the skills necessary to effectively augment their capabilities with AI.
The writing is accessible and insightful, making it valuable for beginners and seasoned analysts alike. By mastering the techniques presented, readers will be well-positioned to leverage AI responsibly and effectively, enhancing their Excel skills rather than replacing them.
Tobias Zwingmann
AI Advisor & Author of AI-Powered Business Intelligence
What sets this book apart is its incredible capability of simplifying complex techniques for all level of users. From beginners to seasoned Excel users, you’ll effortlessly understand and apply the concepts to your work. Combining Python, Excel, Power Query, and Power Pivot, it empowers you to explore boundless possibilities in data analysis and automation.
I highly recommend Modern Data Analytics in Excel! In this book, Excel MVP George Mount takes us on a comprehensive journey through Microsoft Excel, showing how versatile it can be for data analytics. Starting with the basics and moving to more complex features like Power Query and Power Pivot, George does an excellent job breaking down advanced concepts into understandable pieces. What stands out is the practical approach used, with work files, exercises, and resource links that let you practice and explore as you learn. As someone reviewing this book from an accountant’s perspective, I believe that its clarity and practicality make it a valuable reference resource for professionals who live in this world. Accountants, who often deal with complex datasets and require accurate analysis, will appreciate the review of fundamental to advanced analytics techniques.
The advanced topics explore how Excel is evolving to incorporate more advanced analytics, including the possibilities available through AI and Python integration. These topics can seem overwhelming, but George makes them less daunting and approachable. This foresight into Excel’s role in future data analysis is helpful, suggesting ways professionals can stay ahead in their field. Looking to build (or enhance) your Excel data analytics skills? Pick up this book!
Companion repo
The companion repository for the book is publicly available on GitHub. This contains all datasets, workbooks, scripts, exercise solutions, and other files used in the book. Please keep in mind this is a preliminary repo that will be edited along with the book as it heads to final release.
You can download a compressed folder of the files or, if you are familiar with GitHub, clone it to your computer.
Updates/errata
- 7/27/2023: Chapters 1, 3, 4, 10 and 12 are now available for early release.
- 9/22/2023: Chapters 1-2 are now available for early release. (This is a complete first draft of the book!)
- 4/26/2024: The book has been sent to press and should be available in paperback and digital copies shortly!
Consulting
If you are looking to make the most of Excel for data analytics at your organization, check out my Modern Data Analytics in Excel workshops:
Book updates and offers
Get the latest updates on this book and other projects by subscribing to my newsletter: