One of Excel’s most useful functions for accountants, financial modellers and data analysts is the EOMONTH function. This function calculates the last day of a month, based on a specified number of months in the past or future. From the name, EOMONTH, it may not be clear what the function does. Once we realize EOMONTH … Read more
Dumbbell dot plots are an excellent chart style for presenting comparative data. These chart styles quickly show the difference or progress between two data points. Often, Excel tutorials for dumbbell dot plots create charts that require manual updating for new data. But using the right Excel techniques these charts can be fully dynamic. So, in … Read more
Over the past months, the topic of using SUMIFS with arrays has come up a few times, so I thought I would write a post to help anyone with the same question. While I’m using SUMIFS throughout this post, it equally applies to SUMIF, COUNTIF, COUNTIFS, MAXIF, MAXIFS, MINIF, MINIFS, AVERAGEIF, and AVERAGEIFS. Table of … Read more
In many companies, analyzing by quarter is common practice. Unfortunately, when we calculate quarter from dates in Excel, we can make things overly complex. This is especially true when working with financial years that do not align with the calendar year. Or even worse, non-standard calendars (such as 4-5-4 or 4-4-5)! So, in this post, … Read more
In Power Query, promoting headers for nested tables before expanding columns leads to fewer and easier transformations. However, these options are not available in the user interface. Therefore, we must make a few changes to the M code ourselves. This post shows two ways to promote headers in nested tables before expanding columns. Table of … Read more
In a recent project, I used FILTER and TEXTJOIN to create a comma-separated list to display in a cell. Unfortunately, when I tried to spill the values for different filter criteria, it created an error. So, in this post, we will work through a solution, showing how to spill multiple FILTER functions in Excel. Table … Read more
There are many scenarios where raw data itself isn’t enough to complete a task efficiently. For example, we may need to add manual information, such as categorization, commentary, or reference numbers. In this post, we look at this process and understand how to add manual information to a query. Table of Contents Key principles Example … Read more
Reconciliations are a common and time-consuming activity for finance and data professionals. So, in this post, we look at how to automate reconciliations with Power Query. A reconciliation is nothing more than comparing two pieces of information and ensuring they contain the same values. Unfortunately, reconciliations can be time-consuming, as a user may need to … Read more
For many Excel users, the transition to Power Query can be difficult. Having spent years working with Excel’s functions, moving to Power Query seems like an alien environment. The good news is that many of Excel’s text functions have equivalent functions in Power Query. The purpose of this post is to provide a reference to … Read more
Our ability to manage inputs effectively is critical to building automated processes in Excel. A common reporting scenario is to get data from the latest file. This generally occurs where each file includes the historical data; therefore, we only need the latest file. In this post, we are exploring how to create an efficient solution … Read more