To ensure that the customers get the right price for the products, it is important to calculate the sales tax. While the US doesn’t have a ruling for the maximum retail price, sales tax is applied according to the regulations of the state. In this article, we will learn how to calculate sales tax in Excel. Even though Excel does not have a dedicated function to calculate the sales tax, the formula is very straightforward, and you will be able to get a grasp of it in no time.
➤ Use the following formula to calculate the sales tax:
=D2*4%
➤ Replace D2 with the price excluding sales tax and 4% with the sales tax rate, then autofill the formula for the whole column of the sales table.
Calculating sales tax is not hard, but it is mandatory to comply with the regulations. In this article, we will learn how to calculate the sales tax, the total price including sales tax, extract the original price from the price including sales tax, and find the tax amount from the total price.
Calculating Sales Tax in Excel
We have a table with some transaction IDs, customer names, the items they bought, and the prices they paid for those items. We will calculate the sales tax from those prices, and the total price that includes the sales tax.
➤ Create two more columns for calculating the sales tax and the total price.
➤ Considering that the tax rate is 4%, insert the following formula in the E2 cell, and autofill the column using the autofill handler:
=D2*4%
➤ Calculate the total price in the F2 cell with the following formula, and autofill till F13:
=D2+E2
Determining the Reverse Sales Tax in Excel
This time, we have modified the table to have it contain the total price instead of the pre-tax price. With the total price, we will calculate the price excluding tax, then the sales tax.
➤ Add two columns for Price Excluding Tax and Sales Tax.
➤ Insert the following formula in the E2 cell to calculate the price excluding tax, then autofill the rows till E13:
=D2/(1+4%)
➤ Now, determine the sales tax in the F2 using the formula below, then autofill the column:
=D2-E2
Using VBA Code to Calculate Sales Tax
We can make use of some VBA code to calculate the sales tax. In this case, after running the code, Excel will ask for some inputs. Once we fill those up, the calculation will be done automatically.
➤ We are once again using the table from the first method, which contains the price without taxes.
➤ Add a column for the sales tax.
➤ Press Alt + F11 to open Microsoft Visual Basic for Applications.
➤ Go to Insert > Module to open the code editor.
➤ Insert the following code in the code editor:
Sub CalculateSalesTax()
Dim taxRate As Double
Dim priceRange As Range
Dim cell As Range
Dim resultColLetter As String
Dim resultColNumber As Long
Dim xTitleId As String
xTitleId = "ExcelInsider"
taxRate = Application.InputBox( _
Prompt:="Input the sales tax as a decimal (e.g., 0.04 for 4%)", _
Title:=xTitleId, _
Default:=0.04, _
Type:=1)
Set priceRange = Application.InputBox( _
Prompt:="Choose the range of pre-tax prices", _
Title:=xTitleId, _
Type:=8)
If priceRange Is Nothing Then Exit Sub
resultColLetter = Application.InputBox( _
Prompt:="Specify the column letter where results should go (e.g., E)", _
Title:=xTitleId, _
Default:="E", _
Type:=2)
On Error GoTo InvalidColumn
resultColNumber = Range(resultColLetter & "1").Column
On Error GoTo 0
For Each cell In priceRange
cell.Worksheet.Cells(cell.Row, resultColNumber).Value = cell.Value * taxRate
Next cell
Exit Sub
InvalidColumn:
MsgBox "Invalid column letter entered.", vbExclamation, xTitleId
End Sub
➤ Now press F5 to run the code, then go to the Excel sheet.
➤ In the first prompt, insert the sales tax percentage; 0.04 is the default, as the rate we are using here is 4%. Press OK to proceed.
➤ Next, the range of prices will be asked. You can use your mouse to select the $D$2:$D$13 range or write it manually:
➤ Now write the column letter where the results should show up; we are using E here:
➤ Upon pressing OK, the sales tax will show up in the E column.
Frequently Asked Questions
How to calculate tax rate in Excel?
The tax rate is variable in the US. Not every state has the same tax rate. You need to learn the tax rate from the local laws to ensure the proper tax rate. However, if you have the tax amount in dollars and the original price, you can calculate the rate using the following formula:
=E2/D2
Here, E2 is the sales tax, and D2 is the original price.
What is the sales formula?
Assuming you want to calculate the sales revenue, you can use the following formula in Excel:
=A2*B2
Here, A2 is the number of units and B2 is the price of each of those units.
How to calculate tax on salary in Excel?
Calculating tax on salary is a bit more complex than applying the tax rate. The tax rate is different at different income levels, and you have to separate the income for different tax slabs. For example, if your gross income is $1000, you might have one rate for the first $500, another rate for the next $300, and another rate for the remaining $200. You need to calculate all of the taxes separately to file your income tax.
What is a tax refund?
While calculating tax, if you make a mistake and pay more than you were supposed to, the state will provide a refund. However, you have to claim a tax refund within 3 years to get the refund. If you don’t have taxable income, you might still get a refundable credit. Either way, filing a tax return is mandatory.
What is a tax code?
The US tax code is used in the legal system to define the federal tax law. It is part of Title 26 in the US Code. The US tax code includes the tax rates of income, payroll, estate/gift taxes, excise taxes, etc.
Wrapping Up
In this article, we have learned how to calculate sales tax both from the price that includes the tax and does not. If you are a consumer, you can use these formulas to know whether the seller is charging the right tax amount to you or not. If you are a seller, you can use the methods to set the prices of your products. Download the Excel file provided with this article to understand the formulas better. See you in another article soon.
