Dealing with sales tax rates in QuickBooks Desktop can be a pain, particularly if you are working with customers in many different locations. It used to be that we just had to worry about our local tax rate, when selling items to customer who walked in the door. Now, most businesses are finding that they have to ship to multiple locations, and each location has a different tax rate. It can be confusing, and it isn’t going to get better soon! And there can be THOUSANDS of tax rates that you have to enter, which can be TREMENDOUSLY tedious. So, how can you keep up to date on sales tax rates in QuickBooks? Let’s take a look at a way to get updated sales tax rates for free and import them into QuickBooks.
Note: If you aren’t familiar with how QuickBooks handles sales tax, please read Setting Up Sales Tax in QuickBooks before proceeding with this article.
Getting the Sales Tax Rates
I often see pleas in various user forums for a “list of tax rates” that you can import into QuickBooks. Where can you get this information? My friend Jim Savage was able to answer that for me – you can download tax rates as a CSV file from http://www.taxrates.com/. Click on “sales tax resources”, select the states that you want to work with (or “all”), enter your contact information and you’ll receive an email with a link to a CSV formatted file.
It’s simple! It’s fast! Best of all, it’s free!
Here’s an example of what the file for California looks like, in Excel.
Now, How to Get That Into QuickBooks?
Getting an up to date list of tax rates is just the first step. Now, how do we get this into QuickBooks?
To add a sales tax item to the QuickBooks item list you need five items of information:
- An item type, usually sales tax item (although in some imports the specific “type” may be different).
- A sales tax name that is unique to the item. This name has to be 31 characters or less in length. This can be one of the hardest parts if you have a long list of sales tax items – coming up with unique names. If you look at the CSV file I show above, there isn’t any single column that is unique to each line. So, in my example, I’ll usually create a new column that is a combination of the state, zip code, tax region name and tax region code. That usually works out to be a unique ID for each line, although it may be a bit cryptic. It is very important that you be consistent so that you can recreate the same name on subsequent imports if you are going to update rates at a later time.
- A sales tax description – this actually isn’t required, but it is helpful. In my examples I’ll usually just duplicate the sales tax name, but you can create any description that you find helpful.
- A sales tax rate. For these imports we will always be importing sales tax items, not sales tax groups, so you need the ‘”combined rate”.
- The tax agency, which is the vendor you are using in sales tax reporting. You won’t find that in the CSV file – you can add it yourself.
Unfortunately, QuickBooks doesn’t directly import a CSV file. You can’t use the Excel imports, as they don’t support all of the fields that are needed. You can’t use the Add/Edit Multiple List Entries feature, as that doesn’t work for sales tax items. What can you do?
IIF Imports On Your Own
QuickBooks has a formatted import feature called IIF that can be used to import list items (including sales tax items). It isn’t my favorite approach for a number of reasons (see this article on IIF) but it is a feature that is built into QuickBooks and it is free. I don’t recommend using it because it is complicated, and there is almost no error checking. You CAN corrupt your QuickBooks database if you aren’t careful.
Before using this, make a backup copy of your QuickBooks file!
Here’s an example of an IIF import file formatted to import sales tax items. I’m not going to explain the requirements in detail – see the IIF article for resources on the structure. It is complicated!
Once you have modified your CSV file to fit the IIF format, in QuickBooks select File, Utilities, Import, and then IIF Files and “open” the file. This will import the records. REMEMBER TO BACK UP YOUR FILE FIRST, however, because imports cannot be un-done.
Here’s an example of a record that I imported with this method.
IIF The Easier Way
IIF is confusing, picky, and a pain to deal with. If you really want to use this method I highly recommend that you purchase the List Importer from Big Red Consulting, which costs just $49. This is a plugin that works with Excel. It will take your CSV file and convert it to the IIF format for you. You do, however, have to do some manipulation of the CSV file to get it into the proper format.
In my version of Excel I find the List Importer in the add-ins tab. There is an option to show examples – this is important because you are going to have to manipulate the CSV file into the required format. Once you do, click the Export menu option and the program creates a properly formatted IIF file.
I have mixed feelings about this. On one hand, I really don’t trust IIF imports. On the other hand, I really like the tools from Big Red Consulting. The tool is very reasonably priced, it does a perfect job of creating an IIF file. My concern isn’t about Big Red Consulting, my concern is with the IIF format itself.
Importing the Best Way
By far, my favorite method of importing information from Excel files is to use the Transaction Pro Importer from Baystate Consulting (see my review of this product). This tool doesn’t use IIF, it uses the QuickBooks SDK, a MUCH more reliable method of importing data into QuickBooks. In addition, it doesn’t require as much manipulation of the CSV file up front. The product is a bit more expensive, but for $199.95 you save a lot of work (and you can use this tool for many other types of imports).
The only reformatting that was necessary was to get a unique name. Here’s my CSV file, with a “name” column added.
Please note that you must use version 5.09 (or later) of the Transaction Pro Importer – earlier versions don’t support importing of sales tax items.
As before, I always recommend that you make a backup of your QuickBooks file before proceeding. Imports cannot be “backed out” if you find that there is some problem, or if you decide you want to change how the naming was done.
You start by selecting the file, specifying that it is a comma-delimited (CSV) file, and selecting the item sales tax import type.
Next, you create a “map”. This is a lot easier than making major changes to the import file. This map can be used for subsequent imports. In this case I specify the “Franchise Tax Board” to be the tax agency, rather than having to enter that value in the CSV file (in my example, all tax items are for the same state).
After the map is created, the program tests the data file. In this case I had several items where the name was longer than what QuickBooks allows. You can edit these values right here, or you can edit your CSV file and start over.
That is all there is! Here is a sales tax item added to my QuickBooks item list.
This is far easier than doing complicated manipulations to get a properly formatted IIF file.
Other Ways of Dealing With This Problem
Importing the rates into QuickBooks only addresses a part of your problem, however. How do you decide WHICH rate to apply to a particular customer? You can’t rely just on zip code, as tax districts will not match that. It can be a major problem.
One way to resolve this is to use AvaTax Calc, one of a family of sales tax management programs from from Avalara. This product integrates with QuickBooks and will determine, by the address (NOT just the zip code), what the proper sales tax rate should be, and handles all the tax rate updates for you. See my review of AvatTax Calc for more information.