Forum Discussion
Understand format of a value
Hi,
I get the file attached from an add-in excel program. I'm getting crazy trying to figure it out how the values in the yellow titled column are. Apparently they are in text format, cause when I create a pivot table and insert the column in value, automatically I got the "count" instead of "sum".
So I tried to change the format with the different option from menu Format Cell but apparently in the pivot I always have the "count". If I change the column in Pivot from count to sum everything is ok.
To avoid this (sometimes I have a lot of columns) I substitued all the values with "N" formula, copy and paste as values in the database and it works for the pivot.
But then, if i want to filter the values in the pivot colums for ">0" I don't have any results, as if the values are not recognised as numers (I think). If I highlight more then one, btw, I have the preview sum (in the bottom excel bar).
I there a way to check the "real" format of the values? Any iea why I cannot use filter is the Pivot table? (with other tables from other sources everything is working ok...".
I attached the file exactly as I got it.
Thanks a lot for your help.
Ciao
Michele
Hi Michelle
You need to click on on a cell in the [.Locationinitiative] column before applying the values filter.
That's the most granular level to filter out 0s
8 Replies
Hi Michele,
In column AY the zero values are actually entered as "-" rather than just formatted zeros. This will most likely be causing your issue.
You could do a find and replace to replave "-" with 0
- Hi Wyn, thanks for your reply. I know it's a text "-" instead of 0. I left the original extraction. Normally I apply the formula "N" for all the value to convert them into numbers. With find and replace sometimes it takes too much time if there are lots of columns and raws.
This solution allow me to create the pivot table with "sum" for value as default. But still I cannot filter those data...Hi Michelle
You need to click on on a cell in the [.Locationinitiative] column before applying the values filter.
That's the most granular level to filter out 0s
Hello Michele
Sorry, there is no pivot table in your workbook.
Generally you can determin wether a value ist text or number by using ISTEXT() or ISNUMBER().
Hi Detlef, thanks for your reply. I didn't create the Pivot to send the original file to leave as much info as I could.
Anyway, I checked with IS.NUMBER formula and they are all numbers.
Here in this file I created the pivot, after converting all the number with N formula.
As you can see...they are number and I can sum them but I cannot apply a filter for the column D E or F.
Thanks a lot for your help
Ciao
