Overview

Hello everyone! Welcome to the solution walkthrough for the Report Formula Challenge. In this guide, I'll break down how to solve each level using efficient Excel formulas. We'll focus on building flexible, robust reports that handle changes like sorting or adding data without breaking. I'll emphasize using tables for structured references (to avoid fragile cell references) and functions like XLOOKUP, SUMIFS, FILTER, and SUBTOTAL for dynamic results.

Remember, there are many ways to solve these including VLOOKUP, INDEX/MATCH, or even SUMIF could work in places, but I'll share my preferred approaches based on modern Excel features. I'll show the exact formulas I use, starting with the top cell in each report section, and note how they fill down seamlessly.

If you haven't tried the challenge yet, pause and give it a go! The workbook has a "Key" tab for each level to verify results. Let's dive in.

Goal

The goal is to write formulas that populate the report through 3 difficulty levels:

Level 1

In Level 1, we have a simple one-to-one lookup: each report line pulls a single value from the data table based on the "FS Line." The goal is to avoid hard-coded cell references (e.g., =E13) because they're fragile. If you sort the data, the report breaks.

First, convert the data range (D12:E21) to a table: Select the range, go to Insert > Table, and confirm. Excel names it "Table1" by default (you can rename it via the Table Design tab for clarity, e.g., "DataTable").

In cell B13 (Cash and Cash Equivalents), enter:

=XLOOKUP(B13, Table1[FS Line], Table1[Amount])

This looks up the value in B13 within the [FS Line] column of Table1 and returns the matching [Amount]. Fill this formula down to B17 (it auto-adjusts thanks to table references).

I handed out the file with formulas that computed the report totals and subtotals. I didn't ask you to review them ... so this is kinda just a bonus tip. In these types of formula based reports (where we have totals with subtotals), I often prefer to use the SUBTOTAL function rather than the SUM-and-pick-and-choose approach. This is because it makes them easier to update and maintain over time when inserting new report values. So, in practice, I would replace the total and subtotal formulas as follows:

• In B18 (TOTAL ASSETS): =SUBTOTAL(9, B13:B17)
• In B24 (TOTAL LIABILITIES): =SUBTOTAL(9, B21:B23)
• In B28 (TOTAL EQUITY): =SUBTOTAL(9, B26:B27)
• In B29 (TOTAL LIABILITIES AND EQUITY): =SUBTOTAL(9, B13:B28)

This is because SUBTOTAL(9, ...) sums while ignoring other SUBTOTALs in the range, making it flexible for future row insertions.

Level 2

Level 2 introduces aggregation: multiple rows in the data share the same "FS Line," so we need to sum them up for each report line.

Convert the data range (D14:F30) to a table: Insert > Table, named "Table2" by default.

In cell B15 (Cash and Cash Equivalents), enter:

=SUMIFS(Table2[Amount], Table2[FS Line], B15)

This sums the [Amount] column where [FS Line] matches B15. Fill down to B19.

Tables shine here: If you add a new row (e.g., type "Payroll Checking" under the table with "Cash and Cash Equivalents" and 100,000), the table auto-expands, and the formulas include it without changes. The report balances at 266,313.

Level 3

Level 3 splits data across two tables: "Values" (AcctID and Amount) and "Lookup" (AcctName and FS Line). We need to match accounts, filter by FS Line, lookup amounts, and sum—without helper columns.

Convert both ranges to tables:

• Values (D13:E29) → Table named "Values"
• Lookup (G13:H29) → Table named "Lookup"

In cell B14 (Cash and Cash Equivalents), enter:

=SUM(XLOOKUP(FILTER(Lookup[AcctName], Lookup[FS Line]=B14), Values[AcctID], Values[Amount]))

Breaking it down:

• FILTER(Lookup[AcctName], Lookup[FS Line]=B14)
  • returns an array of account names matching the FS Line (e.g., "Checking account", "Money market", "Savings").
• XLOOKUP(..., Values[AcctID], Values[Amount])
  • looks up those names in [AcctID] and returns matching amounts.
• SUM(...)
  • aggregates them.

Fill down to B18. The report again balances at 266,313.

Recap

We started with basic lookups in Level 1, added aggregation in Level 2, and tackled multi-table matching in Level 3. Key takeaways:

• Use tables for structured references since they auto-expand and prevent slippage when filling formulas.
• Prefer XLOOKUP over VLOOKUP for flexibility (it handles left/right lookups and exact matches by default).
• FILTER + XLOOKUP + SUM scales for complex data without helpers.
• SUBTOTAL makes totals robust against insertions.

Invite to Continue Learning

Hopefully, this challenge sparked some "aha!" moments and showed how far formulas can go. If you'd like to level up your Excel skills, check out my guided learning paths at Excel University. They're hands-on, step-by-step programs for all levels covering everything from basics to advanced reporting like this. Check out our Training Passes. Thanks for participating, keep practicing, and see you in the next one!