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
24 Responses to “10 Supercool UI Improvements in Excel 2010”
The best improvement by far is the Collapse Ribbon ^ button !
Kind of a shame that some of the best improvements are actually returns to old functionality. One thing I don't like is that to get to recent files I need to do an extra click after File - apart from Save As, that's why I'm usually in the File menu. I like the sparkline options, though they are still as not fully featured as some of the free and pay options out there.
The collapse button for the ribbon menu is good news. Can you make the ribbon menus stick too?
Nine improvements, not ten. You can also select multiple objects in 2007. Click on the Find & Select item at the far right of the Home tab, and the dropdown looks remarkably like your 2010 screenshot.
@Jon.. Thank you. Dumb me, I somehow thought we couldnt select objects in Excel 2007. Just saw the "select menu" and it is there. I have corrected the post and removed the point. I have added the "you can make your own ribbons" instead. Thanks once again.
@Arti: what do you mean by make ribbons stick?
@Alex: May be it is my installation, but when I go to "File menu" I see "recent files" by default.
For example, if I am working with one of the contextual ribbon menus (Pivot tables, Drawing/Chart etc), as soon as I click away from the selected object, the menu tabs vanish. If I click on the object again immediately, then Excel will remember what I was looking at, but if I wander away and click on a Pivot, then back again on the Chart, the menus will 'appear' but not get activated, thereby causing much annoyance and additional clicking.
I want to "pin" the whole menu (not invididual commands) somehow, so that I can have the menu there for the length of the time I am working with graphics. Excel 2003 used to have the Drawing toolbar you could detach and hover while you were working, but this functionality disappeared in Excel 2007.
My thought was Excel should just allow a 'pin', similar to the Recently Opened files menu, for the Ribbon Menus as well. If I have not selected any Drawing object, the commands can be greyed out, but I want the menu as a whole to 'stick'.
@Arti... I think MS solved this problem differently. When I select a pivot and go to "design" tab Excel 2010 remembers this and automatically takes me to "design" tab when I reselect the pivot.
Apart from this you can also define your own ribbon with all the things you normally do. See the above article (I have added this after Jon's comments)
Nice feature. About time for a upgrade for MS Office
Oh... okay. That might be a start. I'd probably just copy-paste the Drawing tab haha. Thanks. I'll definitely give Excel 2010 a try.
Btw - have you considered getting into / gotten into the world of Excel as it meets SharePoint?
Actually, the replacement new thing is probably better than all the rest. One thing that the designers of the Office 2007 ignored was allowing regular users to customize their own interface. Office 2010's interface was expanded in this way to address the huge uproar.
Is there still a limit on how many things you can add to the QAT bar? (I'm too lazy to look myself.)
@Jeff.. it seems to take quite a few, but only shows one line and gives a little arrow button at the end. (summary: shucks!)
The best thing is you can edit the ribbon directly from excel, so now i can create my own bar with just the things I use regularly!
One of the annoying things in 07 for me is the Add-Ins menu bar - in 03 I could keystroke directly to menu add ins.. In 07 I needed an extra keystroke just to activate the add-in menu, then the keystrokes as normal.. Hope this marek sense..
John -
If you remember the old Excel 2003 Alt-key shortcuts, you can still use them in 2007. To get to the Add-In dialog:
Alt-T-I
Dear Arti & Chandoo
Seen your comments over some issues. Hope you are form India, gone through your comment expecting a pin to command it as a whole, great, hope if someone out of MS have read it, it may be kept in mind while the next R & D of Office Ver. 16
Just incase someone forgot CTRL+F1 will collapse the ribbon.
[...] was pleasantly surprised when I ran Microsoft Excel 2010 for first time. It felt smooth, fast, responsive and looked great on my [...]
I like the sparklines, and the ability to modify the charts
How do you get rid of the advertisment on the right hand side? If you upgrade then will it take off the ads?
Once again Microsoft has re-decorated the Office and we are NOT pleased!
The graphics object selector can be found in the Home ribbon under Find & Select, Select Objects near the bottom of the drop down. You can make it part of the Quick Access toolbar by right click over it and selecting Add to Quick Access toolbar.
The graphics "cursor" will now appear on the mini-toolbar at the top left of the window.
How to get rid of "Add-Ins" button in Backstage (File)" menu by means of XML code, i.e. to hide, to delete or to disable this button?
This button is usually situated in the Backstage menu between "Help" and "Options" buttons.
Vladimir, did you ever get an answer to your question?
I am tying to customize the ribbon UI for a file using XML, and this is precisely the piece I can't figure out. I can hide other tabs, remove items from QAT and backstage - all except the options that are showing up under add-ins in backstage. If there is an XML syntax for referencing this thing and making it invisible, I cannot find it.
Hey, nice tutorial. Please check my video tutorial on similar topic at the below link and provide your comments:
http://www.youtube.com/watch?v=TeIFc0jYjpA