2

My x-axis for an Excel graph is missing some labels and I cannot figure out why.

More specifically, my x-axis is supposed to look like this (I will bold some of them so it's easier to read):

50, 100, 150, ..., 500, 50, 100, 150, ..., 500, 50, 100, 150, ..., 500, 50, 100, 150, ..., 500, 50, 100, 150, ..., 500, 50, 100, 150, ..., 500, 50, 100, 150, ..., 500, 50, 100, 150, ..., 500

Instead, it looks like this:

50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450

So it skips the number 500.  I made sure that the label 500 was checked in the category filter so it would appear on the graph.  I also searched online but cannot find a solution.  Any help would be appreciated.

Here is a screenshot: enter image description here

The graph shows the percentage improvement for 80 different cases.  (The percentage improvement is the y-axis and the 80 different cases are on the x-axis).

The reason why I have 50, ..., 500 repeated 8 times is because I'm looking at 8 different scenarios for the cases 50, ... , 500.  (So we have 8*10 = 80 different cases in total.)

To create the chart, set up data like this in the worksheet:

 X Y    
50 51.61440739
100 105.2234802
150 153.4851506
 ︙ ︙   

where the data in the X column are formatted as text.  Then create a Line chart, using defaults throughout.

Below is a screenshot of how the label 500 is checked:

enter image description here

4
  • 1
    Can you include some screenshots to better diagnose please. – NetworkKingPin Jun 17 at 15:26
  • @NetworkKingPin I added a screenshot – Artus Jun 17 at 20:11
  • 1
    A screenshot of the chart isn’t very helpful. Please tell/show us more of how you’re getting the chart; i.e., show us your data and maybe some of the chart properties. You say “I made sure that the label 500 was checked”; tell us and/or show us where you did that. – Scott Jun 18 at 1:11
  • @Scott I tried to clarify the question and added another screenshot to show how I checked 500. – Artus Jun 18 at 7:34
2

You’re misrepresenting the problem.  You say,

… my x-axis … looks like this: 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, 50, 100, 150, ..., 450, …

… when in fact it looks like

50, 150, 250, 350, 450, 50, 150, 250, 350, 450, 50, 150, 250, 350, 450, …

It’s not skipping just 500; it’s skipping 100, 200, 300 and 400 also.  So it’s a good thing that you posted the image of the chart!  Here is a detail from your image:

Excerpt of OP's illustration

In other words, it’s skipping every other value.  It’s doing so because, at the scale and format you are using, there isn’t room to display all the labels.


I was able to (roughly) reproduce your chart:

My chart that looks sort-of like the OP’s chart

I found three ways to display all the X values (labels).

  1. Simply stretch the chart so there’s more room:

    My chart, but stretched (horizontally)

  2. Make the labels smaller.  Right-click on one of the labels (“50”, “150”, “250”, etc.), select “Font…”, and set the font size smaller.  The above two images use 9 pt. text (the default); this uses 6 pt.:

    My chart, but with labels using 6 pt. text

  3. Tell Excel to display all the labels.  Right-click on the X (horizontal) axis and select “Format Axis…”. A “Format Axis” panel will appear (on the right side of your window).  Click the fourth (last) icon, with hover-text “Axis Options”, if it isn’t already selected.  Scroll down to “LABELS” and expand it.  Change the “Interval between labels” to 1:

    “Format Axis” dialog

    Excel automatically turns the text vertical to make it fit:

    My chart, but forcing all the labels to appear

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.