Hello, everybody, welcome to dashboard training. In this video, we will learn how to create the first pivot that's needed for our dashboard. Now let's see, the pivot that we are going to create is job type, and comparing to those of experience needed, what are the number of employees provided for the same. So let's see how it works. As our data is dynamic, the pivots that will be created will be connected to the same data and will be dynamic. When one data gets updated, automatically, the relevant pivots created out of the same data will also get updated.

And we'll see an example in the upcoming videos. So let's start we keep the cursor in the data. From the Insert tab we use pivot table. Now if you're using 2013, and above versions, there's a feature called insert pivot table. Or there's another option that says recommend pivot recommend pivot is a pivot that provides you some right recommendations based on the pivot selected. So there are relevant types of pivot tables that we can try out.

But we're not going to use that we're just rather going to prepare own pivots. If you're using 2010 or older versions, the interface is a little bit different. And rather than just having pivot table, you will have a drop down that says, pivot table pivot chart. And now we can select the pivot table. But if you select the chart, you will be provided with a pivot table that has a chart attached to it. The chart is quite easy, and we'll explore that later.

And for now, just try with pivot table, just click on pivot table, the source or the range will be selected. And now some of you might have already used pivot tables. So you do know when we select the data, automatically, it shows you the reference, but this time our data is dynamic. And it shows us the name of the debt the data. So in our example, the data name was mastered data. So now the table or the data understands what I've selected, and that I'm planning to create a pivot.

I rather personally suggest you always go for a new worksheet because in the in the existing if in the future it expands itself, it will be difficult to work with the pivot being in the existing table. So we click on the new worksheet and click on OK, which automatically creates a new sheet to the left of the active sheet and the pivot is dumped into it. Now the first thing we'll just drag it and push it and after the existing sheet and name it pivot one. Earlier I said that I would like to go for job title and based on the job title, I would like to see the experience needed. So I'll just take job title and drop it into the role label. So whatever dropped into the role label automatically falls one below the other as a category.

Now to get the data across. As a heading that is experienced in I wish that I have as a parameter or categories, so I'll drag in and drop it into the column, which becomes categories in the columns. Now to populate this with data, I can either use text as for count, or any of the numerical values for summation. Currently, it will go with salary and I'll drag and drop it into the value field and that gives me the total salary paid out. Example now this pivot is actually representing the data like for admin, if a person has specific knowledge, you may get this amount and so on and so forth. So this is cumulative answer.

A pivot always provides us a cumulative answer. Now if in the future, there is a new data added, say for example, I'll just add, save it 1933 US dollar with a salary of 9 million So we'll go with admin in India. Today's date and experience in Excel and require the experience of maybe five years once the data gets updated. Now if I come back to our pivot, you can see here that the data did not get updated as I promised. That's because we have to refresh. So simply right click reflect fresh or all five is the shortcut.

Now as you can see, under admin, there's an amount increment of 1 million earlier it was eight. Now it's added up with 1 million. So if I delete that row and refresh it, just focus on the Excel amount. Now this time, I'm going to use the shortcut that is alt f5. And there it is. You see the number updated.

Sorry for that. It was nine, not eight. So in the next video we will see how to name these labels properly directly from the system and how to convert these blanks into zeros. So that when the charts are created, it looks a bit better and how to convert these rather than giving our salary but average. So see you in the next video till then keep practicing. Thank you