0

Want to draw a graph on a range of data which length can change depending on user input. So, empty cells at the end of the range should not appear on the graph, and the axis should not have these values at all.

Detail:

  • Compound interest calculator. User can enter investment period.
  • If the graph displays correctly for 10 years, and the user changes the period to 5 years: Then the graph should only show data (and axis values) up to 5 years, so, it should shrink to half the size.

Tried:

  • Filters to hide rows that should not appear: Works once, but then you have to undo and refresh the filter.
  • Dynamic named ranges: Empty or #N/A cells are not plotted, but still takes up space on the time axis.
  • Tried changing 'Hidden and Empty Cell Settings': Didn't work either.

Edit:

  • The named ranges almost work.
  • There are only 2 issues:
    • There are always 2 extra years added on the x-axis. See image below.
    • The x-axis starts at 1 instead of 0.

enter image description here

For those of us that don't know how to create a graph using named dynamic ranges (how I did it):

  • First create a graph on a visible range:
    • Select data including column names
    • Click 'Insert' and select a graph type
  • Create the range:
    • Select the vertical range (one of 3 in my case)
    • Go to Formulas | Define Name | Define Name
    • Enter a name
    • Enter a formula for example: =$A$21:INDEX($A$21:$A$42,COUNT($A:$A))
  • Edit the graph:
    • Right-click on the graph and select 'Select Data'
    • Select a series and click 'Edit'
    • In the 'Series Values' box, enter something like: ='Spread Sheet Name.xlsx'!RangeName
    • Where 'Spread Sheet Name' is your spreadsheet name and 'RangeName' is your range name.
Yster
  • 3,147
  • 5
  • 32
  • 48
  • 1
    Search on here, been asked before and has answers. – Solar Mike Sep 05 '21 at 21:08
  • Forgive me if I don't believe that, Mike. – Yster Sep 05 '21 at 21:37
  • How about sharing your knowledge, @SolarMike? – Yster Sep 05 '21 at 22:20
  • 1
    How is the data formatted? How does the user make the year selection? This sounds like an easy solution using a Pivot Chart and Timeline slicer, but you should be able to leverage the year selection to create Named Ranges that limit the data as well. – Jody Highroller Sep 06 '21 at 01:35
  • 1
    Sharing knowledge - I have. And on here. Or do you expect me to search and reference it for you? – Solar Mike Sep 06 '21 at 04:51
  • @JodyHighroller One enters the number of years into a cell. I did the named ranges again, and you're right. The only problem is that there are always 2 extra years added on x-axis. Will add an image. Will be very happy if you post a solution. – Yster Sep 06 '21 at 09:40
  • @SolarMike Sorry for not believing. – Yster Sep 06 '21 at 09:46
  • @JodyHighroller Tried a pivot table does not change when changing the duration. Incredible that it is so hard to do a simple thing like this. In Google Sheets this is the default. No need to do anything. Blank cells at the end of the range does not appear on the chart. – Yster Sep 06 '21 at 16:01
  • @SolarMike Can't find the solution on SOF. But (like I tried to say earlier) I do believe that the answer is on here. Will be glad if you can post the solution. – Yster Sep 06 '21 at 16:12
  • 1
    @Yster In your screenshot, is the Period column column A? If so, your range is picking up additional values from the duration and initial amount. I would use something like this instead =$A$21:INDEX($A21:$A$42,SUMPRODUCT(--(LEN($A$21:$A$42)>0))) Also, why is period showing up in your legend? You shouldn't have a "Period" series, that should be your chart axis. – Jody Highroller Sep 08 '21 at 10:51
  • @JodyHighroller You're a rockstar! Yes, the one issue was the 'Period' named range that should not have been a series, but on the x-axis. The other issue was my named range formula, that always added 2 extra cells on certain columns. Your formula worked like a charm. Thanks a million. You're welcome to add an answer to gain some points. – Yster Sep 08 '21 at 20:02

3 Answers3

2

In order to not plot the blank values in the chart, create a dynamic named range to find the non blank values. You can then use those ranges as the chart series.

This formula will return a range of contiguous values starting at A21.

=$A$21:INDEX($A21:$A$42,SUMPRODUCT(--(LEN($A$21:$A$42)>0)))
Jody Highroller
  • 999
  • 5
  • 12
1

Old question, but the answer is also old and has been oft repeated.

First of all, and not the problem, is that your chart has no X values. Period is the first series; notice how it appears in the legend? The values are single digits, though, so they are overwhelmed by the millions of dollars in the investment vehicle.

I also suspect your dynamic range names. If column A is the one with the periods, COUNT($A:$A) is counting two numbers in the information above the chart in addition to the calculations below the chart.

But the main answer is that it's easier to make a simple expanding dynamic chart like this without needing to use defined names. The trick is to convert your data range into a Table, which is a data structure with special properties. To do this, select the range, and on the Insert tab of the ribbon, click Table (or use the handy Ctrl+T shortcut).

Below I show a stacked column chart with a Table. The chart was constructed with Period as X, and Total Input and Interest as Y.

What's so special about a Table? In addition to the sort/filter buttons in the top row, it will expand dynamically when data is added. So type in Period 6, and a new blue row appears to include period 6 and its related values. And any formula that refers to a column of the Table will adjust so it continues to refer to that column of the Table. This includes chart SERIES formulas. The X axis will include the new period, and new bars will show the totals for the new period.

Dynamic Excel Chart with Source Data in a Table

Here's a tutorial I wrote long ago about the magical dynamic nature of Tables: Easy Dynamic Charts Using Lists or Tables.

Jon Peltier
  • 5,895
  • 1
  • 27
  • 27
0

Well, you can work with this:

enter image description here

Used "" so no values are given for X or Y axes. The #N/A is ignored for the second series. A method of highlighting a particular value in a chart that may be up for discussion.

As for the extra space on the rhs, can't control that (yet), but the binary values in Col A limit the Q shown.

I would also link the chart title and X & Y axes titles to cells on the sheet for flexibility as well, along with choose() to pick up which data to plot - when the data is changed and the titles match you can show lots of data in one single chart place.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • Thanks a million for the post, Solar Mike! Don't know how you did everything, but it's great that you got rid of the values on the x-axis. Too bad they take up space, because in my case, the max data range will be hundreds of cells. So, when a short duration is chosen, more than 90% for the chart will be blank. – Yster Sep 06 '21 at 19:03
  • @Yster then consider using indirect() to control the range the chart series function gets... Don't have time to play with that now though - work material to sort. All the functions on that sheet are shown though. – Solar Mike Sep 06 '21 at 19:08
  • Thanks for the advice Mike, will look at that tomorrow. – Yster Sep 06 '21 at 22:05