Excel for Begginers

Thiago has a degree in Production Engineering, a graduate course in statistics and a degree in administration from the Federal University of Juiz de Fora (UFJF). Black Belt in Lean Six Sigma, worked with methodologies for cost reduction and process optimization at Votorantim Metais, subsequently joining MRS Logística as a trainee, where he held positions of manager and specialist in continuous improvement. With Microsoft Office Specialist (MOS) certification and ISO 9001 Auditor Lead Advisor, attended several companies in consulting projects, in addition to training and lectures related to Lean Six Sigma, Career Entrepreneurship in nationally renowned congresses such as ENEGEP (National Meeting of Production Engineering) and international as International Six Sigma Brazil congress. In the academic environment, he works as a professor of undergraduate and graduate courses in the areas of management and entrepreneurship. Thiago had the opportunity to participate in projects of several segments. Founder of the Voitto Group, he was selected in the Promessas Endeavor Program, having the opportunity to receive valuable mentoring to accelerate his business. He is currently a business mentor and dedicates himself to the executive front of Voitto, carrying with his team the vision of being the best online school of management in Brazil.

Founder of Voitto Group, currently a business mentor and dedicated to the executive front of Voitto, carrying with his team the vision of being the best online business school in Brazil.

Discover how to use the Pareto chart to optimize a process and extract maximum productivity!

How does the Pareto chart help you eliminate process failures? Check it out now!

Learn how to build a Pareto Chart with Excel

Do you know how to create a Pareto chart in Excel?

If you need to decide which issues you&#39;re going to deal with most urgently in the company you work for, the Pareto Diagram is the ideal tool for that! With it, you will be able to have more control over the nonconformities and thus increase the quality of the process.The Pareto chart allows for easy visualization and identification of the causes of a problem. From an analysis of the frequency of occurrences, it is possible to distinguish which the company&#39;s priority actions should be and where it should direct its resources to solve a certain type of problem.In this article you’ll see:<ul><li>What is a Pareto Chart?</li><li>How does the Pareto Chart work?</li></ul>How to create a Pareto Chart using Excel?<h2>What is a Pareto Chart?</h2>The Pareto Diagram is one of the main tools when it comes to quality in an Organization. The Pareto Principle, better known as Rule 20-80, tells us that 20% of the root causes account for 80% of the problems in an organization, that is, if we solve this 20 %, we end up with most of the problems. <Image quality="95" width="397" height="151" src="https://images.prismic.io/voitto-blog/239dac33-e3e0-4107-bb5a-637a4e97d7b3_1.png" alt="Pareto Chart" title="Pareto Chart" /> Are you tired of missing out on incredible career opportunities because you don&#39;t know about the main Excel tools?So you are in the right place! In the<a target="_blank" rel="nofollow" href="https://thinkleansixsigma.thinkific.com/courses/excel-for-beginners"> Excel for Beginners</a> course, you will learn to work with data in an agile way.In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!<a target="_blank" rel="nofollow" href="https://thinkleansixsigma.thinkific.com/courses/excel-for-beginners"> <Image quality="95" src="https://images.prismic.io/voitto-blog/45fd4463-eb50-470d-a2e2-1a98155e7fdc_Bot%C3%A3o++Banner+-+Cursos+TLSS+%282%29.png" width="980" height="180" alt="Excel for Beginners" title="Excel for Beginners"/></a><h2>How does the Pareto Chart work?</h2>The Pareto chart is a vertical bar chart that arranges information in such a way as to make the prioritization of themes evident and visual (organized in descending order). Some of its features are:<ul><li>The bars on the left are relatively more important than the bars on the right.</li><li>The information thus disposed also allows the establishment of viable numerical targets to be achieved.</li><li>It separates the &quot;most vital few&quot; from the &quot;most useful many&quot; (Pareto Principle).</li></ul><h2>How to create a Pareto Chart using Excel?</h2>To create a Pareto Chart with Excel we need to break the process down into a few steps. By doing this, the task will become much easier. The steps will be:<ul><li>Collecting Data</li><li>Sorting and Organizing Data</li><li>Identifying parameters</li><li>Creating the Chart</li><li>Configuring the Pareto diagram</li></ul><h2>Collecting data</h2>To explain better, we created a very recurrent situation in our daily life: Loss of power in a car. Whenever this problem occurred, the mechanic would make a diagnosis in the car and find the cause for the problem. So, the table was created: <Image quality="95" width="344" height="154" src="https://images.prismic.io/voitto-blog/ODA4MjA0MzYtY2FjZC00ZWMwLWFhNmItMmY0YTQ0YTI4Y2Zl_89411fa5e.png" alt="Collecting data" title="Collecting data" /><h2>Sorting and Organizing Data</h2>Therefore, every time you want to work with this tool, the first step is to collect the causes/defects and the frequencies that they have occurred in a certain period of time.The second step is to place these frequencies in descending order. To do this, simply select any cell inside the table and click the Data &gt; Sort. <Image quality="95" width="382" height="135" src="https://images.prismic.io/voitto-blog/N2YxMDA1YjktMGJkOS00MjZiLTllOGItNTFlZmRiZmRlNTI5_8951aa5a0.png" alt="Sorting and organizin Data" title="Sorting and organizin Data" /> The following panel will appear, in which you will sort in descending order the number of occurrences: <Image quality="95" width="992" height="331" src="https://images.prismic.io/voitto-blog/MTk4YmJmYWEtMThjYS00MGVjLWI4MjUtY2YyNTBjZDBmMzI5_8969e571d.png" alt="Sorting and organizing data" title="Sorting and organizing data" /> The result will be: <Image quality="95" width="347" height="159" src="https://images.prismic.io/voitto-blog/NzdhNTBhNzktMWIwMC00Zjg1LTgyMjAtMTAwZWYzMDI0NWEz_8973bc1e1.png" alt="Sorting and organizin data" title="Sorting and organizin data" /><h2>Identifying parameters</h2>Now we have to identify two parameters: the percentage (%) that each occurrence obtained in the total and what is the accumulated frequency, given by the sum of the frequency of the defect with the previous one. In the case of the first data this sum does not occur. Let&#39;s see: <Image quality="95" width="568" height="171" src="https://images.prismic.io/voitto-blog/YTJkMGEzZjQtYTkxNi00NWQ1LTk1NjEtNmYwYzQyNmUxYzZk_899a189ee.png" alt="Identifying parameters" title="Identifying parameters" /> <Image quality="95" width="561" height="169" src="https://images.prismic.io/voitto-blog/NTViYmZhNjEtZGFhMi00Yzg5LThjY2YtMzkxZmY0YzAwYmQ4_898162274.png" alt="Identifying parameters" title="Identifying parameters" /> We use the function = SUM () to find the total number of occurrences. Therefore, we only divide the occurrence of each defect by the sum of the defects. Next, let&#39;s calculate the cumulative frequency: <Image quality="95" width="566" height="166" src="https://images.prismic.io/voitto-blog/MmQyNDc1ZDctMDE0NC00MDg0LThiZTItZjZiOGQ3NDk2YzE5_9001bb74c.png" alt="Identifying parameters" title="Identifying parameters" /><h2>Creating the graph</h2>As explained above, simply add up the frequency of the defect and the previous one. Obligatory, the last data in the list has to appear 100.00%, since it shows that all data has been calculated.With this data, just create the chart. To do this, we will click on an empty cell in the spreadsheet and insert a graphic of 2D Columns: <Image quality="95" width="630" height="431" src="https://images.prismic.io/voitto-blog/ZTNiNDI1ZTEtZmI2NC00ZDRmLWEzNzctM2VmNGY5ZmIwMGNh_90193a600.png" alt="Creating the graph" title="Creating the graph" /> A &quot;whiteboard&quot; will appear, because our chart does not have data yet. So let&#39;s select it and click on the option: Select data. <Image quality="95" width="401" height="165" src="https://images.prismic.io/voitto-blog/NjAwYzQ0OWEtMGI4MS00MzRmLTgzNjItNDE2YTY0ZDQ1MDBl_902a9c012.png" alt="Creating the graph" title="Creating the graph" /> When we click on this option, the table below will appear: <Image quality="95" width="608" height="336" src="https://images.prismic.io/voitto-blog/YTMyYTMyNmQtYjI1Yy00YTY1LTg0YjAtNTA4YzQyM2YwMDE5_904976ddb.png" alt="Creating the graph" title="Creating the graph" /> In the field of Subtitle Entries (Series) we will put the data present in the columns: Number of Occurrences and % Accumulated. To do this, click on Add: <Image quality="95" width="562" height="350" src="https://images.prismic.io/voitto-blog/YzIzNjVmNTEtNDE4OS00Mzk2LWI1ZjMtOTA5NjIyZjQxNzEy_903f82f86.png" alt="Pareto Diagram" title="Pareto Diagram" /> In the name of the series we insert the cell with the name &quot;number of occurrences&quot; and in values of the series the interval referring to the number of occurrences of each defect. The result was: <Image quality="95" width="1135" height="357" src="https://images.prismic.io/voitto-blog/Y2JkY2ZkZGMtNjEyMy00NmRlLWEyN2EtYWQzZDQ3YjI4NWYx_9050b5e69.png" alt="Pareto Diagram" title="Pareto Diagram" /> We will do the same procedure for the % Accumulated series, getting the result: <Image quality="95" width="561" height="334" src="https://images.prismic.io/voitto-blog/Y2Y4MjQ1YjUtNTBkYS00OGIyLTgwNjgtMDMyYzU5N2E0YmFh_906adb7c3.png" alt="Pareto Diagram" title="Pareto Diagram" /> Now we have to place the horizontal axis labels. To do this, we will use the Horizontal Axis Labels field (Categories). Let&#39;s select the data present in the field &quot;defect types&quot;: <Image quality="95" width="1087" height="327" src="https://images.prismic.io/voitto-blog/ZmNmNzM1NzItYWVlZi00NDJkLThiZTAtNjc3MDFlYzk0OWY1_9091668de.png" alt="Pareto Diagram" title="Pareto Diagram" /><h2>Configuring the Pareto Diagram</h2>We can see that the % Accumulated series is on the Percentage scale and the series number of occurrences is in Number. So let&#39;s put the % Accumulated series on a secondary axis.To do this, we will click on the bars of this series with the right mouse button and select the option: format data series &gt; secondary axis. <Image quality="95" width="580" height="361" src="https://images.prismic.io/voitto-blog/ODNjMzEwOWItMWMxYi00Y2QxLTljZjUtYmIzMWEyOTM5ZGVl_910635a66.png" alt="Pareto Diagram" title="Pareto Diagram" /> Our graph will look like this: <Image quality="95" width="947" height="409" src="https://images.prismic.io/voitto-blog/ZmIxYzdlOTctMzIwNS00YWQyLWE3NmItMTMxZTQ2ODgwNDFh_9128e7239.png" alt="Pareto Diagram" title="Pareto Diagram" /> We will change the chart type from the % Accumulated series to 2D Rows. So, just click on this series &gt;Insert &gt; Graphics &gt; 2D Line &gt; Line with Markers: <Image quality="95" width="848" height="525" src="https://images.prismic.io/voitto-blog/ODYxMmQwMDQtYTljNi00MjdmLWFkYTgtOWRlMGExOTRjYTUx_916439b8d.png" alt="Pareto Diagram" title="Pareto Diagram" /> The result will be: <Image quality="95" width="510" height="309" src="https://images.prismic.io/voitto-blog/ODJhNmYxOTQtY2ZiZC00ZjUzLWIyNmYtMTMwNDY5YzJhODA1_91715228d.png" alt="Pareto Diagram" title="Pareto Diagram" /><h2>Conclusion</h2>We can see that the main causes of the loss of power are the adulterated fuel and clogged nozzles. Therefore, stopping to refuel at gas stations that do not have quality seal can contribute to solving these two main problems.<h2> If you made it this far, I have a question for you... </h2>Several times we get lost in so many Excel functions, and we don&#39;t know where to start and what to learn. To help you, we created the <a target="_blank" rel="nofollow" href="https://conteudo.voitto.com.br/ebook-primeiros-passos-no-excel">First Steps eBook in Excel</a> to guide your practice with the tool, as well as how and where to learn about its most complex resources. Learn how to solve everyday problems quickly and efficiently in Excel.By downloading our eBook, you will learn the first steps to create your spreadsheets and use their basic and essential functions.Let&#39;s get started?<a target="_blank" rel="nofollow" href="https://conteudo.pocketbook4you.com/ebook-fist-steps-in-excel"> <Image quality="95" src="https://images.prismic.io/voitto-blog/5ad839f3-56eb-4c5b-a198-380f5e858ce8_Bot%C3%A3o+Banner+Cursos+-+TLSS+%2817%29.png" width="1025" height="205" alt="[EBOOK] Fisrt Steps in Excel" title="[EBOOK] Fisrt Steps in Excel"/></a>