How to calculate percentage in Excel? ( Discount, GST, Margin, Change/growth, Markup)

In this Excel tutorial, you will learn how to calculate various types of percentages in Excel.

I have tried to include common types of percentages like percent discount, GST  percentage (GST% ,GST amount, reverse GST) , percentage marks,  percent contribution from total or percent mix, markup and markdown percentage and gross margin percent calculation in Excel. 

Excel formula for percentage discount (discount rate)

To find the percentage discount or discount rate in Excel follow the below steps.

  1. Find the difference between MRP and Sale Rate
  2. Divide difference amount by MRP
  3. Format discount rate values as percentage

Discount Rate= (MRP-Sale Rate)/MRP *100 (without percent sign in the cell)

or Discount Rate= (MRP-Sale Rate)/MRP  (with percent sign in a cell formatted as percent)

Cell D10 formula =B10-C10

Cell E10 formula =D10/B10

Alternatively, you can directly calculate percentage discount without helper column like below

 In cell D10 you can directly write =(B10-C10)/B10 to find discount rate in Excel.

 

Discount rate Excel formula

To show the percentage sign in discount rate follow the below steps

  • Select discount rate cells
  • Go to Home tab the select percentage format from the dropdown.

Alternatively, select range then press CTRL+SHIFT+% shortcut key to bring the number in a percentage format.

Show and remove percentage sign in Excel​ | Percentage to a number

Format cell values as percent

Calculate price after discount in Excel

You can calculate price after discount using MRP and discount rate in Excel.

Follow the below steps

  1. Find discount amount (MRP*Discount percent)
  2. Subtract this discount amount from MRP 

Discounted Price = MRP-Discount amount

or Discounted Price = MRP-(MRP*Discount percent)

or Discounted Price = (100%-Discount percent)*MRP

Cell D2 formula =B2-(C2*B2)

Calculate price after discount in Excel- Method1

If in discount value % sign is not there then use the below formula to get the original price after discount.

Just add percent sign after discount value cell address

Cell D2 formula =B2-(C2%*B2)

Calculate price after discount in Excel- Method2

Alternatively, you can use the below formula

Cell D2 formula =(100%-C2)*B2

The above calculation concept is like below

If there is a 10% discount then you will pay 90%. So to get the price after discount just multiply the pay percentage 90% by MRP.

Calculate price after discount in Excel- Method3

How to find the original price before discount ?

Suppose you have discount percentage and rate after discount in Excel sheet, here you want to get the original price before discount or undiscounted price.

In the below image price after discount and discount rate is known, you have get the original price before discount means MRP.

Cell D2 formula is =B2/(100%-C2)

Here is a breakdown or understanding for “Earphone A10”

In Cell D2

  •  (100%-C2)=(100%-10%) will give 90%  ( can be written as 90/100) which is a portion of the original price one needs to pay after the discount.
  • B2/(100%-C2) =B2/90%=450/(90/100)
  • Rearranging 450/(90/100) will give (450/90)*100. Here (450/90) will give the price of 1% portion. 
  • When  (450/90) multiplied by 100 you will get the price of 100% portion which is the original price before discount.
Find original price

Note:- If you are not using  percentage sign in discount rate then the cell D2 formula will be like below.

=B2/(100-C2)*100

Find original price before discount in Excel

Calculate GST tax amount in Excel

In the below image taxable amount (rate without tax) and GST rate is given.

To calculate GST tax amount you can use the below formula

GST Amount = Taxable Amount*GST Rate%

or

GST Amount = Taxable Amount*(GST Rate/100) 

 Cell D2 formula =B2*C2% , since there is no percent sign in GST rate 

Cell D4 formula for GST Amount =B4*C4, since there is a percent sign in GST rate.

After calculating the GST tax amount you can get the Amount with GST using the below formula

Amount with GST tax = Taxable amount + GST amount

Cell E2 formula for amount with GST tax =B2+D2

Calculate GST tax amount in Excel

Reverse GST calculation formula in Excel (GST reverse charge)

Many struggles to calculate the taxable amount and GST amount when the amount with tax and tax rate (GST rate) is provided in Excel.

Below is the formula for reverse GST calculation (GST reverse charge)

Taxable Amount = Amount with tax / (100+GST rate)*100

GST Amount = Amount with tax / (100+GST rate)* GST rate

 Cell D2 formula for taxable amount =B2/(100+C2)*100

Cell E2 formula for GST amount =B2/(100+C2)*C2

Alternatively, you can also get GST amount by subtracting Amount with tax with taxable amount

Reverse GST calculation formula in Excel

If the GST rate has a percent sign then use then instead of only 100 use 100% in the formula.

cell D2 formula =B2/(100%+C2)*100%

cell E2 formula =B2/(100%+C2)*C2

So, in reverse GST calculation, you split the amount with tax into the taxable amount and GST amount.

Reverse GST calculation formula in Excel

Excel formula for percentage of total in Excel

Sometimes you may need to calculate the percentage of total value in Excel.

By calculating percentage of total you can know the mix percent or percent contribution of each element.

In the below table category and sale amount is given.

To calculate the mix percentage or percentage contribution of each category in total sale value, do the below steps.

  • Calculate total sale amount by applying Excel Sum function

In cell B9 total is calculated using formula =SUM(B2:B8)

  • Divide individual value with total amount

cell C2 formula =B2/$B$9. Now select cells  (C2:C7) then press CTRL+SHIFT+% to show percentage sign (format number as a percentage). 

If you do not want to show the percentage sign then the cell C2 formula becomes  =B2/$B$9*100, keep cell or range format to “General” or number format.

Excel formula for percentage of total

Percentage of subjects marks Excel formula

Students and teachers require to calculate the percentage of marks in a given subject.

To calculate marks percent just divide marks got by max marks.

Marks percentage = Marks got / Max marks   (format cell as a percentage to show percent sign)

or

Marks percentage = Marks got / Max marks  * 100 (keep cell format general or number to show numbers without the percent sign)

Cell D2 formula =C2/B2 

After applying the formula, select cells then press CTRL+SHIFT+%  to apply percentage number format on cell.

Percentage marks Excel formula

Calculate percentage increase or decrease (percent change) in Excel

In the below image quarter wise sale of two years is given. 

Step to calculate percentage increase decrease (% Growth) in Excel

  • Find the difference between the latest year’s sales and the past year’s sales.

Growth amount = Latest year sale – Past year sale

Cell D3 formula =C3-B3

  • Divide Growth amount by  past year sale

Percentage growth = Growth amount/ Past year sale  (format cells to percentage to show percent sign)

or 

Percentage growth = Growth amount/ Past year sale *100 (keep number format general or number – without the percent sign)

Cell E3 formula =D3/B3

Calculate percent increase decrease in Excel

To calculate percentage increase and decrease directly, without using the growth amount column use the below formula.

Cell D3 formula =(C3-B3)/B3  (format cell to percentage to show % sign)

If you do not want to show the percentage sign then use the below formula and keep the cell format general or number.

Cell D3 formula =(C3-B3)/B3*100

Calculate percent increase decrease in Excel

Calculate markup and markdown percentages in Excel

Markup and markdown percentage

Markup percentage is on each 100 rupees cost, how much amount you want to add.

Markdown percentage is on each 100 rupees value, how much amount you want to subtract.

Calculation steps

  • Find the difference between sale rate and cost
  • Divide difference amount by COST amount then multiply it with 100

Cell D2 formula =C2/A2*100

If you want to show a percentage sign then do not multiply it with 100 and change cell format to the percentage by pressing CTRL+SHIFT+%

In the second example markdown is done.

percentage markup, markdown calculation in Excel

Calculate original price before markdown in Excel

Here you have markdown rate and amount after markdown, you want to find out the price before markdown.

Original price = Price after markdown / (100- markdown rate) *100

Cell C2 formula =(A2/(100-B2))*100

Note:- If markdown rate has percentage sign then use below formula

Cell C2 formula =(A2/(100%-B2))*100% 

calculate original price before markdown in Excel

Calculate price after markup in Excel

Price after markup = (100+markup rate)%*Cost

or Price after markup = Cost + (Cost/100)* markup rate 

Cell C2 formula =(100+B2)%*A2

If markup rate has % sign then C2 formula =(100%+B2)*A2

Intuitive explanation:- Markup rate is on every 100 rupees, how much amount you want to add.

So, in 300, three 100 rupees are there (100+100+100)

For 30% markup rate total value will be  add  30 Rs. on each 100  (30+30+30) =90

Price after after markup =  300 + 90 = 390

Calculate price after markup in Excel

Gross margin percentage calculation in Excel

Definition:- Gross margin percentage is on each 100 rupees sale, how much profit is made.

Note:- Gross margin is calculated on each 100 rupees sale not cost.

Follow the below steps to get the Gross margin percentage

  • Find Gross profit amount by subtracting Sale with Cost
  • Divide Gross profit amount by Sale then multiply it with 100

Gross margin percent = (Gross profit amount/Sale amount) *100

or Gross margin percent = ((Sale amount- Cost amount)/Sale amount) *100

 cell D2 formula =C2-B2

cell E2 formula =(D2/C2)*100

Gross margin percentage calculation in Excel

How to add 5 percent to a number in Excel?

To add 5 percent to a number or increase any number by X percent, one can use one of the below formulas.

Increase number by X% = (100%+Increment %)*Number

cell C2 formula =(100%+B2)*A2

Use this formula when increment number percent has % sign

Add 5 percent to a number in Excel. Increase number by X%

If increment number percentage do have % sign then use the below formula

Cell C2 formula

=(100+B2)%*A2

Add 5 percent to a number Excel formula

Alternatively, you can use the below formula to add 5 percent to a number

Increase number by X% = (Number*Increment %) + Number

Cell C2 formula =(A2*B2%)+A2

Notice in the below image 5 does not have percent sign.

If 5% is written instead of 5 then formula will be

cell C2 formula =(A2*B2)+A2

Increase number by X percent in Excel

You can also watch below video in Hindi

How to calculate percentage in Excel | Percent increase in Excel

Leave a Comment

Share via
Copy link
Powered by Social Snap