Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i have a pivot table and it is showing 0 for amount values
Solved by S. H. in 45 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 03/09/2018 - 09:43
Hello, Welcome to Got it Pro.
User 03/09/2018 - 09:44
hi
Excelchat Expert 03/09/2018 - 09:44
Can you please forward the sheet so that I can have a look and help you.
User 03/09/2018 - 09:45
ok hang on - give me a minute
Excelchat Expert 03/09/2018 - 09:45
Sure
User 03/09/2018 - 09:48
:-)
User 03/09/2018 - 09:48
i am just masking some data - pl bear with me.i didnt think that i was going to send the file across
Excelchat Expert 03/09/2018 - 09:48
Ok.
Excelchat Expert 03/09/2018 - 09:48
Because I need to have a look on where the problem is
User 03/09/2018 - 09:53
sure - just closing the file
Excelchat Expert 03/09/2018 - 09:55
ok
User 03/09/2018 - 09:57
ok sharing
User 03/09/2018 - 09:58
file shared
[Uploaded an Excel file]
Excelchat Expert 03/09/2018 - 10:00
O my goodness, you have shared 90000 rows of data :)
Excelchat Expert 03/09/2018 - 10:00
where is the pivot?
User 03/09/2018 - 10:01
it was in the other file !!
User 03/09/2018 - 10:01
hang on
Excelchat Expert 03/09/2018 - 10:01
where are you seeking my help
Excelchat Expert 03/09/2018 - 10:01
?
User 03/09/2018 - 10:02
sending u the pivot
[Uploaded an Excel file]
Excelchat Expert 03/09/2018 - 10:02
what was the other sheet about?
User 03/09/2018 - 10:03
it has the pivot which was the problem
Excelchat Expert 03/09/2018 - 10:03
so I can ignore the last sheet that you have shared?
User 03/09/2018 - 10:03
yes please
Excelchat Expert 03/09/2018 - 10:04
where is the base data from where this pivot has been created?
User 03/09/2018 - 10:04
i was analyzing the file building the pivot and started getting zeros... the first file is the base data
Excelchat Expert 03/09/2018 - 10:05
ok so one file has data and other has pivot
User 03/09/2018 - 10:06
yes i had the pivot in the unedited file - i sent u only the base data file after masking some of the entries
Excelchat Expert 03/09/2018 - 10:09
the sheets that you sent me doesn't have the same data from which pivot has been created
Excelchat Expert 03/09/2018 - 10:09
month column is not there plus there are values that are missing
Excelchat Expert 03/09/2018 - 10:09
Still
Excelchat Expert 03/09/2018 - 10:09
Look at the B19 in your pivot
User 03/09/2018 - 10:09
there was no month column - only date and the pivot made the month automatically
Excelchat Expert 03/09/2018 - 10:10
where you created the month?
User 03/09/2018 - 10:10
nope only the DOC_DATE field in the spreadsheet
User 03/09/2018 - 10:11
when i select the field Excel is making the month and Quarter
User 03/09/2018 - 10:11
i have not created this
User 03/09/2018 - 10:12
B19 is showing 0 like all the other cells but they have amounts !!
Excelchat Expert 03/09/2018 - 10:12
I can see the month provided in base data from where Pivot is created but this column is not there in the spreadsheet that you sent me
[Uploaded an Excel file]
Excelchat Expert 03/09/2018 - 10:13
Lets talk about your 0 values for now.. because I can track some of that data.
Excelchat Expert 03/09/2018 - 10:13
Look at B19
User 03/09/2018 - 10:13
ok yup i am at B19
Excelchat Expert 03/09/2018 - 10:13
Can you see the sheet at the right side of this screen?
Excelchat Expert 03/09/2018 - 10:13
I pasted few values
User 03/09/2018 - 10:13
yes
Excelchat Expert 03/09/2018 - 10:14
So B 19 is the sum of these values hence it is 0
User 03/09/2018 - 10:14
these are the values which should show up in the pivot
Excelchat Expert 03/09/2018 - 10:14
306750 and -306750 =0
User 03/09/2018 - 10:14
i got it
Excelchat Expert 03/09/2018 - 10:14
Did it solve your query?
User 03/09/2018 - 10:15
but then i need them both in the pivot
Excelchat Expert 03/09/2018 - 10:15
ohk so you do not want to sum
User 03/09/2018 - 10:15
no s
User 03/09/2018 - 10:15
see each entry has its own story
User 03/09/2018 - 10:15
the - minus means money given out
User 03/09/2018 - 10:16
and the + means money received
Excelchat Expert 03/09/2018 - 10:16
ok
User 03/09/2018 - 10:16
it is an accounts voucher - so it will show Debit and Credit undee the same voucher number
Excelchat Expert 03/09/2018 - 10:16
wait.
Excelchat Expert 03/09/2018 - 10:17
Let me explain you how pivot works.
Excelchat Expert 03/09/2018 - 10:17
for the same entries it will perform calculations like sum, count, average and so on.
Excelchat Expert 03/09/2018 - 10:18
so if you look at the data at the right side of the screen that is associated with B 19 of your pivot
User 03/09/2018 - 10:18
yes ..
Excelchat Expert 03/09/2018 - 10:18
so pivot an show the sum of these two transaction amount which is 0, it can count and say 2 and so on
User 03/09/2018 - 10:19
i got that - but then is there a way to show only the negative amounts ?
Excelchat Expert 03/09/2018 - 10:19
but you just want to populate the data as it is without performing any calculation. you have to move the data from values to column
Excelchat Expert 03/09/2018 - 10:20
populating data only negative or positive is possible but populating data means it should not be in the values field (where pivot perform the calculations)
User 03/09/2018 - 10:20
:-)
User 03/09/2018 - 10:20
it says i cannot place someting with more than 16k rows into column
Excelchat Expert 03/09/2018 - 10:20
but again there is a column limit in excel so pivot can not populate the data for the number of rows you are trying to handle
Excelchat Expert 03/09/2018 - 10:21
powerpivot may help you for this
User 03/09/2018 - 10:21
whats that !
Excelchat Expert 03/09/2018 - 10:21
that is an etc add-in you need to download and install .. that just increase the capabilities and features
User 03/09/2018 - 10:21
i learned pivot over the past 5 days and not i hve power pivot to see
Excelchat Expert 03/09/2018 - 10:22
that is not a normal inbuilt in excel. Power pivot is a step ahead of pivot
User 03/09/2018 - 10:22
ok
Excelchat Expert 03/09/2018 - 10:22
Excel can not handle the data with pivot that you are trying with
User 03/09/2018 - 10:22
oh ok
Excelchat Expert 03/09/2018 - 10:22
if you want to perform calculations like sum , we can do it
Excelchat Expert 03/09/2018 - 10:22
Did it solve your query?
Excelchat Expert 03/09/2018 - 10:23
I tried my best to explain you.
User 03/09/2018 - 10:23
yup thanks this has been a great help and yes my query is solved
Excelchat Expert 03/09/2018 - 10:23
Please end the session, I will appreciate a 5 star rating. I will also provide a 5 star rating for you so that you get the best expert next time.
Excelchat Expert 03/09/2018 - 10:23
Thanks for using Got it Pro. Looking forward to help you further. Appreciate it :)
User 03/09/2018 - 10:23
one last question
Excelchat Expert 03/09/2018 - 10:23
Yeah say
User 03/09/2018 - 10:23
is there a formula which i can use to eliminate the duplicate rows
Excelchat Expert 03/09/2018 - 10:24
Yeah
Excelchat Expert 03/09/2018 - 10:24
you just want to highlight or want to remove duplicates?
User 03/09/2018 - 10:24
sorry ... i meant the second row with hvg the same DOC-REF but a positive value
User 03/09/2018 - 10:25
if i remove the second row my problem i solved - i am interested in the - minus value because that is the pay out
Excelchat Expert 03/09/2018 - 10:25
what do you mean by second row?
Excelchat Expert 03/09/2018 - 10:26
ok you are referring to the number i posted at the right
User 03/09/2018 - 10:26
yup
Excelchat Expert 03/09/2018 - 10:26
this is just the data that pivot has picked up from the base sheet.
Excelchat Expert 03/09/2018 - 10:26
in just one row it is looking easy but for many entries like you have 90000 plus rows it wont be easy to identify
Excelchat Expert 03/09/2018 - 10:27
you can filter the transaction amount (only negative values) manually in base data and copy that in a new sheet
User 03/09/2018 - 10:27
yes i know - but if i run a script or something in the base document i can eliminate the second row - identified by the DOC-REF and then whether the amount is - or +
Excelchat Expert 03/09/2018 - 10:27
after copying the whole data for negative values, create a new pivot,... would be easy
User 03/09/2018 - 10:28
ok filter and then move ahead
User 03/09/2018 - 10:28
fine - thanks
Excelchat Expert 03/09/2018 - 10:28
Thanks :)
User 03/09/2018 - 10:28
this has been a great help and sets my mind to rest :-)
Excelchat Expert 03/09/2018 - 10:28
I hope you will get the solution
Excelchat Expert 03/09/2018 - 10:28
You can end the session, don't forget to rate a 5 star.
User 03/09/2018 - 10:28
i was really worried as i have a delivery in the nxt fewhours
Excelchat Expert 03/09/2018 - 10:28
you will be fine. Have a good day
User 03/09/2018 - 10:28
oh sure 5 start or more
User 03/09/2018 - 10:28
:-)
Excelchat Expert 03/09/2018 - 10:28
:)
User 03/09/2018 - 10:29
tks u hv a good day too

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.