Learn how to create Excel Dashboards - Part 3 - Going Interactive ! | Abdelrahman Abdou | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Learn how to create Excel Dashboards - Part 3 - Going Interactive !

teacher avatar Abdelrahman Abdou, Experienced Excel & Power BI Trainer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

17 Lessons (1h 24m)
    • 1. Part 3 Introduction

    • 2. Please Follow me

    • 3. Form Controls Introduction

    • 4. Buttons

    • 5. The Checkbox

    • 6. The Listbox

    • 7. The Combobox

    • 8. The Scrollbar Control

    • 9. Scrollbar Application- The Scrolling list

    • 10. Scrollbar Application - The Scrolling Chart

    • 11. Spin Button

    • 12. 1 Option Buttons How to Insert

    • 13. 2 Option Buttons (Part 2) Formatting

    • 14. Option Buttons (part 3) Group boxes

    • 15. Slicers

    • 16. Pivot Charts

    • 17. Thanks for Watching

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Link for the other 3 classes in the series:

This class is the Third class in a series of classes on how to create Interactive, Professional looking dashboards using Microsoft Excel.

This Third class shows you how to use form controls to add interactivity to your Excel dashboards.

Form controls are an important element for any interactive dashboard!

If you haven't enrolled in the previous classes you can always enroll using the below link:

Class 1

Class 2

Class 4

Class 5 

Meet Your Teacher

Teacher Profile Image

Abdelrahman Abdou

Experienced Excel & Power BI Trainer


Abdelrahman Abdou is a Microsoft Certified Trainer (MCT) and Analytics Consultant with over 8 years of Experience in Data Analytics, Reporting, and Business Intelligence in the largest Telecom companies in Egypt & Canada.

Abdelrahman is passionate about learning & teaching Excel & Power BI and loves simplifying information for his students and audience.

Abdelrahman loves watching football, playing video games, traveling, and going to the gym.


See full profile

Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Part 3 Introduction: hello and welcome to the second class in this Siris of classes for creating Excel dashboards in this class, we're going to go through the elements that we, ah, need to create interactivity into our dashboards. Thermostats just form controls and slicers and pivot charts. And if you haven't joined the first class, if you haven't enrolled into the first cost, please Ah, and roll into the first class using the link below. And with that being said, let's start our second class. 2. Please Follow me: 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. Form Controls Introduction: Hey, guys, welcome back. So in this video, we're going to speak about form controls. So form controls are objects that are available in excel that enable us to change some parameters or some values. And based on these changes that are done with form controls, other values would change as well. Like you would have this form control linked to a cell, and it would help it. A number in that cell and then a formula would be linked to that numbers whenever that number changes. That formula changes. And this room that could be opening some values to a charge to this enables you to control the values in charts, as we will see later on in the course. So let's see. The most common form controls are available in Excel These air not all the form controls, but they're actually the most commonly used ones. So we got our button with our combo box, the spin button, the scroll bar, the shock box, the option button and the list box. And we're going to discuss all of these in detail in the upcoming videos. So to get the form controls, you need to have the developer top on the ribbon in your excel enabled. And to enable the developer top. You can simply right click here on the ribbon anywhere in the ribbon and then click on customize the ribbon here and then under the customize ribbon top here under the main tabs , you'll have the developer top here. Just check it and click. OK, Ok, so this was a quick introduction to from Controls Guys on the upcoming videos were going to discuss them in detail. So thank you very much watching this video and I will see on the next one. 4. Buttons: Hey guys, welcome back. So in this video, we're going to discuss the first type of form controls, which is the button control. OK, so buttons air used to trigger Mac Rose. Okay. And a macro is basically a program in Excel on a program is basically a set of steps that you ask the computer to perform or ask Excel to perform. Okay. And ah, macro Zehr written in a language called VB A and that stands for visual basic for applications on. We're not going to go into the details of vb A on this course because it needs a course old by its own. However, I'd really encourage you to learn Vehbi, eh? As it would give you a very good skills. Okay, so I've got a button here on the left, and it's already connected to a macro. And all that macro does is that it writes used the button form control in cell F six as we can see her of deleted the contents of cell F six and I'm just gonna press the button here . And you can see her that it rights to use the button form control in cell F six. Okay, So let's see how we can insert a button in excel. Okay, so you need to go to the developer tab on the ribbon, click on insert. And then, as you can hear, this Ah, shape here represents a button. Okay, so click on it. And then you can left, click and drag to dro the button wherever you want. Okay? And then excel when you drove the button. Excel will start asking you, Ah, which macro you would like to assign to that button so that when that button is pressed or clicked, the macro would get triggered. Okay, so I'm gonna choose here the button macro. Okay. And I'm going to click. OK? And as you can see her, my button has been drone. You consider it's been the same that anymore, but nine. But I can get it's name by right clicking on it and clicking on it It text. Okay. And then I can start editing. It's think OK, so I can call it Molly Button or so Okay. And it's now connected to the same macro as the button on the left. I'm just gonna delete here the contents of cell F six, and if I click on the button here. You can see her. The in cell F six, it's written used the button form control. You can also assign Micro's two shapes of Got here a shape, actually. Ah, that is used as the button. Okay to trigger macro. So there is a market already assigned to it. So if I click on it, you can see here that there is a macro this written used the shape macro. By the way, both these macros have been written by myself. So ah, to insert a shape you can go to insert and then go to shapes, and then you can choose any shape here. But I'm gonna choose the rounded Richt rectangle here, and you can just left, click and drag to draw the shape. Okay? And ah, you condone Just change its name. I'm going to change it to my shape as well. Okay. And to assign a macro to a shape you can right click on it here and click on a sign macro, and you can assign any macro to it. I'm gonna assign the macro called shape macro, which is the same macro for the shape on the left here, So I'm just going to leave the contents of cell F 11 year. And if I click on money shape here on the right, you can see her that the macro has been triggered, and, ah, the contents of cell F 11 hour used the shape macro. Okay, so let's check the code for the Mac Rose that are triggered by the shape and the button. Okay, So to do that, I'm gonna go to the developer tab on the ribbon here earlier and click on Micro's and then on this button macro here, I'm going to highlight it and then click on edit. Okay, so now I can see the button macron, actually, just below it. There is the shape macro. Ah, they're just Ah, very simple Micro. Only do is that they write, um, used the shape, use the button form control in cell F six, and you also insulated 11. The right its rights used the shape Macro this for the shape macro. Okay, so they're very simple. Micro's just one line that writes a text in a cell. Okay, so now we're gonna close the V b A editor, and I'm gonna also show you how you can record a micro so you can go to the developer tab on the ribbon Click on record. Macro. Okay, I'm gonna call it Hello. Um, macro here, cause this Ah, it's gonna just right. Hello? Okay. So if if you just write the name of the macro and click, OK, Excel will start recording. So I'm gonna go to sell F 15 year and right. Hello? Okay, I am Press enter. And now I want to stop recording so I can stop recording by clicking on stop recording here under the developer top on the ribbon. So if you click, stop recording here. Excel will stop recording. And then you can insert a button by going to a developer. Time on the ribbon, clicking on insert here and then clicking on button. And then you can draw another button here you can assign to it the new macro. Hello, Macro. Okay. And I'm just going to change the name of the button here to hello, for example. Okay, So if changed its ah name and then I'm just going to lead the contents of cell F 15 and click on the button. You can see her That Ah, the word hello has been written in cell F 50. Okay, so one more thing about using buttons and versus using shapes is that actually, shapes have an advantage that they have mawr formatting that they that can be done to them . Okay, so with shapes, you can do a lot more formatting. So if you select a shape here, you can go to format and you can just give it ah, lot of colors, different colors year. And you know, you can give it even many more. And you can also ah, do some effects to it so you can give it a bevel here, okay, to give it some sort of a three dimensional effect. And you can also make it glow and stuff like that. Okay, so this is ah, very distinct advantage of shapes over. Ah, buttons. They can both sugar macron's. But this is ah, an advantage for shapes over buttons. Because buttons cannot change their colors. Their ah aesthetics are basically limited. Okay, so thanks. Guys were watching this video and l c on the next one 5. The Checkbox: Okay, guys. So now we're going to speak about the check box form control. And as you can see her as I take an antic the check box here on the left, the orange Siris for average, it appears and disappears, as you can see here. So, actually, the orange Siris for the average sales for 2014 it is linked to this check box. Is Aziz You can see on also in this cell here if you check it. When? When this check boxes checked the cell up. It's a true. And when this check boxes unchecked, the cell opus a false. Okay, so this cell as well is linked to the check box, and this is actually how you create the interactivity. Okay, guys, let's see how you can insert a check box are going to go to the developer top on the ribbon here and click on insert. Okay. And then you're gonna select the check box. Here is the check box shape here. You're gonna click on it and then left. Click and drag to dro your check box. Okay. So you can see here. It's been inserted with a default name. You can Ah change its name if you want. Okay, so I'm gonna call it my check box, for example. Okay. So Ah. Now, in order to create the link with the cell, you need to configure the check box. Okay, So going to right? Click on it and click on formal control. Okay. And as you can see here, this is the window for formatting. Your check box. Um, you can give it a three D shape boy. Just checking this three d shading option. Okay, this is just a aesthetic option. And now you can actually create the cell link through this box here so you can just click on this blue button and then select a cell, and then this cell will be linked to the chuck box and outputs a value of either true or false, depending on whether the check boxes checked or not. Okay, so if you check it, this cell will help it. It true, if you on check it, the cell would output a false Okay, So using this information and the fact that it alpa's a true or false in a cell, you can link a formula. Okay, That would actually be willing to that cell and depend on the value of the cells of the cell value is true. The formula would give us a certain result. And if it is false, then the former they would give us another result. So if you can see here and the cells here representing the average, you can see here that the formula in old the cells is basically the same. And you can see her that it says if the value of cell K two is equal to true, then give me the average. And if if otherwise, OK, and other ways could be full, sir, could be any other value. Okay, then give me a hash and a okay. It's gonna give us a hash and air. This is the hash and air. This is produced through the n A function. Okay, there is a function in excel that is called an A that you could use to produce the harsh and a her Okay. And this hash and a error is actually what results in the line disappearing. Okay, the line for the average sales. Okay, so if you check that here, you consider that when the line disappears this is because there is a hash and a we cannot use zero, for example, because if we make we make the formula produces zero in case of fools instead of the harsh and a error this actually would not make the line disappear would just make it appear at the bottom here. Okay, so Ah, you need to use the hajj and a in order to make ah, this line serious for the average disappear. Okay, so this is how you can use the check box guys to create interactivity to your dashboards. Okay, So thank you very much for watching this video and I'll see on the next one in all of the cells is basically to say, 6. The Listbox: Hey, guys, welcome back. So in this video, we're going to discuss another form control, which is the list box. Okay, so the list box is basically a form control that allows you to put items in the list and toggle between them. Select any one of them individually. Okay. As you consumerism, selecting the different items I their sales and units sold get plotted on these two charts . Okay, so each item select, I get its sales and its units sold. Okay, so we're going to see how this works. But before doing that, we're going to see, first of all, how we can insert a list box. OK, so we're going to go to a developer tab on the ribbon click on insert on, then choose the list box here, and then we're going to click on it and then click on and drag to draw away. OK, so let's check It's formatting how to format it. I'm going to right click on it and click on format control, OK? And ah, you can see her. There is an imp a trained and there's the cell link. The import ranges basically the range of cells that contains your items. Okay, so is the range range of cells that would contain your list basically so that you could generate a list out of that range of cells. And there's also the selling, which is basically the cell that you're gonna output three orginal position of the item that you're going to select in. OK, so we're going to see that in action and for the selection type, we're only going to bother with the single selection type because the multi and extend the need vb a code to run we're not going to bother with. That is outside the scope of the course. And there is also the option for three D shading, which gives it a bit of a three D. Look, it's an aesthetic option. So I'm gonna click council here. I'm gonna bleat my list box. I'm gonna check the configuration off this list box that I've got already up and running year. And as you can see here, we've got an import train. So to check that we can see her that in the analysis sheet get the impetus train jizz cells before to be seven, and we've got also the cell link. It's actually Selby, too, As you can see here, it's It's this cell here. Be to this is the cell link. And what this selling does is basically contains the order position of the item that we select. So as you can see her, we've selected here PC's, for example, you consider that is gotta one. If we select laptops, this is the second item isn't in the list. We're gonna have it too. If was elect ablest, we're gonna have a three and so on and so forth. And this number is very useful because you can use it with an index function, for example, to get the name of the item that you selected, for example. Okay, so putting a number here is is very useful, and this can be used with lots of look of functions, and it can be very useful. Okay, So one more thing is, actually, as you can see here, when you select one of the items Onley, that item appears on the chart and actually the rest of the items disappear. So let me just explain to you the mechanism behind that. So, um, these two charts here the sales reads from this range the sale stores here is the ah items in the chart. And these air the months and their values. OK, and the units sold. It reads from this range Basically. Okay. And as you can see here, I've set up the four minute here, for example, on the sale. Start to check if, ah, if actually the number beside the item here. So you can see here that on the items have got some numbers, which is basically the ordinance position in the list. Okay. And I've set up actually the for minister, that he would check that. Ah, the number of the item is equal to the list boxes selling value. Okay. And if that's the case, then return some data from the sheet they scold, rotated. Let me just on hide that for you. The row data sheet. As you can see here, it's reading its values from this sheet. Okay, so this is basically the shed that contains the source values, and this is basically analysis. Shed the intermediate sheet, and this is basically the presentation sheet or the dashboard cheat. Okay. You remember guys when you spoke about the structure of a dashboard containing sheet for road eight. Another one for analysis and the 3rd 1 for your dashboard to output it or to show it to the customer. Okay, so this is the analysis sheet here. It's basically it checks if the item here is basically the one that you selected. And if so, it gets the value from the road data sheet, and if it isn't, it gets zero, or it actually helps zero in the cell. Okay, So because the PC's is the items selected at the moment, it's not opening a zero, but here is actually out putting a zero. But why is there not a zero? They're actually blank cells. Well, the reason for that is that I have changed the formatting of the cells. OK, so I'm going to right click, click on form. It sells to show you that. And as you can see here of change the formatting of the cells to show a blank value in case you get a zero. Okay. Well, um, number formatting is a big topic, and it probably needs a video all by itself. Okay, but, um, I'm gonna just make it shorter for you. This part here the first part represents how Excel would deal with the number if it's positive. The second part here The general also represents how Excel would deal with it if it's a negative, and 1/3 part is how it deals with it. If it's zero on the fourth, but is how it deals with it if it's a text. Okay, so I've actually put put it to deal with it if it's zero to make it to give me a blank value. And the reason I'm doing that is because a blank value will just not show any labels on the other items that are not selected. Okay, where is if you actually put it as a zero? So let me just change that to zero just to show you here. If you put it a zero, for example, you're gonna get zeros on the data labels here, as you can see for the other items that you haven't selected. Okay, so this is to allow me to have the data labels for the items that are not selected. Not so have zeros, but to be blank. OK, so I'm actually just going to switch it back here, my guy. And the same goes for actually the chart at the bottom. Okay, I'm actually planning to make another video or by myself for number formatting cause the big topic there needs to be discussed in and have a video all by itself. Okay. And the reason also I haven't used the hash and a that we used previously is for the same reason. If you use the hash and a, it's going to appear on the daily data labels and you can try it yourself. Okay, so you can just try to open a hash and a or the n A function instead of a zero, you'll see that it will appear on the data labels. OK, so this is kind of an exercise to do. And one more thing I need to tell you as well is that the list box? If I right click on it and click on form and control, it actually can have a list through a named range instead of just ah range of cells. OK, so actually, I've got a named range that I had created before. It's called list Strange. Okay, so far, right click again here. Quicken format control for right list range of the same spelling. It's actually it actually can read it correctly, and it will still have the correct items in the list. Okay, so that's perfect. Thank you guys for watching this video. If you have any questions, leave them in the Q and A section. Thank you very much for watching this video I'll see on the next. 7. The Combobox: Hey, guys, welcome back. So in this video, we're going to speak about another form control, which is the combo box. So the combo box is similar to the list box. It enables us to collect an item from a list. OK, but it's the only difference is that the list box shows all the items in the list at the same time, so you can see them at the same time. However, on the combo box, you need to actually cascade the combo box to be able to see your item. Okay, but they work pretty much the same way. They both enable us to select an item from a list. Okay. And if you right click on a combo box and click on Former Control, you can see where this got the same inputs. So we've got the import trains, which is basically your list and also the Cell Inc. Which is basically the cell that's gonna help put the ordinary position off the item. Okay. And you've got the drop down lines Here is how maney items you can see when you cascade the list. OK, so how many items you can see straight away and they're more than that number of items you'll have. Ah, scroll bar on the right here to just scroll through the items. Okay. Now, let's see how we can insert a combo box. You can go to the developer tab on the ribbon, click on insert, and then click on combo box here. And then you can just draw your combo box like that. Okay? And you can just right click, click on form and control and start for months and get there Is the three d o a shading option as well, which is an aesthetic option, as we have seen before. Okay, that's it, Guys for the kobo box. This is just a quick introduction about it, because this very same Merton list box. If you know how to work with the list box, you're gonna be able to work with the combo box and we're going to see ah applications for in the upcoming videos. Thank you very much watching. I'll see on the next one 8. The Scrollbar Control: Hey, guys, welcome back to in this video. We're going to speak about another form control, which is this crowbar. So this crowbar control allows you to create a moving image off your data. So as you can see here, as I'm scrolling through the scroll bar, I get the sales for my different items. Okay? And I'll show you how it works. But first of all, let's see how we can insert a scroll bar control so you can go to the developer tab on the ribbon, click on insert, and then click on scroll bar here. And you can either dro a horizontal one or a vertical one. Okay, so you can just draw it as a horizontal in, and it will be drawn as a horizontal one. And you can also draw as a vertical one like that. Click and drag. And it will be Joan as a vertical one. Okay, I'm just going to lead the vertical one here, and let's see the horizontal one that we've created. Let's right. Click on it, click on Former Control to see what it's controls are so you can see her. The 1st 1st of all, is the current value. You don't need to worry much about the current value because it's basically the value that it has right now. And it will change as you use it. Okay, as you scroll through this crowbar control, okay? The minimum value is basically ah, the minimum value that it would have. So, in this case here, because I've got 12 months scrolling through, it's actually the months of the year, actually, two months of 2014. So the minimum value in my case here is gonna be a one, okay? And the maximum value is gonna be 12 because they've got 12 months in the year. And the incremental change here is just a one because we need for each press on the scroll bar, forward or backwards to just change our months by one. So go one months forward or woman's backward. Okay, If you need to go forward or backward by more than that, then you can just change it to two or three or whatever. Okay. And the page changes also. How many months forward or backward, you go when you press actually on in these areas. So in this area or in this area, no on the ah, no, on the arrows on the right and left. But actually in the areas between the this block and and the arrow. Okay, so in this area or this area Okay, so we will set that to one as well, cause we want to move forward or backward by just one when we click on any of these areas because we just got 12 months, okay? And we scroll through them one by one. And the cell link is basically where you help put the ordinary position off the item that you selected. So I'm going to select your cell J two. It's actually the same cell that this school bark control is. Using the one on the left on the three d shading is just to give it a bit of a three d. Look, it's an aesthetic setting. So if we click OK, here, you can see her that I can actually scroll through forward on backward on the school control score Bar control. Okay. So I can go forward and I reach a maximum value of 12 and it can scroll backwards and I reach a minimum value of one. And also for the page change. When he actually click in the middle of the scroll bar, he conceded that it increments Bali one forwards or backwards. Okay, because we set the page change option to be a one. Now, I'm gonna delete the school bar control that I created here. Okay? And we're just ah, gonna have a look at the data that we're charting. Okay, So you can see here as I scroll through the school bark control here, the selling changes. And if we check the formula for for these items here, they're actually being looked up from a sheet called this crowbar analysis. You can see her. It's just a table with, ah, the items and the months across the columns. Okay. And, ah, I want you to see something here If you go to January. Okay. You can see here that the school Bahr el puts a one on the selling. However, you can see here that on the formative for the V, look up for all the items you consider that the column index number is basically the pulpit of this crowbar on the selling offset by a positive one. Okay, so it's the output of the school bar on the selling care plus one. The reason is it is plus one is because January here is in the second column on on the table. OK, so if we have the month of January with cell link of one, we need to add an extra plus one Okay, to get the values for January and if we check February, you can see her that it help? It's too on the cell link. OK, And if we check the school bar analysis worksheet to consider that February is in the third columns, this is why we need to offset that to hear on the selling by a positive one to get a three on the column Index number input for the V, look a formula. Okay. And you can see her also that February itself is being looked up as well from the table. So everything, including the months, is being looked up from the school bar analysis table. OK, so this is basically the school bar Ah, form Control is it's basically a form control the outputs a number, okay. And this number can be incremental it and you can link that number in a function or formula to create interactivity to change your data and create interactivity this way. Okay. Thank you very much. Guys, For watching this video, we will see applications for the school bar form control in the future. Okay. Thank you for watching. I'll see on the next one. 9. Scrollbar Application- The Scrolling list: Hey, guys, welcome back. So in this video, we're going to discuss another application for this crowbar control, which is the scrolling list says he could see here. I've got a list of names. This list contains 10 names. Okay. And as you can see here, I'm able to use a scroll bar control toe scroll through more than 10 names, actually. So I've got 100 names, actually behind the scenes, and I'm able to use this crowbar control here to scroll through the 100 names in Onley. 10 records. Okay. And as you can see here, the values of the records are changing. Basically. So let's see the road data behind that. Since you can see here, we've got the on this road data sheet here. We've got the 100 names, Okay, Got 100 records, and I've created actually an I. D column. OK, so it's calm that I've created here, and all that does is that it increments by one. Okay, just counts the names. Okay, So is get numbers between one and 100 is just counting the names. Okay, you can either create a calm formula or you can just, you know, type one here. And then when you drug that you know, excel, we'll give you the option to fill the Siri's year with with the one from between one on 100 . So it's really up to you. You can do it using any method that you would like. Okay. And, ah, let's go to actually the explanation sheet here to explain how we have done that. Okay, So to do that, you need to create the same columns as your road eight up plus the i. D column. Okay, that you created on the road data. And also, you need to have a scroll bar control inserted. Okay. And then on the i D. Column here, you need to type a one, and then you can type a formula on the second row here on the just to sell below it. OK, which is the starting number for the I d. Plus one. And then you can drag the formula to the lost row here. Okay. To the 10 throw. So you've got 10 rows or 10 records and you're gonna be able to display ah 100 names on these 10 records and the reason of done this plus one formula is that we're gonna have this crowbar control connected to the cell here, having the idea of one which is Selby to. And then when this crowbar control increments numbers these numbers as well will be incremental did with it, okay? Or these numbers will basically offset be offset by one. And then, you know, this number will be offset from this number by one and so on so forth. So when When the i d. Contains 20 for example, this one will contain 21. And then this 1 22 and then this 1 23 and so on and so forth. Okay. Okay. So after inserting our score, bar control, we would need to configure it. So I'm going to right click on it here and click on format control. And ah, first of all, the current value, you could put it as one. Or you can put it at as any value will change anyways, okay. And then the minimum value is gonna be a one, because this crowbar control is gonna be connected to Selby too. Okay, So you'd want our minimum value to be a one, because this is the minimum value and Selby to and this is actually are released. I d number. Okay, so this is why it is the one. And then the maximum by value is gonna be 91. In our case, if you've got 100 records. The reason for that is that if this is 91 than the following one is going to be 92 93 94 95 96 97 98 99 then 100. Because so because we have got a total of 100 records. Okay, we would want this to be 90 ones that the other nine cells would add up to. Ah, 100 Basically. Okay. And then the incremental change is gonna be a 10. So that's every time you press on the scroll bar buttons. This number, this idea number would be incriminated by 10. Okay. And then the page change is gonna be 10 as well. Which is when you click in the middle of this crowbar control. Okay. And our cell link is gonna be cell B two, okay? And get a three d shading his roles are gonna click. OK? And as you can see here when we use the scroll bar control the numbers on the I d get incremental because the first number gets increment id here by 10 and then the other numbers air linked by formulas so they get incremental by one, as you can see here. Okay, so the next step here is to actually look up the names from the road data worksheet. So to look up, the names were actually going to use this. I d. Number here to look up the names and actually phone numbers and country and city and street address from the road data tables were going to use a V look of formula. Okay. And we're gonna look up the i d number. Okay? And we're gonna actually absolute the column part because we need to drag the formula to the right, and then our table array is going to be this stable here. Okay? And you can also use a dynamic range and put that table and Anoma cranes if you need to allow for any expansion and our column index number. Okay, our column index number is gonna be basically in this case there for looking up the name. It's gonna be too. And then the range look up is gonna be an exact match. So free closer brackets and press enter is you can see her. We're able to look up the names and when we scroll through the names air changing. Okay, So for the phone number, it's the same for men. Except that we need to change our column in X number two or three and for the country need to change it to a four and so on and so forth. So we need to change the column. Index number. Okay, So a better trick to use rather than just changing the column index number manually for each column is to actually used the columns function. So I was We've learned on a previous video the columns function. What it does is that it counts the columns in a range. Okay, so on the name column here, you can use the columns and actually highlights columns. A to be. I'm just gonna close my brackets here, and you can actually absolute just the first part off the columns formula here. Okay. So I'm just gonna absolute the first part, which is column A and I'm gonna leave Column B two B relative. So in this case is gonna count Columns eight to be on the name column. However, on the phone number column, the countings going to be expanded to be from columns A to see because the reference on the ending column part is relative. It's not absolute, so it's not fixed. So it's expanding. And if if I expend the formula here or if a drag it to the right, you can see here that the reference for the ending column is expending. So as you can see here, this is a better way to do it, actually, rather than just manually typing the column index number as we've learned on a previous video. OK, so actually, as you can see her weaken, try our list. And it's working absolutely fine as the the example that was seen. Okay, so now we're gonna discuss as well another possibility. So what if we need to allow for the expansion off the list? Okay, so what if we need to allow the user to, like, add more than 100 names to the roadie to list? Okay, so how do we allow for that expansion? So I'm gonna go back here to my explanation sheet and I'm going to right click here on my scroll Bar Control. And let's say we put the maximum value to be 200 instead of 91 let's scroll through the names and see what's gonna happen. So as you can see here after the 100th name, we've got actually hash and a error. So the reason for that is that the I d names are actually incremental ing, and they're going past 100. And we're doing the look up using V look up and were not able to find the I. D. Number 104 for example. And this is giving us an error. So actually to kind of mitigate that and make sure we allow for the expansion as well without getting these errors, Okay, because the user's seeing the hash and a er is not gonna look good. Okay, is that we're actually gonna put an if air for the four minute here for the V look of formers who can put in if error and then we're gonna make the the cells display a blank value instead of the hash and a error. Okay, so in case of having an error, we're going to display a blank value. We're actually going to do that. As you can see here is displaying a blank value, which looks, ah, lot better than the hash and air. Okay, so when When the user is crawling through the list, Okay, I don't see any names than the list is done. This is the limit for for my name's basically and this looks much better. You can also type something else. Which is, for example, end of list A swell. You know, she can type that make it end of list or something. You know, the possibilities are basically endless here. Ah, the possibilities are infinite. Utkan apply whatever idea you would like basically OK. And one more thing off course to note is that the i d number should be hidden. Okay. When the user is crawling through the list, Actually, this is the form that's gonna appear to the user gonna hide the I d. Number guys. We're just gonna have name, full number, country, city and street address, and the user is gonna scroll through the list this way using this crowbar control. So this was an explanation and how to create a scrolling list using the school bark control . Thank you very much, guys, for watching this video and I'll see on the next one. 10. Scrollbar Application - The Scrolling Chart: Hey, guys, welcome backs in this video. We're going to discuss another technique called the scrolling chart. Okay, so as you can see era, I've got a scroll bar control, and I've got some data here below it. Okay? And as you can see here as a scroll through using the scroll bar control, the data in the table changes and the data plotted on this column chart as well changes. Okay, so let's see, while you would need to use such a techniques, I'm gonna go to a scroll bar chart analysis sheet here. And as you can see here, we've got a big data set, okay? Of months and their sales. The data actually spends between 2010 until 2000 and 14. So let's say we want to draw a column chart for that data set. I'm gonna highlight it, go to insert here, and then go to my column charts and just Joey column chart. And as you can see here, if you draw a column chart for that is gonna be a long charter is not gonna be pretty. You know, it's it's not gonna look good, cause we've got a lot of data points. So maybe if we have some sort of a method or technique to reduce the number of data points , but at the same time be able to scroll through all the data points, that would be better. Okay, maybe we can go year by year. Says to compare the sales for the same year of different months of the same year together. Like to compare the sales of the different months in 2010 together and then compare the sales of the different months of 2011 as well together, you know, because the months of a certain year would have a something in common, which is that they are in the same year. Okay, I'm just gonna delete this chart here, and I'm going to go back to the scrolling chart tub to see how we can implement that. Okay, guys. So this chart here is just charting this table, OK? And you can see her. That row number 15 is hidden, so I'm just gonna hide it here because there's something here in row number 15. Okay, so there are numbers. Okay. And you can see her that this cell here, so be 15 has a value of one, and then, ah, the cell C 15 has a value of B 15 plus one. And then you know D 15 is C 15 plus one. So each cell increments one or adds extra one to the cell before it. Okay, when you go from left to right, and if we use the scroll bar control, you can see here that actually the numbers are in commenting forwards up until 60. Okay, because we've got five years by 12 months, has 60 points. This is why the maximum number here is a 60 or the number on the very right is the 60. Okay. And let's check here in the hour. Scroll bar form control, check. How is configured? So I'm going to, right? Click and click on format control. And as you can see here, this score bar from control is linked to Selby 15 okay? And the minimum value is one, and the maximum value is 49. And the reason for that is that first, the minimum value is gonna be a one. Okay. And then as you scroll forward with the incremental change and the paid change, Okay? They both scroll forwards by 12 months is gonna be one and then adding 12 to scroll one year forwards gonna be 13 and then another 12 to scroll the second year. Poor. Would this gonna be 25? Okay. And then 37 then 49. So this is why the maximum value is 49 cases. This is by scrolling forward by an extra four years. Okay. And so the incremental changes a 12 year, which is when you press on the buttons for the school bar control and the page changes Well , when you press in the middle, okay, is also a 12. Okay, also, guys, if we check the form, it is here in rose 16 and 17. We can see here that we've got a V look up formula is looking up the months and the sales from the score bar chart analysis sheet here. OK? And we've offset the value of the column index number that we're getting actually from bro 15. Ok, so actually, the V lookups are getting there. Column index number values from row 15 but it's offset. They plus one okay. To make sure that we get the correct values from the school bar chart analysis. Okay, because there's an extra column here. That is the month and the sales. Okay, so this is why we ops offset the column in X number by a plus one cause, for example, January 11 is in the 13th Column, but we need to start the look of from this column. This is why we add a plus one to account for that, to be able to get the value for January 11 that month correctly, and also to be able to get the this value for its sales correctly as well. Okay, okay, guys said this is basically the scrolling charts technique, guys. It's a very easy technique. It's similar to the techniques that was seen with the scroll Bar control previously, OK, and this just got a schooling chart days, incremental numbers, and these numbers are connected to the look of form. It is so that we're able to look up the correct values. Okay, and we make sure to offset, for example, the column index number by one if we need to, so that we can get the correct values, OK, and basically this chart is just charting. This stable is starting a static table OK? Or is charting the table aesthetically. But the values in the table are just dynamic. Okay, So, like Ah, the table is static. The table is self aesthetic, but the values are dynamic and they change as you're using this cool bar control. OK, so sick. Basically. Guys, for the schooling chart technique. Thank you very much for watching this video. I'll see you on the next one. 11. Spin Button: Hi, guys. Welcome backs in this video, we're going to discuss the spin button control to spin button control. It's got the same formatting or the same inputs when you try to format it as the scroll bar control. Except for the page change, which is, you know, when you click in the middle of the scoreboard, controls has got two buttons where if you click the button, the has the up direction, it increments up. And if you click a button having the down direction order facing down, basically it will, um they will increments down. It will increment lower with the incremental change. So a zika NSEERS got a minimum value, a maximum value and an incremental change doesn't have a page change, and it's gotta sell Link. Okay, I've got here. Ah, ah, loan calculator that built as an example on how to use the spin button you can see here. We've got the loan amount, the payments and the interest rate, and what the monthly payment will be if you select this loan amount on payments and interest rates. So how much you're gonna pay per month? Okay. If we examine the first spin button, I'm selecting the formatting for here. You will see that the cell link is this cell, okay? And, ah, you'll see that there's there's an incremental change of only a five. The reason for that is because the there is, there are limitations on the implementation off the spin button control. So the minimum can only be a zero on and maximum can only be 30,000 and the income on to change between zero on 30,000 actually, because the minimum and the maximum are between zero on 30,000 I cannot put anything more ah than, uh than that here on the minimum maximum value. And because I want values here to appear, there are more than 30,000. Okay, so what? What I do is that I would link the spin button to this cell here, and then this cell would simply take the number in this cellar multiplied by thousands. Okay, because I cannot if I want to reach this 115,000 figure here, I cannot write it directly in the maximum value field here because the limit is between zero and 30,000. This is the limit. Were off the numbers that you can put here in the spin button. Okay. This is why we're creating here some sort of an intermediate cell or a bridge cell that we linked the spin button to it. And then this cell just takes this the number in this cell and multiplies it by 1000. Okay? And I've put the incremental change by five, and I'm multiplied by 1000 here. That means that we increments by 5000 here on the loan amount. Okay. And, ah, put directly here. Ah ah. Put the number of payments are actually here in this cell and and linked the spin button to this cell for the payments. Because actually, the months are well between the limits of zero on 30,000 and the incremental changes by six months. So you can take a loan and and have it for a year or a year and 1/2 2 years, two years and 1/2 etcetera. Okay. And this is for four year and four years and 1/2. 54 months. Okay. And now there is something. Also, I need to ah, explain about the interest rate. Well, the interest rate to get to 1%. Okay, to get a 1% you can either put one divided by 100 or 10 divided by 1000 or 100 divided by 10,000. Okay. However, if we put one divided by 100 ah, we will not be able to increment with the small in Currence such as open 05%. Also, the case if we put 10 divided by 1000 if you put the 1% is 10 defied, divided by 1000. So I'm just gonna It is straight that for you here, um, this spin button here are built, okay? And I put the minimum value as a 10 and the maximum value is 150. Okay. And if we divide this, these figures 10 by but by 1000. If I divide 10 by 1000 they'll give the 1% if a divide 1 50 boy ah, 1000. That would give the 15% okay, 15% interest. So if I built it this way with this, um, spin button year and I link the spin button to this cell, okay? And I divide the amount, um, nor the number in this cell by 1000. Okay, So what would happen is that we would be only able to do, like 5.5% 6% 6.5% 7%. 7.5% insider will be able to increment up by only opened 5%. And that's okay if that's all what you need. You don't wanna do 8.25% for example eight point 2%. 8.15%. If you don't want to do that, then absolutely go for it. That's fine. Over if you need more precise, um, selection or ah, like if you need to have, ah finer percentage or, you know, more precise selection of the percentage of the loan. So you do like 2.25%. 2.3%. 2.35% 2.4%. You know, if you want to do this, it's better to do it, or you won't be able to achieve that except if you do 1% as 100 divided by 10,000. So that means if we'd write like red click on this spin button and do a formal control press, former control here would be having the limits between 115,000. And of course, they are linked to this cell. Okay, And this is Ah, this is the cell here that is in commended by the spin button. And then this cell simply takes the number in this cell here and divides it by 10,000. To be able to achieve that. And you'll see also that the incremental changes by a five when five is divided by 10 1000 you be able to achieve an incremental change of open old 5% which is open 05 divided by Ah , 100. Okay, so you can try it. Guys, please try to practice it to ah, be ableto even understand it better or on If you try to practice that the concept will stick to your head better basically. Okay, so this is a simple use of the spin button. I typically don't use the spin button a lot in on my dashboards. It's mainly used to incriminate numbers here. Really? I don't use it a lot. I think people who, um who use it are more use it more for for making forms for people to enter data like to select numbers like this to increment them. And so and normally, uh, are typically in these forms to be locking these cells. Okay, so they be right clicking on them, collecting, clicking and clicking on form it sells, and them on the protection. They would leave this this as locked, OK? And then they would lock the sheet by clicking on review tab on the ribbon and think they can protect sheet here. Um, i'll be making ah video for for protecting sheets and workbooks. One more thing. I need to adhere. Goings is that normally you be, ah, hiding the cells containing these bridge numbers or bridge selling cell links here. So what you would do is that you would simply hide this column here if you're making ah riel form and a real dashboard, um, to present it to, ah, client or customer. So because you don't want them to see these breach cells, you condone also, uh, make the funds invisible, make it ah, white fund, for example, in a white background so that you know, it wouldn't have a color, and the user wouldn't see them. And of course, as we explained you be locking these numbers is if you don't want them to under the numbers manually. Just want them to enter them. Enter the numbers. Um, toe, enter the numbers through the spin button. You be locking these cells and locking these cells so that the only these air only be able to enter the numbers through. Ah, the spin bone. So thank you, guys, for watching this video. Please follow me on to the next video. 12. 1 Option Buttons How to Insert: Hi, guys. Welcome backs In this video, we're going to discuss option buttons, and sometimes they're cold radio buttons, so option buttons enable us to create lists. However, unlike the list boxes, for example, where only one list box would contain the whole list in case of option buttons. If you've got a list, you need to ah, sign an option button to each item in the list. Okay, so let's check here the option buttons that I've got here, inserted on the sheet. If I right click on that PCs option button on, I go to former control. I'll see that it on option button would have only one, ah, inputs that you need to give to it, which is the selling. And if we check the selling here, it's the Selby 20 in the analysis time, okay? And, ah, if we check the option button Okay, we will see that in the analysis. Stab in Selby 20. It's got the number once it's produced the number one in the selling, and if we check laptops, it's produced the number two, and if you check tablets, it's produced the number three. The reason for that is that the option button for PCs is the first option button that has been inserted on the sheet on This is why it produces the number one and the second one here. Laptops has been inserted second in order of insertion on the sheet. This is why produces number two and tablets has been asserted. Third, and this is why produces the number three and so on and so forth. So let's see how we can insert an option button. We would need to go to the developer. Tab on the ribbon will click on Insert on. Then we will click on Option Button. We will dro our option button here. Then we're going to right click on it and click on Edit Texts were gonna name that Mobil's for the fourth item in the list. Now I'm going to right click on it to format it, and what we will see here is that Excel as recognized. I've got already option buttons on this sheet, and they are assigned. The selling could be 20 so it's actually assigned at the cell link of B 20. In the analysis, stab to the fourth option, but of inserted for Mobil's, so actually was Excel was smart enough to determine that I've already got option buttons. So, um, you know what most probably need this option button to be. Ah, having this selling here the same selling as the previous three. Okay, so a press OK here, and I'll check the option button for Mobil's, as you can see her because it was inserted as the fourth option button s. So it's produced the number four here on the selling. Another way to create an option button is by copying and existing one. So I'm gonna select the my Mobil's option button here by right clicking on it and then left clicking on the edge. And then if I press my control key, you'll see that there is a plus sign here appearing on my cursor. And if I, uh, press my left mouse button and drag this option button and then you can see her that by pressing my control and my left most click, I can put it anywhere on the sheet over. If I also in addition to my control and left mouse button, I pressed this shift key on my keyboard. I can make sure that I can Onley copy it ah, aligned with with the original one, either horizontally or vertically. Okay, so if you press your shift while pressing your control and left click, you can make sure that it's aligned with the previous option button. And now I'm gonna release my left. Most click as you can see her. It's been copied, and it's got the same name as the original one that we've copied from. If we right, click on it and go to former Control if you consider that it's ah, been given the same cell link as the previous one. And if you check that option, button the new one here, you can see area that it's producing the number five on the selling because this is the fifth option button there's been inserted on the sheet. I'm gonna delete it because we don't need it anymore. You can also copy several option buttons at the same time. So more than one option button you can right click on one of them and then, while pressing your shift key, you're going to right click on the others year to select them on. Then you're gonna press your control key, and you're going to see this plus sign our new cursor, you're gonna copy them by dragging them whether you're left, click and while pressing your control key. And if you need them to be aligned either horizontally or vertically with the previous ones , you compress your shift key as well. In addition to your control. Unless Les Miles Button, you're gonna release your left click here and there you go. They have been copied. And, of course, they're going to be linked to the same cell link just as we copy the single option bun. Okay, I'm gonna delete them now, guys, because we don't need them. One more thing I need to explain here is that sometimes when you insert an option button because Excel considers your objects to be in different liars. So sometimes when you insert the option button, it would be in the layer in the very back. So I mean, I've inserted the option, but in here, But in this case it was in the layer that is in the front. However, let's put it in the layer that is in the very back by going to order here and then put it on centre back. So sometimes, if you sometimes when you insert an option, but it would be in the layer in the very back. So if you try to put it on top of your chart here or in top of an object on top of a shape , for example, any other object, it would be in the very back, so you won't be able to see it. So in this case, what you need to do is to right click on it and go to order. And then, uh, you're going to bring it to front. To this way. It would be in the layer in the very front on your, uh, off your sheet here and you be able to put it on top off another object, which in this case, is the chart. 13. 2 Option Buttons (Part 2) Formatting: one more thing I need to show you is that weaken group option buttons together so that we can deal with them as one unit so they can be moved together. For example, is one unit or copy together as one unit as well? How we can do that is by selecting all the option buttons. So I'm going to right click on one of them here and then press my shift key and then right , click, right click, right click on the others here. And then I'm gonna go to the format top on the ribbon. I'm going to click on the group, but in here, I'm gonna click group again. Okay? And now, as you can see her, they have been grouped together, okay? And they can be moved as one unit together, and we ca Nhan group them as well by following the same steps. Except we click on one group instead of group. Okay, Okay, I So one more thing I want to show you is how to align the ocean buttons if they are scattered. So when I'm going to do, I'm gonna scratch them. I'm bright clicking here on my tablets and dragging with my right click on. Then I'm going to click on move here. When I get this menu menu on, I'm going to do the same thing with my PCs. And now I'm going to select my Mobil's option button here would by right clicking and pressing on the edge here. And because this is the first option button there has been selected all the other option buttons. I'm just selecting the other ones here, Um, and all the option. Other option buttons will be aligned to, ah, to this Mobil's option that I'm gonna line to the left edge of the Mobil's option button so they'd be all aligned vertically to that. It's left edge going electric on format here on the ribbon. And then I'm going to click on a line and then press a line left. And now they they have been all aligned Teoh, the left edge of Mobil's. There are also other options here for a line center. Align right and you know, different options. Feel free guys to experiment with them. Another option is well. That you can do is that you can distribute them vertically. Ah, and this is to create equal spaces between them. However, these equal spaces are created between their top edges and their top, the top edge of one of them and the bottom edge of the other here. So this is why, actually, there isn't unequal space between oldest circles here, so all the circus don't have equal spaces between them. So what you need to do in this case is to select all of them and give them, um, a unified size. So I'm going to give them, give them a size, for example. But off half an inch by an inch, for example. So now they all have the same size. Okay, I'm gonna click on a line again, and then I'm going to click on distribute vertically. And now, as you can see here, all the circles have equal spaces between them. Okay. Okay, guys. So I'm gonna select all my option buttons here by pressing the control key. And while pressing the control key, I'm going to right click on each of them. And then I'm gonna left click and try to drag them. And as you can see here, I can put them just about anywhere on the worksheet. They can move freely. Okay? and there is an option here that you want to show you. I'm going to go to the former tub on the ribbon. I'm gonna click on a line and then I'm going to click on snap to grid. Okay, on. And as you can see here, there is ah, some sort of ah, green lining around the shape here on the borders. So that means that the option is turned on. And if you try to move them this way, if you try to move your option buns after ah, selecting snap to grid, you can see here that they cannot move Freedy as they were doing previously. They need to be aligned to the the Excel grid, so they need to be aligned vertically here to a column, for example. Okay, s So this is another option that you can use while moving your ah, your option buttons or your or any shape actually in excel to make sure that it aligns with the grit. Okay, so thank you guys for watching this video and please follow me on to the next video 14. Option Buttons (part 3) Group boxes: Hi, guys. Welcome back. So in this video, we're going to discuss group boxes. So group boxes enable you to create a different groups of option Dutton's so that each group would have ah, separate selling. So, as you can see here, I've got to group boxes. And in each one, I've got three option buttons, okay? And each group of option buttons housed in a group box have a different cell link. So, as you can see here, the first, uh, group, it has this the selling for the one for that selling yellow, as you can see on the second group, has ah, selling quid that cell in blue. Okay, so this is useful if you want to create, like, two different filters on your charts, like you'd have ah, filter for the region and other filter for the product type, for example. Okay. And of course, we have explained how you can control charts with option buttons on the previous video, so I'm going to show you how you can do that. First of all, you're going to go to the developer tab, you're going to click on insert, and then you're gonna click on this icon here for group books, The one having X y Z on it. And you're gonna draw your group bucks by dragging with your left most button on. Then we're gonna insert an option button here, and I'm gonna assign ah, link cell to it. I'm gonna great click and click on formal control, and I'm gonna put a selling for it here and now. If you click that option button, it gives us the number one. And this is ah, separated from the others. And as you can see here, although this is the seventh option, but the husband inserted because it's in a group box of its own and the others are in group box of their own. You know, Excel starts counting again from one on the linked seller on the selling. Okay, You know, if you insert the other option button here Ah, and if you right click on it, you will see that Excel will automatically pick up the links sell here or the cell link. OK, it will pick up the same cell link as this one eyes the one that this one had. Okay, this is because you have inserted it in the same group box. So Excel was smart enough to know that because the 2nd 1 was inserted in the same group box that you need to ah, link it to the same Ah, cell or two you need to make it uses the same cell linker. Okay, so this is guy guys, how you can use group boxes to create different groups of option buttons. So thank you, guys for watching this video and please follow me unto the following. 15. Slicers: Hey, guys, welcome back. So in this video, we're going to speak about slicer. So slicers are actually user friendly filters and the enable us to filter are pivot tables and thus adding interactivity to our dashboards this way, because we can filter the data that is shown on our pivot tables. And this data would be linked to other elements in our dashboard and thus changing the data appearing on our dashboard, changing the numbers on our charts and so on slicers are only available starting from Excel 2000 ton. Now let's see how we can insert a slicer. So you need to select any cell inside your pivot table and then go to the analyze tab on the ribbon, click on insert slicer and then select which field in the pivot table you would like to filter. So here, for example, I'm going to select the country because I want a filter my pivot table using the country on . I'm gonna get the same slicer that I've got here on the right hand side, which is the one filtering the country's. So if you click OK, here, you're able to insert a slicer that filters your countries So now you can change your countries here, and you can see here that whatever countries you filter for here on the slicer are reflected on the pivot table here. And note also that the filtration on your slicer works, even if that particular field is not put as a filter field on your pivot table, so you can see here the country is not. He filter field on the pivot table, and still the slicer works, so I'll just delete that one because we've got a similar one here at the top. Now let's speak about slicer formatting, because you can actually change the colors of your slicers so you can select your slicer. Go to options here, and you can select a different color for your slicer. You can also change the color of multiple slicers by selecting one of them president control and selecting the other and selecting a different color. As you can see here. Also, if you click on this arrow here and you can click on new slicer style, you can create your own custom slicer style, the own colors, your own everything. So feel free to experiment with these options because you can color the slicer and make it suit your own branding. You can also make your slicer control multiple pivot tables. So here at the moment, this particular slicer only controls this pivot table here on the left and we get another pivot table here on the right. This pivot table is called pivot table two, and this one is called pivot Table one. So if you right click on your slicer and click on report connections because here that this Leiser is connected only to pivot table one if you check pivot table two and click OK, you're gonna be able to control both. Pivot Table says you can see here when I do a change on the year the year gets changed on both pivot tables and you can do the same as well for this one so as to make the country change on both off them at the same time. However, please note that in order to make a slicer control multiple pivot tables, you need to have all the pivot tables having their data source from the same source, so they need to all have the same data source, so they need to be all casing from the same table. OK, so I've got both these pivot tables here. They're getting their data source from this table here on the left. If it had been the case that each one of them had a different data source, you will not be able to control them using the same slicer, at least without involving V v A and without having some code to do it for you also, you can actually select multiple items on your slicer at the same time. So here I'm selecting the year 2012 I compress control and select 2013 as well. So now on filtering with the data for both 2011 and 2000 and 13 You can also use the shift key to select multiple contiguous years or multiple contiguous items on your slicer. So here I'm selecting 2011. If you press the shift key and select 2013 you will select everything from 2011 till 2013. So all these contiguous items You can also select multiple contiguous items by clicking on dragging. So if you click 2011 and drag while clicking until 2013 you're able to select all these contiguous years. You could also remove all the filters by clicking on this red button here the clear filter button, and this will actually select all the items on the slicer. So currently we're selecting all the years from 2011 to 2014. Because we've removed any filtration, you could also remove their headers. So basically, as you could see previously, there was no red button here. And that is because this slicer had its header removed so you can remove its header by right clicking on the slicer, clicking on slicer settings year and then until the display header button and click. OK, this removes the headers, and this could be required sometimes because you're filters are pretty obvious. So here it is obvious that 2000 of in 2012 13 and forcing our years, so maybe you don't need the year header and the slicer looks sleeker this way and also here on this slicer, we could remove the Hatter for the country because it is very obvious that Australia, Canada, the UK and USA are countries. You could also, of course, change their sizes using this handle here. So you congee just Ah, expand them and shrink them. And you can also go to the options tab here on the ribbon after selecting your slicer to make them have multiple columns. So, as you can see here, there's an option for changing the number of columns. So here you could have two columns. You can have three columns. So if you have a lot of items, this could be useful for you. And this also enables you to have or you filters or isn't Lee beside each other so you can shrink your slicer after having multiple columns trinket like that, and have your data arranged in a horizontal fashion instead of a vertical fashion. Okay, guys, So that sit for slicers? Thank you very much for watching this video and I'll see on the next one. 16. Pivot Charts: Hey, guys, welcome back. So in this video, we're going to speak about pivot charts. So first of all, let's see how to insert a pivot short. So to insert a pivot chart, you can click inside your table or your data clicking on any cell in your data on. Then go to the insert tab on the ribbon here and click on pivot chart, and then you're going to click on Pivot short again. And now, as you can see here, your table or your data range is ah labeled at the moment. Okay, so you can click, OK, and you private chart is going to be inserted on. Also, you're gonna have a pivot table inserted with your pivot short, and then you can start populating your pivot chart so you can put the country here on the the categories access, and also you can put the order amount for example on the values. And as you can see here, both the pivot table and the pivot short are being populated with the data. Another way to insert a pivot chart is to click on any cell inside your data and then go to the insert tab here on the ribbon Click on recommended charts and you'll find that recommended charts Button Onley in Excel 2013 and 2016. So if you click on that, you can see here that you've got some suggested charts here and all these charts the are having two headed arrows in almost a 90 degree there. Um, these charts axity are pivot shorts. Okay, so this logo here having to had it arrows means that you're going to insert a pivot short. So here, if I click Okay, this one will insert a pivot chart. This is a some of your butt country. This is meaningless. Actually, no, I don't want the order. I d by country as well. Maybe the units sold by country soul Insert that one and click. OK, as you can see here in a new sheet, a chart husband inserted with a pivot table as well. Ok, one more thing I want to tell you as well is that pivot shorts and pivot tables are connected. So actually, each pair of pivot chart and pivot table is interconnected. So that means that this pivot chart is connected to this pivot table. So as you can see here. If I filter on Australia, for example, on the pivot table, My selection off Australia gets reflected on the pivot short. But if you check the other pivot table pivot chart combo, this has not been affected. The this combo Although it's reading from the same data set from the same rayo data from the same table, it's not been affected by the filtration on this pivot table. OK, so here we can filter for Canada as well, for example and the pivot chart is gonna be affected here. But actually the other pivot table pivot chart combo, it's not gonna be effective. Okay, so that's something to note as well. One more thing I want to talk about here, which is actually what distinguishes a pivot chart from a normal chart is actually field buttons. So, as you can see here, there some buttons on the chart. Okay, these field buttons can allow us to actually filter are pivot chart and this effects are pivot table as well. So I conforto here for Canada, for example, and this effects both the pivot chart and that pivot table. OK, And if you would like to hide or on hide this field buttons, you can right click on a field button. Okay. And you can either hide just this one button here by clicking on hide value feel button on chart, or you can hide all field buttons on charts. If you click on this, this is gonna hide all that fueled buttons on the chart. If you would like to own, hide them again, just select your chart and then go to the analyze tab on the ribbon. And here you can actually untech the hide. All and all of them will be, uh, on hidden okay. Or you can actually hide them one by one. Ah, by actually unthinking the show. Ah, button of each of them. OK, so show this if you want ticket is gonna hide it. Okay, so that's how you can use feel buttons and hide and a non hide them. One more tip you'd like to tell you as well is that when creating dashboards usually are gonna have your pivot tables and pivot charts on different cheats because you would have your pivot tables on your analysis sheet, for example, and you would have your pivot chart on your dashboard cheat or on the sheet that would be actually facing your customer or the interface of the dashboard. So if you'd like to move your pivot chart, you can select your pivot chart on press control and X on your keyboard for for cutting. And then you can go to another sheet and paste that using control and V on your keyboard. You can also select your pivot charts. That's another way to move it. And then you're going to go to the design tab on the ribbon click on Move chart, and then you're gonna actually move it to another sheet. So, for example, if I want to move this to sheet five, for example, and then I click OK, and you can see here that has been moved to Sheet five. OK, so that was an introduction about pivot charts. Thank you very much, guys, for watching this video I'll see on the next one 17. Thanks for Watching: 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.