2

I am trying to create a simple line chart in Excel, using the data such as the following:

enter image description here

However, when inserting the graph using this data, the X axis is labelled with daily intervals, rather than yearly, which is the data I have used (the first column in the table), giving me a graph such as:

enter image description here

How can I get the X axis to plot the points on the annual basis as per the data in the first column, rather than on a daily basis, which it seems to be doing at the moment?

Edit in response to answer:

I removed the first column, and added it back as the Horizontal Axis labels, but for some reason, it only displays the first cell's data:

enter image description here

When I right-click on the x axis, and choose Select Data, for some reason, the data selected only gives the contents of the first cell in the selection, and the rest appear blank:

enter image description here

Improve this question
1
  • Check which data is used as x-axis, it seems to be the wrong data set (series 1 being the years you actually want).
    – Excellor
    Commented Aug 9 at 12:21

1 Answer 1

Reset to default
2

If I am not mistaken, your chart should be like this:

enter image description here

Presently the Series 1 shows under legend entries as per your screenshot it needs to be removed and needs to placed under Horizontal Axis lables, like the one I have shown.

  • For doing this select the chart, From Chart Design Ribbon Tab --> Click on Select Data.
  • Select Series 1 which is Column A and Remove.
  • Now, select Edit under Horizontal Axis Lables and select the range from Column A and press OK Twice. You will see the desired output.

Also, in order to make the Chart Dynamic I will define the series name using a dynamic formula and use it within the Chart for Legend and Horizontal axis. Please refer the screenshot 2nd one.

enter image description here

• For Years:

=$A$1:INDEX($A$1:$A$7,COUNT($A$1:$A$7))

• For Series 2:

=$B$1:INDEX($B$1:$B$7,COUNT($B$1:$B$7))

• For Series 3:

=$C$1:INDEX($C$1:$C$7,COUNT($C$1:$C$7))
Improve this answer
10
  • Thanks for your answer. I gave what you suggested a go, but after removing series 1 (the years), and selecting them as the Horizontal Axis Labels, I only get the first cell displayed in the horizontal axis - not sure why this is... Will update my OP to show it now.
    – Noble-Surfer
    Commented Aug 9 at 13:19
  • You shouldn;t be getting the first cell only unless you are not selecting the whole range. Not sure what you did
    – Mayukh Bhattacharya
    Commented Aug 9 at 13:21
  • @Noble-Surfer are you using Pivot Tables? And do you have empty cells in between ? Your initial screenshot shows only years, please post your actual source data
    – Mayukh Bhattacharya
    Commented Aug 9 at 13:27
  • No, I'm not using Pivot tables, and although the data was originally populated in this table using a number of VLOOKUPs to copy it from elsewhere in the workbook, I have copied and used 'paste special' to paste the values looked up over the top of the formulae, so these cells now only contain the values, not the formulae. There are no blank cells between years.
    – Noble-Surfer
    Commented Aug 9 at 13:32
  • 2
    @Noble-Surfer that is why I have suggested a dynamic solution using defined names. It will be dynamic, you dont have to worry for the range.
    – Mayukh Bhattacharya
    Commented Aug 9 at 14:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .