Hi!
I have created a pivot table and from that data a slicer. The data in the pivot table are % but in the slicer I can't get the date to appear as %, it will only show as the numbers.
Does anyone have a clue how to fix it?
Thanks a bunch!
Inge
Hi!
I have created a pivot table and from that data a slicer. The data in the pivot table are % but in the slicer I can't get the date to appear as %, it will only show as the numbers.
Does anyone have a clue how to fix it?
Thanks a bunch!
Inge
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks for helping me!
I have my table where the percentage is a result of a calculation and formatted as percentage
Capture2.JPG
In my pivot table it refers to that table and it's showing as percentage
But not in the slicer that is based on both.
Capture1.JPG
I hope I'm being clear in my explanations..
Can you upload sample workbook (with desensitized info) that demonstrate your issue?
When tested on my end, I had no issue displaying slicer with percentage, even for calculated values mixed with #div/0 error.
0.JPG
I have edited the file, removed a lot of data but enough left to see the problem. The strange thing is that in the original file I have one tab for a year that is working correctly and showing the percentage. But is created the exact same way and has (as far as I can tell) the same properties etc.
Ah I see. Your issue is that you are using entire column range "'# per company all'!$A:$E" as source for Pivot Table.
That will mix cells formatted as Percentage as well as blank cells formatted as General into the mix. And since, majority of cells are in General format. It's interpreted as text column by Excel.
Edit: Rather it's using General format for all values instead of percentage.
To fix this... you can make your pivot table source dynamic by doing one of following.
1. Convert source range to Excel Table and use Table name as your source.
2. Create dynamic named range that covers your data range and use that as your source.
See link for detail on how to create dynamic named range for Pivot table.
http://excelpivots.com/excel/pivot_t...g_data_ranges/
Last edited by CK76; 12-20-2017 at 02:34 PM.
Ahaaaaaaa... now that makes sense! I still have the problem that most cells in that table are general and not % Even if I use the Dynamic range. But I now know the reason and can move from here.
Thanks!
Inge
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks