# Excel Pivot Table Calculated Fields

#### AlexaS

##### Board Regular
Hello,

I've looked for this before, but it's come up again at my job and now I need to find a definitive answer (hopefully in the affirmative).

I have data that is linked to a pivot table and on the side of the pivot table are calculated fields (we call them "bolt-on fields"). We want to try to add these fields directly into the pivot table.

EXAMPLES
Data Table:

 Fiscal Year End November Sales November Cost 2014 5 2 2014 5 2 2014 5 2 2014 5 2 2014 5 2 2015 6 3 2015 6 3 2015 6 3 2015 6 3 2015 6 3

<tbody>
</tbody>

Pivot Table:
D & E are manually typed in formulas to the right of the pivot table currently

 A B C D E 1 Fiscal Year FY15 v. FY14 GM% Diff x Net Sales 2 Data 2014 2015 3 Sumof Net Sales November 25 30 = C3 - B3 4 Sumof Cost November 10 15 = C4 - B4 5 Gross Margin % = (C3 - C4) / C3 = C3 * D5

<tbody>
</tbody>

The problem is the way that the data is set up. I can't figure out how to do the calculations in D & E and actually get results. There is nothing in the data table column "November Sales" or "November Cost" that tell it which year it's in. Is there a way with a calculated field that I can say:

= 'November Sales' in 'Fiscal Year' 2015 - 'November Sales' in 'Fiscal Year' 2014

Even if I first have to set up a field for "November Sales in Fiscal Year 2015", is there a way to do this? I'm banging my head against a brick wall trying to find it. And the whole reason is because we want it to be "pivot-able". I dislike pivot tables severely.

Any help would be greatly appreciated.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Pup Denab

##### Active Member
HI AlexaS
In this situation, I would recommend doing the calculations in the data set, not in the pivot table, then you can simply view the results in the selected format required, as a pivot table, you will most likely use sumif or sumifs, as that is what the pivot table is doing, also it uses countif & countifs, does that help, or I can try to do it for you if required

#### AlexaS

##### Board Regular
Thank you for your response. That is the other avenue I'm attempting. Although it is posing it's own problems, as the data set is set up as a table. So my problem with that is trying to make the formula (which pulls a table heading) dynamic so that I don't have to change it every month.

#### RoryA

##### MrExcel MVP, Moderator
You can do D3 and D4 in the pivot using calculated items, but not the others, I'm afraid.

Replies
5
Views
281
Replies
3
Views
123
Replies
1
Views
45
Replies
2
Views
44
Replies
6
Views
542

1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

### 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.

### Which adblocker are you using?

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

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