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.
