[Calc] hide empty rows in stacked area graph

[tonyrulez]

I have a stacked area graph that looks like this:

And the raw data (replaced with random values so it won’t correlate with the original graph):

Problem:
In value range, I selected A1:L70, so when the next month comes the graph will update itself. However the empty lines are still shown on the graph. The graph should look lite this in my opinion (which works in Google Sheet btw):

But I can only achieve this by modifying the range so it only includes filled out rows. However I do not want to do this every month (or more often if I need to do a stacked area graph for daily values…) The cells in row 64 and so on are totally empty, no formulas whatsoever that return “” or something else. How can I achieve the second graph look?

[LeroyG]

I can’t see a direct answer to the question.

EDIT: An option (simple to implement) is to hide the empty rows until needed.

---

Also you can use a Pivot Table, then you can create a Pivot Chart, and refresh/update it when you add new data.

See sample file.

/EDIT.

---

• Set your chart to include only the cells with data, so no empty space is shown in the chart.
• Enable Expand references when new columns/rows are inserted (choose menu Tools - Options… - LibreOffice Calc - General) (take note that this setting will affect all your spreadsheets).
• When needed, insert new rows just below the last with data, and add the new data.

Tested with LibreOffice 7.0.6.2 (x64); OS: Windows 10.0.

---

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark ( → ) to the left of the answer that solves your question (it may take a little while until it turns green).

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).

[tonyrulez]

Column M and so on also contains formulas (which are not part of the graph). When I insert a new row. these formulas won’t get copied into the corresponding cells. Is there an option that also allows formulas to be expanded into the new row?

[LeroyG]

I don’t remember there being an automatic way. You can select the cells with formulas, and drag down the fill handle (in the bottom right corner of the selected cells).

Inserting new cells from column A to L (as I thinked before) will leave a gap in the references of the formulas.