Print This Post

Importing Sales Tax Rates into QuickBooks

December 13, 2012 | By | 20 Replies More

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.

Sales Tax Rates from taxrates.com

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.

Sample Sales Tax CSV file

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!

Sample IIF sales tax import file

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.

Sales Tax Item in QuickBooks

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.

Sales Tax Import with Big Red Consulting

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.

Sales Tax Import file for Transaction Pro Importer

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.

Transaction Pro Importer, first step

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).

Sales tax item mapping

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.

Highlighted sales tax names are too long

That is all there is! Here is a sales tax item added to my QuickBooks item list.

Sales Tax Item

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.

Tags: , ,

Category: QuickBooks Tips/Tricks, Working with QuickBooks

About the Author ()

Charlie Russell is the founder of CCRSoftware. He's been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had a one-floppy disk drive system, loading the program from one floppy and then replacing that with the other floppy to hold the data. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise. He also is a Xero Certified Partner. Visit his CCRSoftware web site for information about his QuickBooks add-on products. Charlie can be reached at [email protected] He is also the author of the California Wildflower Hikes blog Connect with Charlie at Google

Comments (20)

Trackback URL | Comments RSS Feed

  1. Charlie,

    This is, as usual, a great and very useful article, from several perspectives. The place to get tax rates as .csv file ALONE made article worth reading.

  2. Jim Ernest says:

    I must have missed something, the rate item you show imported is a state overall rate 8.75%. that 8.75% is composed of 4 rates (state, county, city, special)

    in QB to track those 4 rates, it takes 4 sales tax items AND a sales tax group item which contains those 4. {in this case 3 actually since city does not have a rate for the ones displayed}

    That is the whole point of a sales tax group, to track the components of the overall state rate.

    I can not see where you accomplish anything other than filling the item with duplicate rates – they are all the same, the total rate.

    • In some states you don’t have the duplicates. This is a base starting point for looking at how to get the rates (which many people are asking for), in any individual situation you will most likely find that you want to do some additional processing before you do the import.

      For complicated situations (where the volume is high) I prefer to avoid all this and work with Avalara, which I’ll be writing about next week.

  3. Charlie,

    Great article on how to use Bay State and Transaction Pro Importer to bring data into QB.

    So this is great information and people will want to use these concepts for importing data into QB in many different situations.

    I usually recommend that the sales tax item list is much shorter though, with each sales tax ITEM being the name of a county. I wrote an in-depth article with sample a import file several years back, and will post that soon. They key is to give us a useful report on which to prepare the payroll tax returns. With each zip code being a separate tax item, it would be more detail than I’d want to deal with.

    Your example is for California, so we do not recommend using Sales Tax Groups as Jim discusses above. The reasons for this are a) simplicity, b) all tax in California is paid to the same tax agency (State Board of Equalization), and c) less file bloating because each sale only will hit sales tax payable with one total as opposed to several times for each item in the sales tax group.

    • I agree, Doug, and I should have probably gone into some more detail, but the details can be difficult because they may be different for different states, and user situations.

      You are correct that in many cases it is better to have just “sales tax items” and not use “sales tax groups”. Note that you cannot import “sales tax groups” in any case, so if you were trying to use groups you would have to import the individual items and then manual create the groups in QuickBooks (although that might be a one-time thing).

      If you believe in working with sales tax items, you would use the “combined” rate.

      If you want to use sales tax groups, you won’t use this particular file, probably, unless you do a lot of work to split out the state, county and “special” rates. And then you have to use IIF files to import.

      I’ll be writing about Avalara AvaTax next week, which (if the price structure fits your situation) is a much cleaner, simpler approach to dealing with all of this.

    • Jim Ernest says:

      Doug in CA when you file state sales taxes, do you have to call out the taxable sales per taxing jurisdiction? In Texas we do, so sales tax items used in a group sales tax item provide that info on the sales tax payable report.

      • Doug Sleeter says:

        Jim,
        Yes, in CA we have to report per “taxing jurisdiction,” but only at the county level. So we recommend using one sales tax item per county and tracking each sale to that county. The rate is the “combined rate” of the state, county, district, etc. – We can do this because all taxes are collected by the same tax agency. If we had multiple collecting agencies, we’d have to use groups… which would be a HUGE pain.

  4. Jim Savage says:

    Great article Charlie! Where the taxrates.com data would be helpful is if someone could write a program to download those csv files and keep them updated, you could have a neat little tool to look up tax rates that you could use as a plug in to your web store. Of course, you could always just subscribe to Avalara…

    I want to take a look at what shopping carts are using Avalara, so maybe I can do an article about that to compliment your next one about Avalara.

  5. Cliff says:

    Our company processes and pays California sales tax via the online eFile website.
    https://efile.boe.ca.gov/boe/express_login_code.jsp

    eFile greatly simplifies sales tax by not requiring the use of QB groups. eFile only requires reporting the customer’s county. eFile calculates the state, district, local, county, etc rates and amounts.
    This makes it much easier to manage sales tax in QB because we only need one sales tax rate for each customer. There is no need to setup groups.

    If we filed a paper sales tax return we would have to use the QB groups. Paper sales tax returns require manually entering all the district, local, and other tax rates, and the amount.

    Anyone using sales tax groups, and filing paper tax returns, should look into filing electronically in their state. This should greatly simplify QB sales tax Items/groups, and speed up sales tax return filings.

  6. David Jennings says:

    Washington State creates IFF files of sales tax rates for direct import into QuickBooks. They update them quarterly for any tax rate changes. You just log on to the state website, download the file and import – couldn’t be easier.

    There may be other states that do that as well.

    We use Transaction Pro Importer for other imports, and, once you get the field mapping correct, it is easy and quick to use.

  7. Sandy Bever says:

    Thanks Charlie, once again for a great article. I have used Transaction Pro Importer for numberous issues such as inventory adjustments and transactions. I also wanted to point out some states are now offering their own sales tax importing tool for QuickBooks. Here is one for the state of Washington. I have used this numerous times and is very easy to use…

    http://dor.wa.gov/content/findtaxesandrates/retailsalestax/destinationbased/quickbooks.aspx

    So I might note it might be worth looking at the states websites first.

  8. Sandra Campbell says:

    Does this article apply to QBs 2013 as well?

  9. Mark Watson says:

    Hi Charlie,

    Thanks for this great information! In Georgia we file to one tax authority and the rates only vary by county (with an exception for the city of Atlanta). The problem we face is determining which county tax rate applies, especially since we ship to multiple locations for several customers and the tax rate depends on the ship to location, not the customers location.

    I’d like to be able to determine the tax rate from the ship to zip code. (That works for Georgia, as I don’t believe zip codes cross county lines).
    If I set up an example as above I don’t know how QB would be able to consolidate the various zip codes for each county into county total each month – am I missing something?

    The only work around I could think of was to set up a sales tax group for each zip code, with the only entry being the county sales tax rate. That’s a huge amount of sales tax groups but once set up it wouldn’t need much updating.

    Any suggestions?

    • Mark, I didn’t look at the file that downloads for Georgia – but if you look at the California example you see that they have a “combined rate” for each zip code / jurisdiction. That sounds like it would work for your situation. If that doesn’t work, you might want to consider the AvaTax Calc product I refer to in the linked article.

  10. Kathy McGill says:

    Thanks for the article. How do you suggest handling items for Caiifornia? I have them by county and then add a date on the end because they change fairly often. We don’t want to overwrite previous items and change the sales tax history. Of course this is getting sort of crazy since all of CA will change. We can make old items inactive. Any other suggestions?

Leave a Reply