Importance of Cell Number Formatting in Percentage Calculation
To enter a percentage you have to use Number format.
Steps:
- Enter a decimal number (.12) in D5.
- Go to Home > Number and click the percent ‘%’ symbol.
The decimal number will be displayed as a percentage.
How to Add the Percentage to the Price with an Excel Formula
1. Add Percentage to Price Applying Simple Formula
The dataset showcases a list of grocery items with their old price and the price increment percentage.
Formula 1:
The old price of the Whole Grain Pasta was $10. The price increased by 12%. 1 will be added to 12%, which results in 112%. The older price $10 is multiplied by 112% to get the new price ($11.20).
Steps:
- Use the formula in E5 and press Enter.
=C5*(1+D5)
- You will see the new price for Whole Grain Pasta. Drag down the Fill Handle (+) to see the new price of the other items.
This is the output.
Formula 2:
- Use the below formula to add percentages to the old price:
=C5+C5*D5
Formula 3:
- Use Formula 1 in two steps.
- Add 1 to the increased percentage.
Steps:
- Enter the formula in E5, press Enter, and use the Fill Handle.
=1+D5
- Use the formula in F5 and press Enter.
=C5*E5
- Drag down the Fill Handle to see the result in the rest of the cells.
Formula 4:
Multiply the old prices by increased percentages to see the increased amount in USD.
Steps:
- Enter the formula in E5, press Enter, and use the Fill Handle.
=C5*D5
- Enter the formula in F5.
=SUM(C5+E5)
The new price for the first grocery item is displayed.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 2 – Using the Excel ‘Paste Special’ Option to Add a Percentage to the Price
Steps:
- Enter the formula in E5, press Enter, and drag down the Fill Handle.
=1+D5
- Select the Old Prices (C5:C12) and copy them by pressing Ctrl + C.
- Select E5:E12 and right-click. Click Paste Special.
- In the Paste Special dialog box, click Multiply in Operation.
- Click OK.
The new prices will be displayed.
Deduct the Percentage from the Price
Steps:
- Enter the formula in E5.
=C5*(1-D5)
- Press Enter, copy the formula to the rest of the cells using the Fill Handle, and see the new prices.
Things to Remember
Add parenthesis in the formulas: =C5*(1-D5). Excel performs multiplication operations before subtraction or addition.
You can also access Paste Special pressing Ctrl + Alt + V.
Download Practice Workbook
Download the practice workbook.
Related Articles
- Excel Formula to Add Percentage Markup
- How to Do Sum of Percentages in Excel
- How to Add 20 Percent to a Price in Excel
- How to Subtract a Percentage in Excel
- How to Use Excel Formula to Calculate Percentage of Grand Total
- How to Add 10 Percent to a Number in Excel
- How to Add 15 Percent to a Price in Excel
- How to Subtract a Percentage from a Price in Excel
<< Go Back to Add Percentage to a Number in Excel | Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!