Search code examples
excelgraphtrailing

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

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

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