Histograms in Excel


Histogram by Michael Pardo, Attribution License

Its easy to make histograms in stats software like SPSS, but what if you are working in Excel and just need a quick histogram. I seem to be in this situation all the time!

If you have Excel 2016, then there are new chart tools that make histograms a one or two step operation. See here for details https://support.office.com/en-us/article/Create-a-histogram-85680173-064b-4024-b39d-80f17ff2f4e8

For older versions of Excel you have three types of choices of varying complexity.

If you are used to using pivot tables in Excel, then that is the easiest and quickest way. See this posting for instructions on how here http://www.dummies.com/software/microsoft-office/excel/create-a-histogram-with-a-pivot-table-for-excel-dashboards/

Another way is to use the Histogram function in the free Analysis Toolpak in Excel. This is slightly more involved as you have to set up the bin ranges first. The bin ranges are the categories for the histogram columns. https://support.microsoft.com/en-us/help/214269/how-to-use-the-histogram-tool-in-excel

You can also use formulas to derive the counts for each category. This post shows you how and also runs through some of the other options above as well. https://www.ablebits.com/office-addins-blog/2016/05/11/make-histogram-excel/


Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s