MrExcel.com - Your One Stop for Excel Tips & Solutions

Calculate a Percentage of Total

March 02, 2022 - by

Calculate a Percentage of Total

Problem: I have a spreadsheet with sales by customer and a total at the bottom. I want to express each customer as a percentage of the total.

Strategy: Divide each row’s sales by the total cell. Follow these steps:

  • 1. Select a cell next to the first revenue cell.

  • 2. Type an equals sign. Press the Left Arrow key.

  • 3. Type the forward slash (/) sign. Press the Left Arrow key. Press Ctrl+Down Arrow key. Your cell pointer should now be on the total cell.

  • 4. Press the F4 key. The formula bar should now show B2/$B$17.

  • 5. Press Ctrl+Enter to enter the formula and stay in the current cell. Format the calculation as a percentage by using the % icon on the Home tab.

  • 6. To use the format 9.2% (that is, one decimal place) instead of 9%, choose the Increase Decimal icon.

  • 7. In cell C2, double-click the fill handle to copy the formula down to the other rows.

  • 8. Add the heading % of Total in cell C1.

Customers in A2:A16 with revenue in column B. B17 contains the Total Revenue. To add a Percentage of Total calculation in C, use =B2/$B$17
Figure 355. Show a percentage of total.

Additional Details: The key element of this procedure is pressing the F4 key to add dollar signs to the reference for the total row. As you copy the formula from C2 to C16, the formula is always going to compare the revenue in the current row to the total revenue in row 17.

Creating a percentage of total is a common task in Excel. Being able to quickly enter an initial formula that can be copied to all cells is a good technique to have in your skill set.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ryan Christodoulou on Unsplash