"Pivot Table or Filter"

[Marwan69]

Hello everyone,

Hope this finds you all well!

I have a long sheet but I'm not sure which sorting feature to use most for my analysis: Pivot table or Filter!
The problem for PV is that it does not give me the value (as is) in the original sheet. It does sum them or count...etc.
On the other hand, the filtering also isn't working properly for me because my data is sorted vertically and headings are below each other, example:

Cost of Resipe Final-PV.xlsx
	A	B	C	D	E
1	Pistachio Waffle - Medium	Quantity	amount	Cost	Sales
2	Waffle Mix	90	gm	0.718	1.5893
3	Banana	95	gm	0.567	1.2544
4	Strawberry	20	gm	1.540	3.4069
5	Kiwi	20	gm	0.240	0.5309
6	Belgium Pistachio Roasted(Nuts)	30	gm	4.560	10.088
7	Soft Sugar(Icing Sugar)	2	mg	0.006	0.0133
8	Brwon Kraft Paper Box with Hinged Lid 25oz	1	pcs	0.138	0.3053
9	Brown Paper Napkin	2	pcs	0.070	0.1549
10	LeDari Black Fork	1	pcs	0.071	0.1571
11	LeDari Black Knife	1	pcs	0.071	0.1571
12	Brown Paper Bag with Flat Handle	1	pcs	0.155	0.3429
13	Pistachio Waffle - Large	Quantity	amount	Cost	Sales
14	waffle mix	180	gm	1.44	3.2637
15	Banana	95	gm	0.567	1.2879
16	Strawberry	35	gm	2.695	6.1216
17	Kiwi	40	gm	0.480	1.0903
18	Belgium Pistachio Roasted(Nuts)	55	gm	8.360	18.989
19	Soft Sugar(Icing Sugar)	3	mg	0.009	0.0204
20	Brwon Kraft Paper Box with Hinged Lid 40oz	1	pcs	0.173	0.393
21	Brown Paper Napkin	2	pcs	0.070	0.159
22	LeDari Black Fork	1	pcs	0.071	0.1613
23	LeDari Black Knife	1	pcs	0.071	0.1613
24	Brown Paper Bag with Flat Handle	1	pcs	0.155	0.3521
25	Belgium Chocolate Waffle - Medium	Quantity	amount	Cost	Sales
26	waffle mix	90	gm	0.72	2.6905
27	Banana	95	gm	0.567	2.1234
28	Strawberry	20	gm	1.540	5.7673
29	Kiwi	20	gm	0.240	0.8988
30	Belgium Brown Chocolate	20	gm	0.860	3.2207
31	Belgium Dark Chocolate	0	gm	0.000	0
32	Belgium White Chocolate	10	gm	0.370	1.3856
33	Soft Sugar(Icing Sugar)	2	mg	0.006	0.0225
34	Brwon Kraft Paper Box with Hinged Lid 25oz	1	pcs	0.138	0.5168
35	Brown Paper Napkin	2	pcs	0.070	0.2621
36	LeDari Black Fork	1	pcs	0.071	0.2659
37	LeDari Black Knife	1	pcs	0.071	0.2659
38	Brown Paper Bag with Flat Handle	1	pcs	0.155	0.5805
Cost of Resipe Final(2)

Cell Formulas
Range	Formula
B3,B27,B15	B3	=189/2
B31	B31	=10*0

So, any idea to choose which is most appropriate for me and how to fix the above issues?

Many Thanks,
Marwan

 

[dougmarkham]

Hi Marwan,

Pivot tables are designed to group data; whereas, filters do not. If you want to add totals, you can either do so at the end of a table or have another table to one side that uses formulas to display totals.
In general, if you just want to see certain data ranges or focus on specific dates etc., it's better to add a dynamic table to encapsulate the full dataset and then apply filters to the data (i.e., not just the Filter on the Data Tab). In your case, I suggest you add another column to the left of col A, and put your headers e.g., pistasio waffle large etc in the new column

Pistachio Waffle - Medium	Waffle Mix
Pistachio Waffle - Medium	Banana
Pistachio Waffle - Medium	Strawberry

etc

Pivot tables are more useful when the purpose is to e.g., combine all the consignment costs of one purchase order number (where one number is on many lines, and you want the sum of cost).
The below link takes you to a YouTube video introducing Pivot Tables. The weblinks on that video are an excellent resource to familiarize yourself with what they can do.
Introduction to Pivot tables

Kind regards,

Doug.