"Change Data Source" Pivot Table Excel 2016

[mark91345]

I first Create Table (where the data is =$A:$B, incl headers).

	A	B	C
Date	Animal
11/01/17	Dog
11/02/17	Dog
11/03/17	Cat
11/04/17	Dog
11/05/17	Cat

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

</tbody>

​

Then, I create the Pivot Table.

	D	E	F	G	H	I
Count of Animal	Column Labels
Row Labels
11/1/2017
11/2/2017
11/3/2017
11/4/2017
11/5/2017
(blank)
Grand Total

<tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Cat[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"](blank)[/TD]
[TD="align: center"]Grand Total[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

</tbody>

Next, in Columns A & B, I add a couple more dates and animals, but it is NOT reflected in the Pivot Table.

	A	B	C	D	E	F	G	H	I
Date
11/01/17	Count of Animal	Column Labels
11/02/17	Row Labels
11/03/17	11/1/2017
11/04/17	11/2/2017
11/05/17	11/3/2017
11/20/17	11/4/2017
11/21/17	11/5/2017
	(blank)
	Grand Total

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Animal[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]Dog[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]Dog[/TD]
[TD="align: right"][/TD]

[TD="align: center"]Cat[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"](blank)[/TD]
[TD="align: center"]Grand Total[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]Cat[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]Dog[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]Cat[/TD]
[TD="align: right"][/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]Dog[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]Cat[/TD]
[TD="align: right"][/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

</tbody>

​

So, when I look online, it says to click on the Analyze tab, then Choose Data Source, and make the proper change. I already have the "ants" surrounding the full Columns A & B, so there is nothing to change. So, I changed the table to $A$1:$B$20 and now it works. Is this a bug? Sometimes, I might know how far to go down to; other times, I just want the whole column.

Lastly, how can I remove the "blank" column from the pivot table?

 

[Jerry Sullivan]

Hi Mark,

Any changes that you make to cells in your datasource range aren't reflected in the PivotTable until it is Refreshed.

You can Right-Click anywhere on a PivotTable, then pick Refresh.

 

[mrshl9898]

The Blank is there as you are selecting a Row with no data in it. Your data is A1:B8 but you are selecting A1:B20. You need to change it or filter the blanks out.