Simple Tax Invoice Sample with Tax Rate List

This simple tax invoice template focuses on the steps of creating the tax rate drop-down list.

This template is based on Simple Invoice Template: Discount Amount Field (c4051).

Let's see how the tax rate drop-down list is created.

1. Backup the template.
2. Open the template.
3. Click the "Settings" button on the worksheet.
4. Go to the "Taxes" tab.
5. Click to select "One Tax".
6. Change the tax name to "TAX". Change the tax rate to the value that you use most often.
7. Click "Apply".
8. Close the "Settings" dialog box.
9. Click the "Design Mode" button on the ribbon tab.
10. Choose an empty location to place the tax rate list. In this sample, we use columns $S and $T.
11. Set a slightly different background color for these two columns.
12. Enter the state names in the first column. Enter the corresponding tax rates in the second column.

    For the purpose of this demonstration, we copied the tax rates from State Sales Tax Rates.

13. Move the tax name "TAX" one column left to leave a blank cell for the state name. In this sample, we move the text from $I$35 to $H$35.
14. Click to select the cell left by moving out "TAX", i.e., $I$35. Name this cell "oknTaxStateName".
15. Switch to the Excel ribbon "Data". Choose "Data Validation" and then "Data Validation...".
16. On the "Data Validation" dialog box, choose "List" from the "Allow" list. Fill the address of the state name list into the "Source" box. In this case, we set it to "=$S$6:$S$56".
17. Make sure the tax rate cell ($J$35) is selected. Assign it a formula like this:

    =VLOOKUP(oknTaxStateName,S6:T56,2,FALSE)
    

    This "VLOOKUP" formula searches for the state name in the cell named "oknTaxStateName" in the first column of our tax rate table and returns the rate in the second column.

18. Exit design mode.
19. Click the "Invoices" button on the ribbon tab.
20. Click "Custom Fields".
21. Click "Add Field".
22. Fill in the new field information:
    • Field Name: TaxStateName
    • Type: Text
    • Default Value: Alabama

      This is the name of the state that you sell to most frequently. When you click the "Clear & New" command, the cell "oknTaxStateName" will be filled with this default value.

    • Size: 50
23. Close the "Add New Field" dialog box.
24. Close the "Custom Field Manager" dialog box.
25. Save the template.

This solution does not save the tax rate table to the database. This means that, if you:

1. Save an invoice that sells products to a state, say Iowa.
2. Several months later, you update the tax rate for Iowa.
3. You reload the document.
4. You save the document again.

In this situation, the invoice that is recalculated with the new rate table will differ from the original one. To overcome this, you need to save the entire rate table using custom fields.

Related templates:

• Mixed Tax Rates (c5034)
• Tax Column (c5001)