Excel Data clean up for Pivot table

[peter8848]

Dear All,

I have a three table of data which I would like to use Pivot table to see the sales or qty or price by brand, group,product and date. However, it is not use friendly yet so what is the best way to convert them into a pivot table use friendly data? Or do I use power BI or Power Pivot will help? Thanks in advance.

Cheers,

Peter

Qty	BRAND 1	BRAND 2	BRAND 3	BRAND 4	BRAND 5	BRAND 6	BRAND 7	BRAND 8
GROUP 1	GROUP 2	GROUP 3	GROUP 4	GROUP 5	GROUP 6	GROUP 7	GROUP 8
Product Name 1	Product Name 2	Product Name 3	Product Name 4	Product Name 5	Product Name 6	Product Name 7	Product Name 8
DATE 1	90	72	28	32	56	39	79	61
DATE 2	100	85	59	99	95	89	85	49
DATE 3	33	97	79	21	42	75	100	35
DATE 4	20	70	43	86	81	94	32	56
DATE 5	72	41	47	93	87	34	66	24
DATE 6	67	66	70	64	66	43	78	69
DATE 7	84	68	99	34	77	57	91	60
DATE 8	26	26	46	23	84	57	91	73
Sales	BRAND 1	BRAND 2	BRAND 3	BRAND 4	BRAND 5	BRAND 6	BRAND 7	BRAND 8
GROUP 1	GROUP 2	GROUP 3	GROUP 4	GROUP 5	GROUP 6	GROUP 7	GROUP 8
Product Name 1	Product Name 2	Product Name 3	Product Name 4	Product Name 5	Product Name 6	Product Name 7	Product Name 8
DATE 1	372.00	277.00	303.00	332.00	358.00	420.00	285.00	779.00
DATE 2	936.00	573.00	630.00	449.00	989.00	761.00	594.00	362.00
DATE 3	640.00	301.00	551.00	895.00	377.00	285.00	392.00	428.00
DATE 4	329.00	524.00	809.00	644.00	282.00	944.00	467.00	831.00
DATE 5	899.00	802.00	752.00	340.00	851.00	429.00	605.00	892.00
DATE 6	871.00	967.00	942.00	204.00	309.00	745.00	614.00	977.00
DATE 7	797.00	900.00	404.00	992.00	933.00	292.00	338.00	885.00
DATE 8	545.00	652.00	568.00	940.00	925.00	703.00	259.00	623.00
Price	BRAND 1	BRAND 2	BRAND 3	BRAND 4	BRAND 5	BRAND 6	BRAND 7	BRAND 8
GROUP 1	GROUP 2	GROUP 3	GROUP 4	GROUP 5	GROUP 6	GROUP 7	GROUP 8
Product Name 1	Product Name 2	Product Name 3	Product Name 4	Product Name 5	Product Name 6	Product Name 7	Product Name 8
DATE 1	4.13	3.85	10.82	10.38	6.39	10.77	3.61	12.77
DATE 2	9.36	6.74	10.68	4.54	10.41	8.55	6.99	7.39
DATE 3	19.39	3.10	6.97	42.62	8.98	3.80	3.92	12.23
DATE 4	16.45	7.49	18.81	7.49	3.48	10.04	14.59	14.84
DATE 5	12.49	19.56	16.00	3.66	9.78	12.62	9.17	37.17
DATE 6	13.00	14.65	13.46	3.19	4.68	17.33	7.87	14.16
DATE 7	9.49	13.24	4.08	29.18	12.12	5.12	3.71	14.75
DATE 8	20.96	25.08	12.35	40.87	11.01	12.33	2.85	8.53

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>

 

[sandy666]

something like this?

PivotTable

Date	(All)
Product	Sum of Price	Sum of Qty	Sum of Sales
Product Name 1	105.27​	492​	5389​
Product Name 2	93.71​	525​	4996​
Product Name 3	93.17​	471​	4959​
Product Name 4	141.93​	452​	4796​
Product Name 5	66.85​	588​	5024​
Product Name 6	80.56​	488​	4579​
Product Name 7	52.71​	622​	3554​
Product Name 8	121.84​	427​	5777​
Grand Total	756.04​	4065​	39074​

 

[peter8848]

Hi Sandy,

Sorry with the pivot table we cannot use wide data right? We can only use long data?

I meant

we can use below (Long DATA)

Product Name 1	1/07/2018	Price	90
Product Name 1	2/07/2018	Price	100
Product Name 1	3/07/2018	Price	33
Product Name 1	4/07/2018	Price	20
Product Name 1	5/07/2018	Price	72
Product Name 1	6/07/2018	Price	67
Product Name 1	7/07/2018	Price	84
Product Name 1	8/07/2018	Price	26
Product Name 2	1/07/2018	Price	72
Product Name 2	2/07/2018	Price	85
Product Name 2	3/07/2018	Price	97
Product Name 2	4/07/2018	Price	70
Product Name 2	5/07/2018	Price	41
Product Name 2	6/07/2018	Price	66
Product Name 2	7/07/2018	Price	68
Product Name 2	8/07/2018	Price	26

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

not (Wide DATA)

		Product Name 1	Product Name 2
1/07/2018	Price	90	72
2/07/2018	Price	100	85
3/07/2018	Price	33	97
4/07/2018	Price	20	70
5/07/2018	Price	72	41
6/07/2018	Price	67	66
7/07/2018	Price	84	68
8/07/2018	Price	26	26

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

Right? So how will we quickly convert the wide data into long data?

thanks

 

[sandy666]

I assumed you want use PivotTable but I see you don't
could you post expected result from your first post?

edit:
or better use PowerQuery
transform each table as you wish then merge (or append) and load to the sheet

 

[peter8848]

ADVERTISEMENT

Hi Sandy,

Sorry about the confusion, Yes I want to use the pivot table but if I used the wide data i just found it was not that easy to use as Long data as shown above. Now i figure it out myself for the above to pivot table. But just confirm, for the wide data shown above, could we have more row or column labels for pivot table formation? say adding day, holiday on the column next to date OR add product brand or category in the rows next to product name row. Would it work?

Cheers,

Peter

 

[sandy666]

First of all, your tables are incorrect. The table should have a single header row and possibly labels.
Your vision of PivotTable cuts functionality such as TotalSum / TotalCount and some more, but it's up to you what you want to get.
That's why I asked for an example of how the result should look like

and again, use PowerQuery to prepare your tables like you want (all with the same layout if you want collapse them together) then use (or not) PivotTable

 

[sandy666]

maybe it will help:

• 23 things you should know about Excel Pivot Tables
• How Do Pivot Tables Work?