Percent of Total in Excel – 3 SIMPLE METHODS (PLUS PIVOT TABLES)

Ever wondered how much each piece of your data contributes to the whole? Calculating a percent of total is a quick way to figure that out, and Excel makes it super easy. Whether you’re analyzing employee performance, student grades, or sales data, this technique can give you valuable insights. In this post, I’ll walk you through three simple methods to calculate percent of total in Excel. We’ll also tackle how to do the same thing in a pivot table. Ready? Let’s dive in.

Here’s the full video tutorial for those who prefer to follow along:

Method 1: Using a Formula

If you’re comfortable with formulas, this is the most straightforward method. Let’s say you have sales data, and you want to calculate each account manager’s percent of total sales in a column next to their numbers. Here’s what to do:

1. Start with a formula: In an empty column, type = and select the first sales figure.
2. Divide by the total: Use the / symbol and add SUM to calculate the total of your sales column. For example:
   =B4/SUM(B4:B12)
   • Replace B4:B12 with the range of your data.
3. Make your total range absolute: Press F4 to lock the total range. This ensures Excel keeps referencing the same cells.
   • Your formula should now look like this:
     =B4/SUM($B$4:$B$12)
4. Hit Enter and drag the formula down the column.
5. Format it as a percent: Select the new column, go to the Number menu, and choose Percent.
6. Adjust decimals: Increase the decimal point to one place if needed. This helps show smaller percentages accurately (e.g., 0.4% instead of 0%).

This method is simple and works great, but it’s easy to forget a step (locking those cell references!). If you notice Excel giving you errors as you drag down, double-check that the total range is absolute.

RELATED TOPIC: How to Create a Pivot Table in Microsoft Excel

Method 2: Using the PERCENTOF Function

Here’s another way to calculate percent of total, but it requires knowing Excel’s PERCENTOF function.

1. Start the function: Type =PERCENTOF( in an empty column.
2. Select your value and total range:
   • Pick the first sales cell for your numerator.
   • Add a comma, then select the entire sales range for your denominator (e.g., B4:B12).
   • Be sure to lock the range with F4.
     Example:
     =PERCENTOF(B4, $B$4:$B$12)
3. Hit Enter and drag it down.
4. Format as a percent (and adjust decimals as needed).

Got that pesky green error in the corner of your cells? It’s Excel telling you something’s off. If you see it, double-check that your range is locked (F4) before dragging the formula.

Why use PERCENTOF? It’s slightly more compact, but it operates the same way as the first method. It’s just another trick to have in your toolbox.

RELATED TOPIC: How to Export Data from a PDF to Excel

Method 3: Using Quick Analysis (My Favorite!)

If you’re not into formulas, this method is for you. Excel’s Quick Analysis Tool can calculate percent of total with just a few clicks.

1. Highlight your data: Select your sales column.
2. Find the Quick Analysis Tool: Look for the little box that pops up at the bottom-right of your selection. Click it.
3. Go to Totals: In the Quick Analysis menu, choose Totals.
4. Select Percent of Total: Scroll until you see this option and click it.

Excel will automatically add a column with each value’s percent of the total. The results include two decimal places by default, but you can adjust that if you prefer.

This is the fastest method, hands down. No typing, no errors—just let Excel do the heavy lifting. The only downside? It’s less customizable than using formulas.

Bonus: Percent of Total in Pivot Tables

What if your data is already in a pivot table? No problem—Excel has a built-in way to calculate percent of total here too.

1. Insert a pivot table: Highlight your data, go to the Insert tab, and select Pivot Table. Place it in a new sheet for clarity.
2. Build your table: Drag Account Manager into Rows and Sales into Values.
3. Add the same field again: Drag Sales into Values a second time. You’ll see it labeled as “Sum of Sales2.”
4. Show Values As Percent of Total:
   • Right-click on the new sales column.
   • Choose Show Values As > % of Grand Total.

That’s it! Excel calculates the percent of total for your pivot table, and it even formats the results with two decimal places.

One quick tip about pivot tables: If your column headers are confusing, you can rename them. For example, you might have “Sum of Sales” and “Sum of Sales2.” Double-click the header and rename it to something like “Percent of Total.” If Excel complains that the name already exists (ugh), add a space at the end—it works like a charm.

RELATED TOPIC: How to Use the Quick Analysis Tool in Excel

Why Does This Matter?

So, why bother with percent of total? Whether you’re looking at sales data, school grades, or employee performance, this calculation helps you spot patterns. For example:

• Are some team members crushing it while others lag behind?
• Does one product dominate your sales figures?
• Are your KPIs realistic, or are they too high?

Take Jessa, for instance. In our example, she contributes just 0.4% of total sales. That’s a red flag. Maybe she’s not getting enough customer opportunities, or maybe she needs extra training. Either way, this number gives you a starting point for action.

Conclusion

Calculating percent of total in Excel isn’t just about numbers—it’s about making smarter decisions. Whether you prefer formulas, functions, or automation tools, there’s a method here that suits your style. And if you’re working with pivot tables, use Excel’s built-in features to save time.

Have questions? Want me to dive deeper into Excel tips? Drop a comment below or check out my site for written tutorials and more.

Thanks for sticking with me, and don’t forget to like, share, and subscribe for more Excel goodness. See you next time!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.