How to Multiply by a Percentage in Excel
Multiplying by a percentage in Excel is a breeze once you know the ropes. First, enter your numbers and percentages into the spreadsheet. Next, If the percentage cell contains a value such as 15%, multiply the number directly by that cell; divide by 100 only when the cell contains the plain number 15. Finally, copy the formula to apply it to all the rows you need. By the end of this guide, you’ll be a pro at multiplying by percentages in Excel.
Step-by-Step Tutorial: How to Multiply by a Percentage in Excel
This tutorial will walk you through the steps to multiply numbers by percentages in Excel.
Step 1: Open your Excel Spreadsheet
Open Excel on your computer and load the spreadsheet where you need to perform the calculations.
You can either create a new workbook or use an existing one. It’s always good practice to save your work frequently.
Step 2: Enter Your Data
In your spreadsheet, enter the number you want to multiply in one cell and the percentage in another cell.
For example, if you want to multiply 50 by 20%, enter 50 in cell A1 and 20% in cell B1.
Step 3: Write the Formula
Click on the cell where you want the result to appear, and type the formula =A1*B1 and press Enter. Because B1 holds a real percentage (20%), Excel already treats it as 0.2, so you don’t divide by 100. If B1 holds the plain number 20 instead, use =A1*B1/100 or =A1*B1%.
Excel will calculate the result for you. In this example, the result will be 10.
Step 4: Copy the Formula
Click the small square at the bottom-right corner of the cell with the formula, and drag it down to apply it to other rows.
This method allows you to quickly apply the same calculation to multiple rows, saving you time and effort.
Step 5: Verify Your Results
Double-check your results to ensure the formula has been applied correctly to all the intended cells.
If any errors appear, review the cells and formulas to correct them.
After completing these steps, you will see the results of your calculations in the Excel spreadsheet, with all numbers accurately multiplied by their respective percentages. The same multiplication is the basis of sales pay, and our guide on how to calculate commission in Excel extends it to quotas and tiered rates.
Multiply a Whole Column by a Percentage with Paste Special
Microsoft’s guide to multiplying by a percentage also shows how to change a whole column of numbers by a percentage without adding a formula column. This replaces the original values, so make a copy first.
- In an empty cell, type the multiplier. For 15% of each value, type 15%. To increase each value by 15%, type 115% (or 1.15). To reduce each by 15%, type 85% (or 0.85).
- Copy that cell with Ctrl + C.
- Select the column of numbers you want to change.
- Right-click, choose Paste Special, select Multiply, and click OK.
- Delete the multiplier cell.
Common Percentage Formulas
- Sales tax: =A2*8.25% gives the tax, and =A2*(1+8.25%) gives the total with tax.
- Discount: =A2*(1-20%) gives the sale price after a 20% discount.
- Tip: =A2*18% gives an 18% tip.
- Commission: =A2*$B$1 where B1 holds the commission rate.
- Percentage of a total: =A2/SUM($A$2:$A$10), formatted as a percentage, shows each value’s share.
- Back out the original price: =A2/(1+8.25%) removes tax from a total that already includes it.
Multiply Two Percentages Together
When both cells are percentages, such as a 50% chance and a 40% chance, =A2*B2 returns 20%. Format the result as a percentage to see it that way. Don’t add percentages that should be multiplied. A 10% increase followed by another 10% increase is =A2*(1+10%)*(1+10%), a 21% total increase, not 20%.
Tips for Multiplying by a Percentage in Excel
- Double-check your percentage formatting. Ensure that the percentage is correctly formatted in the cell (e.g., 20% rather than 0.20).
- Use absolute cell references if necessary. If you want to multiply multiple numbers by the same percentage, use a dollar sign ($) in your formula (e.g., =A1*$B$1).
- Copy formulas efficiently. Use the fill handle (small square at the cell’s corner) to drag and copy formulas quickly.
- Utilize the formula bar. If you’re unsure about the formula, double-click the cell to see it in the formula bar for easy editing.
- Save your work frequently. Regularly saving your Excel workbook prevents loss of data due to unexpected shutdowns or errors.
Troubleshooting
The Result Is 100 Times Too Small or Too Large
You divided a real percentage by 100, or multiplied by a plain number as if it were a percentage. Check what the percentage cell holds: 20% means 0.2, while 20 means twenty. Use =A1*B1 for the first and =A1*B1% for the second.
The Result Shows as 0% or a Strange Percentage
The result cell picked up the percentage format. Select it and choose Number or Currency from the Number Format box on the Home tab.
Every Row Uses the Wrong Percentage After Copying
The reference to the percentage cell moved when you dragged the formula. Lock it with dollar signs, such as $B$1. Press F4 while the cursor is on the reference to add them quickly.
The Formula Returns #VALUE!
One of the cells contains text, such as a number pasted with a space or a % typed into a text cell. Retype the value, or use =VALUE() to convert it.
Frequently Asked Questions
How do I format a cell to show percentages?
Select the cell, right-click, choose "Format Cells," and select "Percentage" in the Number tab.
Can I multiply by a percentage without typing the formula every time?
Yes, you can drag the cell’s corner to copy the formula to other cells.
What if my percentage calculation isn’t working?
The most common cause is dividing a real percentage by 100 twice. If B1 shows 20%, use =A1*B1, not =A1*B1/100. Also check that the numbers aren’t stored as text and that the reference to the percentage cell is locked with $ when you copy the formula.
How can I apply the same percentage to a range of numbers?
Put the percentage in one cell and lock it with dollar signs, such as =A2*$B$1, then drag the formula down. To change the original numbers in place, use Paste Special, described above.
Is there a way to automate these calculations?
Formulas recalculate automatically whenever the numbers or the percentage change, so most people don’t need anything more. Put the rate in its own cell, refer to it with $B$1, and change it once to update every result. For a growing list, turn the data into an Excel table (Ctrl + T) so the formula fills down to new rows.
You Usually Do Not Need to Divide a Percentage by 100
If a cell already contains a true Excel percentage such as 15%, multiply directly with a formula such as =A2*B2. Excel stores 15% as 0.15. Dividing B2 by 100 again would produce the wrong result. Use =A2*(B2/100) only when B2 contains the plain number 15 rather than the percentage value 15%.
Calculate the New Total vs. the Percentage Amount
=A2*15% returns 15 percent of A2. If you want to increase A2 by 15 percent, use =A2*(1+15%). To decrease it by 15 percent, use =A2*(1-15%). These formulas answer different questions, so decide whether you need the percentage amount or the adjusted total.
Related: how to keep one cell constant in Excel.
Related: how to subtract one cell from another in Excel.
Related: change the number format in Excel.
Related: calculate a weighted average in Excel.
Related: how to make a pie chart in Excel with percentages.
Related: How to Round in Excel.
Related: How to Create a Multiplication Formula in Excel.
Related: how to calculate compound interest in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.