Forum Discussion
Limit chart data displayed in excel
I have a chart with 2 data series: YTD Budget and YTD Received. Both the YTD Budget and YTD Received data are in running total columns. I manually enter the weekly amount Received.
The current week number is in Cell M1. The Week Beginning Date is displayed by the following formula: =VLOOKUP($M$1,$M$5:$W$57,2,FALSE)
Row 5 is Week 1, and Row 56 is the last week for 2019.
All the YTD Budget data points should be displayed, but I only want the non-zero YTD Received data points displayed.
The Series Values for Received is: =Sheet1!$U$5:$U$56
"$U$56" displays ALL 52 weeks of YTD Received amounts, but I want to end the series at the last week that has data in the Received column.
Can I use current week number is in Cell M1 to limit the Series Values?
Thank you for your help.
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
2 Replies
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
