# Pivot table number format based on title

#### gdel02

##### New Member
Working with this:
Code:
``````For Each pvtFld In Pvt.DataFields
If pvtFld.Name = "*Rate*" Then
pvtFld.NumberFormat = "%0.00"
Else: pvtFld.NumberFormat = "#,##0"
End If
Next pvtFld``````
So that for each field in a pivot table, if the pivot field name contains "Rate" it will format as a percent, otherwise a number.
The code runs correctly without any stops or errors, but the resulting chart still shows values as a number format. Any suggestions?
Thanks

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### CMAnslow

##### New Member
Hey,

I think the number format for percent is :-

Code:
``NumberFormat = "0.00%"``

Try this and see if it works.

#### gdel02

##### New Member
You're right that is my mistake. The problem though is that the code isn't recognizing the field title, and formats all values as a number.

#### CMAnslow

##### New Member
Try the following and see that you have the correct name for the field.

Code:
``````For Each pvtFld In Pvt.DataFields
msgbox(pvtFld.Name)
If pvtFld.Name = "*Rate*" Then
pvtFld.NumberFormat = "%0.00"
Else: pvtFld.NumberFormat = "#,##0"
End If
Next pvtFld``````

#### gdel02

##### New Member
I was able to solve by using the Like command:

Solution:

Code:
``````For Each pvtFld In Pvt.DataFields
If pvtFld.Name Like "*Rate*" Then
pvtFld.NumberFormat = "0.00%"
Else: pvtFld.NumberFormat = "#,##0"
End If
Next pvtFld``````

Replies
0
Views
52
Replies
5
Views
150
Replies
1
Views
56
Replies
0
Views
167
Replies
5
Views
721

1,127,711
Messages
5,626,420
Members
416,183
Latest member
IanA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back