How to calculate sales tax in excel
Sales taxes are taxes added to the purchase of goods and services. In the United States, each individual state can determine the tax rate to be charged for goods and services. Sales taxes can differ depending on locality within a state, such as Wisconsin, which includes an additional county tax.
Taxes can also depend on the type of good or service, such as Tennessee, which charges more tax on non-grocery items than on grocery items. With all of the variations on taxes, it is convenient to set up a spreadsheet in Excel to automatically calculate tax due on purchases.
Locate sales tax information for your state and locale for the specific good or service that you provide. Each state has its sales tax information available online. The web address varies by state, but a simple search for "sales tax" and your state's name will locate the most current tax rates.
Open the Excel program on your computer. Starting with Column A, row 1, label column headings: Item, Subtotal, Sales Tax, Sales Tax Calculation and Total.
Set the formatting for the Subtotal, Sales Tax Calculation and Total columns to accounting. Select the entire column, by clicking on the column heading (B, D and E, individually). Right-click on the column heading. Select "Format Cells" from the drop-down menu. In the pop-up window, select the "Number" tab. In the "Category" listing, select "Currency." Select "OK." Set the formatting for the Sales Tax column to percentage. Select the entire column, by clicking on the column heading (C). Right-click on the column heading. Select "Format Cells" from the drop-down menu. In the pop-up window, select the "Number" tab. In the "Category" listing, select "Percentage." Select "OK."
On the second row of the spreadsheet in the Item column, enter a description of the good or service purchased. In the Subtotal column, you will enter the cost of the goods or services that have been purchased. In the Sales Tax column enter the sales tax rate that you researched in step 1. In the Sales Tax Calculation column enter the following formula: =B2*C2. In the Total column enter the following formula: =B2+D2.
If you are adding the calculation of sales tax to an existing Excel based invoice, ensure that the Sales Tax Calculation formula references the subtotal, or retail price, of the goods or services purchased and the sales tax rate. The Total formula should reference the subtotal, or retail price, of the goods or services purchased and the Sales Tax Calculation.
Sales tax rates may change over time and rates should be verified on an annual basis.