Create an Excel Dashboard to Easily Track Budget and Actuals with Variances
ฝัง
- เผยแพร่เมื่อ 7 มิ.ย. 2024
- 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 ( • Index Match Advanced: ... ) 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-budg...
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: 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:
/ @leilagharani
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: • How to Lock Cells 🔒in ...
Calculate Percentages the Right Way in Excel: • Calculate Percentage i...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / 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
⬇️ Grab the workbook I use in the video from here: pages.xelplus.com/actual-budget-file
I want to discuss about some important things and business, how can I contact you?
This is a very useful video. I am a mechanical engineer. I like your videos because they are always straight to the point. I use some of your idea and concepts to build efficient tables and clearer reports.
That's great to hear!
There is always something new to learn in every tutorial video posted by Leila. Thanks!
I’ve got two of your dashboard courses (dashboard and charts) can’t say enough how tremendously useful they’re!!! I’m not even thinking about making pivot charts anymore, to be honest I doubted before why you’re using simple charts and not pivot charts. Now I know why and now my reasons are the same! My dashboard looks so much presentable and professional!!! Thank you so much!!!
Dear Leila, thank you for your posts, I love them and your name is already famous among my colleagues - with note: look what I have learned from Leila. Thank you for improving the excel and logic skills among people. Love to see your next video.
It was helpful watching you talk us through the process from beginning to end while considering which approach to use and THEN show an alternative. It was like a classroom but better because I could pause and think about how I would approach it before hearing your approach.
I’m working on building an in-house timesheet, payroll, and certified payroll system for the construction industry using Excel and find myself building complex tables when better knowledge of functions would be more effective and efficient. And, again, because I’m spending a lot of time, trying to work my way through how to build something in excel, it was helpful to watch you do the same thing, so thank you.
That's great to hear Jason! All the best for your project. Sounds like a nice challenge to tackle and a great opportunity to learn a lot of Excel tricks 🙂
Thanks for always simplifying data analysis.
Hi Leila, this is so useful. Am gonna applied this is my analyst roles! Thank you very much for the easy understandable video!
Just found your channel. Looking forward to learning from you. The presentation is wonderful. Thank you for making this education available!
Absolutely awesome!!!
You are amazing. Thank you for posting this.
Very nice. The stacking visuals of Zebra BI to show actual to PY was neat.
One edit I'd make to your dashboard table is keep the location list dynamic based on the source data. That way, if you add a new location, you won't run into missing data on the dashboard.
Explained in amazing and easy way.
This is nothing less than amazing. Learned so much in 1 exercise, thank you, you’ve gained a follower.
Welcome aboard!
Excellent Leila!!
Brilliant!! Fantastic!! And YOU!! Dearest sweetie pie!! In a little bubble !! right there! Floating in my spreadsheet!! !! I absolutely love it !! loads of lotssss !! ❤❤❤❤ thank you! You’re amazing!
This video is a fortune, it has helped me with tons of problems I was facing while analyzing data for my work, especially that multiple Xlookup commands within one command. Thank you so much Leila for this amazing video and this amazing channel. 🙏🙏🙏
You're very welcome!
I was just looking for a video presentation on Zebra BI.
This is perfect.
Thanks Leila.
Glad it was helpful.
Thanks Leila, definitely filter function nested in a sum will be my no1 option..this add-in is outstanding..No doubt it will inhance my dashboards and dynamic reports. Very well explained as usual 👏
Thank you so much!
As always Best learning platform
Thanks Leila. so useful.
Thank you for this very useful course!
Thank you so much for clear expresion. I will use for my monthly report
Bravo, @LeilaGharani.... Bravo. This was great. And you looked so pretty in the video, too.
Thanks Leila, useful for me😊
I appreciate the content. Thank you for making it easy to understand.
Glad to hear that!
I've only worked on one Excel project that included a dashboard. Ah, but what a dashboard it was! The model was created over a long period of time by a woman who was the manager of an industrial maintenance and safety department, and she was an awesome manager as well as an Excel wiz of your caliber. The model took a feed from a mainframe and did all manner of calculations from it, many of which I didn't understand. I was brought in for 5 months to clean up the formulas and add some VBA automation. By the end of that time, I knew more about tables, charts, domain lookups and statistical calculations than anybody else I know (would have wanted to). Of course, I've forgotten most of it, as that project ended more than a dozen years ago, but thank you for the fond memories and for showing how some of these things have changed since Excel 2010 (which is what they were using at the time).
Thanks for sharing your experience! It sounds like it was not only a great learning opportunity but also a memorable collaboration. I'm glad our content could stir up some good memories and provide insights into the newer features.
Excellent.. as always 🎉
Thank you so much 😀
Thank you Leila!
Your videos are the best. You explain things well and go slow enough for the likes of me to keep up. Is it possible to create a dropdown list based on Table names? For example, I have 10 weeks, same table format but different data. I want to be able to select which week to look at without creating 10 different tables. Do you have a video for that? Thank you so much!!
Your videos are explaintion is good 👍👍👍👍👍👍👍
Very inspiring 😊
Love this!!! Dynamic is always best --
👍
Leila could you please do a deep dive video comparing Business Intelligence tools? I really really want to learn, but I am nervous I am going to jump into the wrong one and have to learn something else in six months. Also, do you have an opinion regarding with BI might be best for a controller at a SMB? Thank you so very much in advance!!!! Your content makes me feel capable rather than incompetent ❤
As always beautiful work.
Thank you so much 😀
Very useful information
Nicely done Leila as usual. It would be great to make this work not just for one account, but to be able to pull any from the chart of accounts and be able to present a report like this. Maybe for a future video? Thanks!
یه دونه ایی لیلا جان 😍 😍 LOVE from IRAN
Amazing, thank you!
Our pleasure!
This is awesome. Zebra BI is great 👍
👍
Thank you so much :)
Amazing!
Thanks for Sharing! Leila 👍🙂
Thanks for watching!
Delighted!
👌👌👌👌👌👌 excellent 👍
Fantastic video, as always, Leila! 🌟 Your insightful walkthrough was both informative and engaging. It's impressive to see how these solutions can enhance data visualization and reporting. This video is a valuable resource for anyone looking to deepen their understanding of effective data presentation. Kudos for the great work!
Looking forward to more collaborative content
🦓
This is an excellent video. Thanks for uploading.
It looks like a nice add-in, but I have a small issue with your pricing model. I'd take a full-feature single user without watermarks for a small fee, but I have no need for a 10-user or more license fee - I'm just one content creator.
Hi Leila, excellent video.. have just started watching your lectures. Just had a query- We have not used "&" in Xlookup formula while looking up for values in the Budget Column due to the way the data has been structured, that is, cities in columns and months in rows-Is my understanding correct?
Super informative as always , thank you . I wonder if I can adapt it to a personal home or retirement budget ?
I think you can pretty much adapt this to any type of report.
How do you remember so much! Can't wait for you to star in a Marvel movie as the super hero who beats up villains with excel sheets lol! =punch(Face&Body,*2,"")+Run(Villian=massive)
🤣
excellent
Awesome
Your a wizard!
datavisard :)
what is the best dashboard to use for sales to gross profit per product? thanks!
Dude. The invert as negative feature for two different colors - I've always done this manually! Amazing, thank you!
Happy to help!
Can this exact dashboard be made in Power BI if you have the data in same format as you showed in this example ?
Hello Leila!
I have a question regarding the Zebra add in. If I create a dashboard with this tool and I send the Excel file to another person who does not have the add in installed... Will they still be able to view the information?
Thank you very much.
Hi Leila. Also. Could you please share a video on how to get the frayed edges in the heading (Sales Performance: Actuals, Budget, Y-O-Y Variance)
Just pick an image from PowerPoint design, or from online image sources 👍
Hi Ad, How to format Actual/PY/Plan at 0:55? Did you make a video before? Could u share the link ! Many thanks
Hi Leila, love your channel, I have a question, as I am trying to use this actuals and budget with multiple entries for each location. When using your example, If I have multiple entries for different amounts for each location for a month, it only captures the first entry. How do you fix the actuals so it adds up the different amounts for a store i.e Detroit has two entries for $107,336.50 how could you get the formula capture these both and add this up so it took the total $214,673.00?
o o
Hi, That was really helpful video. Can we get the excel workbook please as It is yet to available on your website?
We're still working on it. Probably in the next few days.
one day my dashboards will be as professional as this!!!
Just click download and you're done! :)
Considering the sales value are Numbers, I would actually have used a SumIfs, setting the sum_range as the entire sales column of the TSales table. Criteria 1 would be the Year column to match the current year, Criteria 2 the month, and criteria 3 the locations list as a range instead of a cell. That way it'll be able to handle multiple entries per location within the same year and month, and will be able to even move over to future years without an issue, and it should spill down automatically per the list of locations which if set correctly can also account for new locations for futureproofing (Would be even easier if the list of locations would be a spill range by way of the Unique formula.)
You beat me to it, was going to post the same thing!
@@deanphillips8408 Hey, ever since Excel added spilling I've been a sucker for consolidated formulas that may be more complex but only need to calculate once per range!
Same Here
Yep. SUMIFS is a great option. I was in an XLOOKUP mood. XLOOKUP would be a good option for when we add the comments column to the source data and need to add that to our dashboard.
Hi, As my work uses old version of excel we dont have XLOOKUP. Considering Left out with sumifs.
Does the add on attache to the file ? Would the recipient be able to see the details assuming that they do not have the add on, especially the paid version ? Thank you for a great video.
Hi, yes, the visuals will be embedded in the Excel workbook, so no additional installation is required :)
Amazing video... I have a queston... I have a slicer in my excel sheet connected with table in power pivot... The items in the slicer are the names of the months... Can a sort the items in the slicer that the months must be like a calendar... january, february and so on?
I've used some of these techniques for a spreadsheet I need to share on our company intranet. The problem is that after it is embedded on the website, the 'Month" list becomes inactive and the user can't change the selection. Is there an option or something else I can do to make the list option changeable when embedded on a website?
Hi Leila. Great video. Would it be possible to share the base excel data for the charts?
Yes. We're working on the blog post. Check back early next week. Once the file is ready, I'll add to the pinned comment.
Thanks, it is so useful. please teach this dashboard wit Index Match formula. or just help me to use these formulas for my dashboard.
Great video. Quick question: if I'm sharing an Excel report externally to a client that does not have the Zebra add-in, will they still be able to view the charts?
The visuals will be embedded in the Excel workbook, so no additional installation is required :)
Excellent video! I've a question about a different topic though, I want the cell A to be the sum of a column, and every time the total sum of the column adds a +12, the cell B will gonna add +1.
For instance: Cell A = 12 then Cell B = 1; Cell A = 24 then Cell B = 2; Cell A = 36 then Cell B = 3; but Cell A = 40 then Cell B is still = 3, only reaching 4 when Cell A = 48.
I don't know it it's clear enough.. can you help me with that? I don't even know how to search it
Pls leila can you make a video on how to calculate likert scale :customer satisfaction (chi square ) with Excel
i want to create data validation in acces. but i want that when i select a contecnt in the cell then in next cell list of content should show related to previous cell selected content iv validation format
Maybe I am being too static in my approach to solving problems, but sumifs feel really easy to do quickly.
I have a workbook with two sheets, Personal Budget and Data.
Data Sheet has row heading, with different items, such as Utilities. The left column has dates from 01-01-24 to 12-31-24 with each month showing a subtotal.
The Budget sheet has columns heading with months, Jan, Feb etc. and the rows show the items listed on the Data sheet. I can get the Data subtotals to populate the Budget sheet using the subtotals from the Date Sheet but is there a way to flash fill (Budget sheet) going left to right, Jan thru December. My formula on the Budget sheet for January , =SUM(Data!AH35) so February would be =SUM(Data!AH65). Row 35 is subtotal for Jan and Row 65 is subtotal for Feb
Currently I’m typing each formula, very time consuming and tedious.
Any Suggestions,
Thanks,
Frank p.s. can you bring back your alter ego in some videos, very entertaining!!!
Could you do a marketing dashboard please?
All of these are great. However, if you have an excel file that has a lot of sheets and data, it will be super slow to respond. Do you have any tips on how to decrease or remove excel file’s unresponsive moments?
We collected some tips for slow spreadsheets here: www.xelplus.com/how-to-fix-slow-excel-spreadsheets/
1) Presentation - excellent as always ... 👍👍
2) Content - interesting but will need to rewatch several times to fully understand the improvement over dashboards by Power BI & Power Query (I'm not suggesting there isn't any but rather I am a slow learner) ... 🤔
PS
At the 7:48 mark; " ... okay, so far so good ..." If only I could utter that phrase ... 😕
Once again, you pass along information, I likely would not find on my own .. thank you ... thank you ... thank you ...😍😍😍
Glad you found it interesting, Chris. Thank you for tuning in!
Hi Leila, I’m looking to buy your bundle excel courses. I just have 1 question: If I have a problem at work and your course doesn’t cover this topic, do you offer support/guidance as well? Is there a chat / forum to post my questions? Thank you!
Great to hear that you're interested in our Excel courses! If you come across anything in the lectures that isn't clear, you can definitely post a comment. We have teaching assistants who are ready to help clarify any course-related content for you. However, please note that our support is focused on the topics covered in the course. While we would love to assist with all Excel challenges, we can't provide consulting services for non-course related projects or work-specific issues.
Question, how can I gif a cell a Color depending on the formula of another cell which is used ?
Fyi Many of us do not have Xfunction available as Microsoft only allows it with a 365 subscription. I just bought MS Office 2021 for Excel and they have not included Xfunction or insert picture & place in cell. I wish above had included both solutions, ie included index match solution as it is a bit tricky.
I like these Zebra charts. Is the license for the developer only? Can I distribute the Excel dashboard file without the users requiring a license to view and change the charts? Thanks!
The receiver will need the add-in installed for that but they can get the free version too.
Just wondering about converting the Today() formula to year. When you start looking at the december details, you are most likely in January of next year, so the year no longer reflects the year of your details. Isn’t it beter to use year(max(date of actuals))?
Good point. Yes. That should be tweaked or changed to a drop-down.
Oh i see.. Thanks a lot
Great video, my question would be, is there an advantage to pulling this together in this way vs using a pivot table and pivot chart?
If you want to build some more complex dashbord/charts with the highest possible level of customisation it’s charts and never pivot charts
@@mindyourown6320 how so? what is restricted in pivot charts that is available in a regular one?
Pivot charts don’t allow to add tons of additional data e.g error bars scatter plots etc which you can easily use in simple charts to make extremely packed and complicated charts. Pivot charts quick and easy regular charts for better detailed and complex visionary. Also you can use name manager to sneak formulas in regular charts. Can you do this with pivot charts? I’m not sure 🤔
@@mindyourown6320 ill experiment on my next pivot chart and see if it works. thanks for the feedback
Big👍👍👍
Your voice is so sweet, you can also start dubbing Hollywood movies like scarlett johansen.
Hi. If you need to share a dashboard, does the team members need the ZEBARA BI license too?
Hi, yes, each user has to have a valid Zebra BI license :)
Hi Leila - thank you for your videos, you are so knowledgeable! I'm fairly good with Excel - but I have an issue that is stumping me right now and I was wondering if you could point me in the right direction. My family does a contest every year where we pick the winners of the College Football Bowl games and whoever gets the most right wins. I have a pretty involved excel sheet that does calcs for me and can even eliminate people if they have no chance of winning. However - the one thing I'm wanting to do is predict the likelihood or odds of who may win based on the remaining games and what people have picked. I'm struggling to find an easy way to do this. The information I have is what people's picks are for the remaining games - I'm wondering if there is an excel function that could help me to figure this out. I've looked into What if Analysis/Goal seek, etc and none of those seem to be the right fit. Any ideas? Thank you!
Something I have wondered about, if I share the workbook with someone who does not have the Zebra add in, will it still look and feel the same for them?
Hi, yes - the visuals will be embedded in the Excel workbook, so no additional installation is required :)
Hey I dont see this data validation list in google sheets?
Hello how can we show in a visual that if the objectives have been achieved or not and if it not achieved, it will show in red and those who are achieved still use other color ?
I have a question out of the video please? what if two different people created a sheet include names and i want to compare names but everyone writes the names by his own spelling for example jo and joe or johnn and john ..etc here the same data but different spelling and i have more than 50 names and all names not repeated again in the same sheet so i can't make it manually by replace so how to solve that because different spelling will affect the comparison and match formula
Ms @LeilaGharani
I could not access the data forecast option on my Mac
By I can access this on my Windows PC using the same id
I don't see data import from PDF likewise I can do it on my Windows PC
Is there some platform-specific differences causing the discrepancy?
I have checked my Mac OS is updated
My office 365 is updated
I have reinstalled office 365
But I still can't figure out
Mac is behind regarding some features. They might come in future updates but some will never come to Mac. That's why for best experience it's always good to use Excel on Windows.
For the step of calculating the percentage of actual VS PY why you add -1
allah tuttugunu altin etsin. yillardir kafamdaki sey....
What if my windows does not have an xlookup function?
I used Zebra BI in PBI. It's great they are now in Excel. However it is shame that if I want to share Excel file with my colleagues who do not have the add-in, they will not be able to refresh data
True. They could install the free version of the add-in though.
Can you please tell how we can fill in all the blank spaces in an excel sheet with '-' character in a shortcut way?
I would use replace. Highlight the data you want to replace the blank with -, then go to replace. Leave Find blank and add the - in Replace. Then select replace all.
When you send the spreadsheet to another person, do they need to have the add-in installed too?
Fantastic question! I'd love to know also
The visuals will be embedded in the Excel workbook, so no additional installation is required :) Just click "I agree" or "Accept & Continue", and they're good to go :)
@@ZebraBI will they be prompted to download or will they just see a blank box?
@@RonDavidowicz sorry for the previous misunderstanding - no additional download is needed; they just need to click "I agree" or "Accept & Continue," and all is good :)
❤
How To calculate QTD and YTD numbers taking the same example of yours
I want to discuss about some important things and business, how can I contact you?