How to use Table slicers for advanced interactivity in Excel

Table slicer selections

Having looked at how to use slicers with PIVOTBY and FILTER in a previous post. Let’s take this a step further and discover how we can use Table slicers for more advanced user interactivity. Table of Contents Example Getting the table slicer selections Changing and applying table slicer selections Disconnected slicers Slicer selections LAMBDA Conclusion … Read more

How to make a Dumbbell Dot Plot in Excel (100% dynamic)

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

Calculate quarter from dates in Excel (inc non-standard calendars)

Quarter from date

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

Promote headers in nested tables before expanding columns (2 ways)

Promote before expand

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

How to add manual information to a query (commentary & references)

Add manual data

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