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.
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:
- 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.
- Click OK when the Create Pivot Table window appears.
- 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.
- Choose Date to add the transaction dates to the pivot table.
- Click Amount to add the amounts to the pivot table.
- Click on cell A4, or the first customer name within your report.
- Click the Collapse field button on the Analyze menu in Excel 2013 and later, or the Options menu in Excel 2010 and earlier.
- Click the +/- Buttons command on the Analyze menu in Excel 2013 and later, or the Options menu in Excel 2010 and earlier.
Filtering Our Pivot Table
At this point we’re ready to filter our pivot table, as illustrated in Figure 4:
- Click the arrow in the Row Labels section of the pivot table.
- Choose Date (or the name of your field that contains dates) from the Select Field
- Choose Date Filters.
- Choose Between.
- Specify 1/1/20 as the Start Date (or a date of your choice).
- Specify 3/31/20 as the End Date (or a date of your choice).
- Click OK.
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.