Excel Graph should not have trailing empty cells take up space on axis

Question

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.

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.

Answer 1

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)))

Answer 2

Well, you can work with this:

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.

Answer 3

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.

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