Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Shows Only Zeros
My pivot table displays zeros instead of numbers. I have tried re-formatting
the numbers to "General" and "Number" and "Text". I have tried changing "Count" to "Sum". I can see the correct numbers in the spreadsheet but not in the pivot table. Please advise. DOUG ECKERT |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Shows Only Zeros
Changing the format of a cell doesn't change the value.
If your data is in A2:A99, then try this in two empty cells: =counta(a2:a99) and =count(a2:a99) =counta() will count the number of cells that have something in them. =count() will count the number of cells that have a number in them. If those formulas don't evaluate to the same thing (especially if =count() returns a 0), then you have more to do to convert the values to real numbers. Find an empty cell edit|copy Select the range to fix Format as General edit|paste special|values This should convert those text numbers to number numbers. Now back to your pivottable and refresh it. Do you see the non-zero sum? DOUG wrote: My pivot table displays zeros instead of numbers. I have tried re-formatting the numbers to "General" and "Number" and "Text". I have tried changing "Count" to "Sum". I can see the correct numbers in the spreadsheet but not in the pivot table. Please advise. DOUG ECKERT -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Shows Only Zeros
Dave Peterson: Both COUNTA and COUNT returned the number one. Pasting the
value and/or format of the blank, general cell did not change the numbers in the base table or in the pivot table. I did revert to the old trick of multiplying the numbers in the base table by 1, but that did not work either. I am open to ideas. I do have a sample of a very similar pivot table I completed a couple of months ago that is correct, but all of the underlying formatting appears to be the same. (How frustrating)! DOUG "DOUG" wrote: My pivot table displays zeros instead of numbers. I have tried re-formatting the numbers to "General" and "Number" and "Text". I have tried changing "Count" to "Sum". I can see the correct numbers in the spreadsheet but not in the pivot table. Please advise. DOUG ECKERT |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Shows Only Zeros
If both =counta() and =count() returned 1, then you only have something in a
single cell and it's a number. Are you sure you pointed at the correct range? DOUG wrote: Dave Peterson: Both COUNTA and COUNT returned the number one. Pasting the value and/or format of the blank, general cell did not change the numbers in the base table or in the pivot table. I did revert to the old trick of multiplying the numbers in the base table by 1, but that did not work either. I am open to ideas. I do have a sample of a very similar pivot table I completed a couple of months ago that is correct, but all of the underlying formatting appears to be the same. (How frustrating)! DOUG "DOUG" wrote: My pivot table displays zeros instead of numbers. I have tried re-formatting the numbers to "General" and "Number" and "Text". I have tried changing "Count" to "Sum". I can see the correct numbers in the spreadsheet but not in the pivot table. Please advise. DOUG ECKERT -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table shows all rows in selection | Excel Discussion (Misc queries) | |||
pivot table - shows zero when should not be | Excel Discussion (Misc queries) | |||
Top 20 pivot table shows more than 20 | Excel Discussion (Misc queries) | |||
Looking for pivot table functionality that shows detail instead of count | Excel Discussion (Misc queries) | |||
The last row of a pivot table shows blanks, but there is data. | Excel Worksheet Functions |