Make X-axis labels show the dates in the series

I am building a bar chart of covid-19 cases in CA; the data comes from COVID-19/csse_covid_19_data/csse_covid_19_time_series at master · CSSEGISandData/COVID-19 · GitHub . The plot is of (X-axis) dates shown in row 1, cells L1:CM1, (Y-axis) cumulative number of cases show in row 63, cells L63:CM63 The chart comes up fine; the Y-axis is labelled properly, but the X-axis simply shows the number of the bar, starting from the left. How do I make the labels of the X-axis show dates taken from row 1?

Here is the spreadsheet containing the chart (Thanks to Opaque):

I will be happy to post the data, if anyone can show me how

Click the edit pencil right above and use the clip symbol to attach your file image description

The x-axis of a columnn chart isn’t just scale like in a x-y-scatter chart. It’s getting what it is supposed to show from a range tiitled ‘Lables’ in th rtespective dialog.

You sure that after 2022-10-30 everything is over?

I was suffering the same problem as the OP for many days after converting a “Columns + Line” chart to a “Stacked Columns + Line” chart when, Hey Presto!, all the formatted dates disappeared from the x-axis of the chart immediately after conversion, being replaced by numbers - the exact problem as the OP (see his CA.ods). Nothing I could do seemed to bring it back until I came across @Lupp’s answer on this page. However, I still had to spend a couple of hours trying to decrypt his answer, since I could not make any sense of “It’s getting what it is supposed to show from a range tiitled ‘Lables’ in th rtespective dialog”, so here is an expanded explanation for others as thick as me.

My spreadsheet is set up with:

  • First row contains Headings for the data columns that are below them
  • First column is headed ‘Date’ and contains formatted dates for each row of data to it’s right

Thus, in extreme simplified form, the SS looks like this:

Date   Data1  Data2
Jan 1  12345  abcde
Jan 2  67890  wxxyz

In the middle of using the Chart Wizard we get to use the Data Ranges dialog. Another way to get there is to:

  1. Right-click on an existing Chart and choose ‘Edit’
  2. Right-click on the now-selected Chart & choose “Data Ranges”…
  3. On the first tab of the Data Ranges dialog (Data Range) put a tick✔ within both “First row as label” & “First column as label”
  4. On the second tab of the Data Ranges dialog (Data Series) place your cursor within the “Categories” section
  5. Enter the reference for the entire column of dates, NOT including the Header (eg for myself that is currently “$‘2020 Stats’.$A$2:$A$117”, or see Lupp’s chart within the SS in his answer)
  6. Repeat for each Data Series
  7. Press OK

The final item is:

  1. Right-click on an x-axis label & choose “Format Axis…”

(if the x-axis labels do not show, then right-click on the Chart, then select “X-Axis” in the top drop-down, then click on the “Format Selection” icon next to it (which gets the same dialog as previous), select the “Label” tab and put a tick in :ballot_box_with_check:Show Labels” & press OK)
2. On the first tab (“Scale”) you can now choose either “Automatic”, “Date” or “Text”.

The difference is that “Date” will show an empty column for any missing dates, whereas “Text” will not.

A final shout-out & thank you to @Lupp for showing me how to fix a knotty problem in a simple fashion.

(tested using LO Version: under Devuan Linux 2.1 (ascii))

If this helps then please tick the answer (:heavy_check_mark:)

…and/or show you like it with an uptick ()

1 Like