QuickBooks

Filtering QuickBooks Transactions by Date Range in Excel

Written by David Ringstrom

You’ve likely struggled in the past to analyze QuickBooks data based on date ranges. Indeed, most transaction-based reports in QuickBooks do allow you to view information based on date ranges, but many times we just want to get our hands on the data in Excel. As I’ve discussed previously, pivot tables are an ideal way to analyze transactions. In this article I’ll build on the concepts from that article to show you two different means for sifting through transactions based on date ranges. The first approach, filtering, works in all versions of Excel, while the second approach, the Timeline feature, is only available in Excel 2013 and later.

Preparing Our Data

In this article I’ll use the Sales by Customer Detail report as I’ve done before. Follow the steps shown in Figures 1 through 6 of my Analyze QuickBooks Data with Excel Pivot Tables article if you’d like to recreate the report that I’ll be using. Or feel free to follow along with your own data. At a minimum, the data you’ll analyze should have three columns:

  • One column containing text; such as a customer or vendor name.
  • One column containing dates, such as a transaction date.
  • One column containing amounts, such as a transaction amount.

You can certainly include other columns if you wish, but those are the minimum requirements for creating a pivot table that contains dates. Figure 1 shows what my sample data looks like based on the Sales by Customer Detail report from QuickBooks.

Filtering QuickBooks Transactions

Figure 1: Data that you wish to analyze with a pivot table in Excel should be structured in this format.

Building Our Pivot Table

Once you have your data in an analysis-ready format, you’re ready to create a pivot table, as shown in Figures 2 and 3:

  1. If your data is already a table in Excel, use the Summarize with Pivot Table command on the Design Otherwise use Insert, and then Pivot Table.
  2. Click OK when the Create Pivot Table window appears.
  3. As illustrated in Figure 3, a Pivot Table Fields list will appear along the right-hand side of your screen. Choose Name to add the customer names to the pivot table.
  4. Choose Date to add the transaction dates to the pivot table.
  5. Click Amount to add the amounts to the pivot table.
  6. Click on cell A4, or the first customer name within your report.
  7. Click the Collapse field button on the Analyze menu in Excel 2013 and later, or the Options menu in Excel 2010 and earlier.
  8. Click the +/- Buttons command on the Analyze menu in Excel 2013 and later, or the Options menu in Excel 2010 and earlier.
Convert your data into a pivot table

Figure 2: Convert your data into a pivot table.

 

pivot table fields

Figure 3: Our completed pivot table includes activity by date, but is collapsed to only show customer names and amounts. Dates are present, but hidden.

Filtering Our Pivot Table

At this point we’re ready to filter our pivot table, as illustrated in Figure 4:

  1. Click the arrow in the Row Labels section of the pivot table.
  2. Choose Date (or the name of your field that contains dates) from the Select Field
  3. Choose Date Filters.
  4. Choose Between.
  5. Specify 1/1/20 as the Start Date (or a date of your choice).
  6. Specify 3/31/20 as the End Date (or a date of your choice).
  7. Click OK.
Filtering Pivot Table

Figure 4: You can filter based on date range in any version of Excel if you include a date field within the pivot table.

At this point the pivot table will only reflect transactions that occurred between January 1, 2020 and March 31, 2020. You can repeat steps 1 through 7 as needed to transform the pivot table for different time periods. You can also choose from the other options listed on the Date Filters submenu, such as the All Dates in the Period option that allows you to specify a specific month or quarter. However, there is a catch to these options if your report includes activity that spans two or more years.

For instance, if a filtering menu in Excel allows you to choose January, be aware that Excel’s definition of January could differ from yours. To Excel, January means January of every year present within the underlying data—you’re probably thinking only January of the current year. If your report only has data for a single year, choosing a month from the menu can be a huge convenience. However, if your report includes activity from two or more years, you’ll be far better served if you use the Between command (step 4 in the preceding list) to ensure that you see exactly the data you’re expecting.

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

2 Comments

  • That was the Nice way to Keep your QuicBooks Record on Excel for the Future comparison and Check the Transition Detail in Excel because QuickBooks Some time facing problem to show the old Records and also most transaction-based reports in QuickBooks do allow you to view information based on date ranges, but many times we just want to get our hands on the data in Excel