1 / 13

QuickBooks and Excel: Moving Information In and Out

Join QuickBooks expert Scott Gregory as he explores different methods and tools for transferring data between QuickBooks and Excel. Learn about file formats, best practices, internal tools, and third-party options. Don't forget to make backups and verify your data!

ronaldh
Download Presentation

QuickBooks and Excel: Moving Information In and Out

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. QuickBooks and Excel: Moving Information In and Out Presented by: Scott Gregory, QuickBooks Expert Bottom Line Accounting Solutions

  2. QuickBooks and Excel • Setting the right expectations • We’ll be reviewing the flow of information between QuickBooks and Excel (and discuss other file formats/options) at a high level today • No way to cover all variations/needs • Be absolutely certain to make a good backup copy of your QB file before doing ANY data import • Best practice – work with a COPY of your data file for bigger projects/ongoing data transfers

  3. QuickBooks and Excel • Understanding the lingo • IIF File – Intuit Interchange Format: “old school” way of moving data into and out of QB. Very structured (and confusing) formatting; prone to errors • CSV File – Comma Separated Value: another format available to move data; data fields separated by commas • Tab Delimited File – Similar to CSV files; data fields separated by tabs instead of commas

  4. QuickBooks and Excel • Other data transfer options in and out of QuickBooks… • ODBC – Open DataBase Connector: moves data in/out of Excel; Access, SQL, Crystal Reports, etc. • WebConnector – a tool developed to help third party programs (i.e. shopping carts, etc.) connect and share data with QuickBooks. • Comes preinstalled with QuickBooks desktop • Each vendor creates their own “connector”

  5. QuickBooks and Excel • Common Situations Where Data is Moved • Startup of new QuickBooks data file • Move from old QuickBooks data file to new one (i.e. too big, etc.) • Extracting data for reports and/or dashboards • Connecting with third-party software packages to import/export transactions(there are thousands of them) • Analogy: “How to Get Downtown” • “Trust, but verify….” (after all, it’s YOUR QuickBooks)

  6. QuickBooks and Excel • Internal Tools within QuickBooks • Importing or Exporting from Excel into QuickBooks – there are some tools within QuickBooks to allow for this. In addition, there are several third-party programs that specialize in the transfer of data between Excel and QuickBooks (more on these later…)

  7. QuickBooks and Excel • Using the internal QuickBooks tools • File > Utilities > Import > IIF Files • File > Utilities > Export > IIF Files • Great to use these tools if you are starting a new QB data file • File > Utilities > Import > Excel Files • “Wizard” method • “Mapping” method • “Add/Edit Multiple Items” method

  8. QuickBooks and Excel • Using the internal QuickBooks tools • The Excel “button” at the top of the Customer, Vendor, Employee and Inventory Centers • Export list • Export transactions • Import from Excel (same as wizard option) • Paste from Excel (same as Add/Edit option) • Differences in Customer/Job vs. Transactions tab

  9. QuickBooks and Excel • Lists vs. Transactions • Notice so far, most of the information has been focused on moving lists (name, accounts, items, etc.) • What about transactions? • Can’t import transactions directly in QB • Download the IIF kit from the Intuit support site for samples and details • Get to know a third party tool (ODBC, WebConnector, TPI, etc.)

  10. QuickBooks and Excel • Third Party Tools • Baystate Consulting – www.baystateconsulting.com • Transaction Pro Importer and Transaction Pro Exporter for data exchange with Excel • Karl Irvin – www.q2q.us • All sorts of utilities here to move data around • Big Red Consulting – www.bigredconsulting.com • Specialists in IIF files and other tools

  11. QuickBooks and Excel • QuickBooks Reports to Excel • Create new worksheet vs. update existing worksheet • Any report can be exported to Excel • Update feature - new in 2011? • How it works? – can do from QB to Excel or within Excel also • Be sure to test and understand limitations • Walkthrough of simple example

  12. QuickBooks and Excel • Wrap-Up of Key Topics • Many different options available for moving data into and out of QuickBooks/Excel • Can’t stress enough: • Make a backup of your QB data before doing any import • Test with a copy of your QB data, especially when connecting to other software! • Trust, but verify!

  13. QuickBooks and Excel • FINAL Q+A FOR TODAY + GIVEAWAY! Your Presenter: Scott Gregory, QuickBooks Expert www.BetterBottomLine.com Your Host: Shannon Tucker, Accounting Users, Inc. QuickbooksUsers.com See quickbooksusers.eventbrite.com for more online seminar details!

More Related