Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

Pivot Table - Group Data Formatting

How do I add formatting to the banding of group data in a pivot table so it shows the 1000 comma separator - changing the number format in the field setting to "number" with the 1000 separator option box selected does not change the formatting.

At the moment it shows for example 1000-1099 instead of 1,000-1,099 as a band.


See attached file example

banding.xlsx
0
doolinn
Asked:
doolinn
1 Solution
 
Harry LeeCommented:
doolinn,

I don't think you can do what you want to the format of the labels in grouped mode.

Since once the label is grouped, it's no longer number and had been converted to text labels.

The only thing you can do is using F2, and edit the text labels manually.
0
 
byundtCommented:
I added a column to the source range for the PivotTable. In that column, I put the bin the data belonged to.

I used a VLOOKUP formula to return the bin text, but could have typed it as well. The second column of the lookup table had commas, as requested.

I then changed the PivotTable to use this new column instead of the original one.
bandingQ28276278.xlsx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now