Exceljet

Quick, clean, and to the point

Get percentage of total

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

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.

Explanation 

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
When working manually, percentage results are calculated by dividing one number by another (for example, a part by a whole) to get a decimal value, then multiplying by 100 to express as a percentage. Excel can display decimal or fractional values...
Excel formula: Get total from percentage
Get total from percentage
In the example, the active cell contains this formula: =C6/D6 Excel simply divides the value in cell C6 by the percentage value in cell D6. The result is the number $1,120, which is the total of all expenses in this case. Note that the value in cell...
Excel formula: Get percent change
Get percent change
Following order of operations, Excel first calculates the difference between the values (the actual change in sales) then divides that result by the original, or "old" value to get the decimal value -0.3435: = ( D6 - C6 ) / C6 = ( 49500 - 75400 ) /...
Excel formula: Increase by percentage
Increase by percentage
In the example, the active cell contains this formula: = C6 * ( 1 + D6 ) In this case, Excel first calculates the result of 1 + the value in D6 (.2) to get 1.2 which it then multiplies times the value in C6 (70) to get a final result of 84: =70 * (1...
Excel formula: Decrease by percentage
Decrease by percentage
In the example, the active cell contains this formula: = C6 * ( 1 - D6 ) In this case, Excel first calculates the result of 1 - the value in D6 (.2) to get 0.8 which it then multiplies times the value in C6 (70) to get a final result of 56: =70 * (1...
Excel formula: Get amount with percentage
Get amount with percentage
In the example, E6 cell contains this formula: = C6 * D6 To calculate the amount, Excel simply multiplies the value in cell C6 ($1,120) by the percentage value in cell D6 (.625). The result is $700, which is 62.5% of $1,200. = 1120 * .625 = $700...
Excel formula: Get percentage discount
Get percentage discount
In the example, the active cell contains this formula: =1-(D5/C5) In this case, Excel first divides the discounted price in D5 (59.5) by the original price in C5 (70) to get 0.85, which is then subtracted from 1: =1-(59.5/70) =1-0.85 =0.15 Note: the...
Excel formula: Project complete percentage
Project complete percentage
At the core, this formula simply divides tasks complete by the total task count: = complete / total which is then formatted as a percentage. To count complete tasks, we count non-blank cells in the range C5:C11: = COUNTA ( C5:C11 ) To count total...
Excel formula: Get original price from percentage discount
Get original price from percentage discount
In the example, the active cell contains this formula: = C6 / ( 1 - D6 ) In this case, Excel first calculates the result of 1 - the value in D6 (.2) to get 0.8. Next the value in C6 (56), which represents the discounted or "sale" price, is divided...
Excel formula: Cap percentage at specific amount
Cap percentage at specific amount
This formula uses the MIN function to make a decision that might otherwise be handled with the IF function . Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for "lesser of the two"...
Excel formula: Summary count with percentage breakdown
Summary count with percentage breakdown
COUNTIF is set up to count cells in the named range "category", which refers to B5:B122. The criteria is supplied as a reference to H4, which simply picks up the value in column F. the COUNTA function simply counts all non-blank cells in the named...
Excel formula: Get percent of year complete
Get percent of year complete
the YEARFRAC function takes two dates, a start date and and an end date, and calculates the time between them in fractional years. In this case, we are using the end date as-is, but generating a start date that equals the first day of the same year...
Excel formula: Get profit margin percentage
Get profit margin percentage
Profit margin is a ratio of profit divided by price, or revenue. In the example shown, we are calculating the profit margin for a variety of products or services. For each item, we have price and cost, but profit is not broken out separately in...

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 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.