4 Tricks to Calculate Percentage Above Average in Excel

Sometimes we need to calculate the percentage above average in Excel. Calculating the percentage above the average means computing the average and then determining the percentage that is bigger or greater than the value of the average value. So, in this article, we are going to show you how to calculate the percentage above average in Excel with different approaches where Conditional Formatting, Data Filter, Excel functions, Excel Pivot Table, etc. are included.

Overview image to calculate percentage above average in Excel


📂  Download Excel File

Download free Excel workbook from here:


Learn to Calculate Percentage Above Average in Excel with These Approaches

While writing this article, “How to Calculate Percentage Above Average in Excel”, we’ve considered a dataset that contains approximately 5 columns and 16 rows. In this dataset, we added Name, Grade, Points, and Percentage to make you better understand. We have also inserted another row name Points to be Achieved from which we will compute the percentage above average. But if you want, you can change the entities and put your own values.

Sample dataset containing the point table of poker game

APPROACH

1. Applying Conditional Formatting to Compute Percentage Above Average

Conditional Formatting is one of the unique features of Excel. You can apply any kind of criteria using this option. In this section, we will use Conditional Formatting to calculate the percentage above average in Excel. First, the Conditional Formatting will separate the values that are above the average value. After that, we will apply the percentage formula. You can achieve this feature if you maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the Home tab.
  • Secondly, select the Conditional Formatting option.
  • Then select Top/Bottom Rules.
  • Thirdly, click on the Above Average feature.

Selecting the Above Average from the Conditional Formatting option

  • Now you will get a dialogue box name Above Average.
  • This time you need to choose your text background color for Format cells that are ABOVE AVERAGE.
  • Press the OK button.

Selecting text background color from the Above Average dialogue box

  • After that, you will see the values that are above average are colored with the desired color you chose.

Colored text backgrounds that are above average

  • Afterward, select cell E6.
  • Write the following formula on that cell.

=(D6/$D$16)

  • After that, press the Enter key.

Using the Excel Formula with absolute cell references to calculate the percentage above average in Excel

  • Belatedly, use the fill handle icon to drag the formula to the entire column as well.
  • Finally, you will see the desired result you wanted to see.
  • Here the percentages that are above the average are colored with the selected background color.

Result after using the Excel Formula to calculate the percentage above average in Excel

Undoubtedly, this procedure is one of the best procedures to compute a percentage above average in Excel.

APPROACH

2. Adopting Data Filter Option to Compute Percentage Above Average in Excel

In Excel, you can use the Filter option to compute the percentage above average. The last method was using Conditional Formatting which was used by applying the Conditional formatting option. The difference between the two approaches is you can calculate the percentage above average by giving conditions from the Conditional Formatting and you can filter data to calculate the percentage above average while using the Filter feature. Follow the given procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, go to the Data tab of your worksheet.
  • Secondly, choose the Filter option.

Inserting the Filter option from the Data tab

  • Now your dataset will look like this.

Dataset with the header to calculate the percentage above average in Excel

  • Thirdly, Afterward, select cell E6.
  • Put the following formula on that cell.

=D6/$D$16

  • After that, press the Enter key.

Using the Excel Formula with absolute cell references to calculate the percentage above average in Excel

  • Afterward, drag the formula to the whole column using the fill handle.

Calculating the percentage from the dataset

  • Sixthly, press the header of the Points.
  • Select the Number Filters and then choose the Above Average option.

Choosing the Above Average from the Number Filters option

  • Finally, you will see that the percentages that are below average are not in there.

The result after using the Data Filter to calculate the percentage above average in Excel

Undoubtedly, this procedure is one of the unique procedures to compute a percentage above average in Excel.

APPROACH

3. Using IF and AVERAGE Functions to Execute Percentage Above Average

If you are searching for an easy and handy way to execute the percentage above average in Excel then this portion is for you. We are going to use the IF and AVERAGE functions to calculate the percentage above average. Here the IF function will find the values that are above average, then the AVERAGE function will perform the rest of the operations. You can do this by maintaining the following instructions.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you have to select cell E6.
  • Secondly, you have to write the following formula there.  

=IF(D6>AVERAGE($D$6:$D$14),D6/$D$16,”Not Found”)

🔨 Formula Breakdown

Here:

👉  The AVERAGE function in this case determines the average value for cells D6 through D14. We have sealed it by applying the Dollar($) sign because it will be solved in other sections as well.

👉  Then, if the comparison between the field data and even the average value is successful, the IF function outputs a percentage. If not, Not Found is shown as the result.

  • Thirdly, press the Enter key.

Using the IF and AVERAGE functions to calculate the percentage above average in Excel

  • Thirdly, you need to use the fill handle to drag the above formula to the entire column.
  • Eventually, you will find that the cells containing Not Found whose corresponding values are not above average.

The result after using the IF and AVERAGE functions to calculate the percentage above average in Excel

Lastly, we must say that this process is easier than most other processes to compute the percentage above average in Excel.

APPROACH

4. Employing Excel Pivot Table

Applying the Pivot Table is a unique method to compute a percentage above average in Excel. Here, you need to create the Pivot Table first. Once you are done with this, you have to calculate the percentage above average. In the previous method, the values that are below average, are identified as Not Found. But in this portion, you will get the result for all the values.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you need to tap Ctrl+T.
  • Then you will get a dialogue box named Create Table.
  • Secondly, put the required range on Where is the data for your table?

$B$5:$E$14

  • Thirdly, check the My table has headers.
  • Then press the OK button.

Selecting range from the Create Table dialogue box

  • After that, you need to go to the Insert tab.
  • Click on the PivotTable and then choose the From Table/Range option.

Choosing the PivotTable from the Insert tab

  • Again you will get a dialogue box name PivotTable from table or range.
  • Give your required range and click on the OK button.

Giving the range for the PivotTable

  • Now you will see PivotTable Fields at the left-most side of your worksheet.
  • Check the following fields: Name, Grade, and Points.
  • After that, drag the Percentage field on the Sum of Points.

Selecting the required fields from the PivotTable Fields to calculate the percentage above average in Excel

  • Afterward, write the Points to be Achieved manually.

Inserting the value manually into the pivot table sheet

  • Belatedly, copy cell A3 to D13 and paste it A19 to D19.
  • Seventhly, select cell D20.
  • Then write the following formula on that cell.

=C20/$C$16

  • You need to press the Enter key.

Using the Excel formula to calculate the percentage above average in Excel

  • After that, drag the formula to the entire column as well.
  • Finally, you will get your required result.

The result after using the Excel formula to calculate the percentage above average in Excel

Indeed, this is one of the simplest approaches to computing a percentage above average in Excel.


How to Calculate Percentage with Formula in Excel

Different approaches are available in Microsoft Excel to create a formula that calculates the percentage of a real number. To achieve our goals, we can use logical computations or add a function. This portion will teach you how to use a technique in Excel worksheets to add the percentage formula of a real number. The general formula to compute the percentage is:

(Value/Total Value)x100

The primary formula to apply a percentage formula in Excel is:

Numerator/Denominator=Percentage

Now follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell D6.
  • Secondly, put the following formula there as well.

=B6/C6

  • Thirdly, tap the Enter key.
  • Consequently, you will get your desired result.

Percentage formula in Excel

Finally, this is the easiest formula to add the percentage formula in Excel.


How to Calculate Percentage Increase with Formula in Excel

If you are looking for the formula for total percentage in Excel then you are in the right place. We will guide you to calculate the total percentage in Excel. Here you can see that dataset has profits for different years of a company. We will compute the percentage to determine whether the profit increases or decreases compared to the previous year. As there is no year given before 2011 in the dataset, so we will perform our operation from the second year (2012) profit. You need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you have to select cell D7.
  • Secondly, put the given formula there.

=(C7-C6)/C6

  • Thirdly, you have to press the Enter key.

Using Excel formula to calculate the total percentage

  • Afterward, use the fill handle icon to drag the formula to the entire column.
  • Finally, you will see the result you wanted to have.
  • Here you will see that some values from the Percentage column are containing Negative () signs which indicate that the profit percentage is decreasing.

The result after Using the Excel formula to calculate the total percentage

Truly, this technique is very unique to calculate the total percentage in Excel.


How to Calculate Percentage of Total with Formula for Multiple Cells in Excel

Using several techniques, we may compute the percentile in our Excel sheet. To start, we’ll solely employ the Forward Slash (/) segment symbol and such Excel percentage formatting to calculate the net Profit percentage for each Year of the Total Profit. Because of this, we’ll start by utilizing the Excel SUM function to calculate the Total. So, you can apply the formula for the percentage of multiple cells in Excel by following the given instructions.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select cell C18.
  • Secondly, write the following formula on that cell.

=SUM(C6:C17)

  • Now press the Enter key.
  • Eventually, you will get the Total Profit.

Applying the SUM function to calculate the percentage for multiple cells in Excel

  • Afterward, select cell D6.
  • Put the given formula there.

=C6/C$18

  • Again tap Enter key.

Applying Excel formula to calculate the percentage for multiple cells in Excel

  • Belatedly, use the fill handle icon to drag the formula to the entire column.
  • Consequently, you will see the required result as well.

The result after using the Excel formula to calculate the percentage for multiple cells in Excel

Truly, this method is very handy to work on.


📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️  You need to be careful while applying the functions of Excel.

🖊️  You should be conscious when you intend to use the Excel Pivot Table.


📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Initially, we applied Conditional Formatting to calculate the percentage above average in Excel.

📌  Secondly, we adopted the Data Filter option.

📌  Thirdly, we used the IF and AVERAGE functions.

📌  Fourthly, we employed the Pivot Table to compute the percentage above average in Excel.

📌  Afterward, we added the percentage formula in Excel.

📌  Belatedly, we calculated the total percentage in Excel.

📌  Finally, we tried to calculate the percentage for multiple cells in Excel.


Conclusion

We wish that you would be able to calculate the percentage above average in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.

(Visited 19 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo