How to change Excel Pivot table “Report Filter”s values cell formatting
up vote
4
down vote
favorite
My Excel is in Finnish, but don't let that bother you...
First Report Filter "Kupi" has only number values in my source table, for example 643203, 3533, 253244, etc. How ever in Pivot's "Report Filter" all those values are converted to date values MONTH yyyy. How do I reformat the filter values to respect the original cell formatting?!
The same problem is with actual date values in my source table when using "Report Filter" in Pivot table. In my source data my dates are in format: dd.mm.yyyy and for some reason in Pivot's "Report Filter" all dates are shown in MONTH yyyy-format?! Why is that and what do I need to do to fix this?
ADDED MORE INFORMATION:
Structure for this Excel workbook is: one source table and about hundred pivot tables from that one source.
I use this Workbook daily and all these Pivot tables have worked before correctly. About a week ago when I opened the workbook all numeric data -cells had changed into date-format from no apparent reason?! I tried to restore to older versions of the document, but no bonus. Then I just manually corrected cell formatting of the source table and it looked just like before, but for some reason after that incident none of the pivot tables have worked correctly and even if I create new Pivot tables from my source table - they still have corrupted cell formatting, which do not respect the source table...
Below you can see a clip from my source table.
Below you can see that even though cell format in source table for column "Kupi" has been set to "Number", in Pivot table it is shown as date and there seems to be no way edit cell formatting in Pivot's Filters.
Below you can see how Column "Tilattu" is shown in Pivot's filters, even though in source data cell format is set to be "dd.m.yyyy". As you can see it is impossible to filter for a specific date!
EVEN MORE INFORMATION ADDED:
I've been testing workbook for a couple of hours now and it seems that no matter what I add to Pivot table, all cell formats defaults to "date"!? This is so frustrating.
Example:
- I created new Pivot table into new sheet.
- I filtered there date from november 2011
- I have two currency columns and sum of all rows in the last row of the Pivot table
- I selected an empty cell and wrote "=" clicked both currency sums and pressed enter to get the total into to empty cell outside of the Pivot table
- Even that cell in new sheet formatted into date!!!
microsoft-excel pivot-table
asked Oct 31 '13 at 3:43
Damiqib
7816
add a comment |
up vote
4
down vote
favorite
My Excel is in Finnish, but don't let that bother you...
First Report Filter "Kupi" has only number values in my source table, for example 643203, 3533, 253244, etc. How ever in Pivot's "Report Filter" all those values are converted to date values MONTH yyyy. How do I reformat the filter values to respect the original cell formatting?!
The same problem is with actual date values in my source table when using "Report Filter" in Pivot table. In my source data my dates are in format: dd.mm.yyyy and for some reason in Pivot's "Report Filter" all dates are shown in MONTH yyyy-format?! Why is that and what do I need to do to fix this?
ADDED MORE INFORMATION:
Structure for this Excel workbook is: one source table and about hundred pivot tables from that one source.
I use this Workbook daily and all these Pivot tables have worked before correctly. About a week ago when I opened the workbook all numeric data -cells had changed into date-format from no apparent reason?! I tried to restore to older versions of the document, but no bonus. Then I just manually corrected cell formatting of the source table and it looked just like before, but for some reason after that incident none of the pivot tables have worked correctly and even if I create new Pivot tables from my source table - they still have corrupted cell formatting, which do not respect the source table...
Below you can see a clip from my source table.
Below you can see that even though cell format in source table for column "Kupi" has been set to "Number", in Pivot table it is shown as date and there seems to be no way edit cell formatting in Pivot's Filters.
Below you can see how Column "Tilattu" is shown in Pivot's filters, even though in source data cell format is set to be "dd.m.yyyy". As you can see it is impossible to filter for a specific date!
EVEN MORE INFORMATION ADDED:
I've been testing workbook for a couple of hours now and it seems that no matter what I add to Pivot table, all cell formats defaults to "date"!? This is so frustrating.
Example:
- I created new Pivot table into new sheet.
- I filtered there date from november 2011
- I have two currency columns and sum of all rows in the last row of the Pivot table
- I selected an empty cell and wrote "=" clicked both currency sums and pressed enter to get the total into to empty cell outside of the Pivot table
- Even that cell in new sheet formatted into date!!!
microsoft-excel pivot-table
asked Oct 31 '13 at 3:43
Damiqib
7816
How are the cell number format set? And the locales?
– Braiam
Nov 2 '13 at 14:05
@Braiam: Added more information to my original post.
– Damiqib
Nov 2 '13 at 20:36
An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
– Luke
Nov 5 '13 at 2:32
add a comment |
up vote
4
down vote
favorite
up vote
4
down vote
favorite
My Excel is in Finnish, but don't let that bother you...
First Report Filter "Kupi" has only number values in my source table, for example 643203, 3533, 253244, etc. How ever in Pivot's "Report Filter" all those values are converted to date values MONTH yyyy. How do I reformat the filter values to respect the original cell formatting?!
The same problem is with actual date values in my source table when using "Report Filter" in Pivot table. In my source data my dates are in format: dd.mm.yyyy and for some reason in Pivot's "Report Filter" all dates are shown in MONTH yyyy-format?! Why is that and what do I need to do to fix this?
ADDED MORE INFORMATION:
Structure for this Excel workbook is: one source table and about hundred pivot tables from that one source.
I use this Workbook daily and all these Pivot tables have worked before correctly. About a week ago when I opened the workbook all numeric data -cells had changed into date-format from no apparent reason?! I tried to restore to older versions of the document, but no bonus. Then I just manually corrected cell formatting of the source table and it looked just like before, but for some reason after that incident none of the pivot tables have worked correctly and even if I create new Pivot tables from my source table - they still have corrupted cell formatting, which do not respect the source table...
Below you can see a clip from my source table.
Below you can see that even though cell format in source table for column "Kupi" has been set to "Number", in Pivot table it is shown as date and there seems to be no way edit cell formatting in Pivot's Filters.
Below you can see how Column "Tilattu" is shown in Pivot's filters, even though in source data cell format is set to be "dd.m.yyyy". As you can see it is impossible to filter for a specific date!
EVEN MORE INFORMATION ADDED:
I've been testing workbook for a couple of hours now and it seems that no matter what I add to Pivot table, all cell formats defaults to "date"!? This is so frustrating.
Example:
- I created new Pivot table into new sheet.
- I filtered there date from november 2011
- I have two currency columns and sum of all rows in the last row of the Pivot table
- I selected an empty cell and wrote "=" clicked both currency sums and pressed enter to get the total into to empty cell outside of the Pivot table
- Even that cell in new sheet formatted into date!!!
microsoft-excel pivot-table
asked Oct 31 '13 at 3:43
Damiqib
7816
My Excel is in Finnish, but don't let that bother you...
First Report Filter "Kupi" has only number values in my source table, for example 643203, 3533, 253244, etc. How ever in Pivot's "Report Filter" all those values are converted to date values MONTH yyyy. How do I reformat the filter values to respect the original cell formatting?!
The same problem is with actual date values in my source table when using "Report Filter" in Pivot table. In my source data my dates are in format: dd.mm.yyyy and for some reason in Pivot's "Report Filter" all dates are shown in MONTH yyyy-format?! Why is that and what do I need to do to fix this?
ADDED MORE INFORMATION:
Structure for this Excel workbook is: one source table and about hundred pivot tables from that one source.
I use this Workbook daily and all these Pivot tables have worked before correctly. About a week ago when I opened the workbook all numeric data -cells had changed into date-format from no apparent reason?! I tried to restore to older versions of the document, but no bonus. Then I just manually corrected cell formatting of the source table and it looked just like before, but for some reason after that incident none of the pivot tables have worked correctly and even if I create new Pivot tables from my source table - they still have corrupted cell formatting, which do not respect the source table...
Below you can see a clip from my source table.
Below you can see that even though cell format in source table for column "Kupi" has been set to "Number", in Pivot table it is shown as date and there seems to be no way edit cell formatting in Pivot's Filters.
Below you can see how Column "Tilattu" is shown in Pivot's filters, even though in source data cell format is set to be "dd.m.yyyy". As you can see it is impossible to filter for a specific date!
EVEN MORE INFORMATION ADDED:
I've been testing workbook for a couple of hours now and it seems that no matter what I add to Pivot table, all cell formats defaults to "date"!? This is so frustrating.
Example:
- I created new Pivot table into new sheet.
- I filtered there date from november 2011
- I have two currency columns and sum of all rows in the last row of the Pivot table
- I selected an empty cell and wrote "=" clicked both currency sums and pressed enter to get the total into to empty cell outside of the Pivot table
- Even that cell in new sheet formatted into date!!!
microsoft-excel pivot-table
microsoft-excel pivot-table
asked Oct 31 '13 at 3:43
Damiqib
7816
asked Oct 31 '13 at 3:43
Damiqib
7816
edited Nov 2 '13 at 21:36
asked Oct 31 '13 at 3:43
Damiqib
7816
asked Oct 31 '13 at 3:43
Damiqib
7816
asked Oct 31 '13 at 3:43
Damiqib
7816
7816
How are the cell number format set? And the locales?
– Braiam
Nov 2 '13 at 14:05
@Braiam: Added more information to my original post.
– Damiqib
Nov 2 '13 at 20:36
An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
– Luke
Nov 5 '13 at 2:32
add a comment |
How are the cell number format set? And the locales?
– Braiam
Nov 2 '13 at 14:05
@Braiam: Added more information to my original post.
– Damiqib
Nov 2 '13 at 20:36
An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
– Luke
Nov 5 '13 at 2:32
How are the cell number format set? And the locales?
– Braiam
Nov 2 '13 at 14:05
How are the cell number format set? And the locales?
– Braiam
Nov 2 '13 at 14:05
@Braiam: Added more information to my original post.
– Damiqib
Nov 2 '13 at 20:36
@Braiam: Added more information to my original post.
– Damiqib
Nov 2 '13 at 20:36
An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
– Luke
Nov 5 '13 at 2:32
An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
– Luke
Nov 5 '13 at 2:32
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
These numbers look like typical "sequential serial number" format of the Date function, meaning
the number of the day.
The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format
since this is not a standard display format.
Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".
If you wish to change the display format of a cell, see
Microsoft's Format a date the way you want.
We need to know more about your datasheets to give better advice.
answered Nov 2 '13 at 15:59
harrymc
250k11258555
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
These numbers look like typical "sequential serial number" format of the Date function, meaning
the number of the day.
The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format
since this is not a standard display format.
Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".
If you wish to change the display format of a cell, see
Microsoft's Format a date the way you want.
We need to know more about your datasheets to give better advice.
answered Nov 2 '13 at 15:59
harrymc
250k11258555
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
add a comment |
up vote
0
down vote
These numbers look like typical "sequential serial number" format of the Date function, meaning
the number of the day.
The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format
since this is not a standard display format.
Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".
If you wish to change the display format of a cell, see
Microsoft's Format a date the way you want.
We need to know more about your datasheets to give better advice.
answered Nov 2 '13 at 15:59
harrymc
250k11258555
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
add a comment |
up vote
0
down vote
up vote
0
down vote
These numbers look like typical "sequential serial number" format of the Date function, meaning
the number of the day.
The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format
since this is not a standard display format.
Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".
If you wish to change the display format of a cell, see
Microsoft's Format a date the way you want.
We need to know more about your datasheets to give better advice.
answered Nov 2 '13 at 15:59
harrymc
250k11258555
These numbers look like typical "sequential serial number" format of the Date function, meaning
the number of the day.
The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format
since this is not a standard display format.
Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".
If you wish to change the display format of a cell, see
Microsoft's Format a date the way you want.
We need to know more about your datasheets to give better advice.
answered Nov 2 '13 at 15:59
harrymc
250k11258555
answered Nov 2 '13 at 15:59
harrymc
250k11258555
answered Nov 2 '13 at 15:59
harrymc
250k11258555
answered Nov 2 '13 at 15:59
harrymc
250k11258555
250k11258555
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
add a comment |
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
– Damiqib
Nov 2 '13 at 20:35
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
– harrymc
Nov 2 '13 at 21:45
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f667648%2fhow-to-change-excel-pivot-table-report-filters-values-cell-formatting%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
How are the cell number format set? And the locales?
– Braiam
Nov 2 '13 at 14:05
@Braiam: Added more information to my original post.
– Damiqib
Nov 2 '13 at 20:36
An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
– Luke
Nov 5 '13 at 2:32