1011 Pivot Table - Refresh vs. Refresh All, Change Data Source and Creating Dynamic Table Data Source

Advanced Excel Crash Course Section 10: Pivot Table
6 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

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.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.