Pivot Table - Changes

  • Thread starter Thread starter Neon520
  • Start date Start date
N

Neon520

Hi Everyone,

I just discover that I can use Pivot Table in Excel to help me manage me
data easier in a table view format.
However, since I'm new with this feature in Excel, I'm having a few questions:
1. I notice that Pivot Table doesn't allow me to delete the data directly in
Pivot Table, but it does allow me to make changes to the data. I thing is I
presume that Excel will make that changes that I made in Pivot Table to the
original data sheet, but as I found out Excel doesn't do that. and I can't
reverse the changes that I made in Pivot Table to the original data sheet
information, so right now my Pivot Table doesn't show what necessarily on my
data sheet, which is not what I want.
Is there a way to make Excel Automatically change the information in the
data sheet as I make changes in the Pivot Table?
If this is not possible, how can I lock Pivot Table so that I won't make any
changes that won't "sync" with the data sheet AND at the same time allow the
Refresh Data Button available when I need to refresh the Pivot Table after
making changes in the data sheet without having to close and reopen file?

2. I notice that the changes in formatting (font size, font type, and cell
border) doesn't stay with Pivot Table as I change the "PAGE" criteria of
Pivot Table. It keeps revert back to the original formatting. Is there a
work around to this?

Sorry for the long post; I just want to make sure to make myself clear.

Thank you very much,
Neon520
 
Hi

The answer to your second question is to right click on a cell in the
PT>Table Options>uncheck Autoformat Table.

The first part of your question I don't understand.
Unless you use VBA code, you cannot alter the data in a Pivot table - so
there is no way that there are any changes to be written back to the source
data.

--
Regards
Roger Govier

Neon520 said:
Hi Everyone,

I just discover that I can use Pivot Table in Excel to help me manage me
data easier in a table view format.
However, since I'm new with this feature in Excel, I'm having a few
questions:
1. I notice that Pivot Table doesn't allow me to delete the data directly
in
Pivot Table, but it does allow me to make changes to the data. I thing is
I
presume that Excel will make that changes that I made in Pivot Table to
the
original data sheet, but as I found out Excel doesn't do that. and I can't
reverse the changes that I made in Pivot Table to the original data sheet
information, so right now my Pivot Table doesn't show what necessarily on
my
data sheet, which is not what I want.
Is there a way to make Excel Automatically change the information in the
data sheet as I make changes in the Pivot Table?
If this is not possible, how can I lock Pivot Table so that I won't make
any
changes that won't "sync" with the data sheet AND at the same time allow
the
Refresh Data Button available when I need to refresh the Pivot Table after
making changes in the data sheet without having to close and reopen file?

2. I notice that the changes in formatting (font size, font type, and cell
border) doesn't stay with Pivot Table as I change the "PAGE" criteria of
Pivot Table. It keeps revert back to the original formatting. Is there a
work around to this?

Sorry for the long post; I just want to make sure to make myself clear.

Thank you very much,
Neon520

__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi Roger,

I tried your suggestion of unchecking Autoformat Table option but it doesn't
work.
The PT still change every time I go to the dropdown menu to change the
criteria I want PT to view.
Does this have anything to do with the fact that I Select All in the sheet
that contain PT and change the font type, size and cell border manually?

Thank you for clarification of question one.
According to you, the data in PT can't be altered, but could you give me any
sort of explanation as to why I can change the text in the PT? And as you
said and as I found out the hard way, the information that I make changes on
PT doesn't get written back to the data source. This is fine with me,
however, since I'm happened to be able to make changes on PT (I'm not sure if
this is normal/the way it should be) Is there a way to lock the PT without
having to lock the Criteria dropdown list on the top so that I can change the
Criteria when need to. I tried Tool > Protection > Protection, but this
locks down everything, even after I change the Format > Cell > Uncheck
Protection for the Criteria cell.

Thank you very much,
Neon520
 
Hi

You should right click PT>Table Options>uncheck Autofit columns > Check
Preserve cell Formatting

To Format the PT>options tab>Actions>Select>Enable selection (the border
will go orange on the icon when it is On)
Then to do the formatting, hover either to the left or above relevant fields
or groupings until you see a solid black arrow and click.
This will select all of the relevant data e.g. just clicking on one subtotal
in this manner, will select all subtotals).
Apply your formatting.

The formatting will now be preserved when you refresh the table.

It is a design feature to be able to change Field labels. It is also a
design feature that any such changes are not carried back to the source
table. For example a column heading in the source data might be Sales of
Widgets.
In the PT, if that was being summed, it would default to Sum of Sales of
Widgets. (A field title in the PT cannot be the same as in the Source data.)

You might decide you just wanted the heading in the PT to just be Sales, so
this you can do for Display purposes.
If you did want it to say Sales of Widgets then you would have to prepend or
append a space to the title, to make it diffent to the source name e.g.
"Sales of Widgets "

If you decided instead to change the name in the Source data and then
refresh, all of those renamed fields will be removed from the PT (quite
rightly) as they are "new" fields as far as the PT is concerned, and it (the
PT) has no idea of where you wish to allocate them. Once re-allocated, they
will remain in place (unless you go an rename the source again).

You can prevent users altering things on a PT with the use of VBA.

--
Regards
Roger Govier

Neon520 said:
Hi Roger,

I tried your suggestion of unchecking Autoformat Table option but it
doesn't
work.
The PT still change every time I go to the dropdown menu to change the
criteria I want PT to view.
Does this have anything to do with the fact that I Select All in the sheet
that contain PT and change the font type, size and cell border manually?

Thank you for clarification of question one.
According to you, the data in PT can't be altered, but could you give me
any
sort of explanation as to why I can change the text in the PT? And as you
said and as I found out the hard way, the information that I make changes
on
PT doesn't get written back to the data source. This is fine with me,
however, since I'm happened to be able to make changes on PT (I'm not sure
if
this is normal/the way it should be) Is there a way to lock the PT without
having to lock the Criteria dropdown list on the top so that I can change
the
Criteria when need to. I tried Tool > Protection > Protection, but this
locks down everything, even after I change the Format > Cell > Uncheck
Protection for the Criteria cell.

Thank you very much,
Neon520




__________ Information from ESET Smart Security, version of virus
signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4836 (20100204) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi Roger,

Thank you for the detail instruction on the formatting part.
I don't know why they have to make this zig-zag way of just dealing with
formatting a PT, can't they keep it straight forward like the regular table
formatting?

For second part, I think you misunderstand my intension.
It makes sense for user to be able to change The FIELD LABEL or HEADER, but
what about the data itself.
I somehow was able to change the DATA in the rows and of course the Source
Data doesn't get change the same way as I change in the PT. I did refresh
(the exclamation mark button) the PT just to see if the correct data got
transfer back to the PT, you know, just like when you update any information
in the Source Data you can then Refresh the PT for it to have the latest
information. But it didn't change, so now my Source Data for that particular
record has different information from what it's shown in the PT.

Any idea?

Thank you very much,
Neon520
 
Hi

If you want to use one of the standard formatting options from the Gallery,
then you can just choose them.

I don't understand you saying you can change the data.
Unless you have some VBA code running to allow it, you should get an error
message telling you that you cannot alter the table.

If you want, you can send me a copy of the workbook direct.
To mail direct
roger at technology4u dot co dot uk
Change the at and dot to make a valid email address.

--
Regards
Roger Govier

Neon520 said:
Hi Roger,

Thank you for the detail instruction on the formatting part.
I don't know why they have to make this zig-zag way of just dealing with
formatting a PT, can't they keep it straight forward like the regular
table
formatting?

For second part, I think you misunderstand my intension.
It makes sense for user to be able to change The FIELD LABEL or HEADER,
but
what about the data itself.
I somehow was able to change the DATA in the rows and of course the Source
Data doesn't get change the same way as I change in the PT. I did refresh
(the exclamation mark button) the PT just to see if the correct data got
transfer back to the PT, you know, just like when you update any
information
in the Source Data you can then Refresh the PT for it to have the latest
information. But it didn't change, so now my Source Data for that
particular
record has different information from what it's shown in the PT.

Any idea?

Thank you very much,
Neon520





__________ Information from ESET Smart Security, version of virus
signature database 4839 (20100205) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4839 (20100205) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top