Jonayed
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.
📂 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.
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.
- 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.
- After that, you will see the values that are above average are colored with the desired color you chose.
- Afterward, select cell E6.
- Write the following formula on that cell.
=(D6/$D$16)
- After that, press the Enter key.
- 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.
Undoubtedly, this procedure is one of the best procedures to compute a percentage above average in Excel.
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.
- Now your dataset will look like this.
- Thirdly, Afterward, select cell E6.
- Put the following formula on that cell.
=D6/$D$16
- After that, press the Enter key.
- Afterward, drag the formula to the whole column using the fill handle.
- Sixthly, press the header of the Points.
- Select the Number Filters and then choose the Above Average option.
- Finally, you will see that the percentages that are below average are not in there.
Undoubtedly, this procedure is one of the unique procedures to compute a percentage above average in Excel.
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.
- 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.
Lastly, we must say that this process is easier than most other processes to compute the percentage above average in Excel.
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.
- After that, you need to go to the Insert tab.
- Click on the PivotTable and then choose the From Table/Range option.
- Again you will get a dialogue box name PivotTable from table or range.
- Give your required range and click on the OK button.
- 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.
- Afterward, write the Points to be Achieved manually.
- 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.
- After that, drag the formula to the entire column as well.
- Finally, you will get your required result.
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.
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.
- 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.
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.
- Afterward, select cell D6.
- Put the given formula there.
=C6/C$18
- Again tap Enter key.
- Belatedly, use the fill handle icon to drag the formula to the entire column.
- Consequently, you will see the required result as well.
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.
