Maybe it’s been a while since you’ve worked with percentages in Excel. Or, it may be that percentage calculation is not your favorite math skill. In this article, you'll see how it can be much easier if you let Excel do the work for you.
If you need to find the percentage of a total or find the percentage of change between two numbers, you can learn more in the article Calculate percentages.
Multiply an entire column of numbers by a percentage
Consider an example table like the one in the figure, in which we've got a few numbers to multiply by 15 percent. Even if the column has 100 or 1,000 cells of data, Excel can still handle it in a few steps.
Here’s how to do it:
Enter the numbers you want to multiply by 15% into a column.
In an empty cell, enter the percentage of 15% (or 0.15), and then copy that number by pressing Ctrl-C.
Select the range of cells A1:A5 (by dragging down the column).
Right-click over the cell selection, and then click Paste Special (do not click the arrow next to Paste Special).
Click Values > Multiply, then click OK.
The result is that all the numbers are multiplied by 15%.
Tip: You can also multiply the column to subtract a percentage. To subtract 15%, add a negative sign in front of the percentage, and subtract the percentage from 1, using the formula =1-n%, in which n is the percentage. To subtract 15%, use =1-15% as the formula.
Change an amount by a percentage
Let's say you need to decrease—or want to increase—your weekly food expenditures by 25%. To calculate the amount, use a formula to subtract or add a percentage.
In this example, we set column B to contain the amount currently spent, and column C is the percentage by which to reduce that amount. Here's is a formula you could enter in cell D2 to accomplish this:
In this formula, 1 is equivalent to 100%. The values inside the parentheses calculate first, so ther value of C2 is subtracted from 1, to give us 75%. The result is multiplied by B2 to get a result of 56.25 for Week 1.
To copy the formula in cell D2 down the column, double-click the small square green box in the lower-right corner of cell D2. You get the results in all of the other cells without retyping or copying-and-pasting the formula.
To increase the amount by 25%, simply replace the + sign in the formula in cell D2 sign to a minus (-):
Then double-click the fill-down handle again.