Category: AccountingWEB articles

By David H. Ringstrom, CPA

Figure 1: It's frustrating when Excel won't allow you to insert columns or rows.

A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

By David H. Ringstrom, CPA

Given the length of the Great Recession, your top customers today might not be the same folks who were your top customers even a couple months ago. In this article I'll demonstrate two ways that you can determine who your top customers are based on total sales.

The first technique involves a minor tweak to an existing QuickBooks report, while the second demonstrates a couple of tricks that I use to transform QuickBooks reports into usable data in Excel.

To quickly determine your top customers for a given time period, follow these steps:

1. In QuickBooks, choose Reports, Sales, and then Sales by Customer Summary.

2. When the report appears onscreen, change the report dates to display the period that you wish to see, and then click Refresh, as shown in Figure 1.

Figure 1: Modify the dates of the Sales By Customer Summary to display the report period in question.

3. Click the Modify Report button that appears at the top of the report window.

4. In the Modify Report window, change Sort By to Total, instead of Default, as shown in Figure 2.

Figure 2: Change the Sort By to Total, instead of Default.

5. Click OK to display the modified report on the screen.

6. To save this report for future use, click the Memorize button that appears at the top of the report window. Assign a name, such as Top Customers, and then optionally choose a Memorized Report Group. Going forward, you can access your report by choosing Reports, Memorized Reports, and then looking for your report title.

This approach is simple enough, but doesn't allow you to limit the report to a particular subset, say your top 10, 25, or 100 customers. Fortunately it's easy to do this with Excel:
1. In QuickBooks, choose Reports, Sales, and then Sales by Customer Summary.

2. When the report appears onscreen, change the report dates to display the period that you wish to see, and then click Refresh, as shown in Figure 1.

3. Click the Export button at the top of the report screen, choose New Excel workbook, and then click Export.

4. The total sales for each customer are a =SUM formula that will get corrupted by a subsequent process that we're going to do to this report, so we must convert the totals to values:

a. Right-click on the sales figure column (for instance, column D in Figure 3), choose Copy to copy the entire column to the clipboard.

Figure 3: Convert the sales figures to numbers instead of formulas.

b. Right-click again on the sales figure column, choose Paste Special, Values, and then click OK.

5. Click on cell A1, and then press Ctrl-* to select the entire report. Use the * key on the number pad of your keyboard, or press Ctrl-Shift-8.

6. Turn on the filter arrows:

• Excel 2007/2010: Choose Sort & Filter in the Editing section of the ribbon, and then choose Filter.
• Excel 2003 or earlier: Choose Data, Filter, and then AutoFilter.

7. Click the filter arrow in cell B1 (as shown in Figure 4), and then choose Text Filters, and then Custom (Users in Excel 2003 and earlier can just choose Custom).

Figure 4: Be sure to use the filter arrow in cell B1.

8. As shown in Figure 5, choose Does Not Equal, enter Total*, and then click OK.

Figure 5: Choosing Does Not Equal and Total* is the same as saying “does not begin with”

9. Use the worksheet frame to select all visible rows from row 2 through the end of the report, and then right-click on any of the rows you selected and then choose Delete, as shown in Figure 4.

10. Click the arrow in cell B1, and then choose All in Excel 2003 and earlier or click Select All in Excel 2007/2010.

11. Delete any blank columns, so that you're left with one column with your customer names, and a second column with the sales amounts.

12. The next step is to eliminate the word Total from the start of each customer row:

a. Click on column A on the worksheet frame to select the entire column.

b. Choose Data, and then Text to Columns.

c. When the Text to Columns wizard appears, choose Fixed Width, and then click Next.

d. Excel automatically guesses that we want to eliminate the word Total, so click Next again.

e. As shown in Figure 6, click on the Total column, and then choose Do Not Import (skip), and then click Finish.

Figure 6: The Text To Columns wizard allows you to delete the word Total from the start of each customer name.

3. You can now use the Top 10 AutoFilter feature to display a selected range of customers. To do so, click the arrow in at the top of the column that contains the sales figures. Next, choose Top 10 in Excel 2003 and earlier, or Number Filters, and then Top 10 in Excel 2007/2010. As you can see in Figure 7, although the feature is “Top 10”, you can really display the top number of your choice. The final report format is shown in Figure 8.

Figure 7: The Top 10 feature in Excel actually allows you to filter any number of customers you choose.

Figure 8: The final report format generated by using Excel's Top 10 filtering feature.

A previous version of this article first appeared on www.accountingweb.com .

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

By David H. Ringstrom, CPA

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

By David H. Ringstrom, CPA

• lookup_value – This is the data that you want to look for in the first column of the table array.
• table array – The table array is a range of two or more columns.
• col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
• range_lookup – In this position you indicate FALSE if you're seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you'd use FALSE to look up the price of an inventory item, or TRUE if you're determining which tax bracket an income level falls into.

• range – This is a column or row where you want to look for specified criteria.
• criteria – This is the same as the lookup_value for VLOOKUP.
• sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

By David H. Ringstrom, CPA

•  data_array – a range of cells containing numeric values
• bins_array –a range of cells containing bins into which the numeric values should be grouped

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

By David H. Ringstrom, CPA

1. Select the range of cells that contain numbers surrounded by < >. It doesn't matter if you select cells that have numbers without brackets, but it's helpful to limit the area that you're searching to avoid unintended replacements.
2. In any version of Excel, press Ctrl-H to display the Find and Replace dialog box shown in Figure 1.
3. As shown in Figure 2:

Figure 2: Replace < characters with a minus sign.