Drilldown results for the SUBTOTAL function include hidden cells

Overview

When using the Drilldown feature to look into entries used to calculate the result of a formula containing the SUBTOTAL Excel function, the Drilldown sheet contains hidden cells not included in the original function’s results. This can cause discrepancies between the results of the SUBTOTAL function and the Drilldown performed for the cell in which it resides.

In this example, the SUBTOTAL function in cell K38 is referencing the range K42#, where several cells are hidden, and consequently not included in the result.

When performing a Drilldown for cell K38, the resulting Drilldown sheet lists entries hidden from range K42# in the original worksheet, for instance, cells K55 and K56.

Cause

The SUBTOTAL function can either ignore or include hidden cells, depending on its first argument, corresponding to its underlying function. For values 1 - 11, hidden cells are included, and for values 101 - 111 they will remain hidden.

Drilldown, however, searches for precedent results using Excel’s internal resources, which do not allow for respecting hidden cells.

Resolution

Refer to the Microsoft Office documentation for a list of accepted values in the first argument of the SUBTOTAL function.

Make sure that your SUBTOTAL formulas use values 1 - 11 as the first argument to include hidden rows in the results and avoid discrepancies between these results and the results of a Drilldown.

Due to the limitations of the Microsoft Office implementation, it is currently not possible for Drilldown to respect hidden cells.