+ Reply to Thread
Results 1 to 6 of 6

Fixed range on x axis

  1. #1
    Registered User
    Join Date
    11-18-2019
    Location
    europe
    MS-Off Ver
    2016
    Posts
    3

    Fixed range on x axis

    Hi,
    I have a pivot table bar chart with working shift hours (with whole hours on the x axis).
    The data set contains values always between 7 am and 3 pm.
    How can I set the x axis with fixed values with the first value being 7 and last value being 3.
    Excel (2016) isn't giving me the option to set the minimum and maximum value for the x-axis.


    Thanks

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,543

    Re: Fixed range on x axis

    Since pivot charts cannot be XY scatter charts, there is no way to have a pivot chart provide "numeric axis" options for the horizontal axis. Pivot charts can only use categorical horizontal axes.

    I am not sure exactly what you are trying to do. It probably depends on exactly what kind of chart you are using for your pivot chart, what your pivot table is doing, whether you are required to use a pivot chart, and other details. Easiest would be to upload a sample file to the forum.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    11-18-2019
    Location
    europe
    MS-Off Ver
    2016
    Posts
    3

    Re: Fixed range on x axis

    I am using a Bar Chart, I am not required to use a pivot chart.

    I have a data set with widgets produced during a shift. Shift hours range from 7 am to 3 pm and are on the x axis.
    I also have slicer to pick a date, when I pick a date the production values get shown on the chart.

    Currently the x axis values keep changing with each date (because on certain dates widgets get produced at 8 am, 9 am and 10 am and some dates 7 am and 11 am etc. etc.)
    This does not read comfortably, and I was wondering if it is possible to fix in place the hours on the x-axis

    Thank you for your time.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,543

    Re: Fixed range on x axis

    A bar chart that is a pivot chart can only show a category x axis and can only link to the categories given in the pivot table. If, after pivoting and slicing the source data, there are only 8, 10, and 14 categories, then those are the only categories that will be shown in the pivot chart. The pivot table/chart only knows what categories are dictated by the source data. If the source data does not have data for a 7 or 10 or 12 category on a given date, then the pivot table/chart will not know that there should be an empty or 0 category that is not in the source data. I'm not an expert in pivot tables/charts, but, I think that you will need to add 0/empty entries for each hour of each day if you want the pivot table/chart to always include a row/category for those entries.

    If you create a regular chart instead of a pivot chart (see here: https://peltiertech.com/regular-char...-pivot-tables/ ), then you can force your x axis to be a date axis, which you can then set the axis limits to be 7 to 15 (so you will need your source data to use a 24 hour clock rather than a 12 hour clock). Unfortunately, a regular chart will not be as tightly linked to your pivot table, so you will need to pay careful attention to category and value range definitions (maybe even need dynamic named ranges for these) so that the regular chart's source data will properly adapt when the pivot table is sliced or refreshed.

    There may be other solutions that I am not seeing, because, as I said, I am not an expert in pivot charts/tables, but I expect that either of those solutions should work. Which approach would you like to try?

    Edit to add: at some point in developing this, we are going to need to know more detail about your project. more about your source data, more about your pivot table, more about what your chart needs to show, and so on. It would probably help us help you if you could upload a sample file with a good description of the data (anonymized as needed), what you can and cannot change, what you are required to do and what would be nice to do, and so on.
    Last edited by MrShorty; 05-14-2020 at 11:39 AM.

  5. #5
    Registered User
    Join Date
    11-18-2019
    Location
    europe
    MS-Off Ver
    2016
    Posts
    3

    Re: Fixed range on x axis

    Anonymizing the data will take a lot of work, but I will post when I have something.

    I will just do a ='Sheet1'!$B$5:INDEX(Sheet1!$B$5:$B$1000;COUNTA(Sheet1!$B$5:$B$1000)) etc.
    to create a named range and build a regular chart.
    My source data already uses a 24 hour clock (Im from europe), so that shouldn't be a problem.

    Thank you very much for your help.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,816

    Re: Fixed range on x axis

    As long as there is data for each hour somewhere in your data set, changing the field options to show items with no data might solve the issue quite simply.
    Rory
    I drink, and I know things

+ 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. Autoscaling (with fixed range) the x-axis of a line chart?
    By excelswimmer in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-10-2016, 01:25 AM
  2. Replies: 2
    Last Post: 07-14-2014, 11:11 AM
  3. [SOLVED] Fixed number of Y-axis labels - possible?
    By GraemeG in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-18-2013, 06:32 PM
  4. Charts: Format Axis -> Axis Options -> Fixed minimum and maximum
    By johnmata in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-28-2013, 05:53 PM
  5. 2007 Fixed X-Axis
    By kaledev in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-25-2011, 09:39 AM
  6. Replies: 0
    Last Post: 12-08-2007, 06:14 PM
  7. Fixed numbers on Y axis
    By Eva_ in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-18-2007, 05:29 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