How to Make a Percentile Graph in Excel

Microsoft Excel is a spreadsheet program that can sort data, calculate profits in various scenarios and produce dozens of different chart forms. For percentile graphs, however, Excel has no ready-made format. Percentile graphs offer a quick visual glimpse of the range of variability of a set of data, but there's no button to click on to produce one. That doesn't mean you can't produce one, it just means you'll need to take a few extra steps. The steps come in two groups: preparing the data and formatting the graph.

Prepare the Data

Enter a header describing your data in cell A1, and enter your data in the cells below in column A. As an example, you could enter "copy paper" as the header and then fill the cells below with the price per package for 10 office supply stores in your area or on the Web.

Select the data you've just entered in column A, and then select "Insert | Name | Define" from the menu. The title you entered in cell A1 should come up as the default option. Continuing the example, the name "copy_paper" will come up as an option and the referenced cells will be listed in a dialog box at the bottom of the Name Definition window.

Label cells B2 through F2 with the following names, in order: "1st quartile," "minimum," "median," "maximum," "3rd quartile."

Enter the formula for the 25th percentile in cell B3. There are a few different ways to enter this, but you can type "=quartile(dataname, 1)" to determine the demarcation point for the lowest 25 percent of the data – the "first quartile" – in the set "dataname." For the example, this would be entered as "=quartile(copy_paper, 1)".

Enter the following statistical formulas in order in the next four cells, from C3 to F3:

C3: "=min(dataname)" D3: "=median(dataname)" E3: "=quartile(dataname, 0.75)" F3: "=max(dataname)"

Using the above example, "dataname" would be replaced by "copy_paper."

Formatting the Graph

Select the block of cells from B2 to F3, then select "Line" from the Chart menu or Chart toolbar and the sub-selection "Line" from the secondary menu.

Select "Switch Plot" from under the Chart toolbar. This will tell the chart to interpret your data as one set of vertical information connected to one horizontal point. Don't be alarmed if your graph is invisible; that will be addressed in the next step.

Select "Chart Layout | Lines | High/Low Lines" from the Chart toolbar. A vertical line now appears connecting the maximum and minimum data point.

Select "Up/Down Bars" from the Chart Layout menu. A box will appear. Modify the line, fill, shadow and gap attributes to give your box the desired appearance. A good choice is black lines, no fill, no shadow and 500% gap.

Select "median" from the Current Selection menu bar of the Format option on the Chart toolbar. Click the "Format Selection" button and pick the "Error Bars" option. Enter a fixed value of "0"; select the "Both" option for the Display and the "Cap" option for the End style. Your percentile plot will now show a vertical line corresponding to the full range of data, a box outlining the range from the 25th to 75th percentile of the data and a tick mark noting the 50th percentile.