PERCENTOF Function in Excel: Share of Total in One Step

The PERCENTOF function in Excel returns the share one set of numbers makes up of a larger set, such as one region's share of total sales. You write =PERCENTOF(data_subset, data_all) and Excel does the SUM and the division in one step. This guide covers the syntax, the version you need, where PERCENTOF really helps (inside GROUPBY and PIVOTBY), and the classic formula that gives the same result in any Excel.

What does the PERCENTOF function do?#

PERCENTOF adds up the first argument, adds up the second, and divides the first total by the second.

=PERCENTOF(data_subset, data_all)

The result is a decimal: 0.24 means 24%. Format the cell as a percentage with Ctrl+Shift+% on Windows or ⌃⇧% on a Mac.

In other words, =PERCENTOF(x, y) is the same as =SUM(x)/SUM(y). There is no new maths here. The value of PERCENTOF is that it's shorter and that it can be passed to other functions as a calculation, which you'll see below.

Version note: PERCENTOF is a recent Microsoft 365 function, introduced alongside GROUPBY and PIVOTBY. At the time of writing, Microsoft lists it for Microsoft 365, including Excel for the web, and not for Excel 2021 or earlier. If you type it and see #NAME?, your version doesn't have it yet. Anyone who opens your file in an older version will see #NAME? too.

PERCENTOF examples on one table#

All the examples use this sales table in A1:C7:

Total sales are 50,000.

One row's share#

=PERCENTOF(C2,$C$2:$C$7)

Kofi's North sale is 12,000 ÷ 50,000 = 24%. Lock the total range with $ (press F4 on Windows or ⌘T on a Mac) so you can fill the formula down column D.

One category's share with FILTER#

To get North's share, pass only the North sales as the subset. FILTER picks them out:

=PERCENTOF(FILTER(C2:C7,A2:A7="North"),C2:C7)

The subset is 12,000 + 6,000 = 18,000, so the result is 36%. FILTER needs Microsoft 365 or Excel 2021, which you'll have if PERCENTOF works.

Use PERCENTOF inside GROUPBY and PIVOTBY#

PERCENTOF is most useful as the calculation in GROUPBY or PIVOTBY. These functions build a summary table from a formula, and their third argument is the function to apply to each group. Pass PERCENTOF there, without brackets:

=GROUPBY(A2:A7,C2:C7,PERCENTOF)

The formula spills a sorted list of regions with each one's share of the total:

The headings are added here for clarity; with ranges that don't include headers, GROUPBY returns just the regions and the shares. Each group's sales are the subset, and all the sales are the total. Before PERCENTOF, you needed a helper column or a pivot table to get this view. The guide to the GROUPBY function explains the other arguments, such as sorting and totals. Both GROUPBY and PERCENTOF spill their results, so read how dynamic arrays work if the spill behaviour is new to you.

Percentage of total in any Excel version#

If your Excel doesn't have PERCENTOF, or your file must work for everyone, divide by SUM. This is what PERCENTOF does behind the scenes.

For North, SUMIF adds 12,000 + 6,000 = 18,000 and SUM gives 50,000, so the result is again 36%. The SUM function guide covers the basics, and the article on percentage of total in Excel shows the classic method step by step, including how to check your shares add up to 100%.

PERCENTOF vs a pivot table#

A pivot table can show the same shares with no formula at all. Add Sales to Values, right-click it, and choose Show Values As > % of Grand Total. The guide to percent of total in a pivot table walks through it, and the article on pivot table Show Values As covers the other options, such as % of Parent Row Total.

Choose by how the result will be used:

• Pivot table when someone else will filter and explore the data, or when the file must work in older Excel.
• PERCENTOF with GROUPBY when you want a summary that updates the moment the data changes, with no Refresh button, and everyone has Microsoft 365.
• SUM division when you need one share in one cell and maximum compatibility.

Common PERCENTOF mistakes#

These slips give a wrong share or an error:

1. Getting the order backwards. The subset comes first and the total second. =PERCENTOF(C2:C7,C2) returns 417% instead of 24%.
2. Multiplying by 100. PERCENTOF returns a decimal. Format the cell as % instead of multiplying, or you'll see 2400%.
3. A subset that isn't part of the total. If the subset range includes rows outside data_all, the share can go over 100%.
4. Sharing the file with older Excel. Recipients without PERCENTOF see #NAME?. Use the SUM version if you're not sure.
5. Repeating the same total many times. If one formula uses the total several times, the LET function can name it once and make the formula easier to read.

Practise it#

PERCENTOF is one of several newer functions that replace helper columns. Practise modern Excel functions in a live spreadsheet: you type FILTER, SORT and UNIQUE formulas and the page checks them instantly. Then try the percent change lesson to get comfortable with percentage formats and formulas, and do the exercise on this page with the version that works everywhere.