Creating Item Sold report using Pivot Table Sample5 pg 110 111

Sample 5

In this sample report, we show the total quantities and values of the monthly sales of each item but the months will appear in the top row.

	A			B				C				D				E				F			G			H
2
3								MTH
4 	ITEM SOLD	Data			0				1				2				3			4 			Grand Total
5 	Model A		Sum of QTY		0				249,048			203,897			282,726		49,324		784,995
6				Sum of 			0				(1,323,990)		(1,099,449)		(1,521,920)	(260,878)	(4,206,237)	
				RM-CREDIT
7 	Model B		Sum of QTY		0				162,610			235,906			252,330		159.215		810,061
8				Sum of 			0 				(865,699)		(1,249,328)		(1,376,298)	(849,610)	(4,340,934)
				RM-CREDIT		0	
9 	Model G		Sum of QTY		0				136,341			198,838			247,709		344,895		927,783
10				Sum of  		0				(722,783)		(1,077,941)		(1,364,610)	(1,779,477)	(4,944,811)
				RM-CREDIT
11 	NR			Sum of QTY		0				180				180				180 		245 		785
12				Sum of 			(42,626,171)	(8.271,572) 	(4,234,128)		(3,083,144)	(5,580,356)	(63,795,372)
				RM-CREDIT
13 	Total Sum of QTY			0				548,179 		638,821 		782,945		553,679		2,523,624
14 	Total Sum of RM-CREDIT		(42,626,171)	(11,184,044)	(7,660,846)		(7,345,971)	(8,470,321)	(77,287,354)

110

Here are the steps:

1. Follow steps 1 to 13 from Sample 1.

2. Drag the button ITEM SOLD into the ROW box and drop it there. Drag the button MTH into the COLUMN box and drop it there. Drag the button QTY into the DATA box and drop it there. Drag the button RM-CREDIT into the DATA box, but below the button QTY, and drop it there.

3. Follow steps 15 to 17 from Sample 1.

You should get the above report in a new sheet.

---

If the item NR appears and you want to remove it, follow the steps shown for Sample 1.

If Count of QTY appears instead of Sum of QTY, again follow the steps shown in Sample 1 to change Count of QTY to Sum of QTY.

If Count of RM-CREDIT appears instead of Sum of RM-CREDIT, follow the similar steps as shown under Sample 1 to change Count of RM-CREDIT to Sum of RM-CREDIT.

If then we want to see only the values (without QTY):

1. Click on the small downward arrow-head on the right of Data label.

2. A list will appear.

3. Remove the mark in the box at the left of Sum of QTY.

4. Click OK.

Now the quantity will not be shown. Only values will be shown.

111