0

Is it possible to have a graph in excel show the collected data over the past year automatically?

Background information:

I collect data and I wish to present it in a graph showing the last year. At the moment i need to manually adjust the dates wich are showing. I have a feeling this can be automated, but wasn't able to find a solution on our great big friend Google

2
  • 1
    probably there is a way yes, however there is no general solution for it. We need to know your data (or sample data) and current chart to be able to understand your problem. – Máté Juhász Jun 21 '19 at 9:56
  • 1
    I would suggest investigating ‘relative references’ in Excel and look to use these in the formula for the ranges. – Daniel K Jun 21 '19 at 11:11
1

Make yourself a "named range" and in your case that range will be a formula. you can define a Named Range or Named Formula using the ribbon, Formulas tab and in the Defined Names section:

Defined Name

Lets say your data is listed vertically in column C starting in row 4 because you have some header and row information before the data.

You could use the TODAY() function to define today as your reference point. However it is a volatile function and could cause a some excess calculations that could bog down your system. Not a big issue on small data, but particularly bad in array functions. I would hazard a guess that you want 365 days before a certain date. Like the day you prepared your presentation/report, not necessarily the day of your presentation or date of print. I would therefore place the reference date some cell. For this case lets use E2 for the end reference date and E3 for the start reference date.

In E2 Just enter the last date to report to.

In E3 either manually enter the last date you want to report to use the following formula to automatically grab a date 365 days earlier:

=E2-365

365 being the number of days in a regular year. If you want to get more complex you can replace 365 with a formula that determines leap year or not but thought I would keep this on the simpler side for now.

The next step is to determine the row of your first date of interest. Assuming your dates are in ascending order C4:C1000 you can use MATCH to find the earliest entries. This can then be passed to INDEX to get the address of the first row.

INDEX($C:$C,IFERROR(MATCH($E$2,$C:$C,1),0)+1)

The IFERROR is there to handle the case of the the date in E3 being off the start of your list. The +1 is to handle the Match result of being the date before your cut off off. It bumps the ruslt to the next row. The MATCH(,,1) finds the largest value that is less than or equal to the search value. I am guessing at being off 1 date entry is not that big a deal, but if it is you can change the +1 to see if the match result for and approximate search is the same as for an exact search or the value in E3.

And then something very similar can be done for finding the last row.

INDEX($C:$C,IFERROR(MATCH($E$2,$C:$C,1),1))

The IFERROR result here is set to 1 since there is no +1 option to consider. by doing so it makes the first date in your list the only result when your selected date range is before all your data.

now that you have the start and end point of your desired date range, define your range in the format:

INDEX():INDEX()

in this case it is:

INDEX($C:$C,IFERROR(MATCH($E$2,$C:$C,1),0)+1):INDEX($C:$C,IFERROR(MATCH($E$2,$C:$C,1),1))

The above covers your dates, but you will also want to adjust it for your Y value (other data). To do so, you just need to change the C:C reference for the INDEX range to the column of your other data. In this example set it would be change C:C to D:D as follows:

INDEX($D:$D,IFERROR(MATCH($E$2,$C:$C,1),0)+1):INDEX($D:$D,IFERROR(MATCH($E$2,$C:$C,1),1))

Now that your have a formula for a range you need to place it in a defined name. Having said that I would place the above formulas in a cell to take care of bracket count checking and making range selection easier. The cell will evaluate to an error when the two indexes are combined but that is ok. You can check the individual indexes to see if it working right. The other reason for putting them in a cell, is you can copy the formula to the clip board before going to the named formula.

Click on Define Name in the ribbon as pointed out at the start of the answer. A windows like the following should appear:

Edit Name

Enter a name for you X series. No spaces and I am not sure but to save grief I would avoid special characters with the exception of underscore. In the example you can see I called it X_Series. At the bottom paste or type in your formula formula corresponding to the series name. Repeat this process for each of the other series you need. In my example I did it again for Y_Series. as you can see in the Name Manager window below:

Name Manager

Finally insert your graph and go to your DATA SERIES information. In this example I was using an X-Y Scatter plot.

SOURCE DATA

Then edit the series and set the x series to workbook/filename ! Named Formula. This means you need to save your workbook first for it to work. In this case I save the workbook as DEMO.xlsx. You can see how I filled out the series below:

Named series

You can also verify that the series are being appropriately picked by selecting the graph and seeing where in the C:C or D:D column in this case is being highlighted:

Highlighting

So now when you change the date in cell E2 the graph will update on its own accordingly. Make sure your remove any manual limits you placed on your graph if you want to see the updates happen as the update may wind up pushing you beyond the limits you previously imposed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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