In this article, we will learn how to calculate the percentage above average in Excel. Sometimes, we need to calculate the average and find the percentage for the values greater than the average value. In the case of large datasets, it is difficult to perform these operations manually. Today, we will show how we can calculate the percentage above average quickly in Excel.

**Table of Contents**hide

## What is Average?

Average is a mathematical operation. Suppose, we have a set of numbers. If we divide the sum of the numbers by how many numbers we have, then we will get the average of those numbers. The average of 5,6,3,2,9 is 5.

We can write,

**Average = (Sum of numbers of a set) / (Total numbers in that set)**

## What is Percentage?

The percentage is a ratio expressed as a fraction of 100. For example, we have a total of 21 balls in a basket and 7 are red among them. So, the percentage of red balls is 30%.

## How to Calculate Percentage above Average in Excel: 3 Ways

### 1. Use of Conditional Formatting to Calculate Percentage above Average

To find the percentage above average, we need to find the â€˜**above average**â€™ values first. Then, we can calculate the percentage. To explain this method, we will use a dataset of three columns that contains the **Sales**, **Target Sales, **and **Working Hour **information of some employees.

We will use the same dataset in all the methods.

Follow the steps below to learn about this method.

**STEPS:**

- In the first place, select the range of cells that contains the values. We have selected
**Cell D5**to**Cell D10.**

- Secondly, go to
**â€˜Conditional Formattingâ€™**from the**HomeÂ**tab. - Then, select
**â€˜Top/Bottom Rulesâ€™**and**â€˜Above Averageâ€™**from the drop-down menu.

- The
**â€˜Above Averageâ€™**window will occur. You format the selected cells with different colors from here. Click**OK**to proceed.

- After clicking
**OK**, the cells that contain â€˜**above average**â€™ values will be colored like below.

- Next, create a column that will display the percentage.
- Type the below formula in
**Cell E5**.

`=(D5/$C$12)`

Here, we are dividing the cells with a constant cell. So, we have locked it by using the **dollar** (**$**) sign.

- Now, go to the
**â€˜Format Numberâ€™**tab and change it to**â€˜Percentageâ€™**.

- Press
**Enter**to see the result.

- Finally, use the
**Fill Handle**to see the results in all cells.

**Read More: **How to Apply Percentage Formula in Excel for Marksheet

### 2. Filter Data to Calculate Percentage above Average in Excel

We can also use the **Filter feature** to determine the percentage above average. In this method, the values below the average will not be displayed. So, it is a perfect method if we want to show the values above average only.

We will use the previous dataset again. Pay attention to the steps below for more.

**STEPS:**

- In the beginning, select any cell in your dataset. We have selected
**Cell C6Â**here.

- Now, go to the
**Data**tab and select**Filter**.

- The header of the columns will have filters like below.

- Next, create a column to display the percentage.
- Type the formula below.

`=D5/$C$12`

- Then, go to the
**â€˜Format Numberâ€™**tab and change it to**â€˜Percentageâ€™**.

- Furthermore, hit
**Enter**and use the**Fill Handle**to see results in all cells.

- Finally, select the header of the
**â€˜Salesâ€™Â**column. - Select
**â€˜Number Filtersâ€™**and**â€˜Above Averageâ€™**from the drop-down menu.

- In the end, you will see the percentage of the
**â€˜above averageâ€™**values below.

Here, **Row 5 **and **Row 7** are hidden automatically.

### 3. Determine Percentage above Average with Formula in Excel

We can easily find the percentage above average with formulas. To make the explanation easier, we will not use the **â€˜Working Hourâ€™** column of the previous dataset.

Follow the steps below.

**STEPS:**

- Select
**Cell D5**at first. - Then type the formula.

`=IF(C5>AVERAGE($C$5:$C$10),C5/$C$12,N/A)`

Here, **the AVERAGE** **function **calculates the average of the cells **C5 **to **C10**. As it will be fixed for other cells too, we have locked it using the **dollar **(**$**) **sign**. Then **the IF function **compares the cell value with the average value and gives a percentage as output if it is true. Otherwise, it displays **N/A** as output.

- Hit
**Enter**to see the result.

- Finally, use the
**Fill Handle**to see results in the rest of the cells.

## Things To Remember

There are two important things we need to remember when we are calculating the percentage above average.

- Be extra careful about the percentage formula, sometimes we need to lock the denominator for our calculation. Use the
**dollar ($) sign**to lock in those cases. - Change the number format to
**â€˜Percentageâ€™**to get the perfect value.

**Download Practice Book**

Download the practice book here.

## Conclusion

Here, we have demonstrated 3 quick and easy ways to calculate the percentage above average value. We have also discussed the basic idea of percentage and average at the beginning. I hope these methods will help you to find a solution to your problem. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.

**Related Articles**

- Calculate Percentage Using Absolute Cell Reference in Excel
- Calculate Percentage in Excel VBA
- How to Calculate Percentage of Filled Cells in Excel
- How to Apply Percentage Formula for Multiple Cells in Excel
- IF Percentage Formula in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Calculate Percentage for Multiple Rows in Excel
- How to Use Food Cost Percentage Formula in Excel
- How to Calculate Variance Percentage in Excel