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.
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 calculations in large data sets. That also, in a very short time. This is what I want to show you today.
1. Using Excel Formula to Make an Excel Spreadsheet Automatically Calculate Percentage
In the following 3 methods, we will 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 Applying 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 Calculating 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 changes 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.
2. Auto Calculating Percentage of Contiguous Cells in Excel Spreadsheet 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 will get the following result.
- Again, you can format the decimal places as you need.
- As 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: How to Add a Percentage to a Number in Excel
Download Practice Workbook
You can download the practice workbook from here.
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. If you have any questions, please let me know in the comments.
Related Articles
- How to Calculate Percentage of Total in Excel
- How to Convert Number to Percentage in Excel
- Convert Number to Percentage Without Multiplying by 100 in Excel
- How to Divide a Value to Get a Percentage in Excel
- How to Calculate Reverse Percentage in Excel
- How to Convert Percentage to Number in Excel
- How to Convert Percentage to Whole Number in Excel
- How to Show One Number as a Percentage of Another in Excel
