Nazmul Hossain Shovon
In this tutorial, I am going to show you 2 easy ways to make an Excel spreadsheet to automatically calculate percentages in various cases. I will also provide some quick tips along the way to format the result to your needs.
Download Practice Workbook
You can download the practice workbook from here.
2 Quick Ways to Make an Excel Spreadsheet to Calculate Percentage Automatically in Excel
Calculating percentage is a common mathematical operation that we use in our everyday life. Although we can do it manually, Excel makes it super easy to automate percentage calculation in large data sets. That also, in a very short time. This is what I want to show you today.
1. Use of Excel Formula to Make an Excel Spreadsheet Automatically Calculate Percentage
In the following 3 methods, we are going to use excel formulas and functions to automatically find out percentage values.
1.1 Using Conventional Percentage Formula
The conventional formula is the simplest way to get our percentage value for a data set. Let us see how. We are using a data set that has the total number of Covid-affected patients and the number of deaths in America.
Steps:
- First, select cell C8.
- Next, double click on cell Type the following formula in that cell:
=C5/C6 
- Then hit Enter.
- As a result, we get the following decimal number in cell C8. But it is a bit long.
- So, we will format this value.
- For that, select cell C8, and go to the Home tab.
- Then, navigate to the Number option there, and press the % button.
- Now, we have our value in a percentage format.
- In addition, you can change the number of decimal points by clicking the increase/decrease decimal button just below the %.
1.2 Apply Excel IFERROR Function
This method can be really helpful if you have a zero-valued cell. The conventional method might produce some errors in this case. So, we use the IFERROR function to avoid any errors. Here, we have an exam mark sheet for four students.
Steps:
- To begin with, we have 2 cells with a value of 0.
- In addition, double click cell E5 and enter the following formula:
- Press Enter. At this time, you should see the following result.
- Also, at the lower right corner of the current cell, you see the Fill Handle.
- Now, Click and drag the Fill Handle to copy the IFERROR function to the remaining cells.
- So, you have finally got your percentage values for all the cells.
1.3 Calculation of Percent Increase or Decrease Automatically
In Microsoft Excel, we can calculate an increase or decrease in values very easily. We’ll be using yearly income change to understand this method.
Steps:
- Firstly, select cell E5 and type the following formula:
- Then, hit Enter. You should get the following result.
- Next, drag the Fill Handle tool to copy this formula to other cells in the column.
- Finally, you get the value for all people’s income change in percentage.
- It is important to note that the minus sign in cell E8 denotes a decrease in salary.
Read More: [Fixed:] Excel Spreadsheet Formulas Not Updating Automatically
2. Auto Calculate Percentage of Contiguous Cells in Excel Using Absolute Reference
The absolute reference method comes in really handy when we want to calculate the percentage with respect to a certain cell. To illustrate this method we will use the following dataset. Let’s jump right into it:
Steps:
- To start with, click on cell D5 and type this formula:
- Also, you can use the F4 key to quickly enter the dollar sign for C9.
- Now, press the Enter key and you get the following result.
- Again, you can format the decimal places as you need.
- Like we did previously, drag the Fill Handle to copy the formula for the remaining cells in the column.
- Importantly, the second cell reference stays as C9 for all the cells below.
- This is because we used the absolute reference using the $.
- Finally, we get the percentage of the obtained marks with respect to the total marks in cell C9.
Read More: Excel VBA: Turn Off Auto Calculate
Conclusion
I really hope that you clearly understood all of the methods to automatically calculate percentages in Excel. And not only that, but you should now be able to apply these methods in various cases, as needed. Don’t forget to download the workbook we provided and practice with the given dataset. You should also practice using your own sets of data. To learn more quick and easy methods in Excel, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
