All solutions General QuestionsPIVOT TABLE Expert Solution – General Questions on Pivot Table

Excel - General Question on Pivot Table - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc