Pivot Table

S

Sineadd

Board Regular
Joined
Apr 24, 2019
Messages
60
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi


I need some help setting up a pivot table. I need to compare sales figures from 2 seperate years for example sales figures from week 1 2016 against sales figures for week 1 2017 for each store. I then need to be able to show the monetary difference between each year and the % difference also. I need to this for 14 stores. Can anybody point me in the right direction or advise if i have the table in the right format to be able to create pivot required.

I have sample data below of the way I have it set up

[TABLE="width: 377"]
<tbody>[TR]
[TD][TABLE="width: 377"]
<tbody>[TR]
[TD]Week No[/TD]
[TD]Date[/TD]
[TD]Store[/TD]
[TD]Sales Values[/TD]
[TD]Sales Returns[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2016[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2016[/TD]
[TD]Store 1[/TD]
[TD]14556.35[/TD]
[TD]12847.35[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2016[/TD]
[TD]Store 1[/TD]
[TD]27535.6[/TD]
[TD]13599.1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2016[/TD]
[TD]Store 1[/TD]
[TD]17773.3[/TD]
[TD]9956.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2016[/TD]
[TD]Store 1[/TD]
[TD]23932.68[/TD]
[TD]16530.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2016[/TD]
[TD]Store 1[/TD]
[TD]7260.6[/TD]
[TD]5942.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2017[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2017[/TD]
[TD]Store 1[/TD]
[TD]24477.8[/TD]
[TD]20783.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14059.9[/TD]
[TD]11858.599[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14200.69[/TD]
[TD]14770.699[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2017[/TD]
[TD]Store 1[/TD]
[TD]6198.5[/TD]
[TD]7744.2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2017[/TD]
[TD]Store 1[/TD]
[TD]18234.68[/TD]
[TD]15949.85[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]07/01/2017[/TD]
[TD]Store 1[/TD]
[TD]28103[/TD]
[TD]17144[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ideally would like data to display like the below. Any help greatly appreciated :-)

[TABLE="width: 357"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 357"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]Variance[/TD]
[TD]% Variance[/TD]
[/TR]
[TR]
[TD]Sales Value [/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]-400[/TD]
[TD]-80%[/TD]
[/TR]
[TR]
[TD]Sales Returns[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]-100[/TD]
[TD]-50%[/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks
Sinead
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Re: Pivot Table Help!!

Sineadd said:
Hi


I need some help setting up a pivot table. I need to compare sales figures from 2 seperate years for example sales figures from week 1 2016 against sales figures for week 1 2017 for each store. I then need to be able to show the monetary difference between each year and the % difference also. I need to this for 14 stores. Can anybody point me in the right direction or advise if i have the table in the right format to be able to create pivot required.

I have sample data below of the way I have it set up

[TABLE="width: 377"]
<tbody>[TR]
[TD][TABLE="width: 377"]
<tbody>[TR]
[TD]Week No[/TD]
[TD]Date[/TD]
[TD]Store[/TD]
[TD]Sales Values[/TD]
[TD]Sales Returns[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2016[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2016[/TD]
[TD]Store 1[/TD]
[TD]14556.35[/TD]
[TD]12847.35[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2016[/TD]
[TD]Store 1[/TD]
[TD]27535.6[/TD]
[TD]13599.1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2016[/TD]
[TD]Store 1[/TD]
[TD]17773.3[/TD]
[TD]9956.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2016[/TD]
[TD]Store 1[/TD]
[TD]23932.68[/TD]
[TD]16530.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2016[/TD]
[TD]Store 1[/TD]
[TD]7260.6[/TD]
[TD]5942.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2017[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2017[/TD]
[TD]Store 1[/TD]
[TD]24477.8[/TD]
[TD]20783.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14059.9[/TD]
[TD]11858.599[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14200.69[/TD]
[TD]14770.699[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2017[/TD]
[TD]Store 1[/TD]
[TD]6198.5[/TD]
[TD]7744.2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2017[/TD]
[TD]Store 1[/TD]
[TD]18234.68[/TD]
[TD]15949.85[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]07/01/2017[/TD]
[TD]Store 1[/TD]
[TD]28103[/TD]
[TD]17144[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ideally would like data to display like the below. Any help greatly appreciated :-)

[TABLE="width: 357"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 357"]
<tbody>[TR]
[TD][/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]Variance[/TD]
[TD]% Variance[/TD]
[/TR]
[TR]
[TD]Sales Value[/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]-400[/TD]
[TD]-80%[/TD]
[/TR]
[TR]
[TD]Sales Returns[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]-100[/TD]
[TD]-50%[/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks
Sinead
Click to expand...

So i have the below sorted but can anyone show me hoe to get the variance and % Variance

[TABLE="width: 470"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Store[/TD]
[TD]Castlebar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week No[/TD]
[TD](Multiple Items)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Values[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]Variance[/TD]
[TD]Variance %[/TD]
[/TR]
[TR]
[TD]Sum of Sales Values[/TD]
[TD="align: right"]68562.5[/TD]
[TD="align: right"]79189.43[/TD]
[TD="align: right"]147751.93[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of Sales Returns[/TD]
[TD="align: right"]59303.93[/TD]
[TD="align: right"]70908.86[/TD]
[TD="align: right"]130212.79[/TD]
[TD]






[/TD]
[/TR]
</tbody>[/TABLE]
When I go to the field settings for the VAriance column and try to change the field settings to variance I am not sure what to choose to get the desired results


Any help appreceiated


Thanks
Sinead
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Need Help - Pivot Table - Months/Days
Replies
1
Views
698
Sineadd
S
M
  • Question Question
Transpose data ready for pivot table
Replies
1
Views
620
sandy666
sandy666
S
  • Question Question
Calculated Field in Pivot Tables
Replies
6
Views
824
sophab
S
B
  • Question Question
Some help creating a tracker...
Replies
1
Views
481
oldbrewer
O
N
  • Question Question
Conditional formatting in a pivot table
Replies
0
Views
443
naylorpsu
N

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top