If you’re like most business owners, you probably use a variety of spreadsheets to manage your data. But what if you want to explore how different factors affect your business? Or what if you want to compare different options? In these cases, a pareto chart can be a valuable tool. A pareto chart is simply a bar graph that shows the percentage of variation in a set of data. So, for example, if you have sales data for five products, you could create a pareto chart that shows the percentage of sales for each product. This will help you see which product is generating the most revenue. To create a pareto chart in Microsoft Excel, first open the spreadsheet where you want to create the chart. Then click on the Insert tab and select Bar Graph from the list of options. (You can also find this tab at the top left corner of Excel.) In the Bar Graph dialog box that appears, select Pareto Chart from the list of options and then click on OK. (You can also find this dialog box by selecting Graphs from the Insert menu.) Now let’s look at some tips for creating effective pareto charts in Excel. ..
Pareto charts are popular quality control tools that let you easily identify the largest problems. They are a combination bar and line chart with the longest bars (biggest issues) on the left. In Microsoft Excel, you can create and customize a Pareto chart.
The Benefit of a Pareto Chart
The main benefit of a Pareto chart’s structure is that you can quickly spot what you need to focus on the most. Beginning on the left side, the bars go from largest to smallest. The line at the top displays a cumulative total percentage.
Normally you have categories of data with representative numbers. So, you can analyze data in relation to the frequency of occurrences. Those frequencies are usually based on cost, quantity, or time.
RELATED: How to Save Time with Excel Themes
Create a Pareto Chart in Excel
For this how-to, we’ll use data for customer complaints. We have five categories for what our customers complained about and numbers for how many complaints were received for each category.
Start by selecting the data for your chart. The order in which your data resides in the cells is not important because the Pareto chart structures it automatically.
Go to the Insert tab and click the “Insert Statistical Chart” drop-down arrow. Select “Pareto” in the Histogram section of the menu. Remember, a Pareto chart is a sorted histogram chart.
And just like that, a Pareto chart pops into your spreadsheet. You’ll see your categories as the horizontal axis and your numbers as the vertical axis. On the right side of the chart are the percentages as the vertical secondary axis.
Now we can clearly see from this Pareto chart that we need to have some discussions about Price because that is our biggest customer complaint. And we can focus less on Support because we didn’t receive nearly as many complaints in that category.
Customize a Pareto Chart
If you plan to share your chart with others, you might want to spruce it up a bit or add and remove elements from the chart.
You can start by changing the default Chart Title. Click the text box and add the title you want to use.
On Windows, you’ll see helpful tools on the right when you select the chart. The first is for Chart Elements, so you can adjust gridlines, data labels, and the legend. The second is for Chart Styles, which lets you select a theme for the chart or a color scheme.
You can also select the chart and head to the Chart Design tab that displays. The ribbon provides you with tools to change the layout or style, add or remove chart elements, or adjust your data selection.
One more way to customize your Pareto chart is by double-clicking to open the Format Chart Area sidebar. You have tabs for Fill & Line, Effects, and Size & Properties. So, you can add a border, shadow, or specific height and width.
You can also move your Pareto chart by dragging it or resize it by dragging inward or outward from a corner or edge.
For more chart types, take a look at how to create a geographical map chart or make a bar chart in Excel.
