"Change Data Source" Pivot Table Excel 2016

[mark91345]

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

	A	B	C
1	Date	Animal
2	11/01/17	Dog
3	11/02/17	Dog
4	11/03/17	Cat
5	11/04/17	Dog
6	11/05/17	Cat

<tbody>
</tbody>

​

Then, I create the Pivot Table.

	D	E	F	G	H	I
2	Count of Animal	Column Labels
3	Row Labels	Cat	Dog	(blank)	Grand Total
4	11/1/2017		1		1
5	11/2/2017		1		1
6	11/3/2017	1			1
7	11/4/2017		1		1
8	11/5/2017	1			1
9	(blank)
10	Grand Total	2	3		5

<tbody>
</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
1	Date	Animal
2	11/01/17	Dog		Count of Animal	Column Labels
3	11/02/17	Dog		Row Labels	Cat	Dog	(blank)	Grand Total
4	11/03/17	Cat		11/1/2017		1		1
5	11/04/17	Dog		11/2/2017		1		1
6	11/05/17	Cat		11/3/2017	1			1
7	11/20/17	Dog		11/4/2017		1		1
8	11/21/17	Cat		11/5/2017	1			1
9				(blank)
10				Grand Total	2	3		5

<tbody>
</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.