Exceljet

Quick, clean, and to the point

Get percentage of total

Excel formula: Get percentage of total
Generic formula 
=amount/total
Explanation 

To calculate percent of a total (i.e. calculate a percent distribution), you can use a formula that simply divides an amount by the total. In the example shown, the formula in D6 is:

=C6/$C$11

Note: the result is formatted with Percentage number format to show 25%, 10%, etc.

How the formula works

In the example, D6 contains the formula below, copied down:

=C6/$C$11

In cell D6, the result of the calculation is .625. The Percentage number format is applied to cell D6, with 2 decimal places, so Excel displays .625 as 63%.

The reference to C11 is absolute (i.e. $C$11) so that it won't change when the formula is copied down column D. This means that each value in column C will be divided by the same total in C11.

Percentage vs number

When you want to display a percentage in Excel, use the Percentage number format, which will automatically display a decimal value as a percentage. If you want a simple number instead without a percent sign (i.e. 75, 85, 100, etc.) you can multiply by 100:

=(C6/$C$11)*100

 

Author 
Dave Bruns
Related formulas 
Excel formula: Percent of goal
Percent of goal
If you need to calculate percent of goal, you can do so with a simple formula that divides the actual by the goal amount, with the result formatted using the percentage number format. This same formula can be used to calculate things like percent of...
Excel formula: Get total from percentage
Get total from percentage
If you have an amount and the percentage that the amount represents of a total, you can calculate the total with a formula that simply divides the amount by the percentage. How the formula works In the example, the active cell contains this formula...
Excel formula: Get percent change
Get percent change
To calculate the percentage change between two values in Excel, you can use a formula that divides the difference between two values by the "old" value. In the example shown, E6 contains this formula: = ( D6 - C6 ) / C6 When formatted as a...
Excel formula: Increase by percentage
Increase by percentage
If you need to increase a number by a certain percentage, you can use a simple formula that multiplies the number times the percent + 1. How this formula works In the example, the active cell contains this formula: = C6 * ( 1 + D6 ) In this case,...
Excel formula: Decrease by percentage
Decrease by percentage
If you need to decrease a number by a certain percentage, you can use a simple formula that multiplies the number times the percent - 1. How the formula works In the example, the active cell contains this formula: = C6 * ( 1 - D6 ) In this case,...
Excel formula: Get amount with percentage
Get amount with percentage
If you have a total and a percentage that presents some amount of the total, you can calculate the amount represented by the percentage with a simple formula that multiples the total by the percentage. How the formula works In the example, the...
Excel formula: Get percentage discount
Get percentage discount
If you have a discounted price and an original price, and you want to know the discount as a percentage, you can calculate the percentage discount using a formula that divides the discounted price by the original price and then subtracts the result...
Excel formula: Project complete percentage
Project complete percentage
To calculate the percentage complete for a project with a list of tasks, you can use a simple formula based on the COUNTA function. In the example shown, the formula in F6 is: = COUNTA ( C5:C11 ) / COUNTA ( B5:B11 ) How this formula works At the...
Excel formula: Get original price from percentage discount
Get original price from percentage discount
If you have a discounted price and know the discount percentage, you can calculate the original price with a simple formula that divides the discounted price by the result of 1 minus the discount percentage. How the formula works In the example, the...
Excel formula: Cap percentage at specific amount
Cap percentage at specific amount
To cap the result of a percentage-based calculation at a a specific amount, you can use the MIN function. In the example shown, the formula in D6 is: = MIN ( C6 * 10 % , 1000 ) which ensures the result will never be greater than $1000. How this...
Excel formula: Summary count with percentage breakdown
Summary count with percentage breakdown
To generate a count with a percentage breakdown, you can use the COUNTIF or COUNTIFS function, together with COUNTA. In the example shown the formula in H4 is: = COUNTIF ( category , F4 ) / COUNTA ( category ) How this formula works COUNTIF is set...
Excel formula: Get percent of year complete
Get percent of year complete
If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function. In the example, the formula in C5, which calculates percentage of year remaining, is: = YEARFRAC ( DATE ( YEAR...
Excel formula: Get profit margin percentage
Get profit margin percentage
If you need to calculate a profit margin, you can easily do so with a simple formula that uses the sale price and the cost. In the example shown, the first formula looks like this: = ( B4 - C4 ) / B4 Make sure you use parentheses to control the...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formula Examples, thoughtfully explained.