Generic formula Show SummaryTo calculate a percentage of a number in Excel, multiply the percentage by the number. In the example shown, the formula in E5, copied down, is:
As the formula is copied down, the results in column E correspond to the percentages in column D. Note percentages must be formatted with the percentage number format, as explained below. ExplanationIn this example, the goal is to calculate various percentages of the number in cell B5. This is a straightforward calculation in Excel. The main task is to correctly enter the numbers in column D as percentages. Once that is done, you can multiply the percentage by the number. The first step is to format the cells in the range D5:D12 with the percentage number format. You can use the keyboard shortcut Control + Shift + %. Once the cells are correctly formatted, you can enter the numbers without the % operator. Excel will automatically add the % sign. Next, enter the formula in cell E5. This formula simply multiplies the amount in B5 by the percentage in D5:
Because the formula will be copied down, the reference to cell B5 must be an absolute reference. This locks the reference and prevents changes as the formula is copied down. Because the reference to cell D5 is relative, it will change at each new row. The results in column E are the amounts of B5 that correspond with each percentage in column D. If the number in B5 is changed, the results in column E are updated. Note: the percentages in column D are completely arbitrary and can be changed as desired. Author Dave BrunsHi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. I am just loving exceljet. Honestly THE best money I ever spent! What is so good about this resource is that it is in short, specific video clips, with both audio and text and with practice downloads for hands-on experience. I like the way you highlight what you are talking about in orange, and it isn’t too fast that the eye can’t see it before you move to the next thing! I love how you provide instructions for the absolute beginner right through to really advanced things. Get TrainingQuick, clean, and to the point trainingLearn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet. View Paid Training & Bundles How to Calculate Percentage Using Excel Formulas?
Let us consider a simple example. On a 15-day vacation, Mr. A spent 10 days in his hometown and 5 days in the USA. What percentage of days did he spend in the USA? The calculations are given as follows:
Hence, Mr. A spent approximately 33% of his vacation period in the USA. Let us go through the following examples to understand the concept of percentage in Excel. Example #1The following table shows the subject-wise marks of 6 students in a school. Since the results pertain to the annual examinations, the maximum marks of every subject are 100. We have to calculate the percentage of marks for all students. The steps to calculate percentages in Excel are listed as follows:
Example #2The following table consists of students who undertake internship in an organization. Their task is to sell products based on pre-defined targets. We want to determine the efficiency level (percentage) of every student. The steps to calculate the efficiency level are listed as follows: Step 1: Apply the formula “sales/target.” Step 2: Format column D to obtain the efficiency levels in percentage. For the last two students (Gulp and Ganapathi), the formula returns “#DIV/0!” errorErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more. Note: If the numerator is zero, the division of the numerator by the denominator returns “#DIV/0!#DIV/0! is the division error in Excel which occurs every time a number is divided by zero. Simply put, we get this error when we divide any number by an empty or zero-value cell.read more” error. Step 3: To eliminate the error, tweak the existing formula as “=IFERROR(C2/B2,0).” This implies that the IFERROR The IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read moredisplays zero if the division of C2 by B2 returns an error. In other words, the “#DIV/0!” error is replaced by zero with the help of the modified formula. Note: The IFERROR functionThe IFERROR function in Excel is used to determine what to do when an error occurs before performing any function.read more helps get rid of percentage errors. Step 4: The output using the IFERROR function is shown in the following image. The efficiency levels of students Gulp and Ganapathi are zero. This is because of the blank (zero) in column B and/or column C. Example #3For 2018, an organization’s sales for the months January and February are given in the following table. We want to calculate the growth or the decline (percentage) in monthly sales. The steps to calculate the increase (growth) or decrease (decline) percentage are listed as follows: Step 1: Apply the formula “=(C2-B2)/B2.” The sales in February (57500) are more than the sales in January (53250). The difference (growth) between the two sales is divided by the sales of the initial month (January). Step 2: Format column D to obtain the growth percentage. Hence, the February sales have increased by 7.98% over January sales. Note: While calculating the growth or decline percentage, if the numerator or the denominator is less than zero, the output is a negative value. Frequently Asked Questions1. What is the Excel formula for percentage? The basic percentage formula is “(part/total)*100”. This formula is used in Excel without the latter part(*100). This is because when the percentage format is selected, the resulting number is automatically changed to percent. In addition, the decimal points are removed and the output is shown as a rounded percentage. For instance, 26/45 is 0.5777. By applying the percentage format, this decimal number becomes 58%. To convert the output to percentage, either press “Ctrl+Shift+%” or select “%” in the “number” group of the Home tab. 2. What is the Excel formula for calculating the percentage change? The formula for calculating the percentage change is stated as follows: “(New value-Initial value)/Initial value” This formula helps calculate the percentage increase or decrease between two values. A positive percentage implies an increase, while a negative percentage shows a decrease. Note: To get the final answer (percentage change), the percentage format is applied to the output of the formula. 3. What is the Excel formula for calculating the percentage of the total? The formula for calculating the percentage of the total is “(part/total).” For instance, column A lists the monthly expenses from cell A2 to cell A11. The cell A2 contains $2500 as the rent paid. The cell A12 contains the total expenses $98700. The formula “=A2/$A12” returns 3% after applying the percentage format. This implies that the rent is 3% of the total paid expenses. Since A2 should change on dragging the formula to the remaining cells, it is entered as a relative reference. On the other hand, the total should remain fixed for all cells, so A12 is entered as an absolute reference.
Recommended ArticlesThis has been a guide to formula for percentage in Excel. Here we discuss the calculation of Excel percentages using the IFERROR formula along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel –
How do I calculate 20% of a number in Excel?If you want to calculate a percentage of a number in Excel, simply multiply the percentage value by the number that you want the percentage of. For example, if you want to calculate 20% of 500, multiply 20% by 500. - which gives the result 100.
How do you get a percentage of a number?Finding the percentage
Divide the number that you want to turn into a percentage by the whole. In this example, you would divide 2 by 5. 2 divided by 5 = 0.4. You would then multiply 0.4 by 100 to get 40, or 40%.
|