Modern Data Analytics in Excel is a technical book primarily focused on delivering dense technical information, which may not be everyone’s cup of tea. However, if you delve into a lot of technical writing, you’ll find that much of it can be humorous and irreverent. I’m fortunate to have worked with a publisher, O’Reilly, who encourages authors to embrace their unique, often eccentric, voices.
To that end, I’d like to present some of my favorite quotes from Modern Data Analytics in Excel, along with a bit of context and relevant passages from the book.
Tables are the portal to Modern Excel
This is more or less the title of Chapter 1. I intended to use this chapter as an orientation or warm-up before diving into Power Query in the rest of Part I of the book.
Tables are becoming an increasingly important feature in Excel, as many of the most powerful tools, such as Power Query and now Copilot, don’t function effectively without them. So this chapter guides the reader through the basics of understanding tables, providing them with a proverbial driver’s license to use modern Excel.
Every step you take… Power Query is watching you
After the introduction to tables in Chapter 1, the rest of Part I focuses on using Power Query for data cleaning and transformation.
In this section, I emphasize the importance of Power Query as a repeatable tool for data cleaning. For example, the Applied Steps menu provides a running log of every action performed on the data. To paraphrase The Police, Power Query monitors every step you take in data cleaning. If you’ve ever been frustrated trying to understand how a weekly report is compiled, or if you feel like you’re spending too much time manually assembling reports, Power Query is designed for you. This part of the book will show you why.
Ditch the Frankentable with Power Pivot
Part II of the book delves into the world of data reporting with Power Pivot and DAX. Those of you who have endured the frustration of manually assembling reports through copy-pasting will also likely relate to the ordeal of a crashing workbook. This often stems from an enormous flat table filled with lookup functions, compiled in an attempt to report data cohesively.
I refer to these cumbersome lookup tables as “Frankentables,” and the remedy for eliminating them lies in Power Pivot. This tool enables you to build relational data models directly within Excel, which means you can create PivotTable-driven reports and dashboards from multiple sources without consolidating them into a flat table.
Power Pivot also offers the advantage of the Data Analysis Expressions (DAX) language, which, to be honest, can initially seem as daunting as a Frankentable to newcomers due to its steep learning curve. However, this section of the book is dedicated to introducing you to some essential DAX topics, helping you begin to build dashboards and analyses that would otherwise be challenging to execute in Excel.
Python as Glue
Part III concludes the book with a discussion of various other tools for analytics in Excel. The final chapter focuses on Python. I have long advocated for Excel users to learn Python, and this idea has gained traction with Python’s recent integration into Excel. However, there remains considerable confusion and resistance regarding why Excel users should learn Python.
One argument I present is that Python has become a core “glue” language in modern software development—a metaphor not originally mine, but one that certainly sticks… or rather, works! I explain it like so in the book:
When I first started as an analyst, my toolkit began and ended with Excel. Data management, reporting, dashboards—all were housed under the familiar green-and-white interface of Excel.
A few years on, the landscape has dramatically changed with the introduction of Power BI, Office Scripts, Jupyter Notebooks, and even Python integration within Excel. This expansion reflects broader technological shifts: moving from a single, all-encompassing application to a network of specialized, interconnected tools.
Navigating this diverse ecosystem requires a “conductor” or “glue” to seamlessly integrate various components. Whether it’s transferring data between platforms, visualizing it in a new way, or deploying a cloud-based machine learning model to a user’s dashboard, Python stands out as an exemplary choice. Its versatility spans from crafting simple scripts to developing complex, enterprise-level solutions, making it compatible with a wide range of operating systems and programming languages.
Microsoft has celebrated Python’s role as a versatile “glue” language, incorporating its use across Azure, Power BI, SQL Server, and more. Python’s popularity among developers and organizations alike has led to a thriving community of users and an abundance of resources.
Again, I recognize that not every Excel user wants or needs to learn Python, and this chapter may be considered the most technically demanding by the audience. However, I hope that including it helps set the record straight about Python’s role in modern Excel usage.
Conclusion
I hope you pick up a copy of the book and perhaps discover other interesting metaphors or quotes that stand out to you. If so, feel free to share them in the comments! Writing a book is a lot of work, and it’s important to have a little fun along the way. It would be wonderful to know that my readers enjoyed the book as well.
Leave a Reply