SOLVED
Home

Limit chart data displayed in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-720000%22%20slang%3D%22en-US%22%3ELimit%20chart%20data%20displayed%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720000%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20chart%20with%202%20data%20series%3A%20YTD%20Budget%20and%20YTD%20Received.%20Both%20the%20YTD%20Budget%20and%20YTD%20Received%20data%20are%20in%20running%20total%20columns.%20I%20manually%20enter%20the%20weekly%20amount%20Received.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20current%20week%20number%20is%20in%20Cell%20M1.%20The%20Week%20Beginning%20Date%20is%20displayed%20by%20the%20following%20formula%3A%20%3DVLOOKUP(%24M%241%2C%24M%245%3A%24W%2457%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERow%205%20is%20Week%201%2C%20and%20Row%2056%20is%20the%20last%20week%20for%202019.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20YTD%20Budget%20data%20points%20should%20be%20displayed%2C%20but%20I%20only%20want%20the%20non-zero%20YTD%20Received%20data%20points%20displayed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Series%20Values%20for%20Received%20is%3A%26nbsp%3B%3DSheet1!%24U%245%3A%24U%2456%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22%24U%2456%22%20displays%20ALL%2052%20weeks%20of%20YTD%20Received%20amounts%2C%20but%20I%20want%20to%20end%20the%20series%20at%20the%20last%20week%20that%20has%20data%20in%20the%20Received%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20use%26nbsp%3Bcurrent%20week%20number%20is%20in%20Cell%20M1%20to%20limit%20the%20Series%20Values%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-720000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720053%22%20slang%3D%22en-US%22%3ERe%3A%20Limit%20chart%20data%20displayed%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720053%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366453%22%20target%3D%22_blank%22%3E%40gmartin1595%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%2C%3C%2FP%3E%3CP%3EIn%20cell%20M1%20I%20generated%20the%20week%20number%20by%20using%20the%20formula%3C%2FP%3E%3CP%3E%3DWeeknum(Today())%3C%2FP%3E%3CP%3EFor%20the%20line%20Chart%20to%20reflect%20the%20values%20only%20until%20this%20week%20(say%20week%2026)%20you%20need%20to%20define%202%20names%3A%3C%2FP%3E%3CP%3EI%20called%20them%20Received%20%26amp%3B%20Budget%3C%2FP%3E%3CP%3EYou%20do%20that%20by%20hitting%20CTRL%20%2B%20SHIFT%20%2B%20F3%3C%2FP%3E%3CP%3EName%3A%20Received%3C%2FP%3E%3CP%3ERefers%20To%3A%26nbsp%3B%26nbsp%3B%3DOFFSET(Sheet1!%24U%245%2C0%2C0%2CSheet1!%24M%241%2C1)%3C%2FP%3E%3CP%3EName%3A%20Budget%3C%2FP%3E%3CP%3ERefers%20To%3A%26nbsp%3B%3DOFFSET(Sheet1!%24W%245%2C0%2C0%2CSheet1!%24M%241%2C1)%3C%2FP%3E%3CP%3ENow%20we%20need%20to%20modify%20the%20data%20used%20for%20the%20Line%20Chart%20%26gt%3B%26gt%3B%20Select%20it%20%26gt%3B%26gt%3B%20Design%20Tab%20%26gt%3B%26gt%3B%20Select%20Data%3C%2FP%3E%3CP%3ESelect%20the%20series%20%22Received%22%20%26gt%3B%26gt%3B%20Edit%20%26gt%3B%26gt%3B%20Replace%20the%20range%20reference%20by%20the%20Defined%20Name%20%22Received%22%20without%20deleting%20the%20sheet%20name%20or%20the%20exclamation%20mark%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20385px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120598i4C1AA104D0B53442%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Series%20Name.png%22%20title%3D%22Series%20Name.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERepeat%20for%20the%20Budget%20Series%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20after%20hitting%20OK%20twice%20the%20Line%20Chart%20will%20only%20reflect%20the%20valid%20period%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20deal%20with%20Zero%20values%20then%20in%20the%20select%20Data%20Source%20box%20%26gt%3B%26gt%3B%20Click%20in%20the%20lower%20Left%20corner%20%26gt%3B%26gt%3BHidden%20and%20Empty%20Cells%20%26gt%3B%26gt%3B%20select%20one%20of%20the%20options%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20715px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F120599i7A4D7ED612A2EF1D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Data%20Source.png%22%20title%3D%22Data%20Source.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BI%20am%20attaching%20the%20file%20for%20your%20reference%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725518%22%20slang%3D%22en-US%22%3ERe%3A%20Limit%20chart%20data%20displayed%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU%20very%20much.%20That%20it%20exactly%20what%20I%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
gmartin1595
New Contributor

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.

2 Replies
Solution

@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

Series Name.png

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

Data Source.png

 I am attaching the file for your reference

Hope that helps

Nabil Mourad

@nabilmourad 

 

THANK YOU very much. That it exactly what I needed.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies