How to Enter an Excel Formula With Price Plus Tax

Calculating sales tax on prices is something you don't have to worry about if you use Excel. You can add a formula to the spreadsheet you use for invoices, bills of sale or quotes, and that's the end of it.

Creating an Excel Sales Tax Formula

Excel doesn't have a built-in sales tax formula, but adding one is only a matter of multiplying your price by the tax rate.

There are two common ways of adding sales tax in Excel. One way is to insert the total tax on a purchase as a separate line item and then add it to the net price. Another way is to apply the tax to the net price to get the total price.

Add Sales Tax as a Separate Line Item Example

In most cases, sales tax is calculated in three separate lines so that customers can see what you're charging them and what the sales tax is as separate line items. In this example, the net price total is entered in cell C8 (with the words "Net Price" appearing in cell B8):

  • Net Price: $24.00 (C8)
  • Tax: (C9)
  • Total Price: (C10)
  1. Click cell C9 and multiply the net price in cell C8 by your sales tax rate by entering a formula such as =C8*0.05 for a 5% tax. If your tax rate is 8%, enter =C8*0.08.
  2. Press Enter and the amount of sales tax appears in the cell you selected. For a 5% rate, $1.20 appears. Calculating the total price is just a matter of adding the amounts in cell C8 and C9. In Excel, this is done with the SUM formula. 
  3. Enter this formula in cell C10: =SUM(C8:C9)
  4. Press Enter and the total price appears with the tax added to the net price. In this example with a 5% tax rate, the total price is $25.20. 

Adding Sales Tax Directly to a Price Example

Applying a sales tax formula directly to a price without a separate line item showing the tax is a simple matter of multiplication. If the sales tax is 5%, for example, then the final price is 105% of the pretax amount. To get this amount, multiply the pretax price by 1.05.

In this example, the cell containing the pretax amount is located in cell C4. (Your spreadsheet will likely use a different cell.)

  1. Select the cell where you want the final sales price to appear. 
  2. Identify the cell containing the pretax amount (C4 in this example). 
  3. Type the following formula: =C4*1.05
  4. Press Enter, and the amount including tax appears in the cell you selected for the final sales price.

Understanding Excel's Syntax

If you're new to Microsoft Excel, what you typed in the cells to calculate the sales tax may not seem intuitive. Knowing what to do usually solves a problem, but knowing why you do it allows you to solve other problems as well.

  • Equal Sign: Whenever you want Excel to perform a calculation or figure out a formula, you start with an equal sign. 
  • Multiplication: Multiplication is straightforward in Excel. Instead of using an X as you do on a calculator, you use an asterisk (*). 
  • Percentages: There are times when it's OK to enter a percentage in Excel, like 10%, but you almost always have an easier time if you use the decimal format instead. So, 5% is 0.05, 10% is 0.1, and 100% is just 1. 
  • Addition: Whenever you want to add two or more cells in Excel, you use the SUM function. Whatever you're adding must be placed in parentheses. For example, if you're adding 10+10, you enter =SUM(10+10), and Excel displays 20 in the chosen cell. 
  • Colon: When you're adding the contents of two cells, you place a colon between the cells. This tells Excel to add everything between those two cells as well as the cells themselves. If you add 10 cells together all in the same column, from A1 to A10, you use A1:A10.