![Chandoo](/img/default-banner.jpg)
- 480
- 48 459 460
Chandoo
New Zealand
Приєднався 1 тра 2009
👋 Hello. I make you AWESOME at your work 😎
I make videos on Excel, Power BI & Data Analytics so that you can be awesome at your work.
✅ My vision is to help 1 million people every month.
✅ I post new videos every week.
✅ I use Excel 365 and Latest version of Power BI in my videos.
💻📗👉 For more advanced training, check out my Excel School program - chandoo.org/wp/excel-school-program/
~
KNOW 😀
About me: chandoo.org/wp/about/
What I'm doing now: chandoo.org/wp/now/
Stuff I use: chandoo.org/wp/about/what-we-use/
FOLLOW 🚶♀️🚶♂️
🌐 Website - chandoo.org/wp/
📃 FREE Data tips newsletter - chandoo.org/wp/subscribe/
📚 Courses - chandoo.org/wp/excel-school-program/
📷 Instagram for quick tips & snippets - chandoo.xlsx
🐤Twitter - r1c1
💥తెలుగు Telugu Channel - ua-cam.com/channels/uaf89M0Lt5pmXzaUssXHvw.html
I make videos on Excel, Power BI & Data Analytics so that you can be awesome at your work.
✅ My vision is to help 1 million people every month.
✅ I post new videos every week.
✅ I use Excel 365 and Latest version of Power BI in my videos.
💻📗👉 For more advanced training, check out my Excel School program - chandoo.org/wp/excel-school-program/
~
KNOW 😀
About me: chandoo.org/wp/about/
What I'm doing now: chandoo.org/wp/now/
Stuff I use: chandoo.org/wp/about/what-we-use/
FOLLOW 🚶♀️🚶♂️
🌐 Website - chandoo.org/wp/
📃 FREE Data tips newsletter - chandoo.org/wp/subscribe/
📚 Courses - chandoo.org/wp/excel-school-program/
📷 Instagram for quick tips & snippets - chandoo.xlsx
🐤Twitter - r1c1
💥తెలుగు Telugu Channel - ua-cam.com/channels/uaf89M0Lt5pmXzaUssXHvw.html
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
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
23:00
It's amazing sir... Thank you very much..
Thanks for this informative video...
Thanks. It was so cool. One question if the sheets have different no. of rows, what to do in such cases?
You can make the range big enough so it gets everything. We are removing the blank values thru filter.
this video just saved me an hour
Awesome as usual Purna ..
The question is WHY?
There is no why; there is no spoon.
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
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.
Top quality content! Totally deserved to be part of an excel conference. Respect 🫡
Thanks for this helpful video
Very good video and rhanks
You're here for Excel. I'm here for Chandoo jokes.
Thanks for this video....very helpful
Epic formula, thanks Chandoo!
Thanks for this video....very helpful
Thanks sir
How to download the pratice data?
Guruji❤❤❤❤❤❤❤❤
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.
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.
Can't Thank you enough brother. Will be joining your excel school from my other ID.
Thanks for sharing your knowledge to everyone .
Thanks 🙏 SIR ❤
Very nice and helpful to learnn😊
Do you have a video tutorial to ADDCOLUMN on IsHoliday (Y/N)? Specially for Easter?
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
Amazing!
Very good video...thanks for this
i cant find the datasets.could you please provide me the datasets
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.
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?
Hello, can see you have been indoors for sometime. Wishing you fast recovery
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
While practicing with datasets ms Excel is not responding re-start,close the program dialog box getting always please give solution for this .
Am learning the ETL
Hi... bro can you send me the your number please
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.
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.
Thank you soooooo much
Thank you so much , your videos are really helpful always !!
Very inforamtive video but difficult in sequence like you tokd to view power video first in one of the question
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
Thank you! Will do!
This is the amazing video....thanks for it
Most welcome 😊
Very good video and i learned all the concepts
👌👌👌
Great Tutorial, helped me a lot!
i'm using excel 2019 and tried VSTACK formula but its showing #Name (Error) please guide
Hindi video bolo sir
Thank you
👌
Great tutorial! Can't get enough of Morph animation effect in PowerPoint. It's so mesmerizing!
I love Morph too. So many creative ways to use it.
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
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.
Pls make a video how can I subtotal only negative values in using subtotal formula