**Question description:**

*This user has given permission to use the problem statement for this blog.*

I want to edit added calculated fields on a pivot table. It is possible to manually enter values to calculated fields on a pivot table? If not, is it possible to insert an editable column on a pivot table?

Solved by Z. S. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
11/06/2018 - 07:26

Hello and welcome to Got it! ;)

User
11/06/2018 - 07:26

Hi

User
11/06/2018 - 07:26

Can you please quickly answer my question

User
11/06/2018 - 07:26

I want to edit added calculated fields on a pivot table. It is possible to manually enter values to calculated fields on a pivot table? If not, is it possible to insert an editable column on a pivot table?

Excelchat Expert
11/06/2018 - 07:27

Unfortunately, we cannot edit fields in a pivot table, nor can we add an editable column on a pivot table. Best would always be add another calculated field. :)

User
11/06/2018 - 07:27

yes

User
11/06/2018 - 07:27

I added a calculated field

User
11/06/2018 - 07:27

set the formula to equal 0

User
11/06/2018 - 07:27

but I want then to change in each cell the 0 to a value

User
11/06/2018 - 07:28

is that possible?

Excelchat Expert
11/06/2018 - 07:28

Okay I see. You can edit the formula in that calculated field, to allow only changing that particular cell. You will need to specify certain criteria in which that cell will only qualify. :)

User
11/06/2018 - 07:28

is there a formula that output what you enter in the cell?

User
11/06/2018 - 07:28

Can you be more precise?

User
11/06/2018 - 07:28

I don't get it

User
11/06/2018 - 07:29

what would I have to write

Excelchat Expert
11/06/2018 - 07:29

For example, we have a calculated cell that always returns 0.

User
11/06/2018 - 07:29

yes

Excelchat Expert
11/06/2018 - 07:30

Okay, let's look at the document preview for a quick sample. :)

User
11/06/2018 - 07:31

can you type the formula?

User
11/06/2018 - 07:31

we only have 14 min left

Excelchat Expert
11/06/2018 - 07:32

The formula will depend on the details of your pivot table.

User
11/06/2018 - 07:32

ok be quick please

Excelchat Expert
11/06/2018 - 07:32

It can depend on the row data, or any of the values computed in the pivot itself.

User
11/06/2018 - 07:32

we dont need that many fields

User
11/06/2018 - 07:32

just a calculated field

User
11/06/2018 - 07:32

and the formual to update cells

Excelchat Expert
11/06/2018 - 07:33

Okay.... For any calculated field, the formula is customized right?

User
11/06/2018 - 07:33

you tell me

Excelchat Expert
11/06/2018 - 07:33

Yes, it is. :)

Excelchat Expert
11/06/2018 - 07:33

Basically, you enter a formula with ample criteria using an IF (possible nested IF) statement.

User
11/06/2018 - 07:33

I want tobe able to update each cell I want from 0 to something else in the calculated field

Excelchat Expert
11/06/2018 - 07:34

If you wish to directly edit the value on the pivot table under the calculated field, that can't be possible.

User
11/06/2018 - 07:34

I know

Excelchat Expert
11/06/2018 - 07:34

We can only modify the formula for the calculated field.

User
11/06/2018 - 07:34

so what would the formula be

Excelchat Expert
11/06/2018 - 07:34

A sample formula would be:

Excelchat Expert
11/06/2018 - 07:35

=IF(Power>100000,Total*1000,0)

User
11/06/2018 - 07:35

no no no

User
11/06/2018 - 07:35

that's not what I asked

User
11/06/2018 - 07:35

that's easy

User
11/06/2018 - 07:36

I don't want the value to be based on other colums of the pivot

Excelchat Expert
11/06/2018 - 07:36

Okay, I see. Where do you need to base it then? :)

Excelchat Expert
11/06/2018 - 07:36

From the source data of the pivot table? :)

User
11/06/2018 - 07:36

I want to be able to set values on each individual cell of the new calculated field

User
11/06/2018 - 07:36

example

User
11/06/2018 - 07:37

formula = 0

User
11/06/2018 - 07:37

it will set everything to 0

User
11/06/2018 - 07:37

I want to be able to set for example

User
11/06/2018 - 07:37

C7 = 300

User
11/06/2018 - 07:37

C8= 2

User
11/06/2018 - 07:37

C190= 40

Excelchat Expert
11/06/2018 - 07:38

Oh I see. Unfortunately, that can't be done with pivoti tables. As previously stated, we can't manually edit data in the pivot tables.

User
11/06/2018 - 07:38

so what would be my alternative

User
11/06/2018 - 07:38

Can I create columns next to it

Excelchat Expert
11/06/2018 - 07:38

In your source data (the source of your pivot), you can create a custom column.

User
11/06/2018 - 07:39

and have it offset lines

Excelchat Expert
11/06/2018 - 07:39

You can also do that. However, the columns won't be part of the pivot table. :)

User
11/06/2018 - 07:39

when the pivot table has more data entries

Excelchat Expert
11/06/2018 - 07:39

I would suggest that you instead create a custom column in your source data.

User
11/06/2018 - 07:39

Can I do that and have the columns follow the added/deleted lines of the pivot

User
11/06/2018 - 07:39

?

User
11/06/2018 - 07:40

I can create it on the data source because it is involving monthly targets

Excelchat Expert
11/06/2018 - 07:40

On your proposal, that can't be done as well. That's why I propose you can just create a custom column in your source data. This would be more flexible and dynamic.

User
11/06/2018 - 07:41

and I have already dates on this pivot

Excelchat Expert
11/06/2018 - 07:41

The weakness of just adding data just beside your pivot table is that it cannot trace the changes done in your pivot table. Like if you add columns/values/rows, it won't adjust correctly. :)

User
11/06/2018 - 07:41

yes I know

User
11/06/2018 - 07:41

that's the issue

User
11/06/2018 - 07:41

each time I refresh it will offset values

Excelchat Expert
11/06/2018 - 07:42

So, it would be best if we could find a way to create a custom column in your source data, which wouldn't disrupt your original data, but will also help in what you need for your pivot. :)

User
11/06/2018 - 07:42

ok

User
11/06/2018 - 07:42

Thanks

Excelchat Expert
11/06/2018 - 07:43

Sure thing! :)

Excelchat Expert
11/06/2018 - 07:43

Any more questions by the way with the pivot table? :)

User
11/06/2018 - 07:43

no thanks

Excelchat Expert
11/06/2018 - 07:44

Just a recommendation, you can always place the custom column on column A itself. In this way, you can just hide it, and just have it be shown with the pivot table. :)

User
11/06/2018 - 07:44

what do you mena

User
11/06/2018 - 07:44

mean

Excelchat Expert
11/06/2018 - 07:44

This won't "distort" the visual aspect of your source data, but it will greatly help you in the customized data you need.

Excelchat Expert
11/06/2018 - 07:45

I mean, you can insert a blank column on the left most part of your source data.

Excelchat Expert
11/06/2018 - 07:45

This will thus move the initial data on column A to column B.

Excelchat Expert
11/06/2018 - 07:45

Then you can enter your custom data in there. In your pivot, you can cover this and show the custom data. :)

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*