First, we're going to have some fun setting up our dashboard in Excel. So, this is where we're going to create our dashboard. We want to take a look at the sales performance of our different store locations, which are these, and take a look at the actual sales compared to previous year plan, add in some variance calculations and some charts. We also want to add a selection for year and month. So, as you can see, currently, I don't have anything fancy here. This is just a shape taken.
From a PowerPoint presentation. I have some text here and some formatting in the cells right here. Where are we going to grab our data from? So, actuals and previous year data are in a tab called "Actuals." Here I have my sales year-to-date information by year, month, store location, and my sales value. This is formatted as an Excel table called "TSales." So, this information can come from anywhere, right? This could be retrieved from another.
System, maybe you're using an add-in to bring the data over to Excel, maybe you just copied and pasted it in this table, or maybe you've used Power Query to grab the data in this table, right? It doesn't matter where it comes from; it ends up in a table called "TSales." Now, our plan data is in a separate tab called "Plan." Here, we have a different structure. We have the store location in the rows, and we have the months in.
The columns. This is the Plan data for the current year, which in this case is 2023. The first thing we need to do is to define our selections. To get the dropdown for the month, we are going to go to the "Data" tab under "Data Tools," select "Data Validation." Instead of "Any value," we're going to go with "List." And for Source, because I have the months already in the Plan sheet, I'm just going to go ahead.
And now I just have to make sure that the formatting of the cell is not a date but just put it back to "General," and I can see the current year. So, if you're using a formula, you probably want to make it visible that this cell is not for input, so I'm just going to copy and paste the formatting here, and you're probably going to want to lock this if you're sharing it with other people..
Now comes the fun part. We are going to write the formulas that bring over our actual data first from the Actuals tab to our dashboard. Now, my question to you is: what formula will you use to do that? Because remember, we have to make sure that the combination of year, month, and location is going to match so that we bring over the correct number. What are you going to use? Let me know in the comments..
So, I'm between INDEX and MATCH and XLOOKUP. I'm going to go with XLOOKUP. Let's get started. XLOOKUP - my lookup value is a combination of these three, so I'm going to start with the year. I have to make sure that I fix the year so when I pull down the formula, the reference to the year cell is not going to shift, so let's fix it using F4. Now, I'm going to use the ampersand because we want to bring in the month as well. Again, let's fix it with.
F4. And the store location, which is right here, that's going to be my lookup value. My lookup array is where I'm looking it up, well it's in this table, and I have to just make sure that I'm referencing these in the same order, right? Year, month, and store location. So, let's just select that: year, ampersand, month, ampersand, store location. I'm using a table so I don't have to worry about the dollar signs here..
That's my lookup array. My return array is my sales column. That's what I want returned. And in case something isn't found, let's go with quotation, quotation, close bracket, press Enter, and that's our sales data for the current year selection. So if I switch this to October, everything updates and brings over our October data. Now, for the previous year, I'm just going to take this cell, copy it, and paste it.
- Power Apps New Features & Functions Index, Search, Find & Replace, String Interpolation
- Tutorial Airtable Automations with Make
- Fazendo Primeiro Cadastro de Membros - Planilha Cadastro de Membros e Controle de DÃzimos. #2
Here. Now, by copying, I make sure that the references to my table headers don't shift, - ID Card Make
But one thing that did get shifted was my reference to the store location, but that's not a problem. I'm just going to pull it back. I also want to make sure that the year goes back one by one, so I'm going to do minus one for the year, and that's pretty much it. I'm going to grab previous year's data automatically with this function..Okay, so now let's take a look at our Plan. So, Plan has a different structure. We have months in the columns and the store location in the rows. Again, you can use INDEX/MATCH, or you can use XLOOKUP. Since I'm in the mood for XLOOKUP, let's go with that. Start off with XLOOKUP. The lookup value here is store location and month. We don't have to worry about the year because we just have the budget for the current year. So, we're going to start off with store location..
Then, my lookup array is where is store location, which is right here. This is not an Excel table, so I have to make sure I fix the references using the F4 key. Next, my return array. Well, my return array is a number, but that number is going to depend on the month, right? So, if I just close the formula and press Enter right now, I get the entire row returned for that store location, but I want to restrict it to the right month..
So right here, for the return array, we're going to make that restriction using a second XLOOKUP. My lookup value is the month because that's going to define which column to look at, and I have to fix it using F4. The lookup array is right here. Again, don't forget to fix using the F4 key, and my return array is that, and again, we want to make sure that we fix it. Okay, so that is our second XLOOKUP..
Now let's just also put, if not found, return nothing, close the bracket, press Enter, and that's going to return the correct value for that month and that store combination. Now, just to make sure I fixed everything correctly, let's send this down and double check. Seattle plan is 12238 for November, so Seattle is right here. That's the correct number. Okay, so far, so good. Now let's go ahead and do the variances as well. I'm going to use the shortcut.
Key Windows + dot and let's bring in this icon here for variance. Then we want previous year percentage. Let's copy and paste it here, and this is going to be Plan percentage. Okay, the formula here is going to be the actual divided by previous year minus one, right? So that's a great shortcut formula that you can memorize. It's always current divided by old, minus one. If you're wondering how that works,.
I have a detailed video on this, and I'm going to link it in the cards and in the description. Okay, so let's send this down. Now one thing I do want to make sure is that in case I don't have any previous year data or something is missing that I don't get error values here. So, let's wrap this inside the IFERROR function. If this results in an error, I want to return nothing. So far, so good. Let's actually drag this over and just correct the reference for Actual. Right,.
So now we have Actual divided by Plan minus one. Let's send this down as well. Okay, we're just going to quickly update the formatting of these. Use the shortcut key, Ctrl + 1. Number formatting, use a comma separator, and zero decimal places. So, we've grabbed all the data. Everything should be dynamic. If I am in May, all of this updates automatically. It's time to add in some visualization, so let's add a column chart that compares actual with Plan,.
And then we're going to add another chart for the variances. So, I'm going to select this. I want Plan, so I'm going to hold down the Ctrl key and select the Plan column, including the headers. Go to Insert and insert a column chart. Then let's just place it on this side here and update the formatting. For Actuals, I want this to be a dark color, and Plan should be a lighter color. Now, this is really going to depend on the guidelines that you have at your company,.
Which colors you are using for which series. For the chart title,
Let's quickly add it in. Now, let's go ahead and add another chart for the variances. I'm going to remove the shape outline as well. So, let's go with no outline. Now, for the change to previous year and change to Plan, I want to have those as a chart as well. So, I'm going to select the store location, including the header, which is empty,.Hold down Ctrl, and select the change to previous year. Go back to Insert and insert a column chart. What I want to do is place it below this one, but we just have to update the formatting of this because currently, it doesn't look that great. Let's remove whatever we don't need. I'm going to add the data labels directly to this, and I want to remove the axis, but I don't want to remove the line. I just want to remove the labels, so I'm going to.
Bring up the chart formatting options by just double-clicking somewhere. Select the axis, go to options here, under Labels for label position, I'm going to go with none. Now, for the color of my series, I'm going to select a series, go to color options, and select "Invert if negative." This way, I can pick two different colors, one color for positive values. This one I'm going to have as green, and another color for negative values. This.
One I'm going to go with this light red. Let's just update the formatting of this slightly as well. I want to go to Format, go to shape fill, no fill, shape outline, no outline, and I just want to move this below this chart, so we can easily see the change for each single store location. Now, we do have to play around with this to make sure everything overlaps nicely, but you have the option of controlling the plot area, which is that inside bit,.
And the entire chart area. So after playing around with this, got it to match. Now I just want to place it below this chart. Okay, so now we immediately have the change to previous year, and it really depends on how big or small you want to make this chart. If we switch our view now from November to October, everything updates automatically, right? We can see it over here. Now, we could also create another chart just like this for change to.
Plan and place it below here. So, it's really up to you on what you want to showcase and what your management wants to see. Now by looking at this closely, we can see that New York isn't doing so well, right? This is actual compared to previous year. They're doing worse than previous year, and they are doing a lot worse than what we budgeted. Honestly, Excel charts are great, and I enjoy the process of making them,.
But they can be very time-consuming and very manual, especially if you want to create really professional charts that pack a lot of information without overwhelming the reader. That's why I'm super excited about this add-in from Zebra BI. It's called Zebra BI for Office, and I just want to show you how easy it is to use and how professional the outcome is. So, we are going to go back in time, and instead of creating all these charts,.
We are going to use the add-in. So we don't even need to bother to calculate our variances because the add-in is going to do it for us, and we also don't need to worry about having a nice table design because again, the add-in is going to do it for us. So I'm going to remove this empty row that I just used for formatting because I don't want it to cause any problems either. To get the Zebra add-in, you're going to go and find Add-ins.
In your Excel version. So, Zebra add-ins work from Excel 2016 all the way up. I have Office 365, and the location of Add-ins has shifted. It used to be in the Insert Tab, and now it's in the Home Tab. So, just go to Add-ins, Get Add-ins, and then search for Zebra. You're going to see Zebra BI Tables for Office and Charts for Office. Now, let's just start off with the tables one. I'm going to click on Add and Continue. Now it.
Immediately tries to pick out my data range, and it picked everything out correctly. And I get my table; my data is formatted in millions. You can adjust this if you want. I have Previous Year, Actual, and Plan, Change to Previous Year, Change to Plan, and the Percentage Variances. So, all of this were calculated by itself. I didn't have any columns for them; the add-in generated these for me. Now you can update these as you want. So, for example, let's say I want to have.
Actuals first. I don't want it to be in descending order; I actually want it to show the dataset the way I have it here. So, I want the data source order. I'm just going to click on this, click on it again, and now I get everything sorted as my data source. Let's just make it a bit smaller here. I'm going to collapse the ribbon so we can see more of this. Now take a look at this. I'm going to shift my view to October. Everything updates automatically. Let's go to April. Let's.
Go to June. Everything is responsive. Now you can update colors; you can update your data range. So, a lot of options are hidden here. In the first one, here we have the Data Source Range. So, in case your data wasn't picked up correctly, you can update it here. You can use an Excel table or a Pivot Table as well. We can see that the columns were recognized properly by the add-in. So, the Actual column is an Actual, PY column (Previous Year), and Plan. We also have the opportunity to.
Add in Forecast and Comments. Now, the Comments are great because you can add dynamic annotations to your charts, which is quite difficult to do in Excel. Let me demonstrate that really quickly because that's really cool. So, let's say we had a Comments column here. Optimally, though, you would have your Comments column in your data source, so you have historical comments, and you'll bring them dynamically to your dashboard. But just for the sake of demonstration, I'm just.
Going to put in some comments here. Let's say, New York City didn't perform well because there was low demand, and San Diego performed really well because they had a new Salesforce. Okay, so I added the comments. I want to bring them into my chart. So, let's go ahead and update the Data Source Range. I'll shift the E to F, click on OK, and my comments are immediately there. So, what happened here? "Oh, there was low demand in New York. How come they performed so well? They.
Had a new Salesforce." Now these are dynamic, so if, for example, in Brooklyn, let's say Brooklyn didn't perform so well because their new CEO isn't so great, we add it in, it automatically pops up in here. Now you also have a lot of settings here, so you can adjust what the title of these should be, how you want to show the variance, if it should be absolute, relative, and so on. All of these are adjustable. Also, all of these are adjustable. So, if you don't want to show a chart.
But you want to show just the data for the table, you can update that as well. Lots of settings here that can be tweaked, and it makes the entire process super easy. If you just want to show a table, you can click on this arrow to go to the next page, and this is just showing you the table view. To remove the chart, we're just going to go here and select Delete. That's it; it's gone. Now let me show you the other one, which was Zebra BI for Charts. So, we're going to go to Get Add-ins,.
Let's search for Zebra, Zebra BI Charts, add this in, and it's immediately going to create a responsive chart based on our dataset. Our annotations are here. If you want to bring attention to the variances, currently, they're too small. We can't really see it. We're just going to break the chart here so that we can concentrate more on the variances. Now, in addition to these options, you can also go ahead and change the layout of the chart. So currently, it's responsive;.
You can switch it to just a waterfall chart. If you want to show it vertically, click on the vertical waterfall, and you have everything vertically. Let's say I want to remove the annotations. I'm going to go back here, update my source settings, change it back to what I had, and now I just have my waterfall chart. Now again, you can go to the next page and get another chart type suggested. Here we have an integrated chart where we can see the data for Actuals, Change to Budget, as well as Previous.
Year values. We also have a Variance chart above. Now this chart is responsive, so if it becomes too small, then it only shows this part. If you make it bigger and you have more space, then there's more space to show more things in this chart. Now again, you can go to settings here, you can change this to just the integrated chart, show it as a vertical chart, and so on. Remember, all of this is dynamic, so when our source data changes, the whole chart responds immediately. So,.
As you can see, the process of creating a dashboard becomes so easy and so much more enjoyable. Plus, charts that you create are a lot more effective. Now in this example, we just had one KPI; it was just sales, but you can transform finance reports like profit and loss statements in just a few seconds. So, one aspect I find incredibly useful is Zebra BI's commitment to clarity and accuracy, which is validated by their IBCS certification. So this means that.
The reports that you create aren't just visually engaging but they're also structured in a way that's easy for decision-makers to understand them and to interpret them. The great news is that you can test it all out for free. You can get this add-in from the Office app. In the free version, you are going to notice a watermark on your charts. Because Zebra BI is sponsoring today's video, I'm able to pass along a special deal just for you. So in the description of this video,.
You are going to find a link which will give you an extended 30-day trial to their Pro version, which is the version that I'm using right here. So just have it go and let me know what you think. Remember to check out the link in the description for the extended trial to the Pro version. And there you have it, a dynamic, interactive Excel dashboard that not only looks great, but also tells the financial story of your data at a glance. With these Excel techniques,.
You're now equipped to add some interactivity to your reports. And with Zebra BI, you've seen how to add that extra polish that makes your report stand out. If you found value in this tutorial, please give it a thumbs up and don't forget to subscribe for more Excel tips and tricks. If you have any questions or insights, drop them in the comments below. I love seeing what you come up with. Thanks to Zebra BI for sponsoring this video, and I'll catch you in the next one..
