Results 1 to 4 of 4

Thread: PivotChart formatting help

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PivotChart formatting help

    Hi all,

    I have a fairly large dataset that is broken down as minute-by-minute data.

    I am plotting various charts from the PivotTable data, generally using averages.

    I have "Time" in the row field on my PivotTable, but when I plot a chart it gives me some strange time values along the X axis, see screenshot below.

    So I have a couple of questions.

    1) As my data contains values from every minute of the day, how can I format the X axis to something more logical, such as every 15 minutes, starting at 00:00, then 00:15, 00:30, 00:45, etc.

    2) Secondly following on from that, how can I make some vertical gridlines appear at set intervals, say on the hour, so every xx:00 has a vertical gridline?

    As always, grateful for any help

    Thanks


  2. #2
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PivotChart formatting help

    Hey, is anyone able to help?

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: PivotChart formatting help

    Yeah, patience. We're volunteers here. Anyway...

    So in the pivot table, the times are probably treated as text labels. If you have a label every minute reliably, and you want to show a label for each 15 minutes, then you can set the interval between X axis tick labels to 15.

    To add the complication of vertical gridlines every hour, I'd have to really see the pivot table. But usually your only option is to get a gridline at every tickmark (every 15 minutes in your case), or add a dummy series to build a dummy set of gridlines, and pivot charts don't let you add data that's not in the pivot table. It's complicated, but it can be done.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PivotChart formatting help

    Quote Originally Posted by Jon Peltier View Post
    Yeah, patience. We're volunteers here. Anyway...

    So in the pivot table, the times are probably treated as text labels. If you have a label every minute reliably, and you want to show a label for each 15 minutes, then you can set the interval between X axis tick labels to 15.

    To add the complication of vertical gridlines every hour, I'd have to really see the pivot table. But usually your only option is to get a gridline at every tickmark (every 15 minutes in your case), or add a dummy series to build a dummy set of gridlines, and pivot charts don't let you add data that's not in the pivot table. It's complicated, but it can be done.
    Hi Jon,

    Yeah no worries - appreciate that. It wasn't a patience thing, more a worried it had fallen down the page thing that was all. Always grateful for help.

    I've managed to get it working, thanks very much

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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