Hello and welcome back. This time we will talk about the few aspects of pivot table which helps us refresh the pivot table based on the change in the original source of data. Now, the change in the original source of data can be twofold. One amongst the existing line items, the numbers can be changed or the data can be changed. Just for example, I'm just changing all of the division to HMD in the second region, or towards the end of the data, you might have new line items that get added every now and then. So from two perspective, we'll see how to refresh the pivot table which you may have already created.

First, let me populate some dummy reports. So I'm just choosing division and I'm choosing name. Okay, I want to put name in the main action area so that I can see the total number of employees in the organization division wise. Now let's look at the data first ad division has 41 employees assume for a moment that I in the staff details That's the mean sheet, one of the employees got transferred from HFD to the division ad. Now by itself, the pivot table doesn't get refreshed unless you right click on any portion of the pivot table, and you say refresh. As you do that, you notice the number in the division, ad changes, and sodas in HFT.

Now, you can also affect this refresh by going to pivot table tools. In the older versions of Excel, you will find the word options here, but in the newer version, it will be analyzed. Within analyze, you'll notice something called refresh. I'm going to click on refresh the shortcut key has given it says Alt F five. I'm going to click on that. Now why do you think refresh all has been given?

Assume that there are some multiple pivot tables which are present in the workbook. Now they may have been made for different purposes. For example, the section Pivot Table, which are just copy pasted, could have been made for, let's say division, and let's say salary, salary total, not count. So I want that I do not need to go to individual pivot tables and say right click and refresh. So for that, I need a better option. I go to analyze, pivot table to analyze, I go to refresh button, and I'll click on refresh all the shortcut key to that is Ctrl.

Alt f5, as mentioned in the key tip, so it refreshes all the data points in the pivot table based on any change in the source data. But this is different from a scenario where new line item gets added towards the end of data. So for example, I'm going to press Ctrl semicolon to give me today's date, that's 19th of January 2015. Enter. I'm going to write my own name rishabh. And let me give a salary a hefty salary in mind.

Yep, there I go. And I'm giving the name of the division Ed rating one, of course, and a hypothetical age 28. Now, this data doesn't get reflected in my pivot table. Unless I go to the pivot table, I go to Analyze tab. The same tab can also be called as Options tab, in case you're using 2010, or seven version of Excel. But then you notice besides the refresh button, there's something called change data source.

So I go to change data source. I click on it. And then I just ensure that I read choose the data starting from the first cell header, shift control, right shift CTRL down, including the extra one line, I press OK. And now when I do that, notice the grand total the grand total of salary has changed. And if I refresh the other table, the question is, does it get refreshed in the similar manner? No, because this change data source works pivot table by pivot table. So if I Go to the first pivot table.

Again, I'll have to go to Analyze tab again, change the data source, again, changed our schools button, and then ensuring again, the data set is choosing till the last very line. Now, wouldn't you find this cumbersome? I know you would. And for this very reason, I have a quick solution for you. One, I can first ensure that the draw data is first recognized by Excel as a table. So I go to Insert Tab, I go to table option table Ctrl T is the shortcut key as mentioned in the key tip, once I do that, it is a coherent unit one single unit.

And based on this, I'm going to prepare a pivot table. So Insert, Pivot Table, or K. This gets me a new pivot table in a new sheet. So I'm going to put division and name in the main action area. Now why am I not able to see that mean actually there right now, because as you remember from our last few discussions that you have to write Click on the grid area of the pivot table, go to pivot table options. And then within the Display tab, click on classic. And once I do that, I get the old layout vintage layout.

So I'm going to put name in the main action area currently chose me for one eight people. Okay, fine. I go to the raw data, which is sheet one staff details. Now I'm going to insert a new name, probably on the same date date of joining. Let's say Ajay. I'm going to put the salary notice as soon as I add that one line, the change in color, as you can clearly notice indicates that the new item has been added to the table.

So I just add some of the basic raw data. And when I go back to our own pivot table, I just have to refresh. That's it, refresh 419 people. So this time, I didn't have to go to change the data source separately. Why? Because the table the entire raw data table has been recognized by Excel as a formal table.

And that is executed by first applying table and after having done so then we applied pivot table. So this was a quick trick to ensure that you save some time on Change Data Source. Next time you work with pivot table and expect some changes and addition of line items.