See all Microsoft Excel tips
Strategy: I hate solutions that require two different formulas, but this is one of them. You will need one formula for cell C2 and a different formula for cells C3 and below.
1) In cell C2, enter a formula of =B2/$B$18. Format the result as a percentage with one decimal place, as shown in Fig. 429.
2) Copy C2 to just the next cell, as shown in Fig. 430. If you want, you can drag the Fill handle down one cell to copy the formula. Or, use Ctrl+C and then Ctrl+V.
3) Hit F2 to edit the cell, as shown in
Fig. 431.
4) As shown in Fig. 432, type a Plus sign and touch cell C2. Hit Enter.
5) Double-click the Fill handle in C3 to copy this formula down to all of the other cells.
Note that you do not want this formula to be added to your total row. As shown in Fig. 433, the dataset was purposely set up with the total row and the data separated by a blank row in order to prevent this formula from copying to the total row.
Additional Information: If you absolutely want to produce this total with a single formula, you could use this formula in C2 and copy it down, as shown in Fig. 434: =SUM(B2:B$2)/B$18. This works because the range B2:B$2 is an interesting reference. It says to add up everything from the current row to the top row. This formula seems a bit less intuitive, so you might prefer the method shown earlier.
Summary: The formula for a running percentage of the total is another common analysis tool. This technique offers two different options for calculating the formula.
See all Microsoft Excel tips