can you change a column of numbers stored as text directly in a pivot table?

[hairyfingers]

Hello

I have some source data in excel for a pivot table that has a column of numbers but those numbers are stored as text. I can add a helper colun to convert them all to numbers and I can use VBA to change them to numbers. However for the end user neither of these is going to be suitable.

when I bring the data into a pivot table all it can do it count the data it can't sum it as it is seeing it as text. I have tried a calculated field =value([fieldName]) but this doesn't appear to work

Is there a way I can get the pivot to see this column as values without having to manipulate the source data?

thanks

 

[alansidman]

Instead of using a regular pivot table, bring the data into Power Pivot and use the DAX measure to create a calculated field. If you are still unsuccessful, then load some sample data to this site with XL2BB. Do not post a picture as we cannot manipulate data in a picture.

 

[hairyfingers]

Instead of using a regular pivot table, bring the data into Power Pivot and use the DAX measure to create a calculated field. If you are still unsuccessful, then load some sample data to this site with XL2BB. Do not post a picture as we cannot manipulate data in a picture.

fantastic that works!