Virtually every tool on the Data tab or menu in Excel works best when your worksheets are structured in the following fashion:
- Data appears in list form, with no blank rows or blank columns.
- Each column has a title in the first row.
- Titles are only on a single row.
- There are no subtotals or headings amid the rows of data.
Prepare Your Report in QuickBooks for Analysis
To my knowledge, there aren’t any QuickBooks reports that meet these exact specifications, however, the Transaction List by Date report comes close. You’ll find this report in the
- Click the Customize (or Modify) Report button.
- In the Columns section, turn off the (Left Margin) field to eliminate an extraneous blank column.
- Scroll down further in the Columns section and replace the Debit and Credit fields with Amount.
- Remove any fields that won’t be relevant to your analysis, such as the Adj, Clr, and Split fields.
- Change any other settings, such as date ranges, if desired, and then click OK.
Figure 1: Customize the fields in your report.
After a few more one-time steps, shown in Figure 2, we’ll be ready to send the report to Excel:
- Press Ctrl-M or click the Memorize button to memorize the report, modify the name if desired, and then click OK.
- Choose View, and then Add “Transaction List by Date” to Icon Bar (the wording may vary based on the name of your report).
- If your icon bar is positioned across the top of your screen, shorten the label to TransRpt, or keep the longer label if your icon bar is on the left-hand side of the screen.
- Click OK to add the report to the icon bar.
- Icons that you add in this fashion generally won’t appear on screen, so next choose View, and then Customize Icon Bar. Drag the diamond icon adjacent to your memorized report into a position where you can access it with one click and then click OK.
Figure 2: Memorize the report and add it to the icon bar
At this point, you have an almost-analysis-ready report you can summon on screen with a single mouse click. The report is ready to export to Excel, after we make one more round of adjustments within QuickBooks, as shown in Figure 3:
- On the Report screen, choose Excel and then Create New Worksheet (or click Export in QuickBooks 2011 and earlier).
- Click the Advanced button or tab.
- Uncheck the Space between columns box, which, from an analysis standpoint, inserts extraneous blank columns that make Excel harder to use.
- Uncheck the Freeze panes box, which QuickBooks implements in a belt-and-suspenders approach. Not only does QuickBooks use the Freeze panes feature, it also uses Excel’s Split feature to keep the top row always visible, which adds extra work to disengage the frozen rows should you need to do so. I’ll show you two different ways to freeze the first row off your reports in Excel with a keyboard shortcut.
- Check the Auto Filtering box, which enables filtering arrows at the top of your reports in Excel.
- Uncheck the Include QuickBooks Export Guide box, as this adds an extraneous worksheet to your Excel-based reports.
- Under Printing options know that choosing to have report titles appear both on printed reports and on screen can make analysis more difficult. If you work with data from multiple companies, then keeping the titles on screen may be a necessary evil.
- Click OK.
Return to the Send Report to Excel window and then click OK to send your report to Excel. Keep in mind that the options that you changed above are global and affect all reports. However, these options are easy to finesse in the future should you need to do so.
Figure 3: Send your report to Excel.
Prepare Your Report in Excel for Analysis
If you opted to send report titles to Excel, you’ll need to insert a blank row between the column headings and the report titles. To do so with a keyboard shortcut, select row 4 of the worksheet, and then press Ctrl-+. Alternatively, you can right-click on row 4 and choose Insert, or from the Home tab choose Insert, and then Insert Sheet Rows in Excel 2007 and later. It’s important in Excel to separate titles, such as rows 1 through 3 in Figure 4, from the data we’re going to analyze, which now starts in row 5. We also need to delete row 6, because it’s important to avoid having blank rows amid our data. At this point, your report should look like either format in Figure 5.
Figure 4: Insert a blank row between the column headings and report titles.
Figure 5: Your report will look like either format, depending on if you decided to send the report titles to Excel.
This next step, as shown in Figure 6, will turbocharge your analytical abilities. Click any cell within the body of your report and then:
- Excel 2007 and later: Press Ctrl-T, or choose Insert and then Table.
- Excel 2003 and earlier: Press Ctrl-L, or choose Data, List, and Create List. Incidentally, Ctrl-L initiates the Table feature in Excel 2007 and later, but Ctrl-T doesn’t have an effect in Excel 2003 and earlier.
Click OK when the dialog box appears on screen. Depending upon your Excel version, some or all of the following will occur:
- All versions of Excel: The Filter arrows appear at the top of your report. In Excel 2010 and later, these arrows stay on screen when you scroll down past the first page of your report.
- Excel 2007 and later: There’s no need to use the Freeze panes or Split feature because the Table feature automatically moves your column headings into the worksheet frame once the first row of the report no longer appears on screen.
- Excel 2007 and later: Automatic shading improves readability of your report.
- Excel 2007 and later: A Design tab appears within the ribbon menu. This tab vanishes when you select a cell outside the boundaries of the table, but reappears when you click inside the table again.
- Excel 2003 and earlier: A List toolbar should appear on screen. If it doesn’t, choose View, Toolbars, and then List.
Figure 6: The Table feature offers many benefits for analyzing your report.
The Table feature (known as the List feature in Excel 2003) offers many other benefits, some of which I’ll explore in future articles. For now, I’ll leave you with one last technique, which is to add a Total Row:
- Excel 2007 and later: Check the Total Row box on the Design tab.
- Excel 2003 and earlier: Click Toggle Total Row on the List toolbar.
Either action will add a Total Row, as shown in Figure 7, to the bottom of your report. By default, the last column of the report will automatically gain a SUBTOTAL function that sums the data if it contains numeric values or a COUNT formula if it contains text. Click any cell in this Total Row to display a drop-down list from which you can turn a Total off; choose another analytical function, such as AVERAGE; or add another formula. The benefit of this Total Row is that as you filter the list, let’s say for Liability Checks, the Total Row tallies only the visible rows.
Figure 7: Add a Total Row to the table.