Automatic change in the chart according to the current date

Let’s have a list of some values. The values are taken every day. The task is to create chart showing only last 5 days from today. Charts must be updated automatically.

Important: I wrote this on 4.7.2012, so for this example is 4.7.2012 the current date.

List of values by date


Step 1 / new table

We create a new table that shows only last 5 days from today.

Last 5 days only

First of all we have to determine current date. Formula in D4 is:


Then we subtract 1, 2, 3 and 4 days from the current day. Formula in D5 is:


Now, we use VLOOKUP function to find right values to the dates. Formula in E4 is:


VLOOKUP function

Argument “Table array” is A:B because we need include all values in column A and B. Therefore, we can add more values to the list of dates and this formula will work correctly.

Step 2 / chart

We create new chart from this auxiliary table. I choose Line chart, but it’s up to you which one you need.
Line chart

Add a comment »2 comments to this article

  1. Do you think that better solution will be if I use Format as Table? It will automatically grow the table range and graph will be also updated. But I like your example with VLOOKUP. I love this formula. It saved me a lot of time because I do not need to use VBA macro for many issues.


  2. hi


Leave a Reply to Eldest Cancel reply

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress