Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer

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.Frequency Chart

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.