This month, Susan Harkins supplies different solutions for the same problem using Microsoft Excel.
If you're familiar with most of Excel's features, you know that there's almost always more than one way to get the information you need from your data. On the other hand, if you're not familiar with all the features, you might spend a lot of time trying to write a complex formula, unnecessarily. This month, I help Kay track outstanding invoice amounts by adding two helper columns and then using Excel's Subtotal, PivotTable, and Table object—no impossibly difficult formulas, no hair-pulling, and no gnashing of teeth!
I'm using Windows 7 and Excel 2007 and 2013. You can download the .xlsx or .xls demonstration file, but my old XP/2003 system is in the shop, so there are no 2003 instructions for this article. The Table object isn't supported by 2003.
A Subtotal solution
Kay tracks invoices, and she wants to sum outstanding invoice amounts that are 30 and 60 days out from the invoice date for each supplier. There are so many ways to do this, but I'll show you how to use the built-in tools to get the job done without code or complex formulas.
The simple data set shown in Figure A uses the TODAY() function to display the current date. By referring to a cell instead of a literal TODAY() function in the formulas, the solution is a bit more flexible, because you can easily change the anchor date.
First, we'll add formulas to identify invoices that are 30 and 60 days past the invoice date. We'll also add a helper column that calculates the total number of days between the invoice date and the current date, so you can see the calculations at work. All of these calculations can be added to a single formula, but I find helper columns easier to manage. Then, we'll use the Subtotal feature to total outstanding amounts for each supplier.
To get started, enter the following formulas and copy them to the remaining data set:
Figure B shows the results of these formulas. Column G returns the total number of days beyond the invoice date if the invoice hasn't been paid. Column H returns the invoice amount for those invoices that are unpaid and where the invoice date is greater than 30 days and less than or equal to 60 days. Column I returns the invoice amount for those invoices that are unpaid and where the invoice date is greater than 60 days. When applying this to your own work, be sure to pay close attention to the equality operators to get the right results for your organization.
Now, let's use the Subtotal feature to sum outstanding amounts by suppliers. First, you must sort by the grouping value, which (in this case) is the Supplier as follows:
- Click any cell in the Supplier column (C4:C8).
- On the Home tab, click Sort & Filter in the Editing group.
- Choose Custom Sort.
- In the resulting dialog, choose Supplier from the Sort by drop-down.
- Choose Values from the Sort On drop-down.
- Choose A to Z from the Order drop-down.
- Be sure to check the My data has headers options (Figure C).
- Click OK.
Subtotal feature relies on sorted data. You can't skip the sort unless your data is already in order by the grouped values. You can choose a more complex sort by adding sort levels. In addition, your data might not have headers; the example data does.
After sorting, you're ready to apply the Subtotal feature as follows:
- Click anywhere inside the data set.
- Click the Data tab and then click Subtotal in the Outline group.
- In the resulting dialog, choose Supplier from the At each change in drop-down.
- Choose Sum from the Use function drop-down.
- Check the 30 and 60 options in the Add subtotal to list (Figure D).
- Click OK to see the totals in Figure E.
With a few simple formulas, a quick sort, and the Subtotal feature, you can see the following:
- Jones has a total of $30 that's 60 days or older.
- Michaels has $92 that's between 30 and 60 days old.
- Michaels has $30 that's 60 days or older.
When applying Subtotal to your own work, remember that Excel can't use Subtotal with data stored in a Table object.
A PivotTable solution
As is almost always the case, you can also use a PivotTable. Figure F shows a simple PivotTable based on the Supplier, 30, and 60 day columns.
Use a PivotTable.
To generate this PivotTable, do the following:
- Click anywhere inside the data set.
- On the Insert tab, click PivotTable in the Tables group.
- In the resulting frame, drag Supplier to the Row Labels section, and drag 30 and 60 to the Values section Figure G.
- If the 30 and 60 columns default to a count instead of a sum, choose Value Field Settings from the field's drop-down, choose Sum, and then click OK.
Don't forget that you must refresh (update) a PivotTable when the underlying data changes. Click inside the PivotTable and then click the contextual Analyze tab. In the Data group, click Refresh (or right-click the PivotTable and choose Refresh).
A Table solution
Perhaps my favorite solution is the Table solution. It's the easiest, in my opinion, but the Table object has limitations. For instance, you can't use Subtotal on a Table, so keep these limitations in mind before choosing a Table.
To convert the text to a Table, do the following:
- Click inside the data set.
- On the Insert tab, click Table in the Tables group.
- In the resulting dialog, be sure to check the My table has headers option.
- Click OK.
I ran into a bit of a snag with the Table object; even after formatting the 30 and 60 in the heading labels as Number, the formulas still didn't work because the Table object doesn't like digits in a header. To simplify this example, I dropped those values in above the Table object's header row, as shown in Figure H. You could just as easily enter the literal values in the formulas, but I think references add flexibility, because you can quickly change the outstanding days component without updating the formulas.
Move the outstanding values out of the Table object's header.
You'll also need to update the formulas in columns H and I:
Column H: =IF(AND(G4>$H$2,G4<=$I$2),E4,"")
Column I: =IF(G4>$I$2,E4,"")
The header references now reference the new values in H2 and I2. Next, display the Total row by checking the Total Row option in the Table Style Options group on the contextual Design tab. To display sums in this new row, choose Sum from the Total drop-down for columns H (Figure I) and I.
Choose the Sum function.
The Table sums the entire data set, but the Table object offers filtering features. From the Supplier drop-down, choose a supplier (Figure J).
Filter by supplier.
Click OK, and watch the Sum functions in the Total row update accordingly, as shown in Figure K.
Outstanding total for a specific supplier.
If you don't want to use the Table object, you can use Excel's Filter feature in the same way, but you'll have to add the totals row manually. For more information, read the section Count filtered records in "Four useful Excel filtering behaviors" and replace the function argument 2 with 9: =SUBTOTAL(9,B4:B10).
I've shared three simple and easy-to-implement solutions. None is superior to the others. The way you use your data will determine the solution that's best for you.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers that I help. You can contact me at firstname.lastname@example.org.