Solved

Time Intervals on X axis PivotChart

Posted on 2011-09-30
5
1,003 Views
Last Modified: 2012-06-21
Hi

I have a pivot chart w/ an x axis w/ times.

Example

4:30, 4:31, 4:32, etc.... going all the way up to 10pm

I want to know how to make it look clean

Right now its it trying to display every value which looks liek way too much on a graph.

If i group by hours it doesnt really do the trick. Maybe i can group by 15-30 min intervals?

Any suggstions?
0
Comment
Question by:kwarden13
5 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36892725
Could you please attach a sample?
0
 
LVL 8

Expert Comment

by:WileECoyote45305
ID: 36894115
Look at this article on how to use timescale values on the X-axis - one example for dates, and one example for times:
http://www.mrexcel.com/articles/excel-time-series-chart.php

I tried the procedure for the time values and was able to successfully set up a clean chart with time values on the x-axis. I used 15 minutes as the Major Unit (0.01042) and let it auto-pick the Minor Unit on the Scale tab. Use the directions to help determine the values for Minimum and Maximum based on your range of time values.

So although I had data for times such as 4:30, 4:31, 4:52, 5:05, 5:12, 6:27, 7:09 and 10:00, I had a chart with x-axis labels of 4:00, 4:15, 4:30, 4:45, etc. all the way to 10:00 and all the points plotted properly.
0
 
LVL 8

Expert Comment

by:WileECoyote45305
ID: 36894133
My comment was for controlling the x-axis labels. If you really do have a data point for every minute, what I did doesn't address all the plotted values in the chart, but cleans up the x-axis. Sorry if I misunderstood.
0
 

Author Comment

by:kwarden13
ID: 36894158
Attached is a sample
TestBook.xls
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36896201
Hello,

does that sample data span several days? The cells in column A show only time values and they seem to be in roughly hourly increments, around 23, 24 or 29minutes after the full hour. Formatting the Time values with General, though,  I can see that some of them have a 1 in front of the decimal, others have a zero. In Excel terms that means that some time values are on a different day than others. Some have no date, some have a date of 1/1/1900.

Is that the intention? Before you can do any meaningful pivot analysis, you will need to clean up your data. There either should be a date value with EVERY time value or with NONE.

If you create a pivot table of that data and want to group in 15 or 30 minute increments, then you will need data that fits that pattern. The sample data comes in hourly increments. What good would 15 or 30 minute increments do?

So, either post a sample set that is more representative of your real data, explain some more what the data is supposed to portray and how you want to summarise it.

You may need to use a helper column in the source file to create a meaningful pivot chart, but that can only be determined if you provide some more info on what the data shows and how you want to summarise it.

For example: You can have several data items per hour for a series of days. The pivot chart should disregard the date and just summarize the data by its time slot, so you see a statistic of values typical for the hour of the day (summing up all data for 9am, 10am, 11am, etc, across ALL days). In this case, the X axis would only show the time and the lines would be the sum (or average) across all days.

Or: You have several dozen data items per hour for a series of days. The pivot table should show the data in a time line, but summarize data in 15 minute blocks per day. The X axis would show each day, broken down into time slots.

Which of these do you want to show?

cheers, teylyn
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question