This is the archive of CPA Now blogs posted on the PICPA website through April 30, 2025. Want more recent blogs?

Read current blogs

While Excel’s Subtotal feature can be a great way to summarize data and create drill-down reports, you are limited to using one of the tool’s 11 summarizing functions:

1. Average
2. Count
3. Count of Numbers
4. Maximum
5. Minimum
6. Product
7. Standard Deviation
8. Standard Deviation of a Population
9. Sum
10. Variance
11. Variance of a Population

Suppose you would like to use Subtotal, but the calculation you require – calculating a median, for example – is not one of the 11 available in Subtotal. Learn how to use a little trickery to “persuade” Subtotal to work the way you want it to.

Add a Different Calculation to Subtotal

To begin, sort the data by the field(s) you want to group on, select Subtotal from the Data tab of the ribbon, and choose the Sum option. In this example, sum the Eng Hours field at every change in Sector (the field on which the data is sorted). Figure 1 displays a portion of the initial results.



With the initial report complete, from the Home tab of the ribbon, choose Find and Replace. Go to Replace, and in the Find & Replace dialog box enter the formulas shown in Figure 2. Click Replace All, and Excel converts all of the formulas in the Subtotal report to Median functions.



Note that this technique will not work on every type of formula that you might want to create in a Subtotal report. However, for relatively straightforward formulas, you can use this work-around to supplement Subtotal’s existing functionality. Excel’s Subtotal feature is a favorite of many business professionals. However, by default, if you choose to use the feature, Excel limits you to one of 11 predefined actions. With just a little bit of trickery, though, you can manipulate Subtotal and make it an even more powerful feature than what it already is.

For a video demonstration of this tip, please visit www.tinyurl.com/k2tips187.