QuickBooks Tech Trends

Two Ways to Tally QuickBooks Customer Sales By City or State in Excel

Written by David Ringstrom

A QuickBooks user recently commented on one of my articles for this blog that it is “very difficult to get marketing data (from QuickBooks desktop) i.e. sales by state. I’ll be the first to admit that many times accounting transactions have the appearance of being trapped under glass in QuickBooks, but there’s always a way to get what you want. In this article I’ll describe two different techniques you can use to tally QuickBooks customer sales by city or state based on a report exported to Excel.

The first challenge with QuickBooks is always to pick the correct report. For instance, you might try choosing Reports, Sales, and then Sales by Customer Summary. When you click the Customize Report button you’ll find that you cannot add or remove columns from this report, as shown in Figure 1. However, if you choose Sales by Customer Detail, you can choose the columns you wish to appear on the report. Further, we’ll see how QuickBooks 2015 makes it far easier customize reports.

QuickBooks Sales by Customer Summary report

Figure 1: The Sales by Customer Summary report does not allow you to add or remove columns.

Carry out these steps to create a Sales by Customer Detail report:

  • Choose Reports, Sales, and then Sales by Customer Detail.
  • Click the Customize Report button.
  • To generate our report we’ll only need 4 fields – it’s easy to make these selections in QuickBooks 2015, as shown in Figure 2:
    • Name
    • Name City
    • Name State
    • Amount
  • Click the Checkmark button twice to group all fields presently on the report together. Turn off the following fields:
    • (left margin)
    • Type
    • Date
    • Num
    • Memo
    • Item
    • Qty
    • U/M
    • Sales Price
    • Balance
  • Scroll down a little further and turn these two on:
    • Name City
    • Name State

In QuickBooks 2014 and earlier you’ll have to expend a bit more effort, as the field list is only scrollable but not sorted in any fashion.

  • Click once on the first field in the list, which is (left margin), and then use the down arrow key to move down through the list. Tap the space bar each time you find a field you want to toggle on or off.
  • In all versions of QuickBooks change the Total By field from Customer to Total Only and then click OK.

QuickBooks Customer Sales By City or State

Figure 2: Follow these steps to customize your report.

At this point your report should look similar to Figure 3. Carry out the following steps to export the report to Excel:

  • Click the Excel button, choose Create New Worksheet, and then click Export.

Export to Excel

Figure 3: Follow these steps to export your customized report.

  • Once the report appears in Excel delete row 2 and column A, so that the report looks similar to Figure 4.
  • Next scroll down and delete the Grand Total row from the bottom of the report. To quickly reach the bottom of a report like this in Excel, you can also click on column A and the press Ctrl-Down Arrow. Assuming that column A doesn’t have any blank cells, you’ll quickly zoom to the bottom of the report.

QuickBooks report in Excel

Figure 4: Once the report is exported to Excel delete column A, row 2 and the Grand Total row.

At this point you’re ready to implement the first of the two techniques that I’ll be demonstrating, which is Excel’s Subtotal feature. Before we implement this feature, we must first sort the data in the desired order. Since I’ve chosen to use one of the sample companies in QuickBooks all of my customers are located in California, so I’ll subtotal the sales by city. To do so, right-click on cell B2—which should contain a city name—then choose Sort, and then Sort A to Z.


Save pagePDF pageEmail pagePrint page

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!”

9 Comments

  • I am a QB Enterprise 2015 user

    I need to tally which user sends how many Purchase Orders.

    I am having trouble creating a report that shows which user sent our Purchase Orders.

    I am trying to track our Purchase Order Accuracy, but at my current site there are 3 different users sending Purchase Orders.

    Thanks

    • Casey,

      In QuickBooks choose Reports, Accountant and Taxes, and then Audit Trail. Click Customize Report, choose the date range in question, and then on the Filters tab limit the transaction type to Purchase Order. You’ll need to send the report to Excel, and then use the Filter feature to condense the report down. Keep in mind that if a PO has been edited then it will appear on the report more than once. You can filter the State column for the word Latest to get one of each transaction. Thanks for your great question. I’m going to explain this technique in much more detail in an upcoming article.

      David

  • Hi David,

    Which version and update of Quickbooks were you using to illustrate this method? I do not have a box to double click to group all fields on the report together, and it would be a great feature. I am running Enterprise for Manufacturing 14, Vers 24.0.4008.0.

    • Tinga,

      Intuit made several reporting improvements the reports in the 2015 desktop versions of their products. In your case you’ll need to upgrade to Enterprise version 15. That little tweak is one of my favorite features now in QuickBooks. It’s similar to when a few versions back they finally added the ability to click the column headings in the Bank Reconciliation window to be able to sort columns.

      David

  • David,
    We use Enterprise 2015. We use classes to track sales and expenses (including trade shows) for various customer classes (segments) We would like to be able to get a report to show us sales and related expenses by area of the country, i.e. states and groups of states like Midwest, southeast etc.
    Is there a way to customize an existing report to get this information? This would be especially interesting for us to be able to justify allocation of marketing funds to various trade shows, etc that we spend to benefit those areas and customer classes within those areas.
    Thanks,
    Bobby

Leave a Comment