# Formula In Pivot table calculated field



## gazmoz17 (Dec 16, 2022)

Hi,

I have used calculated fields before however for some reason I cant add a new one.

Is it possible to expand formula box? When I'm clicking insert field I cant see the full formula, its like its hidden on a different line? Its very frustrating why I cant write this formula.

Do I need to use 'field' the single hyphen character?


The below is after double clicking "Carriage" and I get the same clicking "Insert field"
Am I correct in thinking Carriage has to be physically already in my pivot table?...or does that not matter as long as its available in field list?







Many Thanks


----------



## RoryA (Dec 16, 2022)

What happens if you press the down arrow in the formula section?
The field does not have to be already in the pivot table.


----------



## gazmoz17 (Dec 16, 2022)

Hi, usually pressing down key does nothing. If do control A I can copy whats there but I just cant seem to write the formula…keep getting error messages. Some times down key will just give me beginning of my formula which can see anyway and then a blank line underlined? Bit stumped.


----------



## Alex Blakenburg (Dec 16, 2022)

Can you check to see if you have a line feed character in front of "Carriage" and can you live without it ?
If you are unsure, if you go ctrl+A in the formula box and paste it to a cell in Excel does the paste take up 2 lines ?

Unfortunately you can't expand or scroll in the Calculated Field Formula box.
To list out all your pivot table formulas you can use _PivotTable Analyze > Fields, Items & Sets > List Formulas_.
It creates a new sheet, listing the Calculated Fields and Calculated Items and their formulas.
To see that there is a Line Feed character there, you will need to turn on Wrap Text or click in the cell with the formula in it.


----------



## gazmoz17 (Dec 20, 2022)

Hi Alex,

Thanks for the reply much appreciated 👍. Apolgies Im late seeing this. 

Thats the normal name of my field "Carriage" in Field List.

Calculated fields

Your correct, I've wrapped all existing Calc Fields and the new one "ggg" is the only Formula accross 2 lines.

In existing working Calc field why are some fields wrapped in 'field' and otehrs are not?

Is this anything to do with me re-naming the field in my actual pivot table...when it states the field name already exists so I've just added a space and then typed Carriage:





Many Thanks
Gareth


----------



## gazmoz17 (Dec 20, 2022)

The error message I keep getting:


----------



## Alex Blakenburg (Dec 21, 2022)

gazmoz17 said:


> Thats the normal name of my field "Carriage" in Field List.
> 
> Calculated fields
> 
> ...


To have the issue you are having you're actual heading in the underlying table on which the pivot is based for the column Carriage has to have an Line Feed (Alt+Enter) in front of the word Carriage.
Renaming the field in the Pivot will have no effect, the calculated field formulas use the actual Table headings not the Pivot Table headings.

To fix it ideally, go to the table heading Carriage, put your cursor before the "C" and backspace as many times as necessary to get the C to the top left of the formula box.
You will need put Carriage back into the pivot and recreate the calculation once you rename it.

Regarding the single quotes, any field that has spaces in it (and this includes a line feed character), will have single quotes around it to signify it is part of the same field name. Only single word fields will not have the single quotes around it.
In our example TPT is the only single word field and therefore doesn't have the single quotes. Carriage has a Line Feed character in front of it so it needs to put the single quotes around it to show that the Line Feed character is part of the field name.


----------



## gazmoz17 (Dec 21, 2022)

Alex Blakenburg said:


> To have the issue you are having you're actual heading in the underlying table on which the pivot is based for the column Carriage has to have an Line Feed (Alt+Enter) in front of the word Carriage.
> Renaming the field in the Pivot will have no effect, the calculated field formulas use the actual Table headings not the Pivot Table headings.
> 
> To fix it ideally, go to the table heading Carriage, put your cursor before the "C" and backspace as many times as necessary to get the C to the top left of the formula box.
> ...


Argh...perfect thanks again Alex 👍. Was really starting to annoy me!

Makes sense, when you know you know but if you dont...I'd never of figured it out. Thats why these forums are invaluable, difficult to even get my problem accross into a google search and expect anything relevant to return as search result. 

Thanks Alex, take care and have a good Christmas


----------



## Alex Blakenburg (Dec 21, 2022)

You're welcome and wishing you a good Christmas too.


----------

