Create an Excel Dashboard to Easily Track Budget and Actuals with Variances
Get Zebra BI-Pro for free for 30 days: https://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 (https://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: https://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: https://www.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:
https://www.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: https://youtu.be/8ibe27Zuf0A
Calculate Percentages the Right Way in Excel: https://youtu.be/UN5PckRADyQ
➡️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: https://www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
LinkedIn: https://www.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
Видео Create an Excel Dashboard to Easily Track Budget and Actuals with Variances канала Leila Gharani
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 (https://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: https://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: https://www.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:
https://www.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: https://youtu.be/8ibe27Zuf0A
Calculate Percentages the Right Way in Excel: https://youtu.be/UN5PckRADyQ
➡️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: https://www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
LinkedIn: https://www.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
Видео Create an Excel Dashboard to Easily Track Budget and Actuals with Variances канала Leila Gharani
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
![116 Excel Tutorials in 1 Video](https://i.ytimg.com/vi/muTLxMjaZww/default.jpg)
![How to Screenshot on Windows 11 📸 - Cool New Updates](https://i.ytimg.com/vi/UCO80F8_uOs/default.jpg)
![How to Use Excel's NEW Lookup of Images 👉 Professional Dashboards Just Got Easy ➕ Bonus Reveal](https://i.ytimg.com/vi/-BiZjkqgaHc/default.jpg)
![The Secret to Making Hand-Drawn Charts in Excel](https://i.ytimg.com/vi/_jWHD0CK9hY/default.jpg)
![Effortlessly Create Dynamic Charts in Excel: New Feature Alert!](https://i.ytimg.com/vi/ehrXyFPljBo/default.jpg)
![How To Use the new Excel TEXTBEFORE & TEXTAFTER Functions to Save Time on Your Next Project](https://i.ytimg.com/vi/p92Ta3J5vGU/default.jpg)
![How to Use Excel's New TAKE Function to Impress Your Boss and Colleagues](https://i.ytimg.com/vi/z66-6WVZSFY/default.jpg)
![5 FREE Cool Add-Ins for PowerPoint to Start Using NOW](https://i.ytimg.com/vi/ssbBaYLq8Mg/default.jpg)
![How Familiar Are You With Excel Cell Modes? (Ready, Enter, Point, Edit)](https://i.ytimg.com/vi/SgXiepZUumc/default.jpg)
![How to Make a Graph Change Color Based on Value | Conditionally Formatting Charts](https://i.ytimg.com/vi/Gk7GNXplymw/default.jpg)
![Easiest Way to Edit Visuals in Power BI - Watch before using the new features (Includes hidden tips)](https://i.ytimg.com/vi/V0E_Hrn-iJU/default.jpg)
![How to use Analyze Data in Excel (AI Creates Pivot Tables and Charts)](https://i.ytimg.com/vi/AXNPR5q1y08/default.jpg)
![The Secret to Note-taking for Busy People](https://i.ytimg.com/vi/yxHPScN_ct4/default.jpg)
![How to Use TOCOL | TOROW Function in Excel #shorts](https://i.ytimg.com/vi/K-FyqwKsIME/default.jpg)
![You Won't Believe These Crazy PIVOT TABLE Hacks!](https://i.ytimg.com/vi/ZZRl7fALdjw/default.jpg)
![Excel FILTER Function TRICK for Non Adjacent Columns](https://i.ytimg.com/vi/Onudkw9DMlU/default.jpg)
![How to Read and Understand a Balance Sheet (Apple in Review)](https://i.ytimg.com/vi/mr8l6tCI6Lk/default.jpg)
![Excel Dependent Drop Down List with Tabular Data without Named Ranges](https://i.ytimg.com/vi/avVLznHODVA/default.jpg)
![How to use Excel LIVE in Teams Meetings - Realtime collaboration](https://i.ytimg.com/vi/kf1i7cK37SQ/default.jpg)
![Cool PowerPoint Features to up your Productivity | Did you know these?](https://i.ytimg.com/vi/cuByjxckpy0/default.jpg)
![How to Easily Create a Dynamic Filled Map Chart in Excel with Power Query](https://i.ytimg.com/vi/bMJyPbUxjYc/default.jpg)