Solutions: Exercise 3 (Excel Tables, Structured References & Micro-Dashboards)

Level 1 Solution: Recall & Conversion (Range vs. Table)

1. The Range vs Table Behavior Drill

• Action: In sheet Table_VS_Range , the workbook contrasts range C6:E12 against Table2 (G6:J13).
• Calculated Formula: In Table2, column Malak uses the explicit formula:
  EXCEL Copy

  =Table2[[#This Row],[Smmar ]]*Table2[[#This Row],[Nariman ]]
  

• Live Total Row (Row 13): Demonstrates filter-aware subtotals:
  • H13: =SUBTOTAL(101,Table2<a href="#" class="internal-link">Nariman</a>) $\rightarrow$ 54
  • I13: =SUBTOTAL(101,Table2<a href="#" class="internal-link">Smmar</a>) $\rightarrow$ 45
  • J13: =SUBTOTAL(109,Table2[Malak]) $\rightarrow$ 14,580

2. Table Creation & Calculated Columns (Sales_Data)

• Structure: The 101 records are converted to SalesTable (A1:L101, 12 columns).
• Production Formulas:
  • OrderYear: =YEAR(SalesTable[[#This Row],[Date]])
  • TotalPrice: =SalesTable[[#This Row],[Quantity]] * SalesTable[[#This Row],[UnitPrice]]
  • EmailDomain: =RIGHT(SalesTable[[#This Row],[Email]], LEN(SalesTable[[#This Row],[Email]]) - FIND("@", SalesTable[[#This Row],[Email]]))

Level 2 Solution: Direct Formula Application (Calculated Columns)

In sheet Product_Inventory, select A1:F52 $\rightarrow$ press Ctrl + T $\rightarrow$ rename to InventoryTable.

Column 1: InventoryValue (Column G)

Enter in cell G2:

=[@CurrentStock] * [@CostPerUnit]

• Explanation: Multiplies units in stock by unit cost. Auto-calculates down all 51 products.

Column 2: GrossMarginEGP (Column H)

Enter in cell H2:

=[@SellingPrice] - [@CostPerUnit]

• Explanation: Computes unit profit margin in Egyptian Pounds.

Column 3: MarkupPct (Column I)

Enter in cell I2:

=([@SellingPrice] - [@CostPerUnit]) / [@CostPerUnit]

• Explanation: Markup over unit cost. Select column $\rightarrow$ press Ctrl + Shift + % to format as percentage.

Column 4: ReorderAlert (Column J)

Enter in cell J2:

=IF([@CurrentStock] <= [@ReorderLevel], "REORDER", "OK")

• Explanation: Evaluates whether current inventory has fallen below the safety buffer. Products like EGY004 Phone Charger (30 <= 35) and EGY014 Laptop HP Egypt (16 <= 47) evaluate to "REORDER".

Level 3 Solution: Total Row & Filtered Subtotals

1. Enabling Total Row

• Press Ctrl + Shift + T while inside InventoryTable (or check Total Row on the Table Design ribbon).

2. Configuring Column Functions

• Click the summary cell in CurrentStock $\rightarrow$ select Sum from dropdown:
  EXCEL Copy

  =SUBTOTAL(109, [CurrentStock])
  

• Click the summary cell in CostPerUnit $\rightarrow$ select Average:
  EXCEL Copy

  =SUBTOTAL(101, [CostPerUnit])
  

• Click the summary cell in InventoryValue $\rightarrow$ select Sum:
  EXCEL Copy

  =SUBTOTAL(109, [InventoryValue])
  

3. Slicer Dynamic Recalculation

• Go to Table Design > Insert Slicer $\rightarrow$ check ReorderAlert $\rightarrow$ click OK.
• Click "REORDER".
• Key Observation: The Total Row figures instantly update to reflect only the low-stock items. Because Excel uses =SUBTOTAL(109, ...), all "OK" items hidden by the Slicer are automatically excluded from the calculation.

Level 4 Solution: Cross-Table Relational Lookups

In sheet Employee_Records, convert A1:I31 to table EmployeeTable.

Column 1: FullName

Enter in column J:

=[@FirstName] & " " & [@LastName]

Column 2: DeptManager (Relational Lookup to Dept_Heads)

The sheet Dept_Heads maps department heads across row 1 ($A$1:$F$1) and manager names in row 2 ($A$2:$F$2).

Method A: Modern XLOOKUP (Recommended)

=XLOOKUP([@Department], Dept_Heads!$A$1:$F$1, Dept_Heads!$A$2:$F$2, "Unassigned")

Method B: Classic HLOOKUP (Horizontal Matrix Search)

=HLOOKUP([@Department], Dept_Heads!$A$1:$F$2, 2, FALSE)

Column 3: TenureYears

=INT(YEARFRAC([@HireDate], TODAY()))

Level 5 Solution: "It's Just a Dashboard!" Architecture

Building the Operational Micro-Dashboard on SalesTable:

1. Ensure TotalPrice is calculated in column G:
   EXCEL Copy

   =[@Quantity] * [@UnitPrice]
   

2. Press Ctrl + Shift + T to activate the Total Row. In column TotalPrice, set function to Sum (=SUBTOTAL(109, [TotalPrice])).
3. Click inside SalesTable $\rightarrow$ Table Design > Insert Slicer $\rightarrow$ check Category and Governorate.
4. Click the Slicer $\rightarrow$ go to the Slicer ribbon tab $\rightarrow$ set Columns to 3 for Category and 4 for Governorate. Position them neatly above the table headers.
5. Click Governorate = Asyut and Category = Electronics.
6. Audit Verification:
   • The table immediately displays only matching transactions in Asyut.
   • The Total Row displays the exact, filtered revenue for Electronics in Asyut in real time.
   • Takeaway: With zero macros and zero external BI software, the analyst has delivered a responsive, interactive executive dashboard!

💡 Key Architectural Takeaways

1. Calculated Columns Guarantee Consistency: In an Excel Table, 100% of rows share identical formula logic, eliminating formula drift.
2. SUBTOTAL(109, ...) is Essential: Never replace a table's Total Row with =SUM(); standard SUM corrupts filtered reporting by adding hidden rows.
3. Tables Feed Downstream BI: Downstream Pivot Tables (Sheet1) and Power Query scripts refresh dynamically without requiring range changes.