How to create a Jitter Plot in Excel (100% dynamic)
Excel Off The Grid Blog
by Excel Off The Grid
1w ago
At this year’s Global Excel Summit, two presentations included Jitter plots. So, this got me thinking about how I would create one in Excel. If you’re not sure what a jitter plot is, this is the final chart we are creating in this post: I decided to make the jitter plot 100% dynamic so the chart automatically updates for new data. In each stage, there are many approaches we could have used. This post contains one method; therefore, you may decide to take an alternative approach for creating your jitter plot in Excel. Table of Contents Example Calculations Sections and labels Chart values H ..read more
Visit website
How to use Table slicers for advanced interactivity in Excel
Excel Off The Grid Blog
by Excel Off The Grid
2w ago
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 Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post. File name: 0198 Table slicers for user interactivity.zip Get access Watch the video Watch the video ..read more
Visit website
How to use slicers with PIVOTBY, GROUPBY & FILTER in Excel
Excel Off The Grid Blog
by Excel Off The Grid
1M ago
Microsoft recently announced the GROUPBY and PIVOTBY functions for Excel. Because these new functions provide PivotTable-type functionality, many have asked if we can use slicers with these functions. The answer is Yes! So, in this post, we look at how to use slicers with PIVOTBY in Excel. But, we also go one step further and apply the same approach to the FILTER function too. Notes: At the time of writing, GROUPBY and PIVOTBY are only available to those on the Microsoft 365 beta channel. Table of Contents Data How to insert a slicer PIVOTBY function Connecting the slicer BYROW/LAMBDA/SUB ..read more
Visit website
EOMONTH function in Excel (How to + 8 examples)
Excel Off The Grid Blog
by Excel Off The Grid
1M ago
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 stands for “End Of MONTH”, suddenly its purpose becomes much clearer. This function has a few quirks, so in this post, we look at the EOMONTH function in Excel and work through 8 examples. Note: All dates in this post are shown in a dd-mmm-yyyy format (e.g., 15-Nov-2024). Table o ..read more
Visit website
How to make a Dumbbell Dot Plot in Excel (100% dynamic)
Excel Off The Grid Blog
by Excel Off The Grid
1M ago
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 this post, we will create a dumbbell dot plot in Excel that updates automatically when we add new data. The final chart looks like this: Table of Contents Data Calculations Named Ranges Create the chart Add data to the dumbbell dot plot chart Chart e ..read more
Visit website
How to use SUMIFS with arrays (Excel problem solved!)
Excel Off The Grid Blog
by Excel Off The Grid
1M ago
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 Contents Example The problem with the SUMIFS function Using range functions to solve the problem Using FILTER to solve the problem How to simulate spilling with SUMIFS GROUPBY – the ultimate solution? Conclusion – SUMIFS with arrays Download the example file: Click the butto ..read more
Visit website
Calculate quarter from dates in Excel (inc non-standard calendars)
Excel Off The Grid Blog
by Excel Off The Grid
2M ago
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, we explore how to calculate quarter from dates in all scenarios. Table of Contents Standard calendar year Financial year not aligned to calendar year Non-standard calendar Conclusion Download the example file: Click the button below to join the Insiders ..read more
Visit website
Promote headers in nested tables before expanding columns (2 ways)
Excel Off The Grid Blog
by Excel Off The Grid
2M ago
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 Contents Example Promote headers – Excel files Promote headers – Other file types What if the header is not the first row? Next steps Conclusion Download the example file: Click the button below to join the Insiders program and gain access to the ex ..read more
Visit website
How to spill multiple FILTER functions in Excel
Excel Off The Grid Blog
by Excel Off The Grid
2M ago
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 of Contents Example Single FILTER function The problem with multiple FILTER functions Multiple FILTER functions inside a Table BYROW & LAMBDA to spill multiple FILTER functions Alternative approach for stacking multiple FILTER functions Conclusion Download the example file:&n ..read more
Visit website
How to add manual information to a query (commentary & references)
Excel Off The Grid Blog
by Excel Off The Grid
2M ago
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 Process Step #1: Connect and Combine the CSV files Step #2: Add manual information column Step #3: Merge the query Table back into the first query Step #4 Refreshing the query Important considerations Using this technique in other scenarios Conclusion Dow ..read more
Visit website

Follow Excel Off The Grid Blog on FeedSpot

Continue with Google
Continue with Apple
OR