Excel Off The Grid Blog
1,105 FOLLOWERS
Hey, I'm Mark. Over the last 20 years or so, I've used Excel almost every day. Excel is my primary tool for the manipulation, presentation, & analysis of data. Through this blog, I want to take you from a beginner/intermediate Excel user to an advanced user. I aim to demystify some of those 'advanced' features & show you that it's not so hard after all. If that sounds OK to..
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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
Excel Off The Grid Blog
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