Share via

How to make a bar chart where the X Axis labels come from your data?

Anonymous
2023-10-25T16:19:58+00:00

The little table below is from a histogram analysis of a dataset. I want to turn it into a simple bar chart where the X Axis is the first column ("Bin") and the Y Axis is the second column ("N").

Bin N 

  0     5  

  3    23  

 10    28  

 30    88  

100   210  

300    66

1000 50

3000 5

If I select the data and try to insert an X, Y ("Scatter") plot, it's not a bar chart.

If I select the data and try to insert the first first bar chart ("2-D Column, Clustered Column") it makes some sort of complete mess of the data which only totals it all moving to the right, with X Axis labels of 1 to 8... where the heck is that coming from?

So if I take the Clustered Column mess and right-click on its data to "Select Data", and then define it as one series where the Series Name is the first column and the Series Values is the second column ... then it is properly making the bars I want relative to the Y Axis. But the X Axis is still gorked. Look at this mess:

All I want is for the X Axis, instead of saying 1 to 8, say 0 to 3000, just like the data says.

And I don't want to have to laboriously do it by hand through something like actually creating 8 text boxes over the X-Axis labels and filling them in by hand. Surely there must be a way for Excel to use the actual data I already told it to use, for Pete's sake!

FWIW this would be absolutely easy if using an X,Y (Scatter) plot. The X Axis would properly take care of itself. But the X Axis seems to lose its marbles for any other chart type, lol. I am a scientist who is almost always only dealing in X,Y data.

I tried googling using a number of different search phrases but only get a zillion hits on - you guessed it - how to make a bar chart.

Thanks if you can help!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-10-27T04:05:11+00:00

    .

    Right-click the chart

    Select Data

    On the right side you can change the labels of the horizontal axis, click this button and select A2:A9:

    (Screenshot is not from the chart above!)

    .

    Andreas.

    2 people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-27T16:53:41+00:00

    That's got it! I had seen that "Horizontal Axis Edit" button, but I assumed it wasn't helpful since it looked like I would have to edit all the individual points it was showing. But like you say, if you just click it, it lets you assign them all in one action, from a selection in the worksheet.

    Great! This functionality is distinctly different from that of the X Axis in the X,Y (Scatter) plots ... but it's good enough. It'll do.

    Thanks again.

    0 comments
  2. Anonymous
    2023-10-27T02:18:46+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    Added 475 missing base data.

    Generate counting histogram from base data.

    Generate text Bin names from base data in exponential steps.

    Same links.

    0 comments
  3. Anonymous
    2023-10-26T14:32:51+00:00

    Thank you very much for spending time on this!

    But ... It doesn't address my problem.

    Your axis is still saying 1 to 8, instead of 0 to 3000.

    Otherwise you've sort of shown what I showed... albeit you turned it sideways and used an actual log axis. (But I don't want the N counts on a log axis, even though you have correctly gathered that it's log data, as shown by the Bin boundaries.)

    I do not want 1 to 8 appearing anywhere on any axis for any reason. It should not be anywhere on the graph at all. Also I really hope I don't have to fake it by hand, by putting 8 text boxes over the axis values... why can't my data be used for axis labels?

    I am trying to show histogram data. Which is to say, frequency counts for occurences in particular bins.

    P.S. In trying to google on this topic, I found a QI Macros page that says:

    Line and bar charts are often the "dumb and dumber" of Excel charts. 

    I believe it! ha ha

    Thanks again for trying to help!

    0 comments