**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 <OMG>

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.*