Calc: How-to Auto-adjust Chart Ranges

[awds]

I finally found an easy way to make charts automatically accommodate changes in the size of the data series.

Having many rows of data that I want to chart individually, and have the charts adjust themselves automatically when I add new data points to the ends of the row, I tried several complicated methods that didn’t work, I finally discovered this method that works well enough that I wanted to make it known to help others who I have seen struggling with the same problem during my on-line search for a solution.

This method should work for columns as well as rows of data.

1. Extend the rows of the data series to a limit sufficient to meet your future needs.
2. Fill the extended area with a non-numerical character, like an asterisk. Doing this gives the charting algorithm something chew on otherwise it will ignore empty cells and shrink its scope to the numerical data only.
3. Create your chart so that data-range and data-series encompass all of your data and the additional cells containing asterisks.
4. Your chart should have a curve bunched at the left edge followed by nothing in the chart area and asterisks in the X or Y axis after the numerical data.
5. Select the columns containing the asterisks then hide the columns.
6. Your chart should now exclude the asterisked placeholders and show your data expanded to fill the chart area.
7. When you need to add a new column of data, unhide the columns of asterisked data then add your new data to the next column after the last numerical entry, replacing the asterisk.
8. Again hide that columns containing asterisks and your chart will show the new data-point appended to the right side of the curve.

This is a simple workaround to an annoying problem that should suffice until charts can accept named-ranges. Good luck! I hope this helps.

[Villeroy]

menu:Tools>Options>Calc>General: “Expand references when new rows/cols are inserted” = ON
This is a global setting for all spreadsheets.
Now any insertion within a range, directly below or behind the last column will expand all references to that range.
“All references” includes cell formulas, charts ranges, named ranges, database ranges, form controls linked to cells, conditional formattings, cell validations.
Any reference A1:B99 expands to A1:B100 when you insert a new row anywhere within or directly below.
Any reference A1:B99 expands to A1:C99 when you insert a new column anywhere within or directly behind.

With the option unchecked:
Insertion on top (A1:B1) moves down the reference. A1:B99 becomes A2:B100.
Insertion within expands the reference. A1:B99 becomes A1:B100.
Insertion directly below does nothing.

A reference to a vector (single row or column) will never expand.
For instance, a sum of a single row =SUM(A1:D1) will not expand when you insert new cells at A1:D1.
Same with a single column: =SUM(A1:A99) will not expand when you insert new cells at A1:A99.
The reference needs to include 2 rows at least (or 2 columns respectively).

Ctrl+ + is the shortcut to insert cells.
Ctrl+ - is the shortcut to delete cells.
In both cases a dialog asks for the direction where to shift the remaining cells.
When you select an entire row or column before no dialog pops up.

[joshua4]

If you do wish/need to have a named range that is a fixed size and to watch your chart “grow” as the range fills then you can combine the chart range with querying empty cells in the named range and run this as an update macro. Of course this would get trickier with more complex charts.

Sub FitChart(SheetName As String, ChartObjectName As String, RangeName As String)
	'SheetName is the obvious
	'ChartObjectName is likely "Object 1", etc., not the name you give it in the UI
	'RangeName is the name of the source range
	Dim Sheet As Object
	Dim Chart As Object
	Dim RangeCells As Object
	Dim RangeQuery As Object
	Dim RangeAddress As New com.sun.star.table.CellRangeAddress
	Dim LastBlankRange As New com.sun.star.table.CellRangeAddress
	Dim LastBeforeBlank As Integer
	
	Sheet = ThisComponent.getSheets().getByName(SheetName)
	Chart = Sheet.getCharts.getByName(ChartObjectName)

	RangeCells = ThisComponent.NamedRanges().getByName(RangeName).ReferredCells
	RangeQuery = RangeCells.queryEmptyCells()
	RangeAddress = RangeCells.RangeAddress 'Assume full range for charting
	
	If RangeQuery.getCount() > 0 Then 'There are empty cells found
		LastBlankRange = RangeQuery.RangeAddresses(RangeQuery.getCount() - 1)
		LastBeforeBlank = LastBlankRange.StartRow - 1
		If LastBlankRange.EndRow = RangeAddress.EndRow Then 'If range runs out with blank cells
			RangeAddress.EndRow = LastBeforeBlank 'Trim range for charting
		EndIf
	EndIf

	Chart.setRanges(Array(RangeAddress))
	'Note: If chart ever gets to just 1 value, you may have to reselect "Data in Columns" after adding new data
	
End Sub

If the data are reduced to just one datum then you may have to re-select Data in Columns (this example is for data in columns).

You do have to determine the object name, not the UI name, for the chart, likely such as “Object 1”. But the Sub provided could be called from a Sub that iterates through charts, responds to events, etc.

ChartFitter.ods (16.9 KB)