Summary
I use Excel to track the budget for our household expenses. My spreadsheet lists each expense category, with a column where I show what percentage of the budget that category represents. That column divides each category by a total row, with dollar signs locking the reference every time. The percentage column is the one I actually watch. While the dollar amounts show how much money was spent in each category, the percentages show if one category is starting to creep into the other categories’ budget. Then I found the PERCENTOF function. It’s designed for exactly this type of calculation, and it’s been hiding in Microsoft 365 this whole time. The way I used to calculate percent of total A division formula, a total row, and a lot of dollar signs I created the budget spreadsheet in the images throughout this article for example purposes. In my budget spreadsheet, I have a list of categories in Column A, and the corresponding expense amounts for the month in Column B. At the bottom of Column B (in row 14 for my example) is a SUM of all the values in the column. That’s my total. In cell C2 I enter the formula =B2/B14, and format the cell as a percentage. I then drag the formula down to get a percentage for all the categories. The dollar signs in the total reference are critical, otherwise Excel will change the row number as you drag the formula down the column, causing 0! errors as it tries to divide by empty cells. And as long as your total remains accurate, the percentages will also be correct. If you add another category to the spreadsheet but not within the range of the total row, the total won’t include that category. PERCENTOF does the part-over-whole math for you Two arguments and no helper total This function takes two arguments: =PERCENTOF(data_subset, data_all). The first argument is the part of the total that you want to calculate the percentage of, and the second argument is the whole or complete data set. Microsoft tells us that this formula is equivalent to dividing one SUM by another SUM. No need for a total row. Instead of using the formula =B2/B14 to calculate the percentage, I now use the formula =PERCENTOF(B2, B2:B13). Of course, the dollar signs are still necessary in order to lock the reference to the whole data set while dragging the formula down the column. PERCENTOF eliminates the need for a total row and the second formula that references it. It also makes the calculation more readable. How do you know what’s in cell B14 if you see the formula =B2/B14? With PERCENTOF, the formula tells us. The main benefit is that the first argument can be a range. Need to know how much you’ve spent on groceries and eating out in combination? =PERCENTOF(B2:B3, B2:B13) will tell you in a single cell; no need to calculate the total first. For example, if you spent 6,000 month on those two things, then it will return 20%. It gets better inside GROUPBY A percent-of-total column in one formula As I was writing this, I decided to try out the PERCENTOF function in GROUPBY, which is one of the places where Microsoft says it is commonly used. If you keep a list of transactions in your budget, where column A is the category and column B is the amount, =GROUPBY(A2:A200, B2:B200, PERCENTOF) will give you all the categories, along with what percentage of your total spending each is, all in one go. GROUPBY has optional arguments for sorting, so you can have the category that takes up the largest share of your budget be at the top of the list. The best part is that if you add a transaction, the summary will automatically update, no need to refresh a pivot table. The only downside is that PERCENTOF needs to be working with numbers, so if you use it with text, such as the category names, you’ll get 0!. The catches I ran into Microsoft 365 only, with a few rough edges Microsoft indicates that PERCENTOF works only on Excel for Microsoft 365. So, if you share a file that uses the function with someone with an older standalone version of Excel, they won’t be able to calculate the formula. Unfortunately, PERCENTOF doesn’t work in Google Sheets either. I also use Sheets, and I still use the equivalent formula, =SUM(B2:B3)/SUM(B2:B13), there. It works the same way, just with more typing. If the total adds up to zero, then you will get 0!, which is understandable. The function also returns a decimal, so you’ll have to format the column as a percentage, just like you always would. If you create a tab for next month’s budget before entering anything, wrap the formula in IF, like =IF(SUM(2:13)=0, "", PERCENTOF(B2, 2:13)), to leave the column blank instead of displaying errors. My budget column finally reads like plain English PERCENTOF may not be the most impressive function, since it only performs one division. But =PERCENTOF(B2:B3, B2:B13) makes it obvious what the cell is for, and I don’t have to worry about a total row getting out of sync. It’s the same feeling I got with TEXTJOIN replacing my string of ampersands: a small function that makes my spreadsheet less error-prone.