+ Reply to Thread
Results 1 to 39 of 39

Not able to change the horizontal axis values

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Not able to change the horizontal axis values

    Hello there

    I am having issues updating the horizontal axis in a column chart for a set of data which I made the chart from pivot table. I want the axis to be displaying 'no.of days' starting from 1 to 200 days with 5 days interval.

    WHen I tried using the layout-->Axis->primary horizontal axis->more primary horiaontal axis options, I can see a window but donno what to update on this.

    Please help resolve this issue for me.

    THanks
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Anyone please help.........desperately need a solution.

    THanks

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

    Re: Not able to change the horizontal axis values

    Post an example workbook rather than images.
    It's almost impossible to determine anything about your data and settings from a picture.
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    your problem is that your axis is a category axis not a value axis so it only displays the data you actually have. unfortunately you also cannot use an xy scatter type for a pivot chart. can you use a normal chart rather than pivot chart?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Please see attached the sample data. Actually I am trying to plot a column chart with x-axis as column E with range displaying 0-5,6-10,11-15,16-20,....etc, Y-axis as no. of sheets (no of rows) say 1,2,3,4 etc. and the legend should be displaying the Project names as XWB,TRENT 1000, TRENT 900 etc.


    please advice if any one could help with this.

    Many thanks
    Attached Files Attached Files

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    in that case add the fix time field to the row are of the pivot table, right-click it and choose group then chose your start and end and interval

  7. #7
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    JosephP

    Sorry but I couldn't understand. I am new to excel pivot tables and charts. Can you please plot on my sample data attachement and send it back??

    Many MAny Thanks

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    please see the attached version of your file
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    That was really helpful, May I know how did you get the range for the Root Cause_Fix Time??? Did you manually enter that?? Also is it possible to get a cumulative count of no.of ABC's Open in the chart?? See I have attached with a chart included for your pivot table. I also wanted to inlucde the cumulative no. of ABC's in the chart like 19,61,70,71,72,74,76,80.... 103 etc
    Attached Files Attached Files

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    I did exactly what I said before-added the number of days field to the row area of the pivot table then right-clicked it and grouped it.
    for the cumulative totals just right-click a value field and set to show values as running total-see attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    JosephP

    Is it possible to add the cumulative no.of ABC's as a seperate line graph in the same chart??? keeping the remaining as before?? see the sample image as I wanted it as.....sorry drawn in paint

    Thanks
    Attached Images Attached Images

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    not with a pivot chart-they don't plot the totals columns-and you can't add a calculated item when you have a grouped field

  13. #13
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    ohh ok, Can I make thi chart dynamic?? actually I retrieve the data from a different spreadsheet which is on the shared drive and its read-only. I usually open that spreadsheet, sort them, filter them and copy the data to a different spreadsheet and then plot the charts. Actually it makes things easier if there is a way to dynamic update the data and the charts straight from the shared spreadsheet.

    say for example I have the buttons Trent 1000, XWB, Trent 900, when I click each button the charts with the no.of ABC's raised,responded, closed each month should be displayed. This was my actual motive, but I am not able to do and understand the concenpt of Dynamic charting.

    See attached the copy of the Shared spreadsheet where I get the data.

    Is there a way to do this????? THanks a Ton.
    Attached Files Attached Files

  14. #14
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    1. please see the forum rules on cross-posting and amend the thread accordingly
    2. I'm ok with answering specific questions but I'm not taking on a badly specified project

  15. #15
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Sorry about that.as I said iamnew to this forum I didn't see the rules.very sorry. Can you help with dynamic charting,I will some how manage returning data from shared spreadsheet.
    Thanks

  16. #16
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    not until you
    -comply with the rules
    -give more details :-)

  17. #17
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Thanks.I would definitely try to comply with the rules, if iam not, please advice me.
    In my previous attachment, how do I create dynamic charts like the one just we did. I think I can retrieve data using a query and when I press refresh all, the pivot table refreshed with the more up to date data. I have done one for Trent 1000 on sheet 2 in the attachment.

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    read the rule about cross-posting then add links here to your cross-posts. until you do that I cannot help you (though I will point out that I already told you a pivot chart will not do what you asked for)

  19. #19
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values


  20. #20
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    Quote Originally Posted by kbaruin View Post
    I have done one for Trent 1000 on sheet 2 in the attachment.
    not as far as I can see

    also, again, you cannot produce the chart you want with a pivot chart.

  21. #21
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Hi

    Is it possible with out using pivot charts or pivot tables? Sorry I missed to attach.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Hi

    Is it possible with out using pivot charts or pivot tables? Sorry I missed to attach.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Hi

    Please can anyone help solve the above issue..........

    THanks

  24. #24
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Not able to change the horizontal axis values

    you could use a macro to build a normal chart using the pivot data and add formulas alongside the pivot table-see sample workbook
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Hi

    FOr some reason I am unable to group the row values again. Please see the 'Chart by days open' sheet on the attached. The source data for the chart is on the 'Query Pre-Prod Data' sheet on the same attachment.

    Very urgent please help.

    Thanks
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Can any one please help with the above issue.

    Thanks

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

    Re: Not able to change the horizontal axis values

    Because the data contains blanks.

    For some reason your source data if all the rows on the data sheet. Modify the range used for source data to enable Grouping

  28. #28
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Sorry but I am unable to understand. Can you advice how to do this??

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

    Re: Not able to change the horizontal axis values

    Use the pivot table wizard to change this source data reference
    '[Query Pre-Prod Data.xls]Query Pre-Prod Data'!$A:$L

    to one that limits the data to the information on the sheet.
    '[Query Pre-Prod Data.xls]Query Pre-Prod Data'!$A1:$L442

  30. #30
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Thanks Andy, it works fine for that particulat attachment. But when I tried with other spreadsheet that extracts the data from another shared spreadsheet for the pivot table, it doesn't let me group. I did exactly how you said limiting to just the data excluding the blank or unfilled data. Is that a rule that none of the cells should have blanks????

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

    Re: Not able to change the horizontal axis values

    Yes. You can not group when the field contains blanks.

  32. #32
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    Hi Andy

    Can you please look into the attachments and let me know how to get the x-axis as range for example: <1,1-5,6-10,11-15 ...etc

    Book1 contains the chart and pivot table, Pre-prod ABC log contains the data for the pivot table.
    I am unable to groupd this.

    Please advice.

    Thanks
    Attached Files Attached Files

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

    Re: Not able to change the horizontal axis values

    In Pre Prod ABC log you have 2 data items that are not valid.

    Row 67 returns ""
    Row 184 returns #Value!

  34. #34
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    IS there anyway that I can skip those 2 rows and work with remaining data??

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

    Re: Not able to change the horizontal axis values

    Not that I know of. You would need to remove them from the data source.

  36. #36
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    The problem is the source data is a read-only file and that is updated by someone else remotely. I want to extract the data and build the chart that changes dynamically based on the source data. I do not have permissions to make changes on the source data. I tried to extract via connection strings but couldnt as the source files are in .xlsx and .xlsm format.

    Please throw some light as to how I can overcome these issues.

    Thanks

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

    Re: Not able to change the horizontal axis values

    If you can not control the source data then you will need to add a field to the data that allocated a group id to the data.

  38. #38
    Registered User
    Join Date
    09-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    81

    Re: Not able to change the horizontal axis values

    What do you mean by group id?? I am new to excel, please help by demonstrating if you can on my attachments.

    Thanks

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

    Re: Not able to change the horizontal axis values

    See JosephP's suggestion at the start of your thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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