12

Here i have a column chart of binomial distribution, showing how many times you can expect to roll a six in 235 dice rolls:

alt text

Note: You could also call it a binomial mass distribution for p=1/6, n=235

Now that graph is kinda squooshed. i'd like to change the Minimum and Maximum on the horizontal axis. i'd like to change them to:

  • Minimum: 22
  • Maximum: 57

Meaning i want to zoom in on this section of the graph:

alt text

Bonus points to the reader who can say how the numbers 22 and 57 were arrived at

If this were a Scatter graph in Excel, i could adjust the horizintal axis minimum and maximum as i desired:

alt text

Unfortunately, this is a Column chart, where there are no options to adjust the minimum and maximum limits of the ordinate axis:

alt text

i can do a pretty horrible thing to the graph in Photoshop, but it's not very useful afterwards:

alt text

Question: how to a change the x-axis minimum and maximum of a Column chart in Excel (2007)?

9 Answers 9

7

Right click on the chart and choose Select Data. Select your series and choose Edit. Instead of having a "Series Values" of A1:A235, make it A22:A57 or something similar. In short, just chart the data you want rather than charting everything and trying to hide parts of it.

1
  • 1
    You also have to change the x-axis label range (turns out there is an x-axis label range). Otherwise it just ends up starting at zero, rather than 23. But it works.
    – Ian Boyd
    Jan 3, 2011 at 3:36
5

Here is a totally different approach.

The screenshot below shows the top of the worksheet with the data in columns A and B and a sequence of charts.

The top left chart is simply an XY Scatter chart.

The top right chart shows the distribution with the X axis scaled as desired.

Error bars have been added to the middle left chart.

The middle right chart shows how to modify the vertical error bars. Select the vertical error bars and press Ctrl+1 (numeral one) to format them. Choose the Minus direction, no end caps, and percentage, entering 100% as the percentage to show.

Select the horizontal error bars and press Delete (bottom left chart).

Format the XY series so it uses no markers, as well as no lines (bottom right chart).

Data and evolution of the chart

Finally, select the vertical error bars and format them to use a colored line, with a thicker width. These error bars use 4.5 points.

Finished chart showing selected data

2

I came up against the same issue, it's annoying that the functionality isn’t there for graphs other than a scatter graph.

An easier work around I found was plot your full graph like you have above. In your case plotting the data in A1:A235.

Then, on the worksheet with your source data, simply select rows A1:A21 and A58:A235 and 'hide' them (Right Click & select Hide).

When you flick back to your graph it will refresh to only show the data from A22:A57.

Done

1

You can run the following macros to set the limits on the x-axis. This kind of x-axis is based on a count, i.e. just because the first column is labeled some number, it is still 1 on the axis scale. Ex. If you want to plot columns 5 through 36, set 5 as the x-axis minimum, and 36 as the x-axis maximum. (Do not enter a date for the kind of scaling you're trying to do here.) This is the only way I know of to rescale the "unscalable" axis. Cheers!

Sub e1_Min_X_Axis()
On Error GoTo ErrMsg

Min_X_Axis = Application.InputBox(Prompt:="Enter Minimum Date (MM/DD/YYYY), Minimum Number, or Select Cell", Type:=1)
If Min_X_Axis = "False" Then
    Exit Sub
Else
    ActiveChart.Axes(xlCategory).MinimumScale = Min_X_Axis
End If
Exit Sub    

ErrMsg:
    MsgBox ("You must be in a chart."), , "Oops!"

End Sub

Sub e2_Max_X_Axis()
On Error GoTo ErrMsg

Max_X_Axis = Application.InputBox(Prompt:="Enter Maximum Date (MM/DD/YYYY), Number, or Select Cell", Type:=1)

If Max_X_Axis = "False" Then
    Exit Sub
Else
    ActiveChart.Axes(xlCategory).MaximumScale = Max_X_Axis
End If
Exit Sub

ErrMsg:
    MsgBox ("You must be in a chart."), , "Oops!"

End Sub
1

Related to @dkusleika's but more dynamic.

Here is the top part of a worksheet with the numbers 0 through 235 in column A and the probability of that many sixes being thrown in 235 tosses of a fair die in column B. The Min and Max of the first column are given in E1 and E2, along with the initial chart of the data.

Data and chart of all of the data

We'll define a couple of dynamic range names (what Excel calls "Names"). On the Formulas tab of the Ribbon, click Define Name, enter the name "counts", give it a scope of the active worksheet (I kept the default name Sheet1), and enter this formula:

=INDEX(Sheet1!$A$2:$A$237,MATCH(Sheet1!$E$1,Sheet1!$A$2:$A$237)): INDEX(Sheet1!$A$2:$A$237,MATCH(Sheet1!$E$2,Sheet1!$A$2:$A$237))

This basically says take the range that starts where column A contains the min value in cell E1 and that ends where column A contains the max value in cell E2. These will be our X values.

Click on Formulas tab > Name Manager, select "counts" to populate the formula in Refers To at the bottom of the dialog, and make sure the range you want is highlighted in the sheet.

In the Name Manager dialog, click New, enter the name "probs", and enter the much simpler formula

=OFFSET(Sheet1!counts,0,1)

which means take the range that is zero rows below and one row to the right of counts. These are our Y values.

Now right click on the chart and choose Select Data from the pop-up menu. Under Horizontal (Category) Axis Labels, click Edit, and change

=Sheet1!$A$2:$A$237

to

=Sheet1!counts

and click Enter. Now select the series listed in the left box and click Edit. Change Series Values from

=Sheet1!$B$2:$B$237

to

=Sheet1!probs

If done properly, the chart now looks like this:

Dynamic chart plotting selected data range

Change the values in cells E1 or E2, and the chart will change to reflect the new min and max.

0

You can use Excel offsets to change the X axis zoom. See this tutorial.

-1

If you select the left axis, right click and choose "format axis" you will see Axis options that allow you to set the min / max values as fixed numbers. You can play with those settings to give you the results you prefer.

2
  • Welcome to SuperUser. The original post had references to the Format Axis dialog. Is there more information you can provide about configuring it to produce the desired results. May 7, 2013 at 19:32
  • I downvoted because the question referred to the horizontal axis, not the vertical axis. Apr 8, 2016 at 14:55
-1

Change your graph type to Scatter. Then set the axis as you wish. Then change back to a graph you desire, and it will keep the axis settings adjusted.

1
  • I downvoted this because it doesn't work. When you change back from a scatter chart to a column chart, the column chart category axis is reinstated, without any mechanism for changing axis limits. Apr 8, 2016 at 14:59
-1

Here's yet another approach. Excel is nothing if not flexible, if you know how to work it.

Double click the horizontal axis to format it. Change the scale type to Date. If Excel applies a date format to the axis labels, change back to a numeric format.

Choose 1/22/1900 (day 22) for the axis minimum and 2/26/1900 (day 57) for the axis maximum.

2
  • Horizontal axes don't have a minimum and maximum setting
    – JohnFx
    Apr 8, 2020 at 7:00
  • @JohnFx If you change the scale type of the horizontal axis to Date, as I specified, the axis scale minimum and maximum settings. Apr 9, 2020 at 15:13

You must log in to answer this question.

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