Show only data between dates in excel chart-CodePudding

I have yearly net profit data that uses spill formulas in Columns A and B and in columns D and E I have used start and end date selectors in G2 and I2 to gather the subset of data for the chart.

The chart data has the Net Profit data range set to ='Yearly Net Profit'!$E$2:$E$35 and the years are set to ='Yearly Net Profit'!$D$2:$D$35.

As you can see from the image below, the chart graphs all the blank space from D10 onwards which I don't want.

I tried using ='Yearly Net Profit'!$E$2# but unfortunately it doesn't like this.

I then added two entries in to the Name Manager, one for YearsForYearlyNetProfitFiltered set to ='Yearly Net Profit'!$D$2# and the second YearyNetProfitFiltered set to ='Yearly Net Profit'!$E$2#.

I re-selected the data for the chart, so Net Profit data became ='Yearly Net Profit'!YearyNetProfitFiltered and the Years became ='Yearly Net Profit'!YearsForYearlyNetProfitFiltered.

Unfortunately this results in only one entry being visible, see image below:

You'll see in cells N and O I have output the named ranges. Why would the named ranges only have one entry?

CodePudding user response：

I am not sure whether you are looking to create a dynamic chart or not, but you may try to follow the steps as is mentioned.

A Quick Gif on the dynamic chart

Steps:

Select the data, and from Insert Tab --> Under Charts Group --> Click Insert Column Or Bar Chart --> Select 2D Clustered Column
Now, create two formulas one for the YEAR and another for the NETPROFIT

• Formula for YEAR

=INDEX($A$2:$A$24,MATCH($D$2,$A$2:$A$24,0)):INDEX($A$2:$A$24,MATCH($F$2,$A$2:$A$24,0))

• Formula for NETPROFIT

=INDEX($B$2:$B$24,MATCH($D$2,$A$2:$A$24,0)):INDEX($B$2:$B$24,MATCH($F$2,$A$2:$A$24,0))

Copy the formulas and place in Name Manager by defining the respective names for each. Like as below:

Next, select the chart, Chart Design --> Select Data
Under Legend Entries Series, Click On the Series of Net Profit --> Click Edit, a new window opens as Edit Series

In Series Values remove everything except the sheet name and press F3 (Function Key) and select NETPROFIT from Paste Names

Like wise from Horizontal (Category Axis Labels) click on edit and do the same as above in bullet 6

Press Ok, to return back to Excel. Change the Start Year & End Year to see the changes you need it will show only the data from the range of years shown in the cells D2 & F2.

Note: I have used Millions as display units for Y-Axis. Here is a copy of the workbook