As an Excel trainer and consultant, I’ve often encountered skepticism about categorizing skills into beginner, intermediate, and advanced levels. It’s understandable—Excel is a vast program, and users employ it in myriad ways. It’s rare to find two people with identical skill sets, and even rarer to find someone who fits neatly into a conventional skill level like “intermediate.” However, the complexity of defining skill levels in Excel shouldn’t deter us from trying, much like George Box’s observation that all models are wrong, but some are useful. In many fields—whether it’s martial arts, la ..read more

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 Th ..read more

I like to say that good data, much like money, doesn’t grow on trees. It takes work to obtain. Often, this work involves retrieving and combining data from multiple sources using techniques like lookup functions, Power Query joins, and so forth. Copilot is emerging as an incredible tool for helping Excel users avoid getting stuck on the technical side of Excel, allowing them to focus on understanding and acting on their data. However, as a tool based on a large language model, it has been somewhat slow to incorporate certain features. Initially, it was more or less limited to working with a si ..read more

Learning objectives are crucial in instructional design because they provide a clear roadmap for both the instructor and the learner, outlining what is expected to be achieved by the end of a course or lesson. They can help in designing a course or book, selecting appropriate instructional strategies, and assessing learner progress. By explicitly sharing these objectives with learners, instructors set clear expectations, which can increase student motivation and focus. Moreover, when learners are aware of the objectives, they can better monitor their own progress and understanding, leading to ..read more

I see people crafting all sorts of Excel pickup lines, but what about trash talk? After all, how many dates are Excel nerds getting anyway? (And let the trash talk begin.) Here are some Excel put-downs and trash talk phrases, in no particular order: “Your attempt at automation is like a self-driving car … from the Flintstones era.” “Your macro is like a soap opera. It runs forever and nothing ever happens.” “Your workbook is so brittle, it breaks faster than your New Year’s resolution.” “Oh, you’re still using HLOOKUP()? That’s cute.” “Your workbook freezes more than Siberia.” “1980’s video g ..read more

I’m genuinely excited about the suite of AI tools Microsoft has been introducing for Microsoft 365, particularly Excel. However, I do wish the naming conventions were sometimes easier to understand. Specifically, it’s quite confusing to follow along with the various products since they are all named “Copilot:” Microsoft: "What should we call this new product?" Microsoft marketing: pic.twitter.com/MQbVJFX9HL — George Mount (@gjmount) March 20, 2024 This post aims to distinguish between Microsoft Copilot and Copilot for Excel in terms of access, pricing, features, and more. Access To start ..read more

Copilot in Excel can help with a variety of everyday tasks for working with your data, such as adding conditional formatting or creating basic calculated columns. But did you know that you can also use Copilot in Excel for some non-Excel tasks? Let’s explore how this works and see how it could actually help in your day-to-day activities as an analyst. To follow along, you can download the exercise here, or open up any dataset in Copilot in Excel: Download the exercise file here   Excel for Copilot vs ChatGPT Let’s take a moment to understand why Copilot in Excel is knowledgeable about top ..read more

One of the greatest wins for Excel users learning Python is access to a comprehensive suite of data visualization tools. However, compared to Excel, the default plots produced with Python in Excel can seem somewhat static. For example, there’s no straightforward way to add tooltips or interactive elements that allow users to click on parts of the chart to understand the data’s origins. Nonetheless, it is possible to make Python plots created in Excel feel more interactive and user-friendly. This post expands on a previous article where I explored using moving averages with Python in Excel. We ..read more

Just like in biology, where cells are the building blocks of life, in your Excel workbook, cells serve as the foundational elements. In this post, you’ll discover how to interact with individual cells and then extend your knowledge to managing entire rows and columns using openpyxl. For this demo, I’ll assume you’re familiar with installing and working with Python packages, along with basic operations such as creating variables, indexing, and so on. If you need assistance with these topics before proceeding, consider checking out my book Advancing into Analytics: Advancing into Analytic ..read more

Working with Python code becomes significantly more manageable when objects are assigned clear, sensible names, and Excel is no different. This blog post covers how to create named ranges and tables in Excel using openpyxl. To begin this demonstration, we will import several modules from openpyxl, along with a sample of rows from the penguins dataset available in the seaborn package. import openpyxl from openpyxl.worksheet.table import Table, TableStyleInfo from openpyxl.utils import get_column_letter from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl import Workbook wb = Wo ..read more