Modern Data Analytics in Excel

Preface

Welcome to the Excel revolution. By updating how you think about and use Excel, you can unlock significant productivity gains and use your data more powerfully. This book introduces the “modern Excel” suite of features and other powerful analytics tools.

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 these objectives, this book makes 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 Microsoft 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 7 of the book briefly explains how to load a Data Model from Excel into Power BI. It assumes that, as a Microsoft 365 for Windows user, you already have the free version of Power BI Desktop installed on your computer. Chapter 12 delves into the integration of Python with Excel, guiding you through the process of downloading Python for free. All subsequent tasks and exercises within the book are designed to be completed exclusively within Excel, eliminating the need for external programs. However, you will configure a few Excel add-ins as part of the process.

Technological Requirements

This book is designed for intermediate Excel users eager to discover modern features with which they might not be familiar. To fully benefit from it, you should already be acquainted with the following Excel topics:

• Working with absolute-, relative-, and mixed-cell references

• Building conditional logic and conditional aggregation functions (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 continuing, I recommend Microsoft Excel 365 Bible by Michael Alexander and Dick Kusleika (Wiley, 2022).

In Part III of the book, you will explore advanced concepts in statistics, programming, and related areas. Don’t be discouraged if these topics appear challenging at first. There are ample resources to assist you in gaining proficiency, and I will provide helpful references when necessary. The primary objective of this book is to demonstrate the vast possibilities that Excel offers.

If you prefer to enhance your knowledge first before delving into these topics, I recommend reading my book Advancing into Analytics: From Excel to Python and R (O’Reilly, 2021). It offers comprehensive insights and guidance on advanced analytics techniques, Python programming, and various other topics relevant to modern data analytics in Excel.

How I Got Here

My journey to the data world started with Excel during the early 2010s, before data science and AI had fully taken the world by storm. At that time, Excel often felt like a closed system. If you desired to perform advanced analytics, it was commonly advised to switch to Python or R. For self-service relational data models, Access was recommended. Many of the complex analyses and automations I aimed to accomplish involved cumbersome VBA modules and unwieldy array formulas, making the user experience less than ideal.

For a while, it appeared that Excel might eventually succumb to obsolescence. However, today’s Excel, bolstered by various features and applications, has undergone a remarkable transformation.

What Is “Modern Analytics”? Why Excel?

Modern analytics refers to the use of advanced tools and techniques to prepare and analyze data, ranging from simple retrospective analyses to predictive modeling and artificial intelligence. In the evolving landscape of data-driven decision making, it’s essential to have tools that are versatile and interoperable, enabling users to perform a wide range of analytics activities.

Previously, Excel fell short in meeting these requirements. However, Excel has undergone significant transformation over the past decade, making it a true powerhouse for modern data analytics.

This book aims to dispel common misconceptions held by technical professionals about Excel and to demonstrate its capabilities in the modern analytics realm. By showcasing features such as Power Pivot, Power Query, and other tools, this book challenges the belief that Excel is limited to basic formulas and functions. It emphasizes that today’s Excel has evolved into a robust platform capable of handling complex data analytics tasks.

Ultimately, this book showcases Excel as a powerful and versatile tool for modern analytics. It seeks to debunk myths, guiding technical professionals and managers to fully exploit Excel’s potential for effective data analysis and decision making. In doing so, it enables users to harness Excel as a crucial component of the contemporary analytics toolkit, providing insights and driving success in our data-driven world.

Modern Excel and Interoperability

Modern analytics emphasizes interoperability, so it’s not surprising that many tools showcased in this book are also prevalent elsewhere in the analyst’s toolkit. Notably, Power Query and Power Pivot, discussed in Part I and Part II respectively, are also available in Power BI, Microsoft’s business intelligence and reporting tool. Python can also be utilized in Power BI. These tools can be combined in various ways, and as you master one, you’re likely to encounter it in a different context. This book primarily focuses on Excel, but it’s helpful to understand how these elements fit into the broader modern analytics toolkit.

Book Overview

To meet the learning objective and scope of this book, I’ve divided the content into three parts.

Part I, Data Cleaning and Transformation with Power Query

Part I focuses on Power Query for data cleaning in Excel, and how it can be used as an extract, transform, load (ETL) tool. You’ll get a tour of the Power Query Editor, learning about data profiling and various transformation techniques such as filtering, splitting, aggregating, and merging data.

Part II, Data Modeling and Analysis with Power Pivot

Part II introduces Power Pivot for Excel, focusing on its use for reporting. You will learn how to define relationships, build a Data Model, and enhance it with calculated columns, key performance indicators (KPIs), and more—primarily using the Data Analysis Expressions (DAX) language.

Part III, The Excel Data Analytics Toolkit

Part III of the book explores several exciting new features for data analysis in Excel. You will learn about dynamic array functions, which enable quick and flexible spreadsheet calculations. Additionally, the book provides a primer on predictive analytics and AI, discussing their potential applications in Excel and offering a glimpse into the program’s future. The book concludes with an advanced topic: building an automated workbook using Python. You will learn how to effectively leverage Python and Excel together to enhance your analytical capabilities.

End-of-Chapter Exercises

When I read books, I tend to skip over the exercises at the end of the chapter because I feel keeping the momentum of my reading is more valuable. Don’t be like me!

At the end of most chapters, I offer opportunities to apply what you’ve learned through practice. Exercises and their solutions are located in the exercises folder within the accompanying repository, organized into subfolders by chapter number. I encourage you to complete these drills and then compare your responses with the provided solutions. By doing so, you will not only enhance your understanding of the material, but also set a positive example for me.

This Is Not a Laundry List

Excel’s rapid development pace and the abundance of new tools can be overwhelming. To avoid losing focus and making the book unwieldy, I have carefully selected a specific set of topics with broad potential and usefulness for intermediate Excel users, drawing from my years of experience as an Excel consultant and trainer.

If your favorite or most impactful feature for modern analytics in Excel is not covered in this book, I encourage you to share your perspective as a valued member of the community. The realm of data analytics in Excel goes beyond the boundaries of a single book, and the Excel community is eager to learn from your insights and experiences.

Are you ready to embark on a tour of modern Excel? I’ll meet you in Chapter 1.

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Indicates new terms, URLs, email addresses, filenames, and file extensions.

Constant width

Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.

Constant width bold

Shows commands or other text that should be typed literally by the user.

Constant width italic

Shows text that should be replaced with user-supplied values or by values determined by context.

Tip

This element signifies a tip or suggestion.

Note

This element signifies a general note.

Warning

This element indicates a warning or caution.

Using Code Examples

Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/stringfestdata/modern-analytics-excel-book.

This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Modern Data Analytics in Excel by George Mount (O’Reilly). Copyright 2024 Candid World Consulting, LLC, 978-1-098-14882-9.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

O’Reilly Online Learning

Note

For more than 40 years, O’Reilly Media has provided technology and business training, knowledge, and insight to help companies succeed.

Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit https://oreilly.com.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

• O’Reilly Media, Inc.
• 1005 Gravenstein Highway North
• Sebastopol, CA 95472
• 800-889-8969 (in the United States or Canada)
• 707-827-7019 (international or local)
• 707-829-0104 (fax)
• [e-mail]
• https://www.oreilly.com/about/contact.html

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/modern-data-analytics-excel.

For news and information about our books and courses, visit https://oreilly.com.

Find us on LinkedIn: https://linkedin.com/company/oreilly-media.

Watch us on YouTube: https://youtube.com/oreillymedia.

Acknowledgments

One of the most fascinating aspects of writing a book, especially the acknowledgments, is that it captures a moment in your life and highlights the people who are significant at that time.

Many of these names can be found in the acknowledgments to my previous book. I am especially grateful to the acquisitions team at O’Reilly, Michelle Smith and Jon Hassell, for giving me the green light to write another book. My friend and fellow O’Reilly author, Tobias Zwingmann, whose work I have mutually reviewed over the years, provided an exceptionally helpful technical review for this project. Additionally, my parents, Jonathan and Angela Mount, have been unwavering in their support, more than I could ever ask for. It’s uncertain how many mothers wish their children to become Excel authors, but mine has been incredibly supportive.

I also had the opportunity to deepen my acquaintance with some individuals through this project. I extend my thanks to Alan Murray, Joseph Stec, and Meghan Finley for their invaluable additional technical reviews. Meghan, in particular, has not only brought her impressive technical editing experience to the book but has also been an incredible support as my girlfriend throughout the writing process. (As any author will tell you, writing a book inevitably becomes a family affair.) Additionally, I am grateful to Jeff Stevens, Laura Szepesi, and Mark Depow for their feedback on the manuscript.

Moreover, I owe a debt of gratitude to the editorial team at O’Reilly, who guided me through the extensive process of writing a technical book. A special thanks to Sara Hunter for being an invaluable editorial sounding board as I embarked on writing my second book.

Lastly, I would like to express my appreciation to the entire Excel community for being such a welcoming and inspiring group. This spreadsheet program has opened up more opportunities and introduced me to more incredible people than I could have ever imagined. I hope this book contributes in some small way to your own remarkable journey with Excel.