Calc charts: automatically increasing data range

[MiguelGuedes]

Is it possible to set up a calc chart such that, given a data table that is updated on a daily basis with new items, the chart automatically increases the data range?

The problem I’m having is that if I, say, set up a chart with the data range $Sheet1.$A$1:$C$16 but there is only data up to $C$10, the data range will revert to the latter. When the data is subsequently updated and new rows added to the table, the chart doesn’t reflect the changes and forces one to manually:

1. double click the chart area to activate it
2. right click and select Data Ranges
3. correct the value in the data range textbox

---

Linux x64; Libreoffice 4.0.2.2

[oweng]

Same question asked in relation to named ranges for charts in April 2013. I have posted a workaround in that thread.

[Pedro1]

To my knowledge this is a limitation in LibreOffice/OpenOffice.

The way to do it would be to create a Named Range and then use it as the range for the chart.

Unfortunately Charts don’t accept Named ranges…

Another long standing limitation is that you can not increase the data range by dragging the data selection borders, which would be better than manually typing the new range…

I will add Enhancement requests to the LibreOffice Bugzilla tracker.

[oweng]

Bug fdo#47584 is related. I think comments #5 and #6 in particular offer a possible alternate (to named ranges) solution.

[Pedro1]

@oweng, those are different ideas. But maybe one of the problems is that LO automatically adjusts the selection to cells with data. Why does it assume that it is smarter than the user? At least it should ask.

[oweng]

I agree it is a difficult problem. As Joel points out, constantly throwing a pop-up (e.g., if you are entering several new rows of values) would be annoying, I guess that is why I like the suggestion in comment #6.

[Pedro1]

Yes, the warning sign is the best option. But it needs to be displayed in both situations: in the case where there is more data that you didn’t select by mistake and this case where you want to select more cells than those with data. Calc needs to discreetly warn the user but NEVER override his decision.

[Lorenzo1]

There is a workaround.

1. First, go to Menu->Tools->Options->Libre Office Calc->General, then check the “Enlarge ranges references when
   adding rows/columns” checkbutton.
2. Add a row and a column to your range and fill them whit non-numerical chars, like “-” (minus) for instance. Let call this row/column in the following as “boundary”.
3. Define this new expanded range as range for your chart, to the “boundaries” included.

When necessary, add a new row/column selecting the “boundary” row or column and making “add row/column”.

The range in your chart will remain defined to the moved “boundaries” and will include the new added row/column.

[deadgnom32]

thanks, this is a good workaround, woks even better than expected.

[vo81]

Season’s Greetings!! Well, not an answer. I just wanted to add that on some charts I do give a bigger range than the data and I do not have to extend on a daily basis. But since a power related crash a few months ago some charts work as I wanted them to but on some charts the bigger range kind of “autofits” to the range with numbers in it. In this case, I have to extend the ange daily. *** My point is that since on some charts bigger ranges including cells with no numbers works fine it may not be Calc problem but some setting that got burned and I am not able to find out. *** Perhaps something related to how blanck cells are treated. I am a novice (user as against power user/ developer) to this wonder-land. Libre Office Version: 4.1.2.3, Build ID: 40b2d7fde7e8d2d7bc5a449dc65df4d08a7dd38 and Linux Mint (no idea how to find out the version, sorry) Thanks, Regards /* **** Editing after about 30 mins ********** / Well, I found some crazy solution to this… based on my hunch (earlier in this answer) that it has something to do with the blank cells … Here is what I did … Say my data is from A1 to C10 and one row gets added every day … I plot the chart as usual … try to give range as A1 to C100 … it snaps back to C10 … … so I add a dummy row at A99 to C99 (or any number big enough) … Rows 11 to 98 are blank … again edit chart to give a datarange A1 to C100 … This time it snaps to C99 … THEN … delete the 99th row - which was dummy anyway … datarange doesn’t snap back to C10 …*** I know this is not the RIGHT way but it works for me *** … BTW, where is something like “Ignore empty cells” box which I can perhaps tick … Hope it helps …