If you want to create a Pareto Chart for categorical data in MS Excel you should first have your data input into Excel already. From your data, you should highlight the cells that you want to count the frequency for and in the frequency box you should type in =COUNTIF and highlight the data you want the frequency for and put in F4 and then press , click on cell to the left and click enter. Now this will work all the way down. If you have not sorted it, you can right click and then click sort. For Cumulative, you should highlight the empty cells in the cumulative tab and type =sum{D14 and click the cell to the left of this and click shift and then colon. Now double click the first cell reference and then hit enter the F4 key. Now put a closed parentheses in and hit F2 in the next cell. Now highlight all the cells and then click F2 and hit /sum{ and highlight all of the cells to the left and hit F4 to lock it and close parentheses and hold control and tap enter. Now you will have the correct percents. Now, highlight all of your range, hold ALT and click F1 and you will have a chart. First, click on all of the tiny red columns and hit Ctrl 1 and choose secondary access. Now, right click and click change series chart type and change this to a line and right click okay. Right click the frequency columns and choose add data labels - do this for the line graph too. Now, move your legend up to the top, click ctrl 1 and choose put at the top. You can also add a label at the bottom. Go to layout, access titles and click primary horizontal access and title below. Hit F2 and then = and click on the cell label you want the title to be and hit enter. To sort, click on the side, right click in the cell and click sort. The graph will change along with this. Sort it largest to smallest. Now, all the data will be categorical.
Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.
Other worthwhile deals to check out:
- 97% off The Ultimate 2021 White Hat Hacker Certification Bundle
- 98% off The 2021 Accounting Mastery Bootcamp Bundle
- 99% off The 2021 All-in-One Data Scientist Mega Bundle
- 59% off XSplit VCam: Lifetime Subscription (Windows)
- 98% off The 2021 Premium Learn To Code Certification Bundle
- 62% off MindMaster Mind Mapping Software: Perpetual License
- 41% off NetSpot Home Wi-Fi Analyzer: Lifetime Upgrades
Be the First to Comment
Share Your Thoughts