Pivot Table displaying incorrect data

[MrAdOps]

Hi Guys this is my first post and i thought why not ask it here. I have a pivot table that is doing my nut in. I have a lot of data that i want to add to each week, how ever when I am putting it into a pivot it displays the incorrect values. I have no idea what I am doing wrong.

However if i was to click a cell in data then insert a pivot table it all works as i want it. But then how do I add data and refresh the pivot table? As this would would collect data from cells with entries.

Any suggestions? I have included some screen shots.

Data:

Pivot Table:

Any help would be lovely Thanks

Paul

 

[RoryA]

What's the problem exactly? Hard to tell from that what figures are wrong for counts.

 

[gsistek]

I am guessing you want the sum instead of the count for the values. To do that, go to the pivot table field list on the right hand side. In the values box you will find Count of Unfilled Impressions, etc. If you click on one of those and choose Value Field Settings..., you can change the summarization from Count to Sum. Another way would be to go to the ribbon section called PivotTable Tools, and click the Options tab. Then, in the Calculations group, click Summarize Values By and choose Sum.

 

[MrAdOps]

I think i have fixed my issue...

by finding this...MS Excel: Change data source for a pivot table in Excel 2010

 

[MrAdOps]

I am guessing you want the sum instead of the count for the values. To do that, go to the pivot table field list on the right hand side. In the values box you will find Count of Unfilled Impressions, etc. If you click on one of those and choose Value Field Settings..., you can change the summarization from Count to Sum. Another way would be to go to the ribbon section called PivotTable Tools, and click the Options tab. Then, in the Calculations group, click Summarize Values By and choose Sum.

WOW!!!! thank you!! thank you!! - i never knew this exsisted! You sir have saved me many many hours of work, from re doing pivot tables!!!

So both problems are fixed..the refreshing of the data when new data comes in. And the calculations of sum on the values within the pivot table!!!

 

[gsistek]

Instead of changing the source everytime you enter new data, you can put the data into a table (Insert tab -> Table) and use the table as the source. Everytime you add data, you just need to go to the piviot table, right-click and choose refresh and it will refresh that pivot table as well as any others that are using that table as their source.

 

[MrAdOps]

Instead of changing the source everytime you enter new data, you can put the data into a table (Insert tab -> Table) and use the table as the source. Everytime you add data, you just need to go to the piviot table, right-click and choose refresh and it will refresh that pivot table as well as any others that are using that table as their source.

Ah i see, but i dont think that will work, as i will be adding more n more data from dates. So the data could range from 01/01/14 to 31/12/14 but id be pulling the data externally from another source weekly. Would a table work still ?

 

[gsistek]

Absolutely. Highlight the data you have, goto the Insert tab and choose Table. As you enter data below the table, the table will expand to include the new data. Now go to your Pivot Table, go to the PivotTable Tools on the ribbon, click Options and choose Change Data Source. Then highlight the entire table you just made and you will see the Reference change from A1 format (ie $A$1:$R$1856) to a reference to the table (ie Table1).

 

[MrAdOps]

Absolutely. Highlight the data you have, goto the Insert tab and choose Table. As you enter data below the table, the table will expand to include the new data. Now go to your Pivot Table, go to the PivotTable Tools on the ribbon, click Options and choose Change Data Source. Then highlight the entire table you just made and you will see the Reference change from A1 format (ie $A$1:$R$1856) to a reference to the table (ie Table1).

Dude you are a legend! Thank you so much...You must live in excel...really loving the tips

 

[SteveOranjinSteve]

So I'm having this same problem, except the totals are just wrong. I had to rearrange some values in the table, and when I hit refresh, the values remain the same in the actual pivot table. When I add another table, it still has the same value (12 instead of 13.) I've counted and there are in fact 13, so the count that the pivot tables are producing is just wrong. I don't know why it is doing this. I tried switching the value of the table from a "SUM" to a "Count" and it comes out as 1.

Please advise.