Financial model review

Hidden rows and totals in financial models

Review hidden rows, filters and subtotals in Excel financial models. Use worked examples to find excluded costs, double counting and misleading report totals.

Lifted emerald louvers reveal a coral cost row above detail blocks and a complete total bar

A financial forecast can look complete while some of its supporting rows are hidden. Its total may include those rows, exclude them or count an intermediate subtotal twice. The right result depends on what the report is meant to represent, not simply what is visible on screen.

Keep the original workbook unchanged and investigate in a review copy. These invented examples use positive operating costs in EUR thousands; they illustrate formula behavior, not an approved budget.

Decide which records belong in the total

A whole-business forecast and a filtered department report answer different questions. Label the intended period, entity and selection before comparing their totals. A department-only number can be correct in its own report and wrong when linked into the whole-business forecast.

Ask which detail rows should contribute, whether any subtotal rows are already included and which output consumes the result. Write down the expected amount independently. Agreement with the visible rows alone is weak evidence when other rows may be hidden.

Reveal the view before rewriting the calculation

Inspect the row numbers, filter icons and any collapsed outline groups. A gap in row numbering is a reason to investigate, not proof that data was deleted. Microsoft explains the indicators and commands in its hide or show rows guide.

Record the filter criteria first, then clear them in the review copy when checking the complete dataset. In desktop Excel for Windows, Data > Clear clears applied filters. Microsoft documents that operation in Clear or remove a filter. Clearing a filter and unhiding a manually hidden row are separate actions.

Preserve the intended reporting view in the delivered model. Removing a filter merely to make two numbers agree can discard a useful department or period selection without repairing the underlying link.

Worked example: a hidden cost changes forecast profit

Assume an operating-cost schedule has three detail rows:

  • B2 contains staff costs of 100.
  • B3 contains contractor costs of 80.
  • B4 contains software costs of 20.

Put the category names in A2:A4, with Category and Cost headings in row 1. This gives A1:B4 a labelled range for the filter test below.

The full cost is 200. Put these formulas below the detail range, outside B2:B4:

=SUM(B2:B4)
=SUBTOTAL(9,B2:B4)
=SUBTOTAL(109,B2:B4)

With every row visible and no filter applied, all three return 200. Now manually hide row 3. The ordinary SUM and SUBTOTAL with 9 still return 200; SUBTOTAL with 109 returns 120.

Microsoft's SUBTOTAL reference distinguishes these options: 9 includes manually hidden rows, while 109 excludes them. Both exclude rows removed from a filter result. These visibility rules concern vertical ranges; hiding a column does not produce the same exclusion across a horizontal monthly range.

Suppose the whole-business forecast has revenue of 350 and links its cost line to the visible-only result of 120. It displays operating profit of 230, instead of 150 using the complete cost of 200. The 80 difference is the excluded contractor cost.

That is a scope error in this example, not evidence that SUBTOTAL is unreliable. A report intentionally limited to the remaining categories may need 120. Check the linked revenue, costs and report labels together before deciding which formula belongs there.

Test a filter separately from a manually hidden row

Unhide row 3 and restore the full view. Apply a filter to A1:B4 that excludes the contractor record. SUM still returns 200, while both SUBTOTAL formulas above return 120.

The same rows can disappear through different operations, with different consequences for a formula. Test one operation at a time and note the result. If a monthly cost chart uses a different total from the profit forecast, follow both references to their source cells rather than assuming the chart is outdated.

An imported cost stored as text introduces another possibility: SUM ignores text in referenced cells. Microsoft's SUM guide explains this distinction. Check the stored values as well as their visibility; a plausible subtotal does not establish that every input was numeric.

Watch for a subtotal counted as another cost

In a separate unfiltered example with all rows visible, use:

B2: 100
B3: 80
B4: =SUM(B2:B3)
B5: 20
B6: =SUM(B2:B5)

B4 is an intermediate subtotal of 180. B6 returns 380 because it includes both the detail amounts and that subtotal. The actual detail cost remains 200.

Changing only B6 to =SUBTOTAL(9,B2:B5) still includes the ordinary SUM result in B4. A row labelled Subtotal does not make its formula a nested SUBTOTAL function.

By contrast, if B4 uses =SUBTOTAL(9,B2:B3), an outer =SUBTOTAL(9,B2:B5) ignores that nested SUBTOTAL and returns 200. The function reference documents this specific nesting behavior. Do not infer that it recognizes every kind of intermediate total.

Map the detail and subtotal levels explicitly. An alternative may be to total only the intended detail ranges. Check the first and last referenced cells using the financial model formula review before replacing formulas throughout a workbook.

Read the formula behind an Excel Table total

An Excel Table's Total Row can also respond to visibility. Microsoft shows a generated SUBTOTAL(109,...) example in its table totals guide.

Inspect the actual formula; the word Sum in a menu or label does not establish the calculation's scope. Check whether a forecast links to a Total Row that changes with visibility when it really needs the complete dataset. Conversely, a filtered management report may intentionally need a total for its selected records. Keep those purposes clear in the cell labels and review notes.

A short review checklist

  1. Preserve the original and record the current filters and hidden sections.
  2. Define the records, period and units that should contribute.
  3. Reveal the relevant detail in a working copy and calculate an independent total.
  4. Inspect the formula, its range endpoints and any intermediate subtotal rows.
  5. Test filtering and manual hiding separately, then restore the intended view.
  6. Follow the total into profit, cash flow and any related report.
  7. Explain the correction and retain unresolved differences for investigation.

Use the units, signs and periods guide if two totals use different scales or reporting periods. For a small remaining difference, investigate its cause with the rounding review rather than inserting a balancing number.

What the saved-file checker contributes

FinModelCheck does not recalculate Excel or automatically diagnose hidden-row, filter or subtotal intent. A normal saved number can still come from an inappropriate selection. Its supported saved-error and broken-reference checks supplement the manual review above.

Open the financial model checker and see the example report for its format. Online results show a provisional score and counts by Finding type, without detailed customer Findings. The complete PDF is free without registration in the current valid owning browser session.

Frequently asked questions

Should hidden costs always be excluded?

No. Hiding is a presentation choice. A whole-business cost forecast may still need every valid cost, while an explicitly selected department report may need only its relevant records.

Does SUBTOTAL automatically prevent every double count?

No. Its treatment of nested SUBTOTAL functions does not identify every ordinary SUM or other calculation that represents an intermediate total. Review the formula hierarchy and intended detail rows.

Does a high checker score prove the report total is correct?

No. The score covers supported saved-file checks. The financial scope of a total and its response to visibility need the separate review described here.

Keep reviewing

Continue with a related guide.

Browse all guides