Leila Gharani | Create an Excel Dashboard to Easily Track Budget and Actuals with Variances @LeilaGharani | Uploaded November 2023 | Updated October 2024, 13 minutes ago.
Get Zebra BI-Pro for free for 30 days: bit.ly/3GjXFKV
In this step-by-step Excel tutorial, we'll create a dynamic and interactive Excel dashboard from scratch. We'll be comparing Actuals to Budget and Previous year data. The Actual data is on another sheet in an Excel table and it has a different structure so we have to use an Excel formula that enables us to lookup values in three different columns and match them so we can find the correct row. You can use different formulas. I used XLOOKUP but you can also use Index and Match (youtu.be/ontXHp9cwOQ) in case you don't have XLOOKUP. Our next challenge is to bring over budget data from another sheet. This time we have to match the rows and the columns. Again, you can use Index Match or XLOOKUP (I show the XLOOKUP approach).
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/actual-budget-file
I also show you how add a drop-down for months and use a formula to grab the current year.
Then I move on to variance calculations (percentage change). Once we have the information we create a column chart to compare actual and previous year data and also add Excel bar charts to show the variance. We make sure that positive values (bars) are in green and negative bars are in red. We do this dynamically of course using "invert if negative" feature of Excel charts. Lots to cover, so get ready to transform your understanding of actual sales, budget figures, and variances into a visually stunning and informative dashboard.
🌍 Join 400,000+ professionals in our courses: xelplus.com/courses
🔍 What you'll discover:
▶ Excel Dashboard Design: Receive detailed, step-by-step instructions on crafting an automatically updating sales dashboard.
▶ Data Management in Excel: Learn effective techniques for extracting data from various sources and establishing intuitive user selections.
▶ Advanced Excel Functions: Gain proficiency in XLOOKUP and other critical Excel functions for precise data analysis.
▶ Variance Analysis: Understand the methods to calculate and visually present data variances.
Excel Chart Visualizations: Acquire skills in adding dynamic column charts for a compelling visual data interpretation.
➡️ Join this channel to get access to perks:
youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
00:00 How to Setup an Interactive Dashboards in Excel from Scratch
00:53 Setup of Data
02:20 Drop-Down for Month Selection
07:49 Calculate Variance Actual to Previous Year/Budget
09:37 Add Visual Insights
14:00 Zebra BI Add-in
21:34 Wrap Up
🎬 LINKS to related videos:
How to Lock Cells 🔒in Excel: youtu.be/8ibe27Zuf0A
Calculate Percentages the Right Way in Excel: youtu.be/UN5PckRADyQ
➡️ Join this channel to get access to perks: youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: xelplus.com/course-quiz
🎥 RESOURCES I recommend: xelplus.com/resources
🚩Let’s connect on social:
Instagram: instagram.com/lgharani
LinkedIn: linkedin.com/company/xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Get Zebra BI-Pro for free for 30 days: bit.ly/3GjXFKV
In this step-by-step Excel tutorial, we'll create a dynamic and interactive Excel dashboard from scratch. We'll be comparing Actuals to Budget and Previous year data. The Actual data is on another sheet in an Excel table and it has a different structure so we have to use an Excel formula that enables us to lookup values in three different columns and match them so we can find the correct row. You can use different formulas. I used XLOOKUP but you can also use Index and Match (youtu.be/ontXHp9cwOQ) in case you don't have XLOOKUP. Our next challenge is to bring over budget data from another sheet. This time we have to match the rows and the columns. Again, you can use Index Match or XLOOKUP (I show the XLOOKUP approach).
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/actual-budget-file
I also show you how add a drop-down for months and use a formula to grab the current year.
Then I move on to variance calculations (percentage change). Once we have the information we create a column chart to compare actual and previous year data and also add Excel bar charts to show the variance. We make sure that positive values (bars) are in green and negative bars are in red. We do this dynamically of course using "invert if negative" feature of Excel charts. Lots to cover, so get ready to transform your understanding of actual sales, budget figures, and variances into a visually stunning and informative dashboard.
🌍 Join 400,000+ professionals in our courses: xelplus.com/courses
🔍 What you'll discover:
▶ Excel Dashboard Design: Receive detailed, step-by-step instructions on crafting an automatically updating sales dashboard.
▶ Data Management in Excel: Learn effective techniques for extracting data from various sources and establishing intuitive user selections.
▶ Advanced Excel Functions: Gain proficiency in XLOOKUP and other critical Excel functions for precise data analysis.
▶ Variance Analysis: Understand the methods to calculate and visually present data variances.
Excel Chart Visualizations: Acquire skills in adding dynamic column charts for a compelling visual data interpretation.
➡️ Join this channel to get access to perks:
youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
00:00 How to Setup an Interactive Dashboards in Excel from Scratch
00:53 Setup of Data
02:20 Drop-Down for Month Selection
07:49 Calculate Variance Actual to Previous Year/Budget
09:37 Add Visual Insights
14:00 Zebra BI Add-in
21:34 Wrap Up
🎬 LINKS to related videos:
How to Lock Cells 🔒in Excel: youtu.be/8ibe27Zuf0A
Calculate Percentages the Right Way in Excel: youtu.be/UN5PckRADyQ
➡️ Join this channel to get access to perks: youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: xelplus.com/course-quiz
🎥 RESOURCES I recommend: xelplus.com/resources
🚩Let’s connect on social:
Instagram: instagram.com/lgharani
LinkedIn: linkedin.com/company/xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
