Chandoo
Chandoo
  • 480
  • 48 459 460
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-program/
~
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/uploads/2024/06/one-formula-dashboard-data.xlsx
⏱ 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 - ua-cam.com/video/mp18miYmmJY/v-deo.html
Excel dashboard in 28 minutes - ua-cam.com/video/35VnP0Rz71I/v-deo.html
How to create an interactive and beautiful dashboard in Excel - ua-cam.com/video/bG1Qha-Ii7A/v-deo.html
Project management dashboard in Excel - ua-cam.com/video/FXnyKU6xZeI/v-deo.html
Full Excel Dashboards course - chandoo.org/wp/excel-school-program/
~
#excel #dashboard
Переглядів: 8 672

Відео

3 Powerful Data Slides for your NEXT Presentation (Free PPT File)
Переглядів 8 тис.19 годин тому
Magic Trick 1: Creating a progress chart with animation Magic Trick 2: Fill a percentage of a shape Magic Trick 3: Jazz up numbers on a slide with transitions ⏱ In the video: 0:00: Demo of the 3 PowerPoint Magic Tricks 0:38: Creating the progress chart (donut chat) 2:30: Animating the progress chart 3:29: Filling inside a shape 5:50: Creating a 3 number slide with wow effect 📁 Download the pres...
Data Engineer vs. Analyst vs. Scientist - What they do, skills and salaries
Переглядів 12 тис.14 днів тому
Do you wonder what is the difference between a data analyst, engineer and scientist is? In this video, let me explain what these 3 roles are, what skills you need to get in, how the career path looks like, what is the typical salary and how the work feels. LET'S GO! ⏱ IN THIS VIDEO: 0:00 - Data Engineer vs. Data Analyst vs. Data Scientist 0:35 - The rules 1:08 - What they do? DE vs. DA vs. DS 4...
Combine Excel Sheets with *this* simple formula
Переглядів 27 тис.14 днів тому
Do you know that Excel now has a formula to combine data from multiple sheets? Yes, the VSTACK() function can combine data from multiple worksheets in one go. In this quick video, let me show you how to use the VSTACK function to combine data from monthly budget worksheets. 📁 Sample data file: chandoo.org/wp/wp-content/uploads/2024/06/vstack-conslidation.xlsx ~ #excel #chandoo #consolidation
End to End Data Analysis Project with Power BI - Netflix Example 📺
Переглядів 33 тис.Місяць тому
Learn how to perform data analysis using a real-life dataset of Netflix shows. We are going to run 10 kinds of analysis & EDA with Power BI. 1) Column Profiling 2) Dealing with missing values (empty / blank) 3) Encoding nulls 4) Imputing missing values 5) Working with Dates and cleaning them 6) Adding new columns of data 7) Splitting / Extracting data 8) Extracting just the first item 9) Text /...
How ChatGPT made me 10X better in Excel Formulas 💡
Переглядів 69 тис.Місяць тому
AI tools like ChatGPT are great for enhancing your Excel skills. In this video, I present 7 tactics I use to 10x my Excel formula skills. 1️⃣ Asking AI for the formula 2️⃣ Explain it to me 3️⃣ Alternatives 4️⃣ Debug the formula with AI 5️⃣ Give me clues 6️⃣Test my skills 7️⃣ Critique my approach 📁 Download sample data file Grab the sample data file from here chandoo.org/wp/wp-content/uploads/20...
Excel PROs use these hidden features to work faster 🚀
Переглядів 32 тис.Місяць тому
📗 Get your Free Excel Tips Book - chandoo.org/wp/subscribe/ ~ Welcome to the first episode of Excel Secrets. Let's zoom in to the home ribbon and uncover some of the most useful and time-saving features. In this video, you are going to uncover the amazing powers of Excel's home ribbon, the most useful and versatile of all ribbons. We are also going to take a closer look at the top secret 🤫 time...
Powerful trick to get data from any folder in Excel (works even after renaming!)
Переглядів 13 тис.Місяць тому
Power Query is great for automation, until it doesn't work because someone (I am not naming them) goes ahead and renames a file or the folder! Then you witness the slow collapse of your automation domino empire. In this video, let me share with you a simple and elegant fix to the problem. Using parameters! Learn how to automate tasks effectively with Power Query using dynamic folder paths. ⏱ vi...
How to make a bar graph in Excel LIKE A PRO - 5 Tricks
Переглядів 11 тис.2 місяці тому
Learn how to create a professional-looking bar graph in Excel with this easy-to-follow tutorial. In addition, discover five expert tricks to take your data visualization skills to the next level. Whether you're a beginner or looking to enhance your Excel proficiency, this video has you covered. Watch now and become a bar graph master! 📂 Sample file for you to practice: Need to practice these ti...
How to make an interactive TOP 10 Chart with Power BI (using parameters)
Переглядів 9 тис.2 місяці тому
🏆 Participate in my Dashboard Competition 👉 bit.ly/3xgC1WB ~ If you make dashboards or reports with Power BI, you often get questions like, "but I want to see top 10 products, not just 5" or "can we see top 10 store names instead of all?" from your stakeholders all the time. In this video, let me present you with an elegant and simple solution to such problems. A dynamic TOPN chart What is TOPN...
Make an Incredible Finance KPI Dashboard with Power BI in 20 minutes
Переглядів 58 тис.2 місяці тому
🏆 Participate in my Dashboard Competition 👉 bit.ly/3xgC1WB ~ Make an incredible Power BI Dashboard to visualize your finance KPI data in just 20 minutes. In this video, learn how to take raw business data and turn it into useful insights by following these 4 steps: 1) Data Preparation - Connecting and transforming data using Power Query in Power BI 2) Data Modelling - Setting up a semantic mode...
How to calculate Compound Interest in Excel - Formula with Examples 💻
Переглядів 15 тис.2 місяці тому
Compound interest is defined as "the interest on savings calculated on both the initial principal and the accumulated interest from previous periods." Classically, known as "interest on interest", this is the most common type of interest used in everyday finance situations. To calculate compound interest in Excel, on principal amount P at the rate of interest R for the number of years N and com...
Automatically Extract BOLD Text with VBA [Adv. Excel]
Переглядів 18 тис.2 місяці тому
It seemed impossible to do in Excel. I had to extract "Bold" portions of 1000s of cells as part of the data cleanup process. Then I came across a simple VBA fix. In this video, let me share the solution and expose you to the limitless possibilities of VBA in Excel. You may want to put on your socks, cause this one is going to knock them right off 🧦🚀 👩‍💻 Get the VBA Code Solution: Visit chandoo....
This ~NEW~ Excel Function is Shockingly Powerful!
Переглядів 268 тис.3 місяці тому
Every heard of SCAN function in Excel? Today, let me show you why you need this function and how it changes the way you solve your data problems. I think SCAN is Magic ✨ You are going learn: What is SCAN function in Excel SCAN syntax and application How to use SCAN to calculate running total quickly Using SCAN to get Maximum values SCAN with LAMBDA Advanced SCAN usage when your data is dynamic ...
SQL Joins - Beginner to PRO Masterclass with 10 Examples
Переглядів 19 тис.3 місяці тому
Left, right, inner, outer, anti, no matter what you call them, Joins are an important part of the SQL for data analysis. In this comprehensive video, learn how to create your first join and more with easy, clear and relevant explanation of the concepts. 💻 Get the data: Visit chandoo.org/wp/learn-sql-for-data-analysis/ to get the dataset. Use the instructions on that page to set up the data in S...
7 Technologies you need to become a Data Analyst in 2024 🚀
Переглядів 49 тис.3 місяці тому
7 Technologies you need to become a Data Analyst in 2024 🚀
How to get Excel & MS Office for FREE (Power BI + SQL too)
Переглядів 29 тис.3 місяці тому
How to get Excel & MS Office for FREE (Power BI SQL too)
Welcome to Chandoo.org on YouTube 👋
Переглядів 29 тис.4 місяці тому
Welcome to Chandoo.org on UA-cam 👋
Honest review of Excel CoPilot - What it can and cannot do 🤖
Переглядів 58 тис.4 місяці тому
Honest review of Excel CoPilot - What it can and cannot do 🤖
Awesome Trick to Get Dependent Drop Downs in Excel (works for multiple rows too)
Переглядів 56 тис.4 місяці тому
Awesome Trick to Get Dependent Drop Downs in Excel (works for multiple rows too)
How to create a Work Schedule (Roster) using Excel
Переглядів 18 тис.4 місяці тому
How to create a Work Schedule (Roster) using Excel
How to "automatically" extract data from a messy PDF table to Excel
Переглядів 20 тис.4 місяці тому
How to "automatically" extract data from a messy PDF table to Excel
Get "ALL" Option in Excel Data Validation with this Crazy Trick 💡
Переглядів 21 тис.5 місяців тому
Get "ALL" Option in Excel Data Validation with this Crazy Trick 💡
I tried all 347 Excel Shortcuts, and here are the BEST ones you need to know
Переглядів 32 тис.5 місяців тому
I tried all 347 Excel Shortcuts, and here are the BEST ones you need to know
How to set up and use Calculation Groups in DAX | Power BI
Переглядів 17 тис.6 місяців тому
How to set up and use Calculation Groups in DAX | Power BI
How to find Outliers in Your Data Easily with Z-Scores in Excel
Переглядів 24 тис.6 місяців тому
How to find Outliers in Your Data Easily with Z-Scores in Excel
How to make an employee time tracker with Excel [Easy setup]
Переглядів 12 тис.6 місяців тому
How to make an employee time tracker with Excel [Easy setup]
How I made a COLOURFUL slicer in Power BI 🎨
Переглядів 14 тис.7 місяців тому
How I made a COLOURFUL slicer in Power BI 🎨
You need these 4 Excel Skills if you run a Small Business
Переглядів 15 тис.7 місяців тому
You need these 4 Excel Skills if you run a Small Business
10 Advanced XLOOKUP Tips & Tricks
Переглядів 28 тис.7 місяців тому
10 Advanced XLOOKUP Tips & Tricks

КОМЕНТАРІ

  • @yourfriend9935
    @yourfriend9935 6 годин тому

    23:00

  • @sarifulhasan
    @sarifulhasan 8 годин тому

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

  • @shreyanshdangi4366
    @shreyanshdangi4366 10 годин тому

    Thanks for this informative video...

  • @ishfaqshah5169
    @ishfaqshah5169 11 годин тому

    Thanks. It was so cool. One question if the sheets have different no. of rows, what to do in such cases?

    • @chandoo_
      @chandoo_ 6 годин тому

      You can make the range big enough so it gets everything. We are removing the blank values thru filter.

  • @jamessmithson4757
    @jamessmithson4757 12 годин тому

    this video just saved me an hour

  • @AP-eb8hd
    @AP-eb8hd 12 годин тому

    Awesome as usual Purna ..

  • @mrbartuss1
    @mrbartuss1 14 годин тому

    The question is WHY?

    • @EamonnCottrell
      @EamonnCottrell 10 годин тому

      There is no why; there is no spoon.

    • @reanalytics1863
      @reanalytics1863 9 годин тому

      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_
      @chandoo_ 6 годин тому

      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.

  • @MihailBaleev
    @MihailBaleev 14 годин тому

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

  • @shreyanshdangi4366
    @shreyanshdangi4366 14 годин тому

    Thanks for this helpful video

  • @shreyanshdangi4366
    @shreyanshdangi4366 15 годин тому

    Very good video and rhanks

  • @Mesjach
    @Mesjach 15 годин тому

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

  • @shreyanshdangi4366
    @shreyanshdangi4366 15 годин тому

    Thanks for this video....very helpful

  • @chrism9037
    @chrism9037 15 годин тому

    Epic formula, thanks Chandoo!

  • @shreyanshdangi4366
    @shreyanshdangi4366 16 годин тому

    Thanks for this video....very helpful

  • @vijaydiwevdi1304
    @vijaydiwevdi1304 16 годин тому

    Thanks sir

  • @aashukumar9237
    @aashukumar9237 16 годин тому

    How to download the pratice data?

  • @vikeyvikey3149
    @vikeyvikey3149 17 годин тому

    Guruji❤❤❤❤❤❤❤❤

  • @BillSzysz1
    @BillSzysz1 17 годин тому

    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_
      @chandoo_ 6 годин тому

      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.

  • @paramveerssachdeva
    @paramveerssachdeva 18 годин тому

    Can't Thank you enough brother. Will be joining your excel school from my other ID.

  • @LokanathReddy-s8y
    @LokanathReddy-s8y 18 годин тому

    Thanks for sharing your knowledge to everyone .

  • @jagatkrishna1543
    @jagatkrishna1543 18 годин тому

    Thanks 🙏 SIR ❤

  • @anandpathare6421
    @anandpathare6421 20 годин тому

    Very nice and helpful to learnn😊

  • @nwitachoudhury5361
    @nwitachoudhury5361 22 години тому

    Do you have a video tutorial to ADDCOLUMN on IsHoliday (Y/N)? Specially for Easter?

  • @chaitanyalanka7043
    @chaitanyalanka7043 23 години тому

    we have to use filters and we can get one link in google then we can place the ID/Name in that link then we will see the output for the particular person/ID the data we are getting for that particular ID/Name we have placed in the filters

  • @ona7574
    @ona7574 День тому

    Amazing!

  • @shreyanshdangi4366
    @shreyanshdangi4366 День тому

    Very good video...thanks for this

  • @FarzanaNoushad-ky5od
    @FarzanaNoushad-ky5od День тому

    i cant find the datasets.could you please provide me the datasets

  • @DEEPANSHUGUPTA-DM23DM077
    @DEEPANSHUGUPTA-DM23DM077 День тому

    I want to learn Power BI as a beginner, but with so many options, it's confusing to choose the best one. Someone recommended UA-cam videos over expensive courses. My questions are: 1) Is your UA-cam playlist enough to go from beginner to expert? 2) Are older videos still relevant in 2024? 3) If I study 2 hours daily, how long will it take to become proficient? 4) Do you have any other important advice? Since its really important for me. I look forward to your prompt response. Thank you.

  • @vijaimahesh277
    @vijaimahesh277 День тому

    Hi man, like what if I want to show a trend change, can i make a single line chart as a gradient from green to red?

  • @emilyenyi7666
    @emilyenyi7666 День тому

    Hello, can see you have been indoors for sometime. Wishing you fast recovery

  • @SoundarabaranidaranDhamodharan

    Check out "The Mentor" Edtech platform where you can learn data analytics from scratch to advance from the current industry experts as 15 candidates per batch with placement assistance. Duration : 3 months Additional features: Resume building Naukri profile building Linkedin profile building Mock interviews Case studies Live projects Beta certification for best performers (value addition to your profile ) Reply here for more details

  • @maheshashu1899
    @maheshashu1899 День тому

    While practicing with datasets ms Excel is not responding re-start,close the program dialog box getting always please give solution for this .

  • @shivaratribikshapathilinus5985

    Am learning the ETL

  • @shivaratribikshapathilinus5985

    Hi... bro can you send me the your number please

  • @Shiva-kl2wp
    @Shiva-kl2wp День тому

    Hi Chandoo. Whenever I see your videos I feel excited and you seem very familiar. I tried to sign up with your Excel School Program, when I hit the button I was speechless with your price. I accept that you are not running charity but the people like me paying 10K Or 16K it's not a piece of cake. Most of the people are often think that they have to learn something, but they don't know the correct path and correct mentor, once they found like you then the 2nd challenge is money. It always be more than their capacity. I have wish to learn advanced excel and I attended for Jatan Sha workshops few time,he said nicely but end of the workshop he explain about the packages and set a price that was so high than my financial position. Same here I felt like that. But your explanation was awesome. Thank you so much.

    • @chandoo_
      @chandoo_ День тому

      You are welcome Shiva. No matter how I price these courses, there will be someone who will find them unaffordable. That is why I give away 90% of everything I create for free thru UA-cam and my website. I charge for other 10% so I can live and support my family. Please use my channel + other resources to learn for free.

  • @mm0dk0ur
    @mm0dk0ur День тому

    Thank you soooooo much

  • @srithiprerana7482
    @srithiprerana7482 День тому

    Thank you so much , your videos are really helpful always !!

  • @shreyanshdangi4366
    @shreyanshdangi4366 День тому

    Very inforamtive video but difficult in sequence like you tokd to view power video first in one of the question

  • @Virgo_0709
    @Virgo_0709 День тому

    This is beautiful. You make learning fun and easy to understand. Even as s beginner. Please can you make more videos on power point presentation. Thank you

    • @chandoo_
      @chandoo_ День тому

      Thank you! Will do!

  • @shreyanshdangi4366
    @shreyanshdangi4366 День тому

    This is the amazing video....thanks for it

    • @chandoo_
      @chandoo_ День тому

      Most welcome 😊

  • @shreyanshdangi4366
    @shreyanshdangi4366 День тому

    Very good video and i learned all the concepts

  • @Fun_is_everything777
    @Fun_is_everything777 День тому

    👌👌👌

  • @myronm973
    @myronm973 2 дні тому

    Great Tutorial, helped me a lot!

  • @FaizanKhan-db9cw
    @FaizanKhan-db9cw 2 дні тому

    i'm using excel 2019 and tried VSTACK formula but its showing #Name (Error) please guide

  • @Plumber_chanel
    @Plumber_chanel 2 дні тому

    Hindi video bolo sir

  • @kpodugbejacob9984
    @kpodugbejacob9984 2 дні тому

    Thank you

  • @VijayPawar-sz6gq
    @VijayPawar-sz6gq 2 дні тому

    👌

  • @howtolearnexcel
    @howtolearnexcel 2 дні тому

    Great tutorial! Can't get enough of Morph animation effect in PowerPoint. It's so mesmerizing!

    • @chandoo_
      @chandoo_ День тому

      I love Morph too. So many creative ways to use it.

  • @AbdulAhad-wy3hi
    @AbdulAhad-wy3hi 2 дні тому

    Very good video to start with. One query from this from the last part you did. At the start when upload the data to Power BI from excel files are these linked from the system or are they uploaded separately once. If integrated then any change in the source file will change the data which means we need to upload a copy file of excel in order not to get our work change every time. If not integrated, then how come your Refresh changed the whole things Thanks a lot

    • @chandoo_
      @chandoo_ 2 дні тому

      The source file (in this example) stays on my computer. Whenever the source changes, I can trigger the refresh in Power BI to update all the calculations and graphs. But in most corporate situations, the source file stays in a network drive / sharepoint / cloud. In such cases, you can also schedule / automate the refresh process so that the graphs are updated automatically.

  • @SandeepSharma-md2ex
    @SandeepSharma-md2ex 2 дні тому

    Pls make a video how can I subtotal only negative values in using subtotal formula