How do I change the format of group by data in Excel 2003 pivot tables

Question

I have a data that lists the value of a contract per contract number. So I've created a pivot table that counts the number of contracts valued at 0 - 10, 11 - 20, etc. I want to be able to format the group, e.g. $0 - $10, $11 - $20 I've tried formatting the underlying data as currency and formatting the column in the pivot table, but it still shows as 0 - 10, 11 - 20

Also I have a column in the pivot table that says Total which is the Count of the number of contracts in that range, i.e. Value Total 0 - 10 1 11 - 20 1

Its an autogenerated column heading that Excel put in. How do I change this to say Number of contracts. I want it changed because when I chart the pivot table, the series is called called Total :(

Answer 1

You are right, it does not show the $ sign. Another simple solution is mentionning the unit in your header, such as Amount ($). This translates well when doing charts afterwards.

And Martha is spot on about having to type header names and chart titles yourself!

Here's what I get:

Amount ($)    Number of contracts
  0-9         1
10-19         1  
20-29         1
40-50         1
Grand Total   4

Answer 2

The most bulletproof approach would be to make another column and construct the proper label with string concatenation. Then use that column as dimension ("Group by" in your post terminology).

If you can give more specifics about the data (e.g. column layout, based on which you construct your pivot table) then maybe we can come up with more specific answer.

Answer 3

It's not a terribly satisfying solution, especially if you have many groups, but you can change the names of the groups by typing over them individually in the formula bar at the top of the sheet. Thankfully, unlike formatting, it remembers the changed names even if you mess around with the contents and layout of the PivotTable. (Of course, if you change the grouping options themselves, your customizations will be lost.)

As far as the "Total" label, it won't let you change that on the PivotTable (not even via the formula bar), but it will let you change the title of the chart - just select the title and type in your desired text. Again, unlike most formatting changes, it will remember your new title even if you play around with the chart options.