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.

~

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

⏱ 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
====================

~

#excel #dashboard

[Mesjach]

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

[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.

[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!

[afhlmd]

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

[TP]

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!

[philym]

Another great and useful video. Amazing teacher. (Just a point - 27 mins into video, the mention of that if you click out of the cell then formula writing toggle can be used, so when you click between sheets, the writing will not disappear, came by this by you for all your tutorials

[skriesten]

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

[BillSzysz]

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.

[jonesemerson]

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

[wreltel]

1 question was it possible to VSTACK The headers on top of the Report array to create a fully 1 cell formula and if done that way what are the disadvantages??

I am an advanced excel user of 11 Years and even i learnt ALOT from you today Chandoo !! WOW How can i buy you a coffee this kind of work needs to be appreciated?

[abhishekupadhyay]

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.🙏🙏

[yashrsingh]

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

[LokanathReddy-sy]

Thanks for sharing your knowledge to everyone .

[pedroemanuelrebelo]

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?

[the_ashish_samrat_shorts]

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

[Jbooth]

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!

[sarifulhasan]

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

[musicon]

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

[b_rizzle]

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

[Mehrdad-ebon]

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