Function PERCENTOF

Sometimes, when creating report, you need to display the values as a percentage. You can calculate percentages manually, and recently Excel also offers a PERCENTOF function to make this process easier. If you use functions such as GROUPBY or PIVOTBY, you can also use this function to calculate percentages when grouping values. Let’s see how we can use it!

Let’s start with a simple table that shows sales by city. The syntax of the PERCENTOF function is:

PERCENTOF (<dataset>,<total value>)

Therefore, if we just need to calculate the percentage of sales in one of the cities in relation to the total value, let’s write the formula:

=PERCENTOF(B3,$B$11)

If we copy the formula into the rest of the table, format the display of the values as percentage with two decimal places, and fix it a little more, we get a report like the one in the picture.

What if we wanted to group the data and, at the same time, calculate the percentages? We would write the following formula:

=GROUPBY(A3:A10, B3:B10, PERCENTOF)

The result is a grouped table that shows sales by cities where, instead of values, table show percentages.