A percentile graph helps us to visualize how a given value ranks against all the available data. It looks at all the data available and generates percentiles based on that data. This tutorial shows you how to create a percentile graph in Excel.
Example
We will use the following dataset of microphone prices in our illustration.
Step 1: Name the data range
- Select the data range A1:A11.
- Click Formulas >> Defined Names >> Define Name Arrow >> Define Name.
- In the New Name dialog box type in the Name text box the name you want to give to the data range and click the OK button. In this case, we have named the range Microphone_Prices.
Step 2: Establish the Top, Bottom, 25th, Median, and 75th Percentiles
- Create five additional columns next to the dataset: Enter 0-%, 25-%, Median, 75-%, and 100-% in cells B1, C1, D1, E1, and F1 respectively:
- Enter the following formula in cell B2:
1 |
=MIN(Microphone_Prices) |
- Enter the following formula in cell C2:
1 |
=PERCENTILE.EXC(Microphone_Prices,0.25) |
- Enter the following formula in cell D2:
1 |
=MEDIAN(Microphone_Prices) |
- Enter the following formula in cell E2:
1 |
=PERCENTILE.EXC(Microphone_Prices,0.75) |
- Enter the following formula in cell F2:
1 |
=MAX(Microphone_Prices) |
The dataset should now look as shown below:
Step 3: Generate the Graph
- Select range B1:F2.
- Click Insert >> Charts >> Insert Line or Area Chart Arrow >> Line.
The percentile graph is generated:
Conclusion
This tutorial has shown you how to create a percentile graph in Excel. It involves naming the data range, establishing five percentiles, and generating the graph.