### Step 6

Make sure the percentage formula in excel is now: =B4/$B$17.

### Step 7

Now we can enter and AutoFill.

Press Control-Enter (on the Mac, press Command-↩) to enter the formula without moving the cursor down to the next row.

### Step 8

Click the Percent Style button on the ribbon or use the Control-Shift-% (Command-Shift-%) shortcut.

### Step 9

Roll the mouse pointer over the AutoFill dot in the lower-right corner of D4.

### Step 10

When the cursor becomes a crosshair, double-click to AutoFill the formula down to the bottom.

Each month will now show how much of a percentage of the grand total it is.

## 8. Percentage Ranking

Ranking numbers by percent is a statistical technique. You’re probably familiar with it from school: you and your classmates have grade point averages, so each of you is ranked in a percentile. The higher your grades, the higher your percentile.

The list of numbers (grades, in this example) are in a range of cells that Excel calls an array. There’s nothing special about an array and you don’t have to define it. That’s just what Excel calls the range of cells you plug into a formula.

Excel has two functions for percentage ranking. One function includes the beginning and ending numbers of the array and the other function doesn’t.

Look at the second tab in the worksheet: Grades.

This shows a list of 35 grade point averages, sorted in ascending order. The first thing we want to do is find the percentile rank for each one. To do this, we’ll use the =PERCENTRANK.INC function. The “INC” part of that function means “inclusive," as it will include the first and last grades in the list. If you want to exclude the first and last numbers in the array, you would use the =PERCENTRANK.EXC function.

The function takes two mandatory arguments and the syntax is: =PERCENTRANK.INC(array, entry)

• array is the range of cells that contains the list (in this example, it’s B3:B37)
• entry is any number or cell in the list

### Step 1

Click C3, the first one in the list.

### Step 2

Start typing this formula, but don’t press Enter, yet=PERCENTRANK.INC(B3:B37

### Step 3

This range needs to be an absolute reference so we can AutoFill to the bottom.

Press F4 (on the Mac, press Fn-F4) to insert dollar signs.

The formula should now look like this: =PERCENTRANK.INC($B$3:$B$37

### Step 4

In each case down column C, we want to know the percent rank of the entry in column B.

Click B3, then close the parenthesis.

The formula is now this: =PERCENTRANK.INC($B$3:$B$37,B3)

Now we can fill in and format the numbers.

### Step 5

If necessary, click back on C3 to select it.

### Step 6

Double-click the AutoFill dot on C3. That automatically fills down the column.

### Step 7

On the Ribbon, click the Percent Style button to format the column as percentages. You should now see the finished result:

So if you had very good grades and your GPA was 3.98, you would say that you ranked in the 94th percentile.

## 9. Finding the Percentile

You can also find the percentile directly, using the PERCENTILE functions. With these functions, you enter a percent rank, and it will return a number from the array that corresponds to that rank. If the exact number you want isn’t listed, Excel will interpolate the result and return the number that “should” be there.

When would you use this? Let’s say you plan on applying for a graduate program, and the program accepts only students who score in the 60th percentile or higher. So you want to know what GPA is exactly 60%. Looking at this list, we can see 3.22 is at 59% and 3.25 is at 62%, so we can use the =PERCENTILE.INC function to find the answer.

The syntax is: =PERCENTILE.INC(array, percent rank)

• array is the range of cells that contains the list (same as the previous example, B3:B37)
• rank is a percentage (or a decimal between and including 0 and 1)

Just like with the percent ranking functions, you could use =PERCENTILE.EXC to exclude the first and last entries in the array, but that’s not what we want in this example.

### Step 1

Click in B39, below the list.

### Step 2

Enter this formula: =PERCENTILE.INC(B3:B37,60%)

Since we aren’t going to AutoFill this formula, there is no need to make the array an absolute reference.

### Step 3

On the Ribbon, click the Decrease Decimal button once, to round the number to two decimal places.

The result is that in this array, a 3.23 GPA is the 60th percentile. Now you know the grades you need for acceptance into the program.

## Conclusion

Percentages aren’t complicated, and Excel calculates them using the same rules of math as you would use with pencil and paper. Excel also adheres to the standard order of operations when you have addition, subtraction, and multiplication in a formula:

1. Parentheses
2. Exponents
3. Multiplication
4. Division