# Excel Binning

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).

So now you can count your data in custom bins and even plot a histogram over it. The only tricky thing is getting the right order for the bins in pivots/plots (since for example as strings “2” > “10”). I’m not sure if there’s a good way to do this in Excel (like an ordered factor in R).