CODEWITHSUNDEEP
excelexcel-formulacharts
Automation Monkey
Automation Monkey

Reputation: 365

Show only data between dates in excel chart

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.

Net Profit Chart

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:

enter image description here

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

Upvotes: 1

Views: 620

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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.

enter image description here

A Quick Gif on the dynamic chart

enter image description here

Steps:

  1. Select the data, and from Insert Tab --> Under Charts Group --> Click Insert Column Or Bar Chart --> Select 2D Clustered Column
  2. 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))
  1. Copy the formulas and place in Name Manager by defining the respective names for each. Like as below:

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

  1. 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

Upvotes: 1

Related Questions