+ Reply to Thread
Results 1 to 8 of 8

fixing x-axis with full 12 months in pivot bar chart

  1. #1
    Registered User
    Join Date
    08-30-2009
    Location
    melbourne
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    84

    fixing x-axis with full 12 months in pivot bar chart

    Hi,

    I have a pivot table starting from Aug 2018 to July 2020.
    Now i want to plot the data in bar chart while they are filtered by years.
    x-axis will be month and y-axis values.

    Problem is that depending on the selected/filtered year, the months in axis changes.
    For example, in 2018 it shows from Aug to Dec while in 2019, from Jan to dec.

    I wish to fix axis range to Jan - Dec no matter which year is selected.

    Please advise how i can fix.

    Regards
    Minki
    Last edited by dogbural; 08-01-2020 at 10:25 AM. Reason: solved

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: fixing x-axis with full 12 months in pivot bar chart

    If you mean your pivot table/chart is missing months as there is no data then use Field Settings > Layout & Print > Show Items with no data.

    If that is not it can you post example file, as per instruction at top of page.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    08-30-2009
    Location
    melbourne
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    84

    Re: fixing x-axis with full 12 months in pivot bar chart

    Thanks Andy,

    That is what I wanted.

    I have further questions.

    Q1. I am wondering why I cannot select "Show Items with no data" (being greyed) when creating pivot table with "Add this data to the Date Model" ticked.

    Q2. Is there any way to remove Data range before/after? As shown in example, <08/09/2018 and >09/07/2020 are listed in the row labels.

    Q3. Can the x- axis be fixed from Jan through Dec when selecting a particular month in month slicer? When I selected the particular month, the x-axis range is changed to the selected month only. Ideally, I would like to highlight the selected month's bar by clicking the month while the other months' bars are greyed.

    Regards,
    MK
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: fixing x-axis with full 12 months in pivot bar chart

    Information on Q1.
    https://answers.microsoft.com/en-us/...3c40ee9?page=2

    Q2. Read the section, Grouping by Date Adds Extra Items.
    https://www.contextures.com/xlPivot07.html

    Q3. You can add extra fields that contain month name and year. Display MonthNames on the axis but use the Date field with the slicer.

  5. #5
    Registered User
    Join Date
    08-30-2009
    Location
    melbourne
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    84

    Re: fixing x-axis with full 12 months in pivot bar chart

    Thank you very much Andy,

    Now I can handle Q1 and Q2 by using power pivot.

    However, I do not quite understand of your advice on Q3. Would you be able to give me an example?

    Regards,
    MK
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: fixing x-axis with full 12 months in pivot bar chart

    I have added MonthName to your DataSource.
    You will need to get additional help on how to order the month names in month, rather than alphabetical order.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-30-2009
    Location
    melbourne
    MS-Off Ver
    Excel 2016 / 2019
    Posts
    84

    Re: fixing x-axis with full 12 months in pivot bar chart

    Thanks Andy,

    I was able to put them in order by adding prefix.

    Really appreciated your help.

    Regards,
    MK
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: fixing x-axis with full 12 months in pivot bar chart

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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: 3
    Last Post: 01-27-2020, 12:46 PM
  2. Plotting values on chart using full x-axis
    By badeend in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-12-2017, 09:45 AM
  3. Problem in fixing month values in X-axis while preparing excel chart
    By joy3939 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-09-2015, 10:45 AM
  4. fixing x-axis for column chart
    By sophiereid in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-02-2014, 10:50 AM
  5. Chart axis resizing affecting full screen
    By msixdatateam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2014, 09:14 AM
  6. Duplicate Months in the axis chart
    By msmonte111 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2013, 03:06 AM
  7. Chart: both quarters and months on X axis?
    By jslag in forum Excel General
    Replies: 1
    Last Post: 03-02-2010, 07:41 PM

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