Summary

The Excel PERCENTOF function is designed to calculate the percentage of a subset of data with respect to all data. The output from PERCENTOF is a decimal number that can be formatted with Excel's percentage number format. PERCENTOF is most useful with the GROUPBY and PIVOTBY functions, where it calculates a percentage for each group.

Purpose
Return a subset of data as a percentage of all data
Return value
A decimal value that represents the percentage
Syntax
=PERCENTOF(data_subset, data_all)
  • data_subset - A subset of data as a range or array.
  • data_all - All data as a range or array.

Using the PERCENTOF function

The PERCENTOF function is designed to return a subset of data as a percentage of a larger data set. The output from PERCENTOF is a decimal number that can be formatted with Excel's percentage number format. Essentially, PERCENTOF performs a calculation like this:

=SUM(data_subset)/SUM(data_all)

The result will be a decimal number that must be formatted as a percentage. For example, if the subset of data is 250 and the full set of data is 1000, PERCENTOF will return 0.25:

=PERCENTOF(250,1000) // returns 0.25

When 0.25 is formatted as a percentage, it will display as 25% on the worksheet.

Although PERCENTOF will work fine on its own, it was originally introduced as a companion to the GROUPBY and PIVOTBY functions to provide a simple way to show data subsets as percentages in the output of these functions.

Key features

  • Divides the sum of data_subset by the sum of data_all.
  • Sums both arguments, so a range returns one result, not a spill.
  • Ignores text and empty cells, as SUM does.
  • Returns a decimal value that needs the Percentage number format.
  • Works as the function argument in GROUPBY and PIVOTBY, where it returns a percentage for each group.
  • Available in Excel 365 only.

Basic examples

PERCENTOF takes two arguments, data_subset and data_all. Each one can be a number, a range, or an array. The result is not limited to 100%:

=PERCENTOF(112000,100000) // returns 1.12

With ranges, PERCENTOF sums each range before it divides. The subset doesn't have to be inside the second range, because PERCENTOF doesn't compare them:

=PERCENTOF(range1,range2) // SUM(range1)/SUM(range2)

To get the percentage for one group, you can use the FILTER function to create the subset:

=PERCENTOF(FILTER(values,groups="A"),values)

Percentage of a total

In the worksheet shown above, the formula in cell E5 looks like this:

=PERCENTOF(C5,$C$5:$C$12)

The result is 37% (0.37) since $950 is 37% of the sum of the range C5:C12, which is $2,570. As the formula is copied down, it returns each value in column C as a percentage of $2,570. The range is an absolute reference, so that it doesn't change as the formula is copied.

On a single column like this, PERCENTOF returns the same result as =C5/SUM($C$5:$C$12), which works in every version of Excel. You might expect to get all eight percentages at once by giving PERCENTOF the full range, but since both arguments are summed, the result is a single 100%:

=PERCENTOF(C5:C12,C5:C12) // returns 1

To spill all eight percentages, use division instead: =C5:C12/SUM(C5:C12).

Percentage by group with GROUPBY

PERCENTOF is most useful inside the GROUPBY function, where it calculates a percentage for each group. In the worksheet below, the same expenses now have a category in column C, and the goal is to sum the amounts by category and show each category as a percentage of the total. The formula in F4 is:

=GROUPBY(C5:C12,D5:D12,HSTACK(SUM,PERCENTOF),0)

PERCENTOF example - percentage by group with GROUPBY

The HSTACK function asks for two calculations, so GROUPBY returns a sum and a percentage for each category and adds the function names as headers. The results spill into F4:H8.

Notice that PERCENTOF appears here without any arguments. For each category, GROUPBY provides the amounts in that category as data_subset and all the amounts as data_all. For Housing, the percentage works out like this:

=PERCENTOF({950;250;185},D5:D12)
=1385/2570
=0.5389

The total row shows 100%, since it compares all the amounts to all the amounts.

The values given to GROUPBY must be numbers. If the values are text, PERCENTOF returns #DIV/0! because the sums are zero. To count text values and get a percentage, give GROUPBY a column of 1s to work with, as explained in Summary count with percentage breakdown.

Percent of row or column totals with PIVOTBY

In a two-way summary, a percentage can be based on different totals. Are we comparing a value to its column total, its row total, or the grand total? The relative_to argument in the PIVOTBY function decides what PERCENTOF uses for data_all. In the worksheet below, the goal is to show how sales in each region break down by product. The formula in F4 is:

=PIVOTBY(B5:B13,C5:C13,D5:D13,PERCENTOF,0,,,,,,1)

PERCENTOF example - percent of row totals with PIVOTBY

relative_to is the last argument in PIVOTBY, so the extra commas skip over the optional arguments in between. Here, it is set to 1, which means row totals, so each row adds up to 100%. Here is what the main options return for hats sold in the East region, where sales are $200:

  • 0 (the default) - column totals. Hats total $600, so the result is 33.3%, and each column adds up to 100%.
  • 1 - row totals. East totals $500, so the result is 40%, and each row adds up to 100%.
  • 2 - the grand total. All sales total $2,000, so the result is 10%, and all the products and regions together add up to 100%.

Options 3 and 4 use parent totals, for when row or column fields are nested. With one row field and one column field, as above, 3 returns the same results as 1, and 4 returns the same results as 0. With regions nested inside areas in the row fields, 4 returns each region's share of its area. GROUPBY has no relative_to argument. It always calculates a percentage of the total for all rows.

Notes

  • PERCENTOF returns #DIV/0! when the sum of data_all is zero, including when data_all contains only text.
  • TRUE and FALSE values in a range or array are ignored, as they are in SUM. =PERCENTOF(range="A",range<>"") returns #DIV/0!. Convert them to numbers first: =PERCENTOF(--(range="A"),--(range<>"")).
  • An error in either argument makes PERCENTOF return #NUM!, not the original error. In GROUPBY, one error in the values makes every percentage return #NUM!.
  • If FILTER returns no matches for data_subset, PERCENTOF returns #CALC!.

PERCENTOF is one of over 50 new functions in Excel, available in Excel 365 only. See New Excel Functions for a complete list.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - 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.