Learn How to create Excel Dashboards - Part 4 - Dashboarding Techniques | Abdelrahman Abdou | Skillshare

Learn How to create Excel Dashboards - Part 4 - Dashboarding Techniques

Abdelrahman Abdou, Data Analyst & Excel Lover!

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
8 Lessons (57m)
    • 1. Part 3 Introduction

      0:39
    • 2. SkillShare Intro Done

      0:17
    • 3. Controlling Pivot Charts with ComboBoxes

      11:59
    • 4. Dynamic date filters Part 1

      12:31
    • 5. Dynamic Date Filters Part 2

      16:08
    • 6. Choosing Different aggregation methods

      11:54
    • 7. Capturing Slicer Selection in Formulas

      2:47
    • 8. SkillShare Outro Done

      0:18

About This Class

Link for the other 3 classes in the series:

In this class, students are going to learn some Dashboarding techniques that they can use to build Interactive dashboards.

This class combines the skills learned on the first two classes.

If you haven't enrolled in the previous classes, you can Enroll using the below links :

Class 1

Class 2

Class 3

Class 5

Transcripts

1. Part 3 Introduction: hello and welcome to third class on the series of classes on how to create Excel dashboards . In this class, we're going to combine the formalism, functions and dynamic ranges that we've learned on the first class and the tools of interactivity that we've learned on the second class into dash boarding techniques that we're going to learn on this third class. And if you haven't watched the 1st 2 classes out, Holly re command, you'd watch them. You can enroll into the 1st 2 classes using the links below, and with that being said, let's start our third class on dash boarding techniques. 2. SkillShare Intro Done: thank you for enrolling into this class before we start the class. Please make sure to follow me on skill share by clicking on the follow button at the top of the screen or on the bottom right hand corner of the screen, so that you be able to receive updates about new classes that I release or any changes to existing classes. 3. Controlling Pivot Charts with ComboBoxes: Hi, guys. So in this video, we're going to discuss how we can create Ah, combo box that controls a pivot chart. So you can see here that, um I've got a combo box here. And when I just choose a different year, it just changes the data on the chart to plot the sales for that particular year. Okay, s. So if a choose your 2012 you can see here that it's the data the data plotted. It has changed to the for the sales in 2012. Now, Ah, let's see how we can create the this kind of effect, because here that I've got a combo box here and we will check what it's connected to, so right, click on it and click on formal control. And you can see here that the MPA trains for the combo box is ah arranged called the year list. And also there is a selling here on sell em seven on the combo box row data sheet. Okay, Now, let's check the combo box row data sheet. You can see here that we've got a table that contains some sales data, and there is ah, that colander is highlighted in yellow. It's a calculated column that extracts the year from the order date. Okay, And also I've got a pivot table here, and this is what's what the pivot Short is connected directly to. So this is what the what? This pivot short, actually. Ah, plots. Okay. And you can see here that there's a filter for the year and actually the combo box. What it does is it changes the year in this filter, and I'll show you how it's it's by using a macro, I'll show you how we can create such a macro, and you can see her that there is upgraded another. Um, I've created another pivot table here with the year only with the Year column on Lee, and it's just listing the years and you can see here this arranging sending order by clicking on that button and then clicking on sort, smallest to largest. And the reason I created this this pivot table is toe have the list of years to have the hammock range for the year list, and so that this this pivot table would feed that range and it would be because the range is dynamic, so If you add data for 2015 for example, this pivot table would expand and the range would expend as well ah to include the year 2015. And so the year 2015 would be included on the list as well. Of course, both these pivot tables, they get fed from the same row Data source to this table here, we've got on the left. Great. Now let's check our dynamic range here the year list. It's a simple range that I've created. Using the offset function as we've seen on the video for creating dynamic range is using the offset functions to please refer back to that video. If you want Teoh, if you need a refresher and how to create dynamic ranges Okay, Now we're gonna I'm gonna close the name manager here and you can see here that there is a cell here, which is the combo box selling. This is the selling for the combo box. For it out puts the index number of the year selected So because on this list, 2012 is the second year on the list, so it outputs to and you can see here that If I choose 2013 it will open 23 and so on and so forth. And this is just ah, on this cell of we've got an index function that, um, gets this the year itself out of, ah, out of the year list dynamic cranes. So, actually, we just use the index function to extract the actual year from the dynamic range. Ah, you Azaz we have We would have the extra year, and then we will build a macro to actually take this actual year and put it here on the pivot table filter. Okay, this is how we can control. Ah, the pivot table and pivot chart using the combo box. Okay, so this is the basic idea behind Ah, that controlling behind controlling a pivot chart using a combo box. So let's build the macro. Okay, guys. So now we're going to see how we can create the macro that we're going to attach to the combo box in order to ah, change the filter on on this pivot table, the year filter. So what we need to do, first of all, to record the macro, we're going to go to our developer Tab on the ribbon and then click on record, Macro. And then I'm gonna name my macro here, Change underscore year. Then I'm gonna click, OK, in order to start recording. And now what we need to do is to just do a year change here on the pivot table filters. I'm gonna change the pivot table filter to be 2013 for example, and then click. OK, And then I'm going to stop recording my macro by going to the developer tub on the ribbon and then clicking on Stop recording that. I'm gonna go to my macro to edit it. Eso I'm going to go to the macro owes button here on the developer tab on the ribbon, click on it and then just gonna highlight my macro that I've recorded and click on it it. By the way, there is another macro here, which is the change the year macro that is actually attached already to the combo box. The one that I have ah illustrated the functionality of the combo box with. So I'm actually creating a duplicate off the same macro here. I'm gonna how I like my change year macro and then click on it it. And now, as you can see here on the code, the first line, what it does is that it clears the filter on the year here on the the year filter on the pivot table. So it clears the year filter and the second line on the macro. What it does is it puts the year 2013 into that filter. So we need to make two changes. First of all, on the two lines. It refers to the active sheet, but we can't refer to the active sheet because the macro is gonna be run on from this sheet here the combo box dashboard sheet, which is a different cheat than the one containing the pivot table, which is a combo box raw data sheet. So what we need to do is to refer to the combo box Rhodey to sheet instead of the active sheet because of referred to the active sheet. Then we're referring to the combo box dashboard cheap. So I'm just gonna right here instead of active sheet, I'm gonna write worksheets combo box row data combo box raw data, and it doesn't it doesn't matter. The case off the letters, whether they are upper case. Lower case. This won't make a difference on day. I mean that on the combo box Rotator the the name of the sheet. It doesn't matter if it's written and Olin cops or only in small letters. I'm also gonna replace the active sheet on the second line here with worksheets combo box raw data on the first part of the second line and the second thing that we need to do so after referring to the worksheets combo box rotate out. The second thing we need to do here is to change the year 2013 to be, ah, this cell in the to be the cell and seven So the value in cell and seven in combo box raw data sheet because we need the value of the combo box, which is represented by cell, and seven okay, to be put in this ah filter on the year filter here on the pivot table. So what we need to do is to write worksheets, combo box, raw data dot range and seven, because now we're referring to that cell and seven dot value, and it's very important to write an adult value port here. And I'm just gonna save my macro by clicking on control on s on my keyboard. I'm gonna close the vb vb a editor, Okay. And then I'm gonna go to my combo box dashboard sheet, and I'm going to right click on my combo box because I need to assign the macro that I've just recorded to the combo box. So as the macro to be launched or to be fired whenever I changed the value on the combo box , I'm gonna click on a sign macro and then click on change Underscore year and then click. OK, so right now what? What we need to do is to try our macro. So I'm just gonna put here the year 2011 And you can see here, guys that the chart is interacting with the change on the combo box The change of year on the combo box. Okay. And you can see here guys as well that I've got a cell here that actually it writes sales and then a dash, and then the, uh the year that I've just chosen and how to do this effect is is using an equation and concoct in ating using the n percent. So I'm just concoct in ating the word sales and then space and then a dash and then space here this string between the two double quotes and ken cutting concoct in a ting it with the cell K five in combo box ready to hear. So with the cell containing the filter and then I've assigned this Ah, the stacks box here on the chart or this chart title I've assigned it to this cell. Normally, we would hide this cell here having sales 2013 bus just I've kept it visible for illustration. So I've assigned that by by going Cuba clicking on this chart title and then clicking here inside the for middle bore and, you know, it would normally wouldn't have it Wouldn't have anything on the formula bar. I clicked on equal and then just clicked that sell, impressed, enter. So now you can see here that the title of the charters interacting with my selection on the combo box. So this is guys how you can create, um, combo boxes that can control perfect charts. So thank you guys for watching this video and please follow me onto the next video 4. Dynamic date filters Part 1: Hey, guys, welcome back. So this video is the first of two parts for dynamic date filters. So in this video, we're going to see a technique for filtering data tables using slicers. So, as you can see here, I'm using a slicer and selecting a certain month. And then my data, as you can see here, is reacting to my selection. This data table here is reacting to my selection, and also we could use our timeline. So actually, as you can see here, I'm selecting between different quarters. And also the data table is reacting and can also select from different years. But actually this status, it contains only one year. That is 2014. But if it had been the case that my data set contained several years, then I would have been able to select from different years. But you can see as well I can select from different month, and I can actually select a period of time spanning between any two months as well using the timeline. So this is actually our final product, and we're gonna look at what is behind all that. What's in the back stage that's going to enable us to do this dynamic selection. Okay, so I've gone here to my row data worksheet, which contains actually the road data behind this data table. So I got here some Road eight is so Espersen the country order 80 order, date order, amount, units sold. And the month The month is actually a column that I have created, which basically tags each state with the first day of the month so that I'll be able to filter between different months. And I'm using actually the date function to do that. So the date function is a function in excel that can generate a date when you give it the year and the month and a day. And I'm actually excusing the year function to extract the year from the date. Okay, So in this case, in case of this state, which is the 18th of April, 2014 is gonna extract 2014 and for the month, it's gonna extract four. And actually, I'm putting the day to be a one for all the cells because I'm just trying to tag the month with the first day of the month, and I'm actually making it. Look, this way how making it look like April 14 this way doesn't look like the first of April 14 . How I made it look like April 14 is by using custom number formatting. So if you right click on your cell, click on form, it sells here and then go to custom. You'll see have a custom number for murder, is applied and discussed. A number format is Mm mm. Dash Y y. And this will give you this. Look for the date. There are other for, missus. Well, so dee dee Dash Mm mm will actually give you the day and the month, which is not actually what we're requiring. So these air different number formance that you can apply for dates, But I'm using this one so that I would have the month having this format and remember, guys is just a form it it does not change the value in the cell. It just changes how the cell looks. And I'm doing this because this format will actually be reflected on my slicers. Okay, so my slicers are gonna be connected to this format, so the slicers actually are gonna look the way these cells look. So I've actually opted for that format so that the slicers would have January 14 February 14 March 14 written on them instead of the first of January 2014. That's not very representative, right? Because we're trying to filter for the month, not for a certain day. Okay, so after creating this data table and creating this Month column, what I did is that I created a pivot table by going to the insert tab on the ribbon. After having any cell in the table highlighted, you go to the insert tab on the ribbon and then click on pivot table here, and then you insert a new pivot table I have inserted the PIB table, shows its location on the pivot sheet here, so you can choose any location and then click OK, and you pivot table is going to be inserted. And here is my pivot table here. Okay, so this is the pivot table that I inserted on this sheet. So after doing that, I actually put the month in the filter here, and I put the country on the rose and the total sales on the summation of values to create this Look for the pivot table and then to insert a slicer, you can highlight any cell in your pivot table and go to the insert tab on the ribbon here and then click on slicer. And then are you need to insert a slicer for the month. I need to make the month the filter for my pivot table. So I selected here month and then click. OK, As you can see here, here is the slicer for the months, and you can actually use this slicer to filter your pivot table, as you can see here. So I'm just gonna delete this slicer cause I've got one already here on this worksheet and to create a timeline, you can just tie light any cell in your table and then go to the insert tab on the ribbon click on timeline. And here I'm going to choose the month column because the Month column is the one that I have selected here in the filter. If you want to choose the order date, you will need to create another pivot table and actually make the order date the filter column here. So I'm gonna click OK here. After selecting the month and Aziz, you can see here. We can actually select between different months, and this will be reflected on our pivot table. And also we can select between different quarters in 2014. But in case of the days as well, the days are actually limited to just the first day of the month because the month column actually contains the value of the first day of the month on Lee. OK, but if you select the order day to be the column for your timeline than in this case, you'll be able to select any day of the month depending, of course, on the availability of the data. So if there are no data in a certain day than your pivot table will just displaying no results. So actually, if you want to use the order date, you will just need to create another pivot table. So if you go back to the road data worksheet here, go to the insert Tom here, click on pivot table, and then I'm just gonna put it in the pivot worksheet as well. Here click OK, and you just need to put the order dates to be the column here in the filter and then country and order amount as well the submission of the order amount. And then you can go to the insert tab. Click on Timeline. By the way, also, in other versions of Excel, it could be the anal. I stopped to create a slicer or timelines or year analyzed Insert slicer. Insert timeline. So you're gonna actually select order date from here? Click OK, and as you can see here now, if used the order date, then you're gonna have all the options available, like quarters and days. A month here is actually going for 2000 and 15 but 2015 does not have any data on then. If we select 2014 then we have our data being reflected here for the month SA's Well, you can select one month. You can select a period of time spanning between different months. You can select a period of time spent spending between different days as well on a certain month if you want to using the timeline. So I'm just gonna delete that. And by the way, what I did to move the timeline or the slicer is very simple. Selected press control on X on your keyboard and move it here. Press control and V to paste it. Okay, so cut and paste. Very simple. So I'm just going to lead that one here. And I'm gonna delete this pivot table as well because I'm basing the filtration on the month columnist on the order date column. Okay, So selected. I like to clear all to remove it. No, I'm going to go back to Slicer on timeline here, and I want to show you something else as well as you can see here. When I'm filtering the data using my slicers in a select a certain month, you can see her that this table is shrinking and expanding. Based on the data that I've got so you can see her The size of the table could change, but actually, the formatting of the table does not change. And he always has the grand total column at the bottom with a certain format and the countries in the middle with a different format and the titles for the table are with a different format as well. So how did we achieve that? We achieve that actually, through conditional formatting and also through formulas in this stable. So this table is reading, actually directly from the pivot table. So the four minute here is actually very simple, he says. If the cell in the pivot worksheet is equal to Blank, then give me a blank else. Give me the value off the cell. So it's basically an equal formula with an if statement, so that if the cell is blanket will just display a blank value. And after doing that, there is actually conditional formatting that gives a formatting for the cell based on its value. So if the cell is one of the country's, it gives it this gray color. If it's the grand Total Cell gives it this blue filling with a white bolted fund. And actually the title cell is is fixed in its place. Okay, so the title so does not have any formula. And it just has this color here. So the title cell does not have any conditional formatting. Okay, so let's check the conditional formatting applied so I can go to the conditional formatting option here on my home tab on the ribbon click on manage rules. And as you can see here in the current selection or you can also choose this worksheet to Ciel, the conditional formatting in the worksheet and let's start first by the conditional formatting of the country's If I Just Highlighted here this conditional formatting that is gray click on it a Truell. You can see here that the formula that the conditional formatting is based on is that the value in the cell does not equal two grand total okay, and also the value in the cell In column C does not equal to blank and notice here as well that I have fixed the column reference for column seen on the conditional formatting. So this is the conditional formatting that is applied for the cells having the countries and for the cells having the grand total or it's actually one sell it actually just tests. If the cell in column C has a grand total, then give me a black color and both conditional formatting Z are applied to the cells in column C and column D, and you can see here that I've applied it till row 23 on each column so that I would allow for expansion and contraction off the table and also the form it is year actually extended even beyond the table range to allow for expansion and contraction off the table. I would recommend that you open this sample file and check it thoroughly. Check the formulas and everything. Okay, so this concludes part one on how to create a dynamic date filter. Thank you very much, guys. Watching this video, and I'll see you on the next one. 5. Dynamic Date Filters Part 2: Hagos, welcome back. So in this video, we're going to discuss in of the technique for filtering dates using combo boxes or using data validation lists. And we're basically gonna have some date ranges so you can select a starting month and you can select unending month. And the data in your table is going to react to your selection. So you're gonna have the data appearing for the sales between these two months. The March 14 for example, on October 14 here. This is one way to do it. Using combo box is the other way, which performs the same function as well. You can do it using data validation lists. So we're going to see how both ways are constructed. And I want to grab your attention as well to the fact that the sales here are sorted in descending order. Okay, so here, because Canada has the highest sales, it's at the top. And then us a having second place and so on and so forth. And this is the case on both tables as well. So we're going to discuss this ah, date range dashboard, and we're going to see what is behind all that. That is feeding these tables. OK, so we're going to go to our data validation dates worksheet here, and we're going to discuss actually the list of months. The are feeding the combo boxes and the data validation lists. So actually, this list of months I have two ways for creating it. The first way is to create a pivot table, OK, And this pivot tables sources actually the road data and have created a month column here and this month column is created using the date function, just as they explained on the previous video. And basically, I've created pivot table from that road. It is sat and just put the month on the rose here. And this will create this pivot table that you see here and to basically guarantee that the months are sorted in ascending order. You just click here and click on sort oldest to newest. And every time this pivot table is refreshed, the months are always gonna be sorted in ascending order and then you can have a dynamic range on that table covering that table, and this dynamic range will cover the list and then you assign that dynamic range to the combo boxes or the date of elevation lists, and we've learned how to do that Previously, you could also do it in another way, which is to basically write the list of months in a column here, which is cold months. And you just write the list of months by just writing, for example, January 14 year and just drag this handle like that. And Excel will understand that you're trying to do a no to fill for months, and it will just fill the months on. Then you create a four minute here that checks if there are dates between this state, which is here the first of January 2014. But because of custom number formatting, it looks like January forcing like that. But the values the first of January. And, um, this formula actually checks if there are dates between the this state and the end of month for this state. Israel. The end of month for the States. The under month function actually returns the last day of the month, so it's going to check if there dates between the 1st January 14 and the 31st January 14 here in this row, data on the order date column, actually, and if there are dates than this, sell here is gonna be returned. So basically, it's going to check if there are dates between first junior 14 and 31st of January. And if there are dates that it's going to return this cell, If not, then it is just going to give you a blank cell. This is account ifs formula. We've learned how to use the Countess formula previously. So I'd recommend you check this formula on the simple workbook here to be able to understand it and implemented as well. And this is done for each and every month. And if they're month exists, or if there are dates, that or between the first of the month and the end of the month, then the months then the month is gonna be displayed here. If not, then it's gonna be blank. As you can see here, the formula is extended, but only if there are dates than the month will appear. If not, then the month will just not appear. It will be just a blank. I personally prefer this method because it's easier to implement because you just create a pivot table and make sure you sort it. But the drawback to this method here, the pivot table method for creating the months list is that you need to make sure to refresh your pivot tables whenever you update your data. OK, so to hit this refresh button here refers All. I've created this shortcut on my Excel, but you can go to data top and click on Refresh old here. But remembering to refresh my pivot tables is not a problem for me, because I always refresh them. It's just second nature to me whenever I update my road eight. I just refresh my pivot tables so it's not a problem for me, but it could pose a problem for other people. This one is harder to implement, however, it actually updates the months lists without needing to refresh the pivot tables. The months will be just updated instantaneously when you add some new months to your rotator, but it requires writing a formula, so you're the judge. Okay, now let's go to our analysis formula stab, and this worksheet contains some analysis form. It is that we would use to control the sorting on this stable and also for the combo boxes because we know that combo boxes would need some cells that actually, they would output the index number for the selection in right, because there is a combo box index number. So, actually, this is the combo box index number four, the date from combo box. And this is the combo box index number for the date to combo box. And after the combo box outputs the index number here for the selection in this cell, there is actually an index formula that extracts the month using the index number, the combo box index number. It extracts the month from the months list. But here, actually, we extract the month from the months list. But we actually used the end of months formula to get the last day of the month, and I'm going to tell you why just in a second, Okay, so we use the under month function to extract the last day because this is actually the ending date that you need your filtration to extend to. Okay, so we need your filtration to extend between the first of March 14 and 31st of October 14 and here we've actually got some some If formula that axity sums up the sales between this date and this date, which this state is actually the 31st of October. So if you check here on the home time on the ribbon, their states, if we just change its for it, it's going to show us 31st of October. This state is actually the 31st of October 2014. So this summer's form it out actually sums up the sales between these two dates and have learned about the summits formative previously. So please make sure to go back to that lesson if you struggle with that formula and this is done for all the countries, and then the grand old is simply the summation off the sales for all the countries. The same thing is done for data validation lists. OK, so we're basically doing a some ish formula and then a some function here for the grand total. To sum up. All the sales were older countries were also using a rank for media the ranked out e que formula which is equivalent to the rank function in Excel 2007. So this rank function basically assigns a rank for each country in a descending order. So actually, the country with the high sales will have the highest rank and then the lowest sales is gonna have the lowest rank. So here, actually Canada is having the highest sales was having ranked number two because actually, this formula is applied to the grand total as well. And the grand total is having ranked number one. Because, of course, it's gonna be higher than any country, right, because the summation off sales for all the countries. So actually the highest country is gonna have number two and then the second highest three and so on and so forth. And then there is actually a sorting table. And this is the stable actually feeding our tables here on the day train stash board. So our tables air basically having an informant s. So if a cell contains a value, then give me the value. If not, then give me a blank. And then there is a formula here that says that if the value is blank and give me a blank else, give me the value of the cell and this is applied for both e stables. This stable is copying its values from this stable and this stable is taking its values from this stable, basically. And here, actually, to make sure that we get the correct rank for the countries we have actually written a view look of formula with and, ah, if our if there is an error than give me a blank value, basically. And here you can see her that the look of value is basically the rose function. And we've learned about the roads function before, and we're generating the number to basically to look up the highest country because we're gonna use this stable for the look up, okay? And we're gonna look up using the rank. So we're actually going to look up the country's name using its rang. And here the Rose, for example, Rose F A to F nine is gonna open it to OK, so too is gonna be our look of value in this stable. And we're gonna get actually the second column, which contains the country. So here this look of formula is going to generate our country's list, and then our grand total is written actually manually here, and there is also the total sales is generated as well using you could either do it using the rank, or you could do it actually using the country. So here, this one is looking up the country, and this one is looking at the sales using the country from this stable as well. Okay, so be sure to check these formulas, and that's it. Basically, this is how you generate the sorting table, and then this dashboard is basically getting its value from the sorting tables. And this is how you make sure that the country's sales are actually sorted in descending order. Okay, So this technique here for the ranking table and the sorting table actually has a drawback , which is, if you add a new country in your row data, you need to make sure to add it to these two tables. Okay, but what I'm going to show you is actually a checkpoint the I have created, which enables us to check for any missing countries on the fly really quickly so as to make sure that we have data integrity and that we're not missing any countries on our data. I'm gonna actually introduce two ways that you're able to do it, and you could actually figure out your own way as well. So here I've written a V look of formula, and this formula looks up the country from the pivot table in this table here and returns it. But it there isn't if condition as well that says that if the cell in the pivot table is blanked and give me a blank value else look up the country from the pivot table in this table and return it basically returned the country itself and have actually put this if the value is blanked and give me a blank. So that will be able to extend the formula beyond the pivot tables as to allow for the expansion off the pivot table so that if we add more countries, pivot table is gonna expand, they're gonna be more roast. Two shows of the pivot table is gonna be longer. And this if allows for the expansion. So basically, this actually looks up the country from the pivot table in this table and returns it. And if the country is not returned and we get a hash and a value than the country does not exist, and we need to add it to the table There is also another way that you could do it here. This formula basically generates true or false values and have added some conditional formatting to as well. So if it's true, it's going to be green. If it's fools, then it's gonna be read. And I'm gonna have a demonstration here for you by adding another country. So, for example, if I take any row here, just, ah, duplicated and just add a different country here. Russia, for example. We don't have this country on our data on I refresh here my table and go back to the checkpoint. You can see here that Russia is reflected here as having a hash and a value on this column and having a false value on this column. Okay, By the way, you don't have to create both these columns. It's just here two ways of doing the same thing. OK, so here you're If you check this checkpoint whenever you update your data, you know, I'm trying to introduce the concept of having checkpoints on your reports or dashboards so that whenever you update the data here on the road data, you look at the checkpoint. You just have a quick lines of the checkpoint to make sure that you have data integrity, that everything is adding up properly and that you don't have any gaps in your numbers. Basically, that all your numbers add up to each other. Because if you're having problems with your data than the total sales on all the countries , air not gonna add up to the grand total, for example. So basically, when we see this, when we have a glance at our checkpoint and we see this, we know that Russia does not exist. So we're basically going to insert in euro here and right Russia here, for example, and right Russia here is well and just drag this formula and direct this formula as well and dragged this rank formula as well. Okay, and everything is gonna be fine. So as you can see here, the checkpoint clears again. And you know that you have accounted for all the countries on your road data. Okay, guys, So that's it for this technique. I hope that I have introduced some good ideas for you that you can implement as is, or you can add your own as well. And I hope I have simulated your minds to come up with some great looking and professional dashboards. I'd recommend that you would check all the formulas in this sample workbook thoroughly so as to be able to understand this lesson better. Thank you very much for watching this video and I'll see on the next one. 6. Choosing Different aggregation methods: tega is welcome back. So in this video, we're going to discuss another dash boarding technique which on that technique, we're gonna use, Ah, a table and a pivot table and a slicer and the choose function. Okay, so the objective is to create different aggregation methods for the user to choose from. Okay. So as you can see her on the slicer, I can choose to get the average off the order amount. And you can see here that the average appears in cell G two or can choose to get the maximum of the order amount, and it appears here and sell G two or can choose to get the minimum, Or I can choose to get the total. So that's for your user who doesn't want to fiddle with the formulas and stuff. They just want to see average, maximum, minimum and total. Okay. And you can apply other aggravations as well so you can expand the options on this slicers or just giving you Ah, an example. Now, I've got here some raw data okay for sales on the left, and we're actually gonna work on the column for the order amounts. This is what we're aggregating as you've seen when I was selecting the different selections year on my slicer. Okay, now, to apply this technique or to ah, to do what I've done here with the slicer I had to create a table, a mini table here. As you can see, that has two columns, the aggregation and a number column that just assigns a reference number. Ah, a counter or so Ah, just a key to the aggregation type that you want to apply. So average have given it number one maximum, giving it number two minimum. I've given him number three and total. I've given a number four. OK, it's just a reference number, okay? And I have created another pivot table here at the bottom that is based on the table at the top. OK, so I just ah created a pivot table that is based on the table at the top. And by the way, if you're ah having any questions regarding tables or if you're not familiar with tables are drink a mint that you watch the lost two lessons here on the course. Ah, that explain tables. They are extra lessons bullet bonus lessons that explain the tables eso the explaining cell tables in detail. Okay, Now, after I've created this pivot table here that is based on the table at the top, I've removed the grand total row from that pivot table. Okay, so I've done that by go by selecting my pivot table, going to the design time on the ribbon and then going to grand totals, clicking on F on off for rows and columns. Okay, so I've removed the grand total row, okay? And I have created a shoes function here in cell G two. Okay, So this choose function is on its first argument the index number argument or the index number input. It's referencing cell K 13 here. That's got, um, estimation off the number, because, ah, the discuss the submission of the number, or actually the number Because the number here has only, um, every record has only ah, every number has only one record here on the table, at the top, on the mini table at the top. So the summation is gonna be the same as the number itself. So the first argument here is referencing this cell here containing ah, the summation. Okay, are actually the reference number on the pivot table. And then the choices that I've put are the average of the order amount. This is the first choice on A Put it as the first choice because the average here is having the number one A referent, the reference number one here on this table at the top. Okay, And then I've got the maximum as having the being the second option. Because it's got number two here, and I've got minimum as having as being the third option, because it's got number three and some as being the fourth option. Because total is number four here for here on the table at the top. OK, and what happens is that when I choose the type of aggregation that I want to apply self a choose average. Okay, this gets put here in the pivot table, okay? And I get here the reference number off the aggregation type that I want to apply on. Then the choose function reads that reference number. Okay, So que 13 is going to be one, and so the chills function is going to choose to apply. The first option here on the first option is the average. This is the aggregation type that's going to be applied. Okay, on if I choose maximum, I will get number two here, okay? And the second aggregation type will be applied, which is maximum. Okay, so got the maximum order amounts put put here and sell G two. And this is gonna be the same story with minimum and with some. Okay, so basically, the truce function will refer Ah, to this or reference, this cell here will get its choice Ah, number or the reference number of his choice from this cell here from self k 13. And then it will apply that aggregation function. Ah, Teoh. And get that the result And actually the aggregation functions are being applied to the column for the order amounts here on the table. And this is a structured reference for the table. And if you're having any problem or you don't understand what a structured references I'd recommend you watch the lost two lessons. Ah, on the course which are bonus lessons about tables about excel tables, you'll be able to understand what a structured references, okay. And I have no problem understanding that. Okay? So, basically, whenever you choose what you choose here on the slicer. The aggregation gets applied here in cell G two, and then we've got some sort of an interactive text here. So actually, I'm aggregating. What's Unseld J 13 here. Okay. With the word order amount. But put a space beside before it so that there would be a space between the aggregation type and the word. The two words order amount. Okay, this is just, um, the text labour leader economic texts label that I've created to just ah, have something that that would change dynamically as I change my choice Year is a choose something different on the slicer. Okay, so Ah, this is how the technique works, guys. Um, so someone would ask, What if I don't choose here? A Ah, I make two choices on the slicer. Actually, Excel will just ah, do the first choice. Or actually, you will got the result off the first choice because we're referring to the first cell on the pivot table here. The first row under the sum of number. Um, call him, and then he just changed the ah, the header of that column to make a reference number something. Okay, of course, Normally you be hiding this, um, pivot table from the user so the user wouldn't see that you'd be putting It may be in another sheet. And you be hiding that many table as well. Okay. And, uh, that's it. You could maybe, um, have like, um, a cell here that would say, um, that would have a formula. Okay, that would count These cells here, give you the count of these cells. And remember, guys that comes function, it counts. Only the cells having numbers. Okay. So, um, you could maybe put an EF four minute here if count of this is greater than one, then, um, please choose one aggregation type. Okay, I'm gonna show you why I have done that. Okay, Else, give me a blank cell. It's on. I'm doing that so that if the user chooses to aggregation types, you had got this cell here saying, Please choose one aggregation type, and you could maybe give this warning, sell some conditional formatting, so maybe you can go to conditional formatting highlights. So rules and then text that contains, please. Shoes. One aggregation type. Okay. Now, so that if ah, the user makes more than one choice, this cell here would have a red color and some sort of a warning that says, please choose one aggregation type, and then the user would, ah, just choose one aggregation type on. This warning would disappear. Okay, so you can do that. Ah, so that you'd give the user some sort of warning if they choose to arm or aggregation types . Zero shows in three works was three. And it works with, you know, four aggregation types. If you want to choose for it works with four aggregation types as well, and it selected a same time. Eso the user will get a warning. You know, when they select more than one aggregation type, but it's just the select. Just one. Um, the the warning disappears here. Okay, So I hope that was informative for you guys. Thank you very much for watching this video and please follow me on to the next video. 7. Capturing Slicer Selection in Formulas: Hey, guys, welcome back. So in this video, we're going to discuss another a technique that involves putting our slices selection into formula. So as you can see, I've got a slicer. And as I'm changing my selection to date on the chart changes this chart actually gets its data from this table here, and this slicer is actually linked to this pivot table. This pivot table is a very simple pivot table that has the model field put in the row section here. And this pivot table is based on the actual sales road eater and the actual sales raw data is one of two row data sets that we've got would get the actual sales and the forecast sales. So this stable axity just references the model name from our slicer selection. So we've got a summits formula that calculates the sales, the actual and the forecast from the respective data sets based on our slices selection and that particular month also, we have actually put this value here in cell p five, which is our slicer selection. We've made it a name drain so created a very simple name, drains a scold slices election. We've just made it equal to that cell just to be able to refer to that cell as slicer selection. It's not necessary, but it just makes things easier. As you can see here, this is a chart title text. This cell here contains the chart title text, and this cell has a formula that tests if the cell under our slicer selection cell is empty or not. So if is blank, so test if. Is this blank or not? Because if it is not blank than we've selected multiple models, so he's sincere. If I select multiple models, it's not gonna be blank. So two or more is gonna make cell P six not blank. And this is something that we don't want to do because we don't want the user to select multiple models because then the summers formulas were not work properly because they can only reference one model. So this is why, in case the user selects multiple models, he will get a warning messages. This please choose one model on Lee. So this is actually a test that we do in order to make sure that the user selects one model on Lee and this title boxier just references this cell so as to make the title dynamic. And basically the title says the slice of selection actual versus focus sales. So that particular model that we selected actual versus forecast sales. So that's it. This is a very simple technique to involve your slicer selection into a formula. So thank you very much, guys, for watching this video and I'll see on the next one. 8. SkillShare Outro Done: Thank you for watching this class. I hope it was informative for you. I really appreciate it. If you could leave a review by clicking on the review, stop at the bottom of the screen. And please make sure to follow me on skill share so that you'd be updated about all my new classes. Thanks for watching. I'll see on the next one.