1
00:00:00,080 --> 00:00:02,973
Here is a fictional six-order sales list.

2
00:00:02,973 --> 00:00:05,865
The first four orders total three hundred dollars.

3
00:00:05,865 --> 00:00:09,843
Two more orders add one hundred dollars, yet a PivotTable created

4
00:00:09,843 --> 00:00:14,182
from the original fixed range can still show three hundred after Refresh.

5
00:00:14,182 --> 00:00:18,159
We will inspect its source, connect it to an expanding Excel

6
00:00:18,159 --> 00:00:21,413
Table, and check the correct four-hundred-dollar total.

7
00:00:22,013 --> 00:00:24,625
Open the practice workbook's Sales sheet.

8
00:00:24,625 --> 00:00:28,728
It has a header and four rows: North one hundred twenty,

9
00:00:28,728 --> 00:00:31,339
South eighty, North sixty, and East forty.

10
00:00:31,339 --> 00:00:32,832
AmountUSD cells are numeric.

11
00:00:32,832 --> 00:00:36,935
The Rows to append sheet contains two later transactions, but leave

12
00:00:36,935 --> 00:00:38,427
those aside for now.

13
00:00:38,427 --> 00:00:43,650
Select Sales cells A one through C five to reproduce a fixed-range source.

14
00:00:44,250 --> 00:00:49,030
In Excel desktop, choose Insert, PivotTable, and put it on a new worksheet.

15
00:00:49,030 --> 00:00:52,708
Confirm the source is Sales A one through C five.

16
00:00:52,708 --> 00:00:55,649
Place Region in Rows and AmountUSD in Values.

17
00:00:55,649 --> 00:00:58,959
The value field should summarize by Sum, not Count.

18
00:00:58,959 --> 00:01:03,004
The expected starting result is North one hundred eighty, South eighty,

19
00:01:03,004 --> 00:01:06,313
East forty, with a three-hundred-dollar grand total.

20
00:01:06,913 --> 00:01:10,700
Copy the two data rows from Rows to append into Sales

21
00:01:10,700 --> 00:01:12,422
A six through C seven.

22
00:01:12,422 --> 00:01:15,520
S one oh five adds seventy dollars for West.

23
00:01:15,520 --> 00:01:18,619
S one oh six adds thirty dollars for South.

24
00:01:18,619 --> 00:01:22,406
The source list now contains six orders worth four hundred dollars.

25
00:01:22,406 --> 00:01:25,161
Save the workbook, then return to the PivotTable.

26
00:01:25,161 --> 00:01:29,292
Its old source reference has not grown just because the sheet did.

27
00:01:29,892 --> 00:01:33,522
In Excel desktop, right-click the PivotTable and choose Refresh.

28
00:01:33,522 --> 00:01:37,515
If it still says three hundred, select the PivotTable and inspect

29
00:01:37,515 --> 00:01:38,967
Analyze, Change Data Source.

30
00:01:38,967 --> 00:01:42,960
In this exercise, the deliberately fixed source is Sales A one

31
00:01:42,960 --> 00:01:47,679
through C five, while the two new rows sit at six and seven.

32
00:01:47,679 --> 00:01:51,672
Refresh reads the assigned source; it does not invent a wider

33
00:01:51,672 --> 00:01:53,124
range outside that boundary.

34
00:01:53,724 --> 00:01:57,767
On Sales, select the complete A one through C seven range.

35
00:01:57,767 --> 00:02:02,177
Choose Home, Format as Table, and confirm that the table has headers.

36
00:02:02,177 --> 00:02:06,220
Give it a clear name such as SalesTable from Table Design.

37
00:02:06,220 --> 00:02:09,527
This is a regular Excel Table, not the PivotTable.

38
00:02:09,527 --> 00:02:14,305
It can expand when you add further rows directly beneath its last row.

39
00:02:14,905 --> 00:02:19,093
In Excel desktop, select the PivotTable again, choose Analyze, Change

40
00:02:19,093 --> 00:02:22,862
Data Source, and enter SalesTable as the table source.

41
00:02:22,862 --> 00:02:24,955
Confirm the dialog, then Refresh.

42
00:02:24,955 --> 00:02:28,305
In some versions the ribbon says PivotTable Analyze.

43
00:02:28,305 --> 00:02:32,493
Microsoft's instructions allow an existing PivotTable source to be

44
00:02:32,493 --> 00:02:34,587
changed to an Excel Table.

45
00:02:34,587 --> 00:02:39,193
If the field list changes unexpectedly, recheck the table headers and

46
00:02:39,193 --> 00:02:40,868
source selection before proceeding.

47
00:02:41,468 --> 00:02:45,211
After your refresh, compare the PivotTable with the Expected totals sheet.

48
00:02:45,211 --> 00:02:48,955
The target is North at one hundred eighty, South at one

49
00:02:48,955 --> 00:02:52,017
hundred ten, East at forty, and West at seventy.

50
00:02:52,017 --> 00:02:54,740
Those four regions add to four hundred dollars.

51
00:02:54,740 --> 00:02:58,483
The workbook has six order rows, so neither the row count

52
00:02:58,483 --> 00:03:02,567
nor the total should stop at the earlier four and three hundred.

53
00:03:03,167 --> 00:03:07,445
When a later order arrives, add it directly below SalesTable and

54
00:03:07,445 --> 00:03:09,389
confirm the table border expands.

55
00:03:09,389 --> 00:03:14,056
Then refresh the PivotTable and reconcile the new sum with source rows.

56
00:03:14,056 --> 00:03:18,335
Excel's automatic PivotTable refresh is not available in every release,

57
00:03:18,335 --> 00:03:21,446
so this guide uses a deliberate Refresh action.

58
00:03:21,446 --> 00:03:25,724
If you see Count instead of Sum, check that AmountUSD cells

59
00:03:25,724 --> 00:03:28,447
are numeric and inspect Value Field Settings.