Another simple way to achieve this is to use Pivot Tables > Grouping option. You can use either formulas or data analysis tools to achieve this. You just need an intermediate step to convert raw data to frequencies by bin. If you do not have Excel 2016, you can still create histograms.
How to make histograms in older versions of Excel Here is an example Pareto chart on number of calls by amount with bin size = $25. You can use custom bin settings too. Just select the chart’s horizontal axis and customize. Here is an example Pareto chart of purchase amounts by representative.
Go to Insert > Statistic chart > Pareto chart. Select a column containing values, optionally include column with category information. Using this chart, you can understand which bins / categories contribute most. Cumulative frequency is shown on secondary axis. In this chart, bins are arranged in descending order of frequency.
Skip ahead to last section of this post if you want to know how to make Histograms in Excel 2013, 2010, 2007 or earlier versions. Using these you can quickly make a histogram and understand the frequency distribution and outliers. But in Excel 2016, Microsoft introduced various new charts including Histograms and Pareto charts. This is where you take raw data and calculate the frequencies by bins. Prior to Excel 2016, making histograms involved an intermediate calculation step. Histograms are a great way to explore the underlying distribution of values. If you visualize the distribution of calls by duration like below, you will get a better picture. If you just average all call lengths, you might get some value like 90 seconds. You have the call log from last month and you want to know how long customers talk to your representatives. Let’s say you run a customer care center. Histogram chart shows distribution of data by grouping it in to bins (range of values). Sounds interesting? Let’s get started then. Creating histograms in old Excel (2013 or earlier versions).Let’s learn all about histograms and Pareto charts in Excel 2016. Time for some statistics and charting fun.