Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a table of data in Excel 2010 that looks something like this (but with hundreds of thousands of rows over multiple days):

enter image description here

I am trying to create a dynamic chart where the x-axis is the timestamp and the y-axis is the value. Each pair of columns is a different series, like so:

graph

Note: The actual chart has a fixed tick width of 1 hour on the x-axis, this is just a quick example I put together.

This is all working fine, but what I want to do is have two cells on the worksheet that define the start and end time of the data to be displayed in the graph.

table with dates

I want the chart to automatically update (and adjust the x-axis while preserving my fixed tick width) to only include data points between the times I've entered into G2 and H2. Is such a thing possible?

share|improve this question
    
When you say fixed tick width on the X-axis, do you mean the actual physical dimension (e.g. 1 inch), or the category interval (e.g. 1 hour)? –  dav May 22 '13 at 20:08
    
You might be better off with a scrolling window as described by Windmill Software. –  pnuts May 22 '13 at 21:09
    
@DavidVandenbos I mean the category interval. Each tick should always indicate 1 hour. I have set that up already in the static chart but wanted to be sure it was preserved in the dynamic one too. –  dpatchery May 22 '13 at 21:30

1 Answer 1

up vote 2 down vote accepted

I assume this is an XY scatter chart, not a line chart. You can create dynamic range names and plug these into the chart. If each data series has its own distinct set of time stamps, i.e. there are different time stamp values in each series, then you need to create range names for each X and each Y pair. If the data shares the same time stamp, you only need one range name for the X labels and can use offsets for the Y data.

In the screenshot below, the X labels are in the chtLabels range name that is defined with the formula

=INDEX(Sheet1!$A:$A,MATCH(StartTime,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(EndTime,Sheet1!$A:$A,0))

The Y value range name is defined with

=OFFSET(chtLabels,0,1)

The marching ants show the current content of the range name chtLabels

enter image description here

Then plug the range names into the chart source:

enter image description here

share|improve this answer
    
Thanks! I was able to get my chart working using this information. The key piece I was missing was the formula for chtLabels. –  dpatchery May 23 '13 at 13:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.