The amazing ONE formula Excel dashboard with LET function

One formula = Excel Dashboard 😎
That is right. In this video, let me share a revolutionary approach to creating Excel dashboards using just ONE formula.
💥 Full video + Excel file: Join my Excel School program to get the full video and unlocked Excel workbook. Go here 👉 chandoo.org/wp/excel-school-p...
~
Excel 365's LET function offers a powerful and elegant way to create dashboards and business reports with just a single formula. In this advanced tutorial, let me share the approach with a full-length example. Your mind will be blown for sure 🤯
The approach:
============
◉ Writing big formulas with Advanced Formula Editor in Excel
◉ Using XLOOKUP to combine data
◉ LET function to create variables for the report
◉ Summarizing totals by sales person using BYROW and LAMBDA
◉ Using IMAGE() to get the image of sales persons
◉ Adding in-cell graphs using REPT function
◉ Generating indicators for icons
◉ Applying dynamic sort order with SORTBY function
◉ Filtering out top x values using TAKE function
◉ Generating 13 week trends using array SUM functions
◉ Adding sparklines
📁 Sample file: chandoo.org/wp/wp-content/upl...
⏱ In this video:
=============
0:00 - The amazing one formula Excel dashboard with LET function
1:01 - The setup & approach
4:28 - How to download the full workbook 📁
5:25 - Calculating extra columns like with XLOOKUP
9:13 - Getting to the totals by Salesperson level
11:30 - Using BYROW and LAMBDA to generate the totals
15:09 - Revenue bar graphs (with CF)
17:05 - Revenue bar graphs with REPT
20:46 - Profit KPIs
23:07 - Dynamic thousands formatting with TEXT function
25:20 - Dynamic sorting with SORTBY
30:55 - 13-week trend with MAKEARRAY, CF and Sparklines
📺 MORE DASHBOARDS
====================
Make Excel dashboard with ChatGPT - • DON'T tell my boss, bu...
Excel dashboard in 28 minutes - • How to make an Interac...
How to create an interactive and beautiful dashboard in Excel - • How to create a fully ...
Project management dashboard in Excel - • Make an awesome Projec...
Full Excel Dashboards course - chandoo.org/wp/excel-school-p...
~
#excel #dashboard

[@Mesjach]

You're here for Excel.
I'm here for Chandoo jokes.

[@jonesemerson7967]

Hi Master Chandoo , my teacher, thanks to keep me update through email and I can watch in detail here. Fantastic and so useful . Enjoy your great life in NZ

[@AnthonyEnglish]

I’m only 8 minutes into this video and can’t believe how much you’re able to pack into it. That formula looks daunting, but you break it down so simply.

[@afhlmd]

Teacher... that was amazing. This video made me fall in love with Excel again. Thank you so much

[@yashrsingh]

Thank you for this video as it helped me to understand some new and functions better.

[@MagicOfMushrooms]

Thank you Chandoo. This is great information. I'm an analyst and I've been using excel for 20+ years and the LET formula for about a year now and it's changed my life. I think Excel Labs may just take it to the next level. Just downloaded it!

[@chandoo_]

Glad it was helpful!

[@TP014563]

Complex formula indeed! I have few of these myself, and I only use them in reports/files where I know that a person wont be asking to constantly change things for them (i.e. new requirements every now and then), because you rightly said at some point in the video, debugging is bit difficult/challenging.
I like the arrangement though, especially the 13 weeks trend analysis, very neat!

[@skriesten]

Thank you, Chandoo. You presentation is provided in such a simple way to follow. It is clear and concise.

[@chrism9037]

Epic formula, thanks Chandoo!

[@BillSzysz1]

Thanks Chandoo 👍👍👍
Maybe it's worth putting the whole thing in a LAMBDA function with three arguments in the form of individual tables?
It would be less scary for not very advanced users and more resistant to unwanted actions.

[@chandoo_]

Yes, we can. As this is a very specific, non-repeating thing, I think a LAMBDA wouldn't be needed. But the AFE makes it easy to convert such formulas to LAMBDAs.

[@LokanathReddy-s8y]

Thanks for sharing your knowledge to everyone .

[@abhishekupadhyay6938]

Another amazing video!!!
I am very grateful that you share such videos which contains so much useful information freely. I have learned much by watching your videos and every time you teach me something new.
Thank you very much for sharing your excellent Excel skills.🙏🙏

[@sarifulhasan]

It's amazing sir... Thank you very much..

[@alexbarbucristi]

Dude, you're the boss of excel!

[@pedroemanuelrebelo2182]

A very interesting example of the potentialities of the LET function in conjunction with HStack.
How can comments be made within the LET function to make it easier to understand?

[@weslleyhenrique2989]

Amazing!!!

[@AP-eb8hd]

Awesome as usual Purna ..

[@jagatkrishna1543]

Thanks 🙏 SIR ❤

[@Jbooth81583]

Love this!

[@vijaydiwevdi1304]

Thanks sir

[@the_ashish_samrat_shorts]

Hey chandoo I really like your videos and I never forgot to give you a 👍

[@chandoo_]

Thank you so much 😀

[@VinodKumarHyd]

Boss, you are the ultimate

[@ajithkumar4904]

Nice....

[@vikeyvikey3149]

Guruji❤❤❤❤❤❤❤❤

[@Jbooth81583]

Hi -- could you explain the LAMBDA -- how does that work? You define "a", then make the 2nd part of the formula equal to a. I don't fully understand whats going on there. Thanks!

[@musicon1636]

chandoo bro google data analyst capstone project
gurinchi oka video chei bro

[@jiskomodo]

This is great. I’m currently working on a report that has over 200 tables with data that I need to combine with vstack, then filter and use the reduce function. Is there a million row limit to vstack? Or can I put this into the Let function and it will work?

[@chandoo_]

If you have such large volume of data, use power pivot and DAX to do you calculations. Formulas will be slow and hard to debug.

[@Mehrdad-eb5on]

Hi Chandoo Ji, Do you see any changes after wearing braces? I think a year has paste, when are you going to remove them?

[@b_rizzle4808]

@Chandoo are we able to create a paginated type report with this and lambda recursion?

[@chandoo_]

Can you elaborate a bit more on what you need?

[@PhatTony]

How to tell if I have Excel 365??

[@mapletech_22]

Amazon 🎉

[@fabianmorrizz1262]

Hi chandoo , will this work in excel 2021 version version built 2045. I am not able to do it , the grid option under excel labs is blank

[@chandoo_]

I am not sure if all the formulas are available in Excel 2021. I suggest using Excel on the web as most of these should work with that (including Excel labs).

[@kilapanga]

UAU

[@rbc13183]

At this point, you're basically coding. In such a case, it's important to focus on readability, so I'd recommend more formatting, such as organizing your code blocks into separate lines. That's the first step to making it easy to understand and maintain later. Having this all bunched together with no line breaks (and blank spacing between sections of code) is one of the primary reasons that the formula is so difficult to follow.

[@chandoo_]

You have to watch the tutorial before making comments like this. The formula is neatly arranged thru advanced editor as shown in the video.

[@mrbartuss1]

The question is WHY?

[@EamonnCottrell]

There is no why; there is no spoon.

[@reanalytics1863]

There are multiple reasons why
1: Sorting the entire report is powerful but only possible when the entire report is an array
2 : Filtering an array is more accurate than filtering individual columns

[@chandoo_]

Because we can. I explain my reasons at the end. It is an interesting experiment. I got to learn many new ways of working with long formulas. Hopefully you got to takeaway something too.

[@RonDavidowicz]

Because it’s there.

[@EamonnCottrell]

@@chandoo_ appreciate these epic walkthroughs 👍👍

[@arslanthemughals]

My manager will fire me if he see this thing.

[@RonDavidowicz]

Genius

[@MihailBaleev]

Top quality content! Totally deserved to be part of an excel conference. Respect 🫡