How to calculate proportions in Excel with the PERCENTOF() function

One of the more recent additions to Excel which I haven’t seen receive much coverage is the PERCENTOF() function. This function calculates the percentage that a subset represents of a given dataset. Although the concept is relatively straightforward, the goal of this post is to explore a method to enhance the readability of its output for users. Please refer to the accompanying exercise file to follow along:

In this example, we are given the frequencies of people’s preferred colors from a set of options. While the raw numbers provide initial insights, converting these numbers into proportions or percentages facilitates easier comparison between different groups or categories.

This normalization process aids in understanding the relative significance of the numbers, regardless of total quantities involved. Moreover, it simplifies the communication and comprehension of data by converting it into a standardized format, enhancing its accessibility across various contexts.

There are several methods to calculate the percentage of a total in Excel, among which the PERCENTOF() function is now likely the most straightforward with its recent introduction. Let’s begin with the basics.

In our first example, we aim to determine the proportion of votes for each color.

The PERCENTOF() function is relatively simple to use. We select a subset of data (usually a single cell), followed by the entire data set, and PERCENTOF() efficiently calculates the percentage:

That was simple enough.

Now, let’s proceed to the next worksheet in the workbook, titled two-way. This dataset breaks down the color by sex, distinguishing between male and female participants. Analyzing a “two-way” proportion breakdown of this dataset is not significantly more complicated than the first example; we simply need to reference both the Male and Female columns in the source data.

This is going great, but I have one significant concern which pertains to the formatting. Specifically, I would prefer if the percentages were placed immediately next to the raw figures, rather than be placed together toward the end of the table.

An attempt to simply cut and paste the percentages next to the figures might seem straightforward, but it will lead to a circular reference issue, as the percentage column will be erroneously included as part of the raw data.

This issue underscores a limitation with Excel’s structured references, which do not allow for referencing nonconsecutive columns, thereby causing complications.

To navigate around this problem, we can employ a workaround using a named range. Essentially, we can create a named range that encompasses both the Male and Female columns, and then utilize this range as the input for the PERCENTOF() function.

To proceed with this solution, navigate to the Formulas tab on the ribbon, then click on Name Manager. Choose “New” to establish a new range. Assign a name to this range, such as male_female, and then specify the Male and Female columns from the two-way table in the following manner: =colors_two_way[Male], colors_two_way[Female].

Now you can arrange the columns in the desired order, ensuring that the proportions are directly adjacent to the raw counts. Modify your PERCENTOF() calculations so that the named range for data_all is updated to male_female. This adjustment will enable you to calculate proportions using a non-adjacent range within the table.

Your calculations will update automatically if you add or remove rows from the table. Additionally, you can rename the source columns, and the named range will adjust dynamically.

Do you have any questions about deriving proportions with the PERCENTOF() function in Excel? Please share them in the comments.