MS365 Pivot table column contains data but is displayed as 0

[copperwasher]

Hi there,

I have a basic pivot table set up to send statements to our business partners.
The only filter is the company name of our partner.

The job ref should display the job ref number/letters as per the sheet it is connected to (Range).
Some of the company job data is displayed perfectly, others, the filled cell data is displayed as a 0.

Ex1 pivot table working, job refs displayed correctly as per the data source sheet.

Co.	partner1
Row Labels	job refs	Sum of £ job	Sum of paid banked	Balance Owed
2022				£0.00
Dec
07/12/2022	259992	£552.00
28/12/2022	260907	£600.00	£1,752.00
2023
Jan
03/01/2023	265307	£1,200.00	£600.00
Grand Total	786206	£2,352.00	£2,352.00

Ex2, same data source (Range), the job refs are in the cells (range), but displayed as 0 here, as well as the correct data!!!

Co.	partner2
Row Labels	job refs	Sum of £ job	Sum of paid banked	Balance Owed
2022				-£4,218.00
Dec
08/12/2022	0	£252.00
12/12/2022	0	£504.00
19/12/2022	0	£600.00
2023
Jan
09/01/2023	0	£558.00
16/01/2023	0	£600.00	£1,104.00
Feb
27/02/2023	0	£696.00
Mar
20/03/2023	23115006	£624.00	£1,158.00
21/03/2023	23125504	£600.00
29/03/2023	0	£342.00
Apr
13/04/2023	23147740	£624.00
14/04/2023	23147868	£480.00
17/04/2023	23147811	£600.00
Grand Total	115683929	£6,480.00	£2,262.00

All of the values in the 'job ref's' are filled with the relevant job ref details, but some of the cells have been replaced with zeros...

Also, the grand total column, for the job ref, is displaying a total of 115683929, which is not required.
How do I blank this cell?

Any help would be greatly appreciated.

My thanks in advance.
Gary

 

[copperwasher]

Brilliant...
That worked, thank you...
I clearly need to learn the basics more so...


One last question

In the Pivot Table below,
Grand totals, I need to display £0.00 in the 'sum of paid banked'
Is that possible when the column is empty (no payments received).

	Co.	xxxxx
	Years	date	job ref	Sum of £ job	Sum of paid banked
	2023	15-Mar	BHL-202623-1	£540.00
		03-Apr	BHL 206091-1	£630.00
	Grand Total			£1,170.00

 

[RoryA]

Yes, there's an option for that - tight-click anywhere in the pivot table, choose Pivot table options, then on the Layout and Format tab enter a 0 in the empty cells option:

 

[copperwasher]

Thank you so much for your assistance RoryA...

All is working well