We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Secondary x-Axis reflecting data point dates

asked 2020-11-24 14:27:36 +0200

AussieWayne gravatar image

updated 2020-11-29 04:01:23 +0200

Hi guys I have a Calc spreadsheet in which I have created a Line Chart with an x axis showing the passage of time in monthly increments starting from the first data point date ie 3rd of December then the next column was 3rd January, then 3rd February and continuing on in this fashion plus a y axis showing numerical values. I am trying to insert an Secondary x axis that shows the actual date contained within each of the Data Points across the columns of the chart. I have been able to insert the secondary x axis BUT it is showing the same dates as the primary x axis when I want it to show the dates relating to the various data points across the chart and not the monthly incremental columns. Does anyone have any advice how I can achieve this outcome please? Thank you in advance Thanks to the helpful suggestion by 'keme' I have now attached a representative sample spreadsheet with 2 charts contained within. One chart has 'Show Category' checked and therefore shows the correct date as a Label and the other has 'Show value as a number' checked and shows the correct input value as a Label. What I want to achieve is the DATE data to be shown across the top in a secondary x axis (not as a label) and relative to the physical position of the Data Point within the chart and the correct number values to be retained as Labels in the body of the chart as it is now Plus the primary x axis across the bottom of the chart to show meaningful dates (not these random dates) say at equal monthly spacings to show the related passing of time. Hopefully that explains it as I await a solution from someone far wiser than myself. C:\fakepath\Sample chart x axis problem.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-11-24 15:12:53 +0200

keme gravatar image

updated 2020-12-05 10:22:03 +0200

Make an X/Y chart with lines.

The "line chart" behaves much like bar/beam charts, and handles the "argument values" (X) as data labels, not coordinate values. This yields even spacing of the data points, not proportionally according to values.

With an X/Y chart, both X and Y values are taken as numerical coordinates.

Edit: Sample file containing chart with data point X values on a simulated X axis.

Final edit (?)

Your data table has categories down and data series across, so columns and rows change places compared to my previous responses.

Stepwise guide

Explaining how I reached this result based on the uploaded sample file.

  • Make a row in your table for the Y position of your "alternate axis". You can hide this row later if you want.
    • I used a formula to automate placement, just above the highest data value.
  • Double click the chart to get the tiny black handles. Your chart is in edit mode.
  • Right click the chart grid or one of the graphs in it, and select the last item from the context menu, Data ranges ... or something like that
  • Select the second tab, Data series
  • Click the button to add a data series
  • In the top right pane, select X values from the list
  • Set the data range to your row of dates, $Sheet1.$B$3:$G$3
  • Select Y values from the list.
  • Set the data range to point to the new row you just created (hodling Y position for the "artificial" alternate axis)
  • Click OK
  • Right click the new graph which has been added. Select Add data labels.
  • Right click the graph again and select Format data labels.
  • Select the Data labels tab.
  • Untick Show values and tick Show category
  • Adjust the label positioning to suit the layout of your chart.
    • Select placement centered, above or below
    • Rotate label if needed.
  • Right click the graph again and select to Format data series.
    • Select a line type/color which does not visually interfere with actual data graphs.
      I used - none -.
    • Select an icon for the data point.
      I used the last entry in Symbol, a vertical bar.
edit flag offensive delete link more


Thank you keme for your advice. I have recreated the chart type as X-Y scatter with lines and points BUT I still cannot find a way to make the SECONDARY x axis show the same dates as contained within the various Data Points shown across the chart? Currently the primary x axis has now changed to seemingly random dates that seem to not relate to the inputted data of the spreadsheet ie the first column of data has a heading of 03/12/19 but the first date on the x axis is now 12/10/19 which precedes the start date of the inputted data so is not featured in the spreadsheet data? The secondary x axis is identical to the primary and I cannot find a way to change either one of these to reflect the dates contained within the various data Points across the chart? I am ...(plus)

AussieWayne gravatar imageAussieWayne ( 2020-11-25 02:17:27 +0200 )edit

Yes, I seem to have misread your question.

See this help page. Does the "Category" item correspond to what you want to display?

Instead of inserting an additional x axis, you could simulate one by creating another line graph (an additional data series) using the same X values and constant Y value. This way you can place your date tags anywhere you like, with a line to represent the simulated axis (or perhaps just as well not), and/or data points for precise marking.

keme gravatar imagekeme ( 2020-11-25 11:53:16 +0200 )edit

keme thank you for link which I have had a look at but I am none the wiser unfortunately although I was able to add the numeric value of the Data Points onto the chart adjoining each Data Point so that is helpful. What I am attempting to achieve is the primary x axis showing monthly dates evenly spaced on the corresponding vertical grid lines PLUS the secondary x axis showing the date of occurrence of each of the separate Data Points. I can see the dates contained in the Data Points when I hover the mouse over the individual Data Point and it is there following the word "values" along with the numeric value as well? Is what I am trying to do even possible??? I am using Libre Office

AussieWayne gravatar imageAussieWayne ( 2020-11-26 05:06:53 +0200 )edit

Edited the answer above. See attachment. Are we getting close?

keme gravatar imagekeme ( 2020-11-26 09:19:49 +0200 )edit

keme yes your attachment is what I am seeking to achieve. I note that the top dates are shown as Labels. Am I able to insert these labels into the top of my original chart and delete the secondary x axis? I am trying to figure it out but maybe you could advise just how you did this? Thank you for your ongoing help

AussieWayne gravatar imageAussieWayne ( 2020-11-26 14:35:38 +0200 )edit

The process is a bit convoluted. Roughly, and by memory (not at my regular computer right now) :

  • Add a "mock" data series to represent the secondary axis.
  • Double click the chart so it gets the tiny black dot handles (not the larger square colored handles). That means you are in chart edit mode.
  • Right click your existing graph line to add a data series (menu item "data ranges" or something like that).
    • Use your mock data with the old x values to create the graph emulating a secondary axis
  • Right click the new graph/axis/whateveryoucallit to add data labels.
  • Right click again to adjust (or perhaps "format") the labels
    • Tick "Category"
    • untick "Values"

I think that's about it. Make adjustments for rotated text, colors, etc. See if you can make it work. I'll look into the details later if you need me to (unless someone else will ...(more)

keme gravatar imagekeme ( 2020-11-27 08:01:32 +0200 )edit

keme thank you once again for having a go at this. I am unfortunately unable to make it work. I can generate the chart with the legend and all the actual values inserted into the chart adjacent to the individual Data Points BUT both the primary and secondary x axes resolve to unusual date that have not been inserted into any of the data in the spreadsheet that then resolves to the chart. I cannot find any way to make either of these axes reflect the date values that are in the spreadsheet and are also visible in the Data Points within the charts when the mouse hovers over a Data Point. So I am at a loss to know what to try from here. I continue to Google in an effort to find a solution but surely it cannot be this difficult to do what is a basic and ...(plus)

AussieWayne gravatar imageAussieWayne ( 2020-11-28 14:05:57 +0200 )edit

Edit your original question to attach a file with the general structure of your data, and also containing your chart.
A copy of your working file, preferably, but remember that this is a public place, so if content is confidential, replace by mangled/random content.

Seeing the actual data, it is easier to create a working solution, and to give an explanation which fits the context.

keme gravatar imagekeme ( 2020-11-28 14:49:14 +0200 )edit

keme I have just posted the file as you suggested. Thanks

AussieWayne gravatar imageAussieWayne ( 2020-11-29 04:02:29 +0200 )edit

Hi guys does anyone have a solution to this little problem please? Please see the attached file if you wish to try and assist. Thank you

AussieWayne gravatar imageAussieWayne ( 2020-12-02 01:14:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-11-24 14:27:36 +0200

Seen: 101 times

Last updated: Dec 05 '20