+ Reply to Thread
Results 1 to 7 of 7

My slicer from the pivot table is not showing %

  1. #1
    Registered User
    Join Date
    12-20-2017
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    4

    My slicer from the pivot table is not showing %

    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

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: My slicer from the pivot table is not showing %

    I assume Slicer is based on Value Field of Pivot Table?

    Is source data formatted as percentage? If not, then it will show as value only.
    0.JPG

    If source data is formatted as percentage, it will show as such in slicer.
    1.JPG
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-20-2017
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    4

    Re: My slicer from the pivot table is not showing %

    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..

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: My slicer from the pivot table is not showing %

    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

  5. #5
    Registered User
    Join Date
    12-20-2017
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    4

    Re: My slicer from the pivot table is not showing %

    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.
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: My slicer from the pivot table is not showing %

    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.

  7. #7
    Registered User
    Join Date
    12-20-2017
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    4

    Re: My slicer from the pivot table is not showing %

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 05-18-2017, 07:58 PM
  2. Pivot Table and Slicer Help
    By matt85webb in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-28-2016, 11:14 AM
  3. Pivot table and slicer help
    By finchfinch in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-23-2016, 07:36 AM
  4. Slicer for table with date column showing wrong result
    By p3rlend in forum Excel General
    Replies: 1
    Last Post: 04-24-2016, 06:37 AM
  5. Replies: 19
    Last Post: 04-04-2016, 02:58 PM
  6. [SOLVED] Use slicer selections on one pivot table to filter another pivot table
    By porkandbeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 12:30 AM
  7. Showing summary information in pivot slicer
    By quickgun in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-28-2012, 02:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1