Excel – conditional chart ranges in excel

chartsmicrosoft excelmicrosoft-excel-2010

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?

Best Answer

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

Related Question