How do I create custom axes in Excel?

Question

I'm creating a "histogram" and need the values of "frequency" to be displayed in ascending or descending order, which means that I need custom values for the x-axis. Instead of having:

1 2 3 4 5 6 7

I need to have this be values of cells:

85 93 45 43 67 89 43

Is there anyway of doing this?

score 10 · Accepted Answer · answered Mar 23 '11 at 05:33

10

Lay out your data in the following format:

Category        Frequency
 85           3
 93           6 
 45           12
 43           20
 67           13
 89           5
 43           2

Start by inserting a new blank chat. Do not select any data. I would suggest using a column chart for this. Just go to Insert -> Chart -> Column -> 2D Column. It should be blank:

excel image

On the Design tab of the Chat Tools section of the Ribbon, click on Select Data. Under legend entries, click Add.

excel image

Select the Series Values box (not the "Series Name" box), and click+drag to select all of the data in the frequency column.

excel image

Click OK. Now, back in the Select Data dialog, click Edit on the "Horizontal Axis Labels".

edit axis labels

Click+drag to select the Category column:

edit titles

Now click OK on everything and set whatever other legend, title, and other settings you wish!

answered Mar 23 '11 at 05:33

nhinkle

37,198
36
140
177

I feel like an idiot for not spotting that. Good post. – James Mertz Mar 23 '11 at 06:16
1

nhinkle: what are you using for those screenshots? I usually use the Windows 7 Snipping Tool, but the results aren't usually that clean. – TuxRug Apr 10 '11 at 04:31
@TuxRug, I use [Shotty](http://shotty.devs-on.net/en/Overview.aspx), as suggested in "[Free screenshot program that respects Aero's transparency layer](http://superuser.com/q/148738/20088)". It doesn't always work perfectly, but most of the time, it does a great job of capturing nice clean screenshots with transparent windows and window shadows. – nhinkle Apr 10 '11 at 04:54
In Windows, you can also use the shortcut ctrl+shift+printscreen @TuxRug which will take a screen shot of the currently active window. – James Mertz Apr 12 '11 at 12:51
@KronoS: yes, but not as nice-looking. – TuxRug Apr 12 '11 at 18:51

score 1 · Answer 2 · answered Apr 12 '11 at 12:01

Next time you make this kind of chart, clear the label above the column of X values (A1 here, "Category"). The blank cell indicates to Excel that the first column and top row are different, so Excel will use the first column for axis labels and the first row for series names.

How do I create custom axes in Excel?

2 Answers2