My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
No matter whether you're an Excel newbie or an expert, be prepared to revolutionize how you work in Excel with ChatGPT. ChatGPT can magically turn you into an Excel wizard within seconds. It can help you find the right function to use, explain how any function works, and even speed things up for you. All …
The GROUPBY and PIVOTBY functions are a significant breakthrough in Excel's toolbox of functions. They let you easily group or aggregate data, a concept that's been around in Excel since the days of PivotTables in 1993 and Power Query since 2010. But what's exciting about GROUPBY and PIVOTBY is that they simplify this process down …
With the cost-of-living crisis affecting everyone, it's essential to get control of your finances. Having a personal budget can be your most valuable tool, but creating a budget spreadsheet from scratch can be intimidating, time-consuming, and, for some, a downright chore. In this post I'll step you through creating a personal budget in Excel, that's …
Imagine having a tool that transforms the way you work, a tool so powerful it's valued at a minimum of $237,600 a year1. Yes, you read that right! Microsoft 365 Copilot, "the most powerful productivity tool on the planet"2 1. This was the minimum investment when Copilot was first available. Thankfully, now you can get …
With AI gradually making its way into every aspect of our lives, working in Excel has become much easier. You no longer need to research the web and copy over the data into Excel. Simply prompt the AI in natural language, and it will spill out the results for you within Excel! All thanks to …
New in October 2023, Microsoft introduced Excel checkboxes embedded directly within cells*. This enhancement is set to streamline data interaction and management in Excel, making it more efficient and user-friendly. *Currently available to Microsoft 365 users on the Beta channel. Table of Contents Download Example File Excel Checkboxes in Cells Video Excel Checkboxes in Cells …
We now have a new, improved way to import data from the web to Excel. Just like in Power BI, the Get Data > From Web connector for Power Query in Excel now auto-detects tables and curates them into a Suggested Tables list. Plus, you can use the AI-driven Add Tables Using Examples to show …
Slicers enable you to quickly and easily toggle filters on and off. They're best known for working with PivotTables, but Slicers also work with Excel Tables. I like to use them to save time applying filters I use regularly, but we can also create dynamic reports by leveraging formulas that can recognize when a row …
Descriptive statistics enable you to summarize, organize, and simplify large datasets to derive meaningful patterns and insights. For a long time there’s only really been one tool to get descriptive statistics in Excel, but in this tutorial, I cover 3 ways. They all have their pros and cons. Table of Contents Download the Excel …
Slicers are a fantastic tool to get a filtered view of your data and visuals. But by default, they get sorted alphabetically or follow the calendar year if your parameter contains months. What if you wanted them sorted in a custom order? Maybe you want to show fiscal years, a Sunday to Monday work week …
Over a billion people use Excel and let's guesstimate half of them know how to write an IF formula. Of that 500 million, 99% don't fully understanding how Excel handles logical tests. This knowledge can be the difference between writing monstrous nested IF formulas like this one below that contains 15 IFs, and 5 CEILING …
The SUMPRODUCT function is one of Excel's most versatile functions. But now that we have SUMIFS, COUNTIFS, AVERAGEIFS etc. it’s easy to think this function is, dare I say, redundant! However, SUMPRODUCT can do things you can’t do with those functions. Table of Contents SUMPRODUCT Function Video SUMPRODUCT Example File Download Introduction to the SUMPRODUCT …
Back in my accounting days I regularly prepared reports that summarized monthly data into quarters. Back then, we used the SUM function with Boolean (TRUE/FALSE) logic, which worked fine, but, there are better ways to do it now that we have dynamic arrays. In this tutorial I’ll cover both options and you can choose the …
Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows. They enable us to write a formula once and have it automatically applied to all rows or columns in the range we specify, improving productivity and accuracy. These functions are currently available to Microsoft 365 users. …
Excel has a load of AI tools already built-in that we can use to improve productivity and make our lives easier. There are Excel AI tools that will clean data, analyze it for you, create automatic forecasts and even write custom LAMBDA formulas. And that’s all before Copilot gets added! Let’s take a look at …
One of the best uses for Python in Excel is creating the amazing charts available through Python libraries like Seaborn and Matplotlib. These libraries open up a huge array of charts we simply cannot make using the built-in Excel charts. Get started with Python in Excel here. Your browser does not support the video …
You can now use Python in Excel natively! Python runs securely in the cloud, and we write Python in Excel like a formula. You can load Python libraries to Excel including Pandas, NumPy, Seaborn, Matplotlib and more. No need to install any add-ins and no clunky separate windows for writing the code. Note: Python in …
Excel dynamic named ranges bring flexibility and efficiency to your spreadsheets. Unlike static named ranges, dynamic named ranges automatically adjust in size as you add or remove data, ensuring that your formulas, PivotTables and charts always include the most recent information. You can also use them to return different ranges based on selections in drop …
Excel functions are the backbone of a financial modeler’s day to day work and as such, it’s essential to have a good understanding of the functions that are commonly used to build financial models. Below are the top Excel functions that you must know as a financial modeler grouped by area. Are there any functions …
Progress charts are a powerful way to track milestones and showcase achievements. However, there’s no built-in way to create Excel progress circle charts, but with some clever uses of doughnut charts we can whip them up in no time. In this tutorial we’ll look at how to quickly create them and link them to Slicers …
Excel Office Scripts: The Future of Automation, Replacing VBA Excel Office Scripts have ushered in a new era of automation, gradually replacing Visual Basic for Applications (VBA/macros) as the preferred method for automating Excel tasks in both the desktop and online. VBA’s limitation is it’s tied to the desktop. VBA cannot and never will be …
Efficiencies gained from Power Query can be life changing. I’m not exaggerating. Power Query can reduce time spent gathering and cleaning data by up to 99%! Imagine if all you had to do was click a single button and all the boring and laborious data gathering and cleaning was done for you. What would you …
Everything you need to know about how Excel handles date and time and all the tools you will need. Read in conjunction with the accompanying Excel file, which you can download below. Download the Files Enter your email address below to download the comprehensive Excel workbook, cheat sheets and PDF. Get Files By submitting your …
Charts provide a visual representation of data, making it easier to understand and interpret large amounts of information quickly. At least that’s the objective, but too often poor choice of chart formatting is more of a hindrance than a help. In this tutorial we’ll look at the types of Excel chart formatting that is an …
One of the downsides of PivotTables is they have a very distinctive look. Some might even say they’re ugly. In this tutorial I’m going to cover my PivotTable formatting tricks that will transform their look and feel. Table of Contents Custom Sort Order Column Labels Inserting Blank Rows Subtotal Position Custom Styles Conditional Formatting Expand/Collapse …
With Microsoft 365 we can now include images in Excel PivotTables, as well as built in data types like Stock and Geography, Power Query custom data types and Power BI Organisational Data Types. This opens up the ability to create dynamic and visually rich PivotTables like never before. When combined with some slick formatting we …
Annoyingly, there’s a shortcut for Merge & Center on the Ribbon, but nothing for Center Across Selection which is far superior. Center Across Selection achieves the same result but doesn’t cause the rude interruptions to selecting ranges that Merge & Center does. To remedy this, I consulted ChatGPT to write a Center Across Selection shortcut …
Dashboards have limited space which is why an Excel scroll and sort table is super handy. Scroll and sort tables allow you to embed an extract of data from a larger table in your Dashboard. Users can scroll to see all the records and toggle which column to sort by and set the sort order, …
Excel Formatting Best Practices Proper use of Excel formatting can significantly impact the speed and clarity in which users can work with and interpret your spreadsheets. It enables you to highlight essential information, direct users, and encode data. Effective formatting subtly enhances usability, while poor formatting can be a distraction. These are my top 10 …
Excel can now write formulas for you. Simply give it an example or two and it’ll suggest a formula to complete the task. Remember how amazing Flash Fill was when you saw it for the first time? Well, Formula by Example is 10x better because it isn’t a one time and done solution. Being a …
