How to Add Percentage to Price with Excel Formula (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I will show you how to add a percentage to a product’s price using an Excel formula. Luckily, adding percentages to a price is very easy. Let’s assume a product’s price has increased by 20%. It means that for every $100, the price has increased by $20. Often, we have to calculate the price increase for a product when its price is raised by a certain percentage. Similarly, while offering a discount on a product, we have to calculate the decreased price. So, let’s go through the tutorial.


Importance of Cell’s Number Formatting During Percentage Calculation

Before entering a percentage in Excel cells, remember you have to change the cell’s Number format. For example, if you write 12 in a cell and then apply the ‘%’ Number format, Excel will display the number as 1200%. Eventually, this will return an error in calculation. So, to get the desired cell Number format you may follow the below instructions.

Steps:

  • Type a decimal number (.12)  in Cell D5.

Importance of Cell’s Number Formatting During Percentage Calculation

  • Then go to Home > Number group and click on the percent ‘%’ symbol.

Importance of Cell’s Number Formatting During Percentage Calculation

  • As a result, the decimal number will be displayed as a percent.


How to Add Percentage to Price with Excel Formula: 2 Easy Ways


1. Add Percentage to Price Applying Simple Formula

Let’s assume we have a list of grocery items with their old price and price increment percentage. Now, I will add these below percentages to the old prices and hence calculate the new prices.

Add Percentage to Price Applying Simple Formula

I have used several formulas to add the percentages. Let’s explore them one by one.

📌 Formula 1:

For instance, the old price of the Whole Grain Pasta was $10. Now the price has increased by 12%. So, I will add 1 to 12%, which results in 112%. Then I will multiply the older price $10 with 112% and get the new price ($11.20).

Steps:

  • Type the below formula in Cell E5 and press Enter.
=C5*(1+D5)

Add Percentage to Price Applying Simple Formula

  • Then you will get the new price for Whole Grain Pasta. Now, use the Fill Handle (+) tool to get the new price of the rest of the items.

Add Percentage to Price Applying Simple Formula

  • As a result, we will get the below result.

Add Percentage to Price Applying Simple Formula

📌 Formula 2:

Instead of using the previous formula (Formula 1), we can use the below formula to add percentages to the old price:

=C5+C5*D5

Add Percentage to Price Applying Simple Formula

📌 Formula 3:

Now I will use Formula 1 in two steps. First, I will add 1 to the increased percentage. To do that see the following steps.

Steps:

  • Type the below formula in Cell E5, hit Enter, and use the Fill Handle tool to get the formula copied in range E6:E12.
=1+D5

Add Percentage to Price Applying Simple Formula

  • Then type the below formula in Cell F5 to get the new price and press Enter.
=C5*E5

Add Percentage to Price Applying Simple Formula

  • Finally, apply the Fill Handle tool to copy the formula to the rest of the cells, and here we get the new prices.

📌 Formula 4:

Here, I will multiply old prices by increased percentages to get the increased amount in USD. Later, I will apply the SUM function to add each old price and increased amount.

Steps:

  • First, type the below formula in Cell E5, and hit Enter on the keyboard. Next, use the Fill Handle tool to copy the formula to the rest of the cells.
=C5*D5

Add Percentage to Price Applying Simple Formula

  • Then, type the below formula in Cell F5.
=SUM(C5+E5)

Add Percentage to Price Applying Simple Formula

  • Consequently, we will get the new price for the first grocery item. Then copy the formula to the rest of the cells using the Fill Handle tool and get all the new prices.


2. Use the Excel ‘Paste Special’ Option to Add a Percentage to the Price

You can use the ‘Paste Special’ option of Excel to copy a range of cells and thus multiply them with another range. So, now I will apply this technique to add a percentage to a price.

Steps:

  • In the beginning, type the below formula in Cell E5, press Enter, and copy the formula over the range E5:E12.
=1+D5

Use Excel ‘Paste Special’ Option to Add Percentage to Price

  • Next, select all the Old Price (range C5:C12) and copy them by pressing Ctrl + C from the keyboard.

Use Excel ‘Paste Special’ Option to Add Percentage to Price

  • Then, select the range E5:E12 and right-click on it. Click on the Paste Special option (see screenshot).

Use Excel ‘Paste Special’ Option to Add Percentage to Price

  • The Paste Special dialog box will appear. Now, click on Multiply from the Operation section and press OK.

Use Excel ‘Paste Special’ Option to Add Percentage to Price

  • As a consequence, we will get all the new prices in the selected column.


Deduct Percentage from Price

In case you are offering a discount on some products, you have to subtract a certain percentage from the original price. To do that simply subtract the percentage from 1 and then multiply the result with the original price. To illustrate, I have applied discounts on the grocery items of the previous example.

Steps:

  • Type the below formula in Cell E5 at first.
=C5*(1-D5)

Deduct Percentage from Price

  • Then hit Enter, copy the formula to the rest of the cells using the Fill Handle tool, and get the new decreased prices.


Things to Remember

➤ Make sure you have added parenthesis on some of the above formulas such as =C5*(1-D5). This is because Excel performs multiplication operations before subtraction or addition. In that case, some of the above formulas will return the wrong result. So to get the exact result, pass the second part of the formula in parenthesis.

➤ You can bring the Paste Special dialog box using the keyboard shortcuts (Ctrl + Alt + V).


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Conclusion

In the above article, I have tried to discuss several methods to add a percentage to the price with an Excel formula. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any questions.


Related Articles


<< Go Back to Add Percentage to a Number in Excel | Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo