QuickBooks QuickBooks Tips/Tricks

Importing Sales Tax Rates into QuickBooks

Written by Charlie Russell

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.

Save pagePDF pageEmail pagePrint page

About the author

Charlie Russell

Charlie Russell has 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 an 8-bit microcomputer with one 8 inch floppy disk drive. 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, as well as being a Xero Certified Partner. Charlie started blogging about QuickBooks in 2008 (Practical QuickBooks) and has been the managing editor and primary writer for the Sleeter Report since 2011. Charlie can be reached at charlie.russell@sleeter.com

Visit his CCRSoftware web site for information about his QuickBooks add-on products. He is also the author of the California Wildflower Hikes blog.


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

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

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

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

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

  • Our company processes and pays California sales tax via the online eFile website.

    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.

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

  • 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…


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

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

  • 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?

  • A quick and easy way to import your sales tax codes into QuickBooks using IIF is to:
    1. Export an IIS file from Quickbooks so you already have the format for import (Files/Utilities/Export/List to IIS files)
    2. Open in excel (select all files or you won’t see it as it is an iis file)
    3. The Text Import Wizard will show up defaulted to Delimited so just hit Next/Next/Finish and the file will open
    4. Locate one of your sales tax items. If none have been entered there is a default code already in Quickbooks called “Tax” and “Non” that are denoted as COMPTAX in the INVITEMTYPE column
    5. Delete all other rows except the header and those rows. Note you are just leaving them in for now as a guide on what data to enter into the file. The header must stay. DO NOT DELETE ANY COLUMNS. It is ok to leave them blank.
    6. I then went to my excel file where I had all of my sales tax codes entered and made sure I spaced out my columns (added and moved where necessary) so that when I cut an paste the data, it would be in the correct column in the exported spreadsheet. Note the only data I in my sales tax code excel file was Name (the abbreviation for the tax code), Description (the full description), Account (Sales Tax Payable), Price (the % formatted as a % with two decimals) and Tax Vendor (vendor name in quickbooks who I will be submitting to)
    7. Once all the column spacing was correct, I cut and past into the opened IIF file.
    8. I then copied the other rows that needed to have data in them all the way down so that every row had the required data. The ones I copied down that didn’t have data after pasting my info were INVITEM (it must stay exactly as INVITEM), INVITEMTYPE (must be COMPTAX), COST (0), TAXABLE (N), DEP_TYPE (0), SALESEXPENSE (0), COSTBASIS (0), ACCUMDEPR (0), UNRECBASIS (0)
    9. I double checked my data to make sure all cells that should be filled were filled and then saved the file. Excel will as you if you want to save as the same file type, which you do. I just saved using the same filename by clicking Save just to be safe.
    10. Exit Excel (it will ask you to save again)
    11. In QuickBooks go File/Utilities/Import/IIF Files
    12. Locate your file and click Open
    13. Done

    Note this took me about 15 minutes total for 368 items, so it was a huge time saver. Also, I tested just one code my first time to ensure the import was successful. After my test I then imported everything.

    • Chris, as I pointed out, IIF can be used, but I’ll still state that I highly recommend not using it on your own, for most people. IIF has minimal error checking, you can easily make mistakes and import errors. IIF has great potential for corrupting a database as well, if an error is made.

      In any case, if you follow your procedure, you left out one important step that you MUST do with ANY bulk import – before importing, make a backup of your database. Then if something goes wrong (and you detect it right away) you can restore it. There is no bulk way to undo an import, by any method you use.

Leave a Comment