Calc chart/graph is empty at the beginning. How to consider only filled cells, not empty ones? (dynamic chart?)

[wrangle855]

In Libre Office Calc (25.2.3.2 Linux Debian stable), my column “A” has dates like 01.07.2025, 02.08.2025, 01.09.2025

I am using that date column in a chart (on X axis, Y is amount), the data series “Categories” is “$Table.$A$2:$A$999”

999 because i do not want to bother editing chart data each time i add new row.

Problem is that the Calc for unknown reason is plotting the chart the way that it is empty at the beginning (despite my A column is not empty at the beginning, but empty at the end (my last filled cell is A134 and rest is empty) and as a time will pass, i will be filling next cells in the A column and need the chart/graph to consider/plot only filled ones.

How should I edit the "Categories" "$Table.$A$2:$A$999" or what exactly to edit (if you can please describe the steps since i am layman in Calc).

This unanswered question MAY be similar: diagram - Dynamic data range in Libreoffice calc - Stack Overflow

[karolus]

Start your chart with the actual data, and later drag&drop the extended Data on the Chart, its a matter of seconds.

or attach a Example.ods with dummy-data, so we can examine.

[wrangle855]

Non helpful post that does not solve the issue:
I can not see the way to DM you, so i will waste the space of this thread by my lame questions:

What exactly is meant by starting chart with actual data? I see that I can indeed select all data in table, and drag/drop it to the chart and it shows more or less similar chart as before, with up to date data:

$Table.$A$1:$G$134,$Table.$I$1:$J$134

but the Data ranges / Data series remains fixed (i see $134 there, and when i add next rows, the chart becomes outdated, and obviously i do not want to bother constantly drag&dropping the data), so my questions remains and if you can please describe the steps since i am layman in Calc. My question is general, I do not want to spend time composing sheet with dummy data.

My dummy data are:

A.............B
Date..........Amount
01.01.2000....100
02.02.2000....200
01.03.2000....150

[LeroyG]

@wrangle855

each time i add new row

How do you add new rows of data? With copy & paste? Using the keyboard?

What chart type are you using? XY (Scatter), Line, Column?

As a workaround, if do you add to the actual data range one empty row below, you can insert empty rows as needed above this empty row, then add the new data. You can add to the last row the text “add data above this row”.

[wrangle855]

@LeroyG

How do you add new rows of data?

keyboard

Chart type is “Area”, “Stacked”

I have not understood your last paragraph. Please provide me exact steps on how to achieve it. (click XY, select “XY”…).

I am seeking universal steps on how to achieve what i need, not any custom solutions which may be useless for most people who come to this page. Thank you in advance.

[LeroyG]

@wrangle855

Chart type is “Area”, “Stacked”

Select from the next row down, and insert rows above. (Edit 2025-09-04) This will add the inserted rows to the chart data range. (/Edit)


Add the new data.

The cell background color is just to see the chart range.

[wrangle855]

@LeroyG

Select from the next row down

Offtopic: Thanks, I am able to do this, but I have not understood what this is supposed to solve. Btw. in one of the rows, I have prefilled a function and yours described process requires me to always remember to copy the function into newly added line. So in this regard would be better to just fill existing lines.

Ontopic - clarifying what issue happens: The empty X axis at the beginning of the chart (going back to year around 1999 while my first date cell is for year 2014 (01.08.2014), see the attached image

) is caused by going to editing chart Data ranges (double-click the chart and click chart icon with the cell - Data ranges), clicking first “Data series”, then clicking “Y-Values” Data range and changing “Range for Y-Values” from $Table.$B$2:$B$134 to $Table.$B$2:$B$135 (considering the line 135 is empty) and last filled is 134.
My target is that i can set 135 but graph would not plot the empty values instead of showing weird lines back to 1999.

[LeroyG]

@wrangle855

considering the line 135 is empty

This means that the date in row 135 is 0, and day 0 is 1899-12-30.
To avoid 0, repeat (Ctrl+D, Fill Down) in the row 135 the date of row 134.

[wrangle855]

@LeroyG

repeat (Ctrl+D, Fill Down) in the row 135 the date of row 134

I have not understood this, but asked AI, which suggested to select last two cells with dates, and use the + sign in the bottom-right corner of these cells, drag it down to the last cell, where date should be filled. It prefilled dates. But this causes empty chart at the end (previously it was empty at the beginning), so this is not good either. I have wasted like 30 minutes playing with AI, but was unable to find “Missing values” (sometimes labelled “Empty cells”) option or other way insert any math which would not plot the empty data.

[LeroyG]

@wrangle855

the + sign in the bottom-right corner of these cells

This is the fill handle.

@wrangle855

this causes empty chart at the end

Just insert new rows when you will add new data.

[wrangle855]

@LeroyG

Just insert new rows when you will add new data.

I assume that you mean the way you have already described, but as said, it causes empty chart at the end. When I empty the date column of the “empty” rows (empty because it does not contain any values beside the date, that i have now deleted). then the graph is empty at the beginning instead of at the end. So the problem of this discussion thread remains, how to not plot cells without data (numbers) while still considering the cells so the chart data sources does not need to be repeated updated once these cells are filled with numbers as time will go.

[LeroyG]

@wrangle855

how to not plot cells without data (numbers) while still considering the cells so the chart data sources does not need to be repeated updated once these cells are filled with numbers as time will go

Just select the last cell with date and as many cells downward as needed, and press Ctrl+D:

Or, drag down the fill handle in the last cell with date while holding Ctrl:


Edit: Once the cells (i.e., A1:A9) are filled, you can active one (i.e., A5) and fill down with double click on the fill handle (holding Ctrl, to repeat the same date; without Ctrl to increment by one day each row): AutoFill.

[wrangle855]

Thank You, your suggestion causes copying content of the last cell with date, into next cells, causing graph to show properly as I needed.
Though the downside of this “hack” is that it requires pre-filling the data instead of a graph using some logic, where it would ignore €mpty cells in its calculation/plotting. Though maybe if that would be possible, it would be too difficult to do for a layman like me. Thank you

[Lupp]

What about …
disask_126079_GapsInX.ods (66.1 KB)

[wrangle855]

No, I do not understand what is different from my file. Please mention exact steps for anyone to follow.

[Lupp]

I won’t write an essay.
Remove confident data from your document, and attach the resulting file to your question.
One of the contributors may then be able to give reasonable and helpful advice without wasting a lot of time.