# Data distribution in Excel 2010

I have a column with close to a million rows, with values ranging from 25-140000 (approx.), although not every value in that range is present. How can I get a graph showing the distribution of the values (probably a bell-type graph), preferably only the ones that actually exist?

-

Enter the pivot table.

highlight data insert pivot table.

Use column lables = data Use count of data on values
Then create your graph from the pivot table results.

-

If you want a chart that shows the distribution, including gaps for values not present, you'll probably want to use Excel's Frequency formula. This takes two arguments, the data array you're analysing and a bin array (to count the unique instances of those values). For the bin argument you can use an array of single values (1,2,3...n) or group values (5,10,15...n). With a range of 25-140,000, bins of a value greater than 1 may be preferable otherwise you'll end up with a chart with 140,000 columns and gaps (or other anomolies) will be hard to see.

Once you have your Frequency array, just chart the values as a column chart and you'll get something that resembles a distribution histogram.

Note: Frequency is an array formula, so you'll need to select the cell range you want it to fill, and finish it with [Ctrl]-[Shift]-[Enter].

EDIT: Here's a sample chart using the data parameters you mentioned. It uses random values so the distribution is relatively uniform, but it shows how dense the values would be.

It's also worth noting that Excel didn't choke too much on the calcs-I got a slightly delayed refresh on the chart formating, but it handled the amount of data fairly well.

-