The following refers to MS Office Excel 2007. Although the information contained herein will be applicable to any version of Excel, users of another version may have to deal with minor differences in layout and display.
Consider the nicely tabularized Excel database shown in Figure 1 below. Beginning in November 2012 and extending indefinitely thereafter, this database attempts to track an individual’s weekly income and expense items. It is not precisely a budget, but could be used as such.
Figure 1: Weekly Income/Expense Items
This is very simple data, organized to indicate regular, recurring weekly income and expense items. Very often an expense or income occurs that is not regular/recurring, and these are lumped under a weekly Miscellaneous category. If an explanation is needed, it is entered into column H Comments.
Cell D1 displays the money on hand when the user first began tracking his income/expenses. Cell G1 (Total) displays the amount in cell D1 (starting cash) plus the sum of the values in column G, from row 4 on down. An income item is indicated by a positive dollar value and an expense item is indicated by a negative dollar value.
Thus, cell G1 indicates the current cash on hand, consisting of exactly $168. This alone is very valuable financial information, but more specific and perhaps even more useful information can be easily obtained, using Excel’s AutoFilter and Subtotal functions.
To begin, it is necessary to turn on the AutoFilter for the data table. This is done by first selecting any cell in the table. Cell A4 is exactly such a cell, and is accordingly selected. Now from the Sort & Filter submenu, the user clicks Filter, with the result as shown in Figure 2 below.
Figure 2: AutoFilter Applied
In row 3, the column labels row, notice the little down arrows to the right of each cell. These are the filter arrows. A filter can be applied to one or more columns simultaneously. Any row that does not satisfy all filtered columns is hidden.
For example, suppose the user wanted to know his net income/expenses for the month of December. To obtain this information, he should filter the Month column for 12 (December). He should click the down arrow to the right of the Month column label. A filter menu will appear, allowing him to select a specific month. He should uncheck the “(Select All)” option, and then select 12. The result should be all income/expense items in December, as shown in Figure 3 below.
Figure 3: All Income/Expense Items In December
The Total (in cell G1) has changed to reflect the filter. It now shows $79. This indicates that the user had a net credit of $79 for the month of December. But suppose that the user wanted to know specifically what occurred in week 2 of December. This is also possible.
The user should leave the Month column filtered for 12. He should now go to the Wk Due column, click the down arrow to the right, uncheck the “(Select All)” option, and select 2. The result should be the income/expense items occurring in Week 2 of December, as shown in Figure 4 below.
Figure 4: Income/Expense Items In Week 2 Of December
Notice that once again the Total (cell G1) has changed. The total now indicates the net of income/expense items occurring during Week 2 of December.
Removing filters is easy: simply go back to the down arrow and recheck the “(Select All)” option. The user must remember to do this for each filtered column. Filters are persistent. When the spreadsheet is closed/opened, any previously applied filters will remain unless explicitly removed by the user.
The title of this article references two functions: AutoFilter and Subtotal. The AutoFilter has already been discussed, but what of the Subtotal function? In fact, the Subtotal function has been used all along. It is the SubTotal function that allows the Total in cell G1 to change in response to an applied filter.
After selecting cell G1 (Total), the formula bar indicates that this cell actually contains a formula that includes the SubTotal function, as shown in Figure 5 below.
Figure 5: SubTotal Calculation
The formula in cell G1 is displayed in the formula bar. The print may be too small to read directly, but the formula is the following: “=D1 + SUBTOTAL(109,G4:G1048576)”.
In this formula, D1 refers to the starting money in cell D1. The addition “SUBTOTAL(109, G4:G1048576) means the sum of all numeric values in column G, beginning from row 4 to the last row of the spreadsheet. The range extends to the bottom of the spreadsheet so that, in case the user enters more income/expense items, the formula will automatically include them.
Thus, the formula refers to starting money plus the net of income/expense items, which is equivalent to cash on hand.
The first parameter of the SUBTOTAL function is 109. This refers to summation, which is by far the most common calculation. However, other calculations are possible. If, instead of 109, the formula contained 101, the formula would calculate the average of the column, rather than the sum. Further details can be found in the online Help. Most important to remember is that these calculations will exclude rows that do not satisfy all applied filters.
Notice that the dataset contains separate Year, Month, and Wk Due columns, as well as a column for actual dates. This seems redundant, and perhaps so, but the user will have constructed this dataset with filter applications involving these columns already in mind. This is a common design practice with Excel databases.
As demonstrated, use of appropriate filters, combined with the Subtotal formula, will enable the user to pull an enormous amount of very useful information from a simple list of data. In fact, these two functions, AutoFilter and Subtotal, might well be considered the “bread and butter” functions of an Excel database. One should therefore expect that most Excel datasets will make use of these functions, and design accordingly.