Limit chart data displayed in excel

gmartin1595 

hi,

In cell M1 I generated the week number by using the formula

=Weeknum(Today())

For the line Chart to reflect the values only until this week (say week 26) you need to define 2 names:

I called them Received & Budget

You do that by hitting CTRL + SHIFT + F3

Name: Received

Refers To:  =OFFSET(Sheet1!$U$5,0,0,Sheet1!$M$1,1)

Name: Budget

Refers To: =OFFSET(Sheet1!$W$5,0,0,Sheet1!$M$1,1)

Now we need to modify the data used for the Line Chart >> Select it >> Design Tab >> Select Data

Select the series "Received" >> Edit >> Replace the range reference by the Defined Name "Received" without deleting the sheet name or the exclamation mark

Repeat for the Budget Series

Now after hitting OK twice the Line Chart will only reflect the valid period

If you want to deal with Zero values then in the select Data Source box >> Click in the lower Left corner >>Hidden and Empty Cells >> select one of the options

 I am attaching the file for your reference

Hope that helps

Nabil Mourad