PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

CREATE A PARETO CHART IN EXCEL

INTRODUCTION

CHARTS are the graphic representation of any data . As we know that EXCEL is a super analytical tool.

Analysis of data is the process of deriving the inferences by finding out the trends, averages etc. about different parameters.

Data is everywhere. There is no work, where we don’t deal with the data. Sales data in business, employee data, patient data in hospitals, students data in school etc. Maximum data is presented in the tables but we all know that visual representation is easier to interpret. That is the reason that as the Excel developed, so the charts options in Excel.

Excel gives us a variety of charts which are beautiful, colorful, more customizable and more powerful.

In this article we are going to discuss one particular type of the charts which are known as PARETO CHART.

PARETO CHART is used to contains both columns sorted in descending order and a line representing the cumulative total percentage.

PARETO CHART is used to show the biggest factor responsible for any process.It is one of the quality tools.

WHEN TO USE PARETO CHARTS

PARETO CHARTS comprise of a histogram type graph in the descending order i.e. the first column represents the factor which has highest value to the last column which has the least value. In addition to there is a line which shows the cumulative values together with the columns.

 

BUTTON LOCATION FOR PARETO CHARTS

The button for column chart is found under the INSERT TAB under the CHARTS SECTION.

PARETO CHART BUTTON LOCATION
EXCEL:BUTTON LOCATION FOR INSERTING PARETO CHARTS

STEPS TO INSERT A PARETO CHART IN EXCEL

EXAMPLE DETAILS

We can demonstrate the chart using an example.
We are taking the example of a fictitious process.
We have five factors, which can be any processes etc. with the values against them.
FACTORS NUMBER
FACTOR 1 1000
FACTOR 2 1200
FACTOR 3 800
FACTOR 4 900
FACTOR 5 1500

The procedure to insert a PARETO chart are as follows:

STEPS TO INSERT A PARETO CHART IN EXCEL:

  • The first requirement of any chart is data. So create a table containing the data.[We have already created in the form of table above]
  • Refer to our data above, we have a collection of different factors affecting a process.
  • Select the complete table including the HEADER NAMES.
  • Go to INSERT TAB> CHARTS>INSERT STATISTIC CHARTS and click the PARETO CHART button under HISTOGRAM as shown in the BUTTON LOCATION above and in the following picture for reference.
  • The chart will be created and shown to you as the following figure.
 
 The complete process is shown in the animated picture below.
PARETO CHART CREATE IN EXCEL
EXCEL:STEPS TO CREATE PARETO CHART

So , we have successfully created a pareto chart in Excel.

We can see that EXCEL put the factors as per their descending values and put the columns as per the value of the factors which makes easy for us to find the most important factor at once. Similarly the cumulative percentage value is also shown as a line graph.

CONFIGURE PARETO CHARTS IN EXCEL

HOW TO CONFIGURE BINS IN PARETO CHARTS

Follow the steps to configure bins in PARETO CHARTS.

Bins are the number of different groups which we use to show our data.

Bins can be adjusted by many ways. For example,

by

  • CATEGORY [ DEFAULT AS SHOWN ABOVE]
  • BY WIDTH [WIDTH AS VALUE]
  • NUMBER OF BINS[ THE VALUES WILL BE DIVIDED IN A SPECIAL PATTERN AND BINS WILL BE FORMED. THE NUMBER WILL BE INCLUDING OVERFLOW BIN AND UNDERFLOW BIN]
  • OVERFLOW BIN [ ALL ABOVE VALUES WILL BE PUT IN THAT]
  • UNDERFLOW BIN[ ALL LOWER VALUES WILL BE PUT IN THAT]

Follow the steps to change the BIN SIZE in PARETO CHART:

Right Click on the horizontal axis >FORMAT AXIS>AXIS OPTIONS [ON THE RIGHT]

The FORMAT AXIS BOX will open on the right.

Go to the AXIS OPTIONS.

Go to BINS.

Choose the type of bin as per requirement.

The chart will be redrawn as per the choice.

PARETO CHART EXCEL CONFIGURATION
EXCEL: CONFIGURATION OPTIONS FOR PARETO CHART
We can choose the bin type from the above options.

Formulas used to create histograms:

Scott’s normal reference rule:   

Formula for Automatic option

Scott’s normal reference rule tries to minimize the bias in variance of the Pareto chart compared with the data set, while assuming normally distributed data.

Overflow Bin   

Formula for Overflow bin option

Underflow Bin   

Formula for Underflow bin option
 
The final CHART AFTER CHANGING THE STYLE ETC. IS SHOWN HERE.
HOW TO DO PARETO CHART IN EXCEL
EXCEL: FINAL PARETO CHART

NOTE:

FOR ALL OTHER TASKS LIKE CHANGING THE NAME OF THE CHART, CHANGING THE AXIS , CHANGING THE CHART STYLE ETC. VISIT HERE [HOW TO CREATE A CHART IN EXCEL].

 

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: