+ Reply to Thread
Results 1 to 3 of 3

How to show % on horizonal axis of Pivot Chart

  1. #1
    Registered User
    Join Date

    How to show % on horizonal axis of Pivot Chart

    Good evening

    I hope you can help me

    I have been asked by a local charity to help with analysing their end of year statistics (the figures are relatively simple)

    They are using Excel 10

    I have done it all except for one thing. The figures need (ideally) to be shown as a Pivot table and chart

    There is no problem with the table, but the chart shows me the percentages as a decimal (on the x axis), even though the chart shows it as a percentage

    I have attached a simple sheet showing the problem (It also shows it below as I want to display it, but I have done that by copying the data, and pasting as values, which I really don't want to do)

    I have never had this problem before

    I really hope you can help me

    Thank you so much
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    North America
    MS-Off Ver
    2002/XP and 2007

    Re: How to show % on horizonal axis of Pivot Chart

    In the absence of other replies, I will venture something, though I am not an expert in pivot tables and charts. I know that pivot charts are strictly limited to a text/category axis for the horizontal x axis. It appears that this limitation of Excel's pivot charts prevents Excel from applying a number format to the text of your column chart. (Interestingly, if I open the file in LO Calc, the horizontal axis is showing the numbers as percents and I can freely change the number format.)

    I have no quick and easy solutions (other than to use a different spreadsheet without this limitation). You can create a regular chart from pivot data https://peltiertech.com/regular-char...-pivot-tables/ Or you can change source data so that column A contains "numbers stored as text in the format you wish to see" so that the pivot chart can see the correct text for the axis labels.

    Does any of that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016

    Re: How to show % on horizonal axis of Pivot Chart

    You can work around the limitation as mentioned by MrShorty by doing 3 additional steps:
    1) add a column to convert the "% from start" to a text format (added column B with the formula B2=ROUND(A2*100,0)&"%")
    2) pull this added field to the Rows section of PivotTable Field List
    3) un-tick the Multi-level Category Labels of the horizontal axis

+ 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: 1
    Last Post: 08-28-2019, 03:09 PM
  2. [SOLVED] Primary Axis & secondary Axis have high gap to show in chart
    By leakhna in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-05-2018, 02:19 AM
  3. Horizonal Axis Values
    By cookhouse in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-18-2018, 06:20 PM
  4. negative y axis on pivot chart but axis doesn't change
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 06-24-2015, 02:19 PM
  5. modifying the horizonal axis
    By Elainefish in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-03-2013, 09:24 AM
  6. Horizonal Line Chart
    By Volsfan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-08-2005, 08:05 AM
  7. [SOLVED] Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis
    By mw210461@yahoo.com in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-13-2005, 05:05 PM


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