# Excel Binning

excel
Published

April 23, 2020

Putting numeric data into bins is a useful technique for summarising, especially for continuous data. This is what underlies histograms which is a bar chart of frequency counts in each bin. There are two main ways of doing this in Excel with groups and with vlookup (you can also do this in SQL).

If you want equal length bins in a Pivot Table the easiest way is with groups. Right click on the column you want to bin and select `Group`

Then enter the start and end of the bin and the length of the bins.

You then get a binned view in the pivot table.

What about if you want irregular size bins, or outside a pivot table? One way is with nested `IF` statements, but this is error prone and hard to maintain.

``IF(B2 < 0.1, "0 - 0.1", IF(B2 < 0.3, "0.1 - 0.3", ...``

An easier way is to make a separate table for your bins that are ordered.

To get the labels I concatenated the values of the bin endpoints: `=CONCAT(F5, " - ", F6)`. I also labelled the region as `Bins` (in the top left) to make it easy to refer to.
Then you can lookup the value with `VLOOKUP`. By default it does a `RANGE` lookup, returning the first row in the table where the value is at least `Bin Start`.
For example I used the formula `=VLOOKUP([@Value],Bins,2,TRUE)`. The first argument is the value to lookup (here I used a column Value of a table, the second is the area we lookup from (the data range `Bins`), the third is the column number of the table to lookup the value from (here 2, the `Bin Value`) and finally if we want a range lookup (`TRUE` - that’s how the binning works).