Periodically you may find yourself needing to carry out ad hoc analysis of QuickBooks data in Excel. As I’ve discussed previously, pivot tables are an ideal way to analyze transactions. I’ve also covered filtering based on date ranges, and using the Timeline feature in Excel 2013 and later. In this series, I’ll build on the concepts from that article to show you two different ways for sifting through data based on the criteria you provide.
The first approach, filtering, works in all versions of Excel—both with lists and pivot tables. The second approach, the Slicer feature, can be used with pivot tables in Excel 2010 and later, two different ways for sifting through data based on the criteria you provide In Part 1 I’ll discuss filtering and slicing lists of data. In Part 2 I’ll turn my attention to filtering and slicing pivot tables.
Preparing Our Data
The first step is to identify a report that will easily lend itself to being filtered. Many QuickBooks reports get to Excel in a format that would look good on paper—lots of white space, total rows, and very little repetition of data. We need exactly the opposite in Excel: a dense list of data to dig our hands into. One of my favorite reports for this purpose is the Custom Transaction Detail Report, as shown in Figure 1:
- In QuickBooks, choose Reports.
- Select Custom Reports.
- Choose Transaction Detail.
- If you wish, you can adjust the settings of this report to choose a date range of your choice, but I’m going to click OK to make the report appear onscreen.
One aspect of the Custom Transaction Detail report that I like is the lack of totals—the report is comprised of a long transaction list. You can often transform other reports, such as the Sales by Customer Detail report. In the Modify Report dialog box you can change the Total By field to Total Only. Many reports default to totaling by customer, vendor, employee, and so on, depending upon the context of the report.
The next step is to export the data to Excel. My favorite approach is to export to a comma-separated value file. This often results in a cleaner report format, plus lengthy reports export from QuickBooks far faster. For this article I’ll send the report directly to Excel, as shown in Figure 2:
- Click the Excel button on the report screen.
- Choose Create New Worksheet.
- Click the Advanced button to carry out a couple of one-time steps that will improve your experience going forward when you export to Excel.
- Uncheck the Space Between Columns checkbox.
- Check the Auto Filtering (Allows Custom Data Filtering)
- Click OK.
- Click Export.
At this point the report should appear in Excel, with filtering arrows at the top of each column.
If you either exported your report to a CSV file or opted not to enable the Auto Filtering option, then you’ll need to turn on the filter feature manually. There’s a catch here, though. QuickBooks tends to make the second row of reports be blank, and sometimes column B is blank as well. Any blank rows or columns amidst your report can cause the Filter feature to appear to malfunction. The nuance to be aware of is that filtering is predicated on the contiguous block of cells that surround the cell that you’ve selected—unless you preselect the data you wish to filter. Thus, it’s easy to encounter a situation where the filter buttons appear, but the drop-down menus are blank. If you encounter this, row 2 of a QuickBooks report being blank is likely the cause.
You’ll have the best experience analyzing your data in Excel when you first remove any blank columns or rows. The Transaction Detail reports tend to particularly lend themselves to easy analysis—this QuickBooks report format removes much of the noise. This doesn’t mean you can’t filter or transform other reports from QuickBooks. Simply click the top left-hand corner of the worksheet frame, above row 1 and to the left of column A. This will select all cells on the worksheet. You can then manually turn on the Filter feature by choosing that command from the Data menu, or from the Sort and Filter menu on Excel’s Home tab.
A particularly helpful aspect of the Filter feature is the ability to instantly see one of each item in a given column. For instance, if I click the arrow in cell I1 of Figure 3, I can see at a glance that certain transactions were assigned to one of three classes. The (Blanks) choice at the bottom of the list signifies that one or more transactions were not assigned to a class. Let’s say I want to see only the transactions assigned to the New Construction class:
- Click the arrow at the top of the column, in this case within cell I1.
- Uncheck the (Select All)
- Check the New Construction
- Click OK.
- A this point the report only shows transactions that have been assigned New Construction as a class.
This technique works in every version of Excel, but it has a couple of drawbacks:
- There’s a fair amount of clicking involved to distill a list down.
- Once you click OK on the Filter list, you then lose track of which items have been hidden from you.
The Slicers feature in Excel 2013 and later helps overcome these frustrations. As we’ll see later in the series, Slicers also make it easy to filter data in pivot tables, but let’s see how to filter lists more effectively. There are a number of preparation steps you’ll need to do in advance:
- If you haven’t already done so, delete any blank columns from the report, including column A, which tends to contain a date range.
- Delete any blank rows, such as row 2, which tends to be blank on reports exported from QuickBooks.
- You must turn off the Filter feature from the Data tab or from the Sort & Filter menu on the Home tab before Excel allows you to create a Table.
- Choose Excel’s View
- If the Split command has a shaded background, click the command to remove the split. This step is crucial, as the slicer you’re about to add can behave erratically if the Split feature is enabled. The Split feature is intended to keep the heading row visible, but we’re about to address this by making the data into a table.
- Activate Excel’s Insert
- Choose the Table
- Click OK.
At this point you’re ready to add a Slicer. To do so in Excel 2013 and later:
- Choose Insert Slicer from Excel’s Design If you don’t see the Design menu, click any cell within your list.
- Choose Class.
- Click OK.
- A Slicer will appear on your worksheet, from which you can choose New Construction.
Notice that only one click is required to collapse the list to only show New Construction transactions. Further, the slicer enables you to see that you’re not presenting transactions assigned to the Overhead or Remodel classes, as well as transactions that aren’t assigned to a class. Visible items are assigned a color in the slicer. Items that are hidden are shown in white.
Keep in mind that the slicer is simply a proxy for the Filter feature. If you were to click on the drop-down arrow in cell I1, you’d see that the filter list only has New Construction selected. Further, if you were to select both New Construction and Remodel from the filter list, you’d see both of those items change color within the slicer list. If you wish to choose one item from the list, simply click a single item from the Slicer. Hold down the Ctrl key while you make your selections if you wish to choose two or more items.
In the next article in this series I’ll discuss using slicers with pivot tables. The ability to filter pivot tables by way of slicers is available in Excel 2010 and later. You’ll even see how you can filter two or more pivot tables at once by way of a single slicer.