How To: Create a Pareto chart for categorical data in MS Excel

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.