How to avoid "More" bin using Data Analysis Histogram (Excel 2010)?

Question

When using the Data Analysis Histogram in Excel 2010, is there a straight-forward way (read: not a work-around) to avoid the "More" bin?

The result that I want is:

I have two ways to work around it myself. But they might be difficult or undesirable for unskilled users.

I am asking if I am simply misusing the Data Analysis Histogram feature, and if I just did "this" or "that", the DAH feature itself would not create the "More" bin, in the first place.

---

The following explains my example in more detail. You can download "data analysis histogram.xslx" . Ignore any preview errors and request to log in.

1. In column A, I enter the following data: 10 ones, 20 twos, 30 threes, 40 fours and 50 fives.
2. In column B, I enter the bin limits: 1, 2, 3, 4 and 5.
3. On the Excel "ribbon", I click Data > Data Analysis > Histogram, and I fill in the following parameters:

FYI, using the DAH feature, my work-around is to specify B2:B5 as the bin range. After clicking OK, I replace the "More" label in C6 with the number 5.

Alternatively, with the original bin range (B2:B6), after clicking OK, select the "More" bin and data (C7:D7), and click Delete > Shift Cells Up.

Both give me the desired result, shown above (second image).

But I want to avoid describing those hacks to other users, who might be unskilled.

Is there a way to fill in the DAH dialog box so that the DAH feature itself does not create a "More" bin?

If "no", so be it. Again, __I__ do not need any help with work-arounds. I just want to be sure I am not overlooking the "obvious".

PS.... I have also learned that in Office Excel 365 and Excel 2016 and later, there might be a more direct way to create the "histogram chart". But I want to use only "legacy" features.

Answer 1

FYI, using the DAH feature, my work-around is to specify B2:B5 as the bin range. After clicking OK, I replace the "More" label in C6 with the number 5.

But I want to avoid describing those hacks to other users, who might be unskilled.

IMHO there is no way to prevent the "more" bin, your workaround is very good.

For unskilled customers I would recreate the UI in an AddIn and use my own code, like the sample below (for others that might not have your skills ;-). So we can show the histogram using a button in "public".

Andreas.

Sub CreateHistogram()
Dim InpRng As Range, OutRng As Range, BinRng As Range

'Input from user interface
Set InpRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set OutRng = Range("F1")
Set BinRng = Range("B2", Range("B" & Rows.Count).End(xlUp))

'Chart creation
#If UseReference Then
'Enable addin 'Analysis ToolPak - VBA' and set a reference to 'atpvbaen.xls'
Histogram InpRng, OutRng, BinRng.Resize(BinRng.Rows.Count - 1), Chart:=True
#Else
Application.Run "ATPVBAEN.XLAM!Histogram", InpRng, OutRng, BinRng.Resize(BinRng.Rows.Count - 1), 0, 0, 1, 0
#End If
OutRng.Offset(BinRng.Rows.Count).Value = BinRng.Cells(BinRng.Cells.Count)
End Sub

Answer 2

IMHO there is no way to prevent the "more" bin, your workaround is very good.

Thanks for the confirmation.