Budget vs Actual with GROUPBY & PIVOTBY - SUPERCHARGE your reporting!

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★
Sign up for our free Insiders program and get the example file:
File Reference for this video: 0252 Actual vs Budget - GROUPBY + PIVOTBY.zip

★ About this video ★
Budget vs Actual analysis is one of the most critical reports in any organization, it helps to track performance against expectations.

Traditionally, these reports rely on static formulas, but in this video, we reveal a dynamic approach using Excel’s GROUPBY and PIVOTBY functions.

The video walks through step-by-step, showing how to apply these functions with different data layouts. Say goodbye to manually adjusting formulas and hello to a streamlined workflow that updates automatically.

If you're ready, let's get started.

0:00 Introduction
0:31 Data structure matters
1:20 GROUPBY: Budget vs Actual
5:16 PIVOTBY: Budget vs Actual
9:13 Wrap up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[h.ragavendranhariharan]

Next to Excelisfun and Leila Gharani, your channel really stands out.

[SimplyDigitalLK]

Very nice explanation. I like your new style of showing the array and how you manipulate it. It's very visual and will definitely help with understanding the approach and what you want to create. I can't imagine the time you put into this :) but well done!

[IvanCortinas_ES]

Thank you for sharing, Mark. As always, you move at a very high level. Congrats.

[DaveIsAtWork-Really]

I like it, I like it, I like it! My reports are typically "Current Year, Budget, Variance2B, Prior Year, Variance2PY" - a little more involved, but the challenge will be fun.

I really need to talk my boss into paying for your academy!

[williamarthur]

I love H / had not thought to use them to create extra columns ; don't know why, it seems obvious,
guess that's why you're presenting and I'm watching 😳😳

[ismaelkourouma]

Excellent Mark as always. I suggest we continue this journey with the Actuals vs Budget grapgh best practices design 😁😁😁

[tracyyott]

So Cool! Now that I know to include a calculation in the hstack for values, I no longer have any calculated columns based on Choosecols in my reports. Much simpler & cleaner. Thank you so much Mark!

[alexanderschmidt]

This is ridiculously cool! Considering going to the Academy!

[RichardJones]

Cool video. Just need to wait until July for the function as in a slow to update version

[Marquinhos_]

Thanks a lot for your advices, this one is very usefull in my work, but I have a question for you, how I can show the same calculate by month trend?

[alhadad]

Initially, if I want to design a dataset, what format should I consider the best, the categorized or flat format?

[abarbeira]

I'm trying to include the titles in the formula but it doesn't work for calculated columns like variance (#VALUE!). Is there a solution for this? And thanks. Just subscribed.

[safuwanch]

is it possible to add lambda functions for calculate variance in case if pivotby