Marrying the abilities of Excel and the data of QuickBooks can sometimes solve problems QuickBooks alone cannot.
One example is when QuickBooks does not possess the capability to create certain reports, like including a calculated column or a customized chart.
Another example is when the information must be shared with someone else, and they need it in an Excel format.
Let’s start with the simplest. Data needs to be shared in a specific format with an outside party.
Positive Pay is a feature many banks offer to cut back on check fraud. Using this service, a company would create and print checks on a given day. Then they would create a report the bank could read, usually by machine, detailing the information on those checks. The bank then has a record of what checks to accept on this company’s account.
Having been through this setup with one company, this is the format. The bank wanted type, date, number, and amount details for each check written in each batch.
While this report can be created in QuickBooks, the bank needed it in Excel format.
Start with a Transaction Detail by Account report. On the Display tab of the Customize Report window, it is possible to select the columns wanted.
Using a dynamic date like “Today”, will allow us to memorize this report and use it over and over without re-setting date parameters.
On the Filters tab, we only want one account, the checking account. And we only want certain transactions. All kinds of checks. So in the Transaction Type filter, choose “Multiple Transaction Types”. Then choose from the list, all kinds of checks QuickBooks could create. This could vary from one company file to another but would include check, bill payment, sales tax payment, paycheck, etc.
The resulting report, once exported to Excel, appears below.
This isn’t good enough. The column with the account name “Checking” is a problem. So are the blank columns.
Back in QuickBooks, Customize Report, Display tab, reset the “Total by” setting. Instead of Account List, change it to Total Only. Change the Sort by to Num.
There are additional settings that can be specified for the export to Excel. Clicking on the Excel button yields the following screen.
Since this will be our first Excel report in the new format, pick the new workbook setting. One of our corrections can be implemented by clicking the Advanced button.
Uncheck the setting the reads “Space between columns”.
Now our exported report looks like this.
Notice the circled date column. That’s still a problem and there is no way to correct this in QuickBooks. However, QuickBooks works with Excel closer than ever. We can fix this.
In Excel, select column A by clicking on the column header.
Right-click in the now highlighted column A. From the menu that pops up, choose Hide.
Save the Excel worksheet. The next time this report needs to be run, create the memorized QuickBooks report.
Export the report to Excel, making this one change in the way we did this earlier.
Choose Update Existing Worksheet rather than Create new Worksheet.
Choose the above settings. These settings will cause QuickBooks to fill-in the previously formatted spreadsheet with new data. Do not choose the option Replace an existing worksheet as this setting overwrites the formatting as well.
The resulting Excel worksheet will retain the same Excel formatting as before. That is, the date column does not show. That column is hidden.
Many formatting possibilities exist with a memorized report in QuickBooks and a memorized (saved) workbook in Excel. The combination allows creation of very specific reports. The data can be updated repeatedly without having to re-format.
Hector Garcia, CPA
Certified Advanced QuickBooks ProAdvisor
12401 Orange Drive #136
Davie, FL 33330
954-414-1524
hector@garciacpa.com
