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:
- Select a cell next to the first revenue cell.
- Type an equals sign. Press the Left Arrow key.
- 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.
- Press the F4 key. The formula bar should now show B2/$B$17.
- 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.
- To use the format 9.2% (that is, one decimal place) instead of 9%, choose the Increase Decimal icon.
- In cell C2, double-click the fill handle to copy the formula down to the other rows.
- Add the heading % of Total in cell C1.
- 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.