If you want to learn the Excel formula of percentage, then this is a must-read blog post for you. Excel offers several ways to calculate the percentage. However, when calculating the percentage using Excel, you need to consider a few important points we have covered for you in this post. It can be done with the utmost ease by following a few easy steps. Once you know the Excel formula of percentage, you can perform several tasks such as calculating discount prices, the percentage change between two values. Let’s proceed further.
Steps To Find Percentage Using Excel
You can calculate percentages in Excel with an easy two-step procedure. In the first place, you need to format the cell to depict the value as a percent. After that, you have to build the percent formula in a cell. Let’s discuss the Excel formula of percentage in detail.
Step 1: Format Values As Percentages
To represent a number as a percent in Excel, you have to apply the Percentage format to the cells. Choose the cells to format and after that, click the Percent Style (%) button located in the Number group on the ribbon’s Home tab. Moving further, you can then increase (or decrease) the decimal place as per the requirement with the Excel formula of percentage.
In Excel, the given value is always present in decimal form. Therefore, even if you’ve used number formatting to depict something in percentage form, it will remain the same (formatting or a symbolic representation of the underlying value). You need to know that Excel always makes calculations on that given value, which is a decimal (0.1). To cross-confirm the underlying value, select the cell and press Ctrl + 1. You can look for it in the Sample box in the General category.
Things You Need To Consider When Formatting Percentages
Before you start formatting percentages, you need to consider the below-given points with the utmost care:
Format existing values
Proceeding further with the Excel formula of percentage, let’s see how Excel format the underlying values. On applying percentage formatting to a cell that already has a number, Excel multiplies that number by 100 and then adds the % sign at the end of the procedure. Let’s understand this with an example:
For instance, type 20 into the cell A2 and then apply the percentage number format. To give the results, Excel will multiply your number (20) by 100 to show it as a percentage. You need to keep in mind that 1% is one part of the hundred, and this is how it works. It is why you will see 2000% in the cell and not 20%. To avoid this, you need to calculate your number as a percentage before proceeding any further.
For instance, if you type the formula =20/100 in cell A2, Excel will show the result as 0.2. Now, if you format that decimal as a percentage, you will see a number like 20%, which is your desired result. You can also type your number in the decimal form directly into the cell.—that is, type 0.2 and then apply the percentage format according to Excel formula of percentage.
Sometimes what you see in a cell (e.g., 10%) is not the same you expected to see (e.g., 9.75%). If you want to get the exact percentage in the cell and not a rounded version, you need to increase the decimal places. Furthermore, keep in mind that Excel always uses the underlying value to make percentage calculations.
Format empty cells
Excel behaves in a different manner when you pre-format empty cells with percentage formatting by entering numbers. If your number is equal to or larger than 1, it will be converted to percentages by default. However, if your number is smaller than 1, which is not preceded with a zero, Excel will multiply it by 100 to convert it into a percentage. For instance, if you type 10 or .1 in a pre-formatted cell, it will display 10% in the cell. On the other hand, if you type 0.1 in the cell, Excel will display 0% or 0.10% on the basis of the decimal setting.
Format as you type
Sometimes the text you see in a cell (e.g., 10%) is not the same number you were expecting (e.g., 9.75%). If you want to see the accurate percentage in the cell and not a rounded version, you need to increase the decimal places. Furthermore, you need to consider that Excel always uses the underlying value to perform calculations.
Format empty cells
Excel behaves in a different manner when applying the percentage formatting in the cells prior to entering the numbers. For instance, if your number is greater or equal to 1, Excel will convert it to a percentage by default. On the other hand, if your number is smaller than 1, which is not preceded with a zero, Excel formula of percentage will multiply it by 100 to convert them to percentages. For instance, if you type 10 or .1 in a pre-formatted cell, Excel will display 10% in the cell. Besides, if you type 0.1 in the cell, Excel will give 0% or 0.10% on the basis of decimal setting.
Format as you type
When you type 10% directly in the cell, Excel automatically applies percentage formatting. This feature of Excel can help when you want to type just a single percentage on your worksheets. It can be a tax or commission rate, or other value.
If you want the formatting of the negative percentage to be done differently in Excel, then there are several ways to do it. For instance, if you want to display the text in the red color, you can create a custom number format: 0.00%;[Red]-0.00%. Furthermore, if you want the text to appear within parentheses—you can use 0.00%_);(0.00%).
Step 2: Calculating the Percentage
Once the formatting is completed, you can use the Excel formula of percentage for calculation. As with any Excel percentage formula, you have to perform the below-given steps:
- In the first place, you need to type the equal sign (=) in the cell where you want to get the percentage.
- After that, type the rest of the formula.
- The basic Excel formula of percentage is =part/total.
For instance, if you want to calculate the grade percentages, you need to apply the formula Actual Points/Possible Points.
To Increase/Decrease An Amount By A Certain Percentage
If you want to calculate a discount of 25% on a certain value, then you can use the formula:
=Price*1-Discount %. (Consider “1” as a stand-in for 100%.)
If you want to increase an amount to a certain percent, let’s say 25%, then you can apply the formula:
=Price*1+Discount %. (Consider “1” as a stand-in for 100%.)
This post can help you to understand and apply the Excel formula of percentage accurately. To get more informative blog posts similar to this, you can visit our blog section.