Integrating Your Accounting Client's QuickBooks & Excel
I have heard so many people say: “I switched to QuickBooks® to get away from Excel,” but I do not understand in what universe these two tools are mutually exclusive.
I agree that you should not run your bank register, invoicing, bank reconciliations and financial statements through Excel, but it does offer so many great tools that work hand-in-hard with QuickBooks – so many that it is my number 2 tool in my arsenal of consulting tools, right after QuickBooks, of course.
In a recent QB PowerHour (see below), I mentioned the top 4 reasons why every QuickBooks Consultant should consider bringing heavy-duty Excel skills to his or her practice:
- Help clients move from “X” accounting system into QuickBooks. Most competing accounting systems are NOT going to make it easy to convert their data into QuickBooks, but they all should have some sort of exporting capabilities that would give you access to lists and transactions in an Excel or CSV file. But, of course, these files are not going to be “import ready,” so you will need to apply some spreadsheet cleanup skills to prepare the data for import.
- Help clients add or update large lists. Many of my clients hire me strictly to help them get a 2,000 item pricelist from a new vendor into QuickBooks, or make cost/price updates every year to their massive inventory item list.
- Copy specific data from “bad” QuickBooks files into a new, clean QuickBooks file. Unfortunately, dealing with clients’ QuickBooks files is such as headache that we deem the files as “bad.” We get so fed up with trying to fix them that is much easier to start fresh. However, oftentimes, certain information just happens to be right, such as the printer checks with the correct dates, amounts and payee. Well, extracting a check report into excel (and then doing some cleaning) will make it easier to import into a new file.
- Downloading (and cleaning up) bank transactions into Excel. Importing into QuickBooks via Batch Enter Transactions (Video) will save you HOURS of data entry work, especially at tax time.
One thing I cannot forget to mention: generally, a project with required QuickBooks/Excel skills tends to pay more handsomely that the single-tool skilled type jobs.
QB PowerHour – April 2
In QB PowerHour on April 2, I covered some real practical examples on how to import an item list, already cleaned up in Excel, into QuickBooks using the Advanced Import tool built-in into all Windows versions of QuickBooks. I also mentioned some of my favorite productivity-increasing Excel add-ons, including KeyRocket, PDF2XL and QB2EXCEL.
Tip: Don’t miss out on Jeff Lenning’s Excel University. Jeff, a CPA.CITP, helps accountants hone down accountant-specific skills tools.