Time for some statistics and charting fun. Let’s learn all about histograms and Pareto charts in Excel 2016. You will learn
- What, why and when?
- How to set up and customize histograms
- How to use Pareto charts?
- How to create dynamic histograms?
- Creating histograms in old Excel (2013 or earlier versions)
Sounds interesting? Let’s get started then.
What is a histogram and why use it?
Histogram chart shows distribution of data by grouping it in to bins (range of values). Let’s say you run a customer care center. You have the call log from last month and you want to know how long customers talk to your representatives. If you just average all call lengths, you might get some value like 90 seconds. But this will not tell you the story. If you visualize the distribution of calls by duration like below, you will get a better picture. That is what a histogram is.
Histograms are a great way to explore the underlying distribution of values.
So how to create a histogram in Excel?
Prior to Excel 2016, making histograms involved an intermediate calculation step. This is where you take raw data and calculate the frequencies by bins. But in Excel 2016, Microsoft introduced various new charts including Histograms and Pareto charts. Using these you can quickly make a histogram and understand the frequency distribution and outliers.
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.
To create histogram in Excel, you need some data. Let’s say you have data like this and you want to understand call duration distribution.
To create histogram:
- Select the call duration column
- Go to Insert > Statistic Chart > Histogram
- You will get histogram of call duration with default binning (using Scott’s normal reference rule)
Customizing Histograms
The histogram chart offers very few customization options (compared to other charts like column / scatter plot etc.). That said, you can customize the most important thing – how Excel bins data.
To customize the bins, select the category / horizontal axis and press CTRL+1 to launch formatting options. You can also right-click on axis and choose format axis options. From here, you can choose how you want to bin your data.
Options for binning and what they do:
- Automatic: This is the default option. It bins your data by dividing it in to bins of width h, where
h= (3.5 * sample standard deviation) / (n1/3). - Bin width: If you know more about your data, you can set a custom bin width to analyze frequency by that. For our call center data, bin width of 15 or 30 seconds or even 60 seconds might be interesting.
- Number of bins: You can specify number of bins and let Excel decide the width.
- By category: Use this if you want to bin data by a category value. To use this option, you need to set category axis to some labels. For example, you can explore number of calls by representative this way.
- Overflow and Underflow settings: Use this to set limits on values to bunch at both edges of your distribution range.
Example Histograms
Check out below example histograms to understand how each of the binning settings work.
Default Histogram – Calls by duration
Histogram – Calls by duration, bin width = 15 seconds
Histogram – Calls by duration, number of bins = 10
Histogram – Calls by duration, bin width = 15 seconds, outliers grouped.
Histogram – Calls by Representative – bins grouped by category
Create interactive histogram
You can combine histograms with interactive features in Excel like slicers or form controls to create amazing histograms. Check out below demo:
To create an interactive histogram, follow below instructions.
- Make sure your data is formatted as table.
- Add a slicer on the column you want filter. Use Insert > Slicer option.
- Insert histogram as usual
- Now, if you operate the slicer, the histogram gets filtered too and shows distribution only for sliced values.
Related:
- Everything you ever wanted to know about Excel slicers – in this handy guide.
- Using form controls in Excel charts – Example
What is a Pareto Chart then?
A Pareto chart is a special kind of Histogram. In this chart, bins are arranged in descending order of frequency. Cumulative frequency is shown on secondary axis. Using this chart, you can understand which bins / categories contribute most.
Here is an example of Pareto chart:
How to create Pareto charts in Excel?
The process is same as Histogram. Select a column containing values, optionally include column with category information. Go to Insert > Statistic chart > Pareto chart. Here is an example Pareto chart of purchase amounts by representative.
You can use custom bin settings too. Just select the chart’s horizontal axis and customize. Here is an example Pareto chart on number of calls by amount with bin size = $25.
How to make histograms in older versions of Excel
If you do not have Excel 2016, you can still create histograms. You just need an intermediate step to convert raw data to frequencies by bin. You can use either formulas or data analysis tools to achieve this. Another simple way to achieve this is to use Pivot Tables > Grouping option.
Creating histograms with Pivot tables:
- Insert a pivot from your raw data
- Add numbers you want to bin to row label area.
- Add some text value or anything else to values area and summarize by count.
- Right click on row labels and choose group
- Set up bin size to group the numbers.
See this demo to understand the process.
The final result looks like this:
Download Histogram and Pareto Chart Examples
Click here to download workbook with histogram and Pareto chart examples. It has all the charts you saw in this tutorial. Examine the charts and settings to learn more.
Do you create histograms & Pareto charts?
I make histograms all the time. They are excellent visualizations to understand your data. I used to make them with pivot / formulas in earlier versions. Now that they are available as charts in Excel 2016, I use them even more.
What about you? Do you create histograms? How do you make them? How do you like the new histogram and Pareto chart options in Excel 2016? Please share your thoughts in comments.
More tips for analyzing distribution of your data
- Show average and distribution to explain your data better
- Box plots in Excel Dashboards – tutorial
- Pareto Charts in Excel – Tutorial
- Introduction to Statistical analysis using Excel
- 15 quick and powerful ways to analyze business data
18 Responses to “Best Charts to Compare Actual Values with Targets – What is your take?”
Great post. I can't vote, though, because the answer I want to put down is "it depends". As with all visualisations, you've got to take into account your audience, your purpose, technical skills, where it will be viewed, etc.
I'm with Andy: It depends. Some I would use, some I might use, some I won't touch with a barge pole.
Naturally I have comments 🙂
The dial gauge, though familiar, is less easy to read than a linear type of chart (thermometer or bullet). It's really no better than the traffic lights, because all it can really tell you is which category the point falls in: red, yellow, or green.
By the same token, pie charts are so familiar, people don't know they can't read them. Remember how long it takes kids to learn to read an analog clock?
Bullet charts don't show trends.
With any of the charts that have a filled component and a marker or ine component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
[...] Best Charts to Compare Actual values with Targets (or Budgets … [...]
I voted for #6 even though I agree with the other comments that it depends.
The majority of the votes are for the #2, thermometer chart. I still have yet to understand what happens when you are above plan/goal, which was brought up in yesterday's post.
Also, I agree with Jon in that it would be better to flip the series and make the filled part the target or goal and the line or marker the actual.
I am also a fan of using text when appropriate if the data is among other metrics in a type of dashboard. Calling it out by saying actual and % achievement is a good option.
Another "it depends" vote. Are you just looking at one or are you comparing a number of targets with actuals? You didn't include a text box. The problem with sentences is that they can get lost in a page of gray text. A text box can call attention to the numbers and line them up effectively.
I'm with Jon: "Some I would use, some I might use, some I won’t touch with a barge pole" and I'm surprised that some of your readers voted for the last group.
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
"It depends" is also my take. What I usually try to drill into my clients dashboard design is the fu ndamental difference between spot results (am I on target for this month) and long term trends.. I always try to create 3 different set of graphs to represent real perormance:
- spot results vs objectives
- cumulative results vs objectives
- long-term trend (moving average) mostly) to see where we're going
[...] Best Charts to Compare Actual Values with Targets – What is your take? (tags: excel charts) [...]
[...] Related: Charting Principles, How to compare actual values with budgets [...]
[...] Excel Charting Alternatives to compare values [...]
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
I totally agree, Bob. I would normally favour a line for the target and a column for the actual, you can see quite easily then which columns break through the line, then.
[...] best charts to compare actual values with targets — den Status mal anders zeigen, z. B. als Tacho [...]
Thermometer charts: "Not appropriate when actual values exceed targets" - this is easily solved by making the "mercury" portion a different color from the border, then you can clearly see where the expected range ends and the actual values keep going.
People seem to knock gauges quite a bit in dashboarding, but trying to show comparison of realtime data between operating sites and targets for each site can easily be done with a bank of gauges that have the optimal operating points at 12 o'clock.
The human eye is great at pattern stripping, and any deviation of a gauge from the expected 12 position will quickly register with an operator and attract his attention. Using a colour background, or meter edge, will also indicate the sensitivity of a particular site.
[…] Best charts to compare actual with target values […]
[…] Best charts to compare actual with target values […]
[…] work laptop I have a favorites folder just dedicated to Excel charts. Its got things like “Best Charts to Compare Actuals vs Targets” and “Best charts to show progress“. I love me some charts […]
I am wondering how will the plotting work, for some of the targets which may have been achieved before time. E.g. for the month of Jul the target was 226 and the actual was 219. So the chart will show a deficit in meeting the target by 7 points but what if this 7 may have been completed earlier in month of June. So ideally it not a deficit.