Practice 10: Total Rows & Multi-Criteria Filtering

Key Topics & Core Concepts

Table Total Row Aggregations: Utilizing the dynamic Total Row with SUBTOTAL aggregations (SUM, AVERAGE, COUNT) that adjust when filters are applied.
Multi-Column Sorting: Building multi-level sort hierarchies (e.g., sort primary by Property Type, then secondary by Nightly Rate descending).
Text and Numeric Filters: Applying customized criteria filters (Begins With, Contains, Greater Than, and Above Average).
Date Filtering: Filtering records by dynamic date intervals (This Month, Last Quarter, or Year-to-Date).

Assignment Overview & Deliverables

In this practice exercise, you will apply multi-level data sorting, configure auto-calculating Table Total rows, and apply compound text, date, and numeric filters to Perfect Vacation Rentals transaction records.

Starter Workbook: Download PerfectRentals-04.xlsx
Completed File Name: [your initials] Practice10_Filtering.xlsx
Active Worksheets: Transactions and Filtered Summary

Step-by-Step Instructions

Step 1: Open Starter & Convert to Table

Download and open PerfectRentals-04.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice10_Filtering.xlsx.
Click inside the data range on the Transactions sheet and press Ctrl + T to ensure the range is formatted as an official Excel Table named tblRentalTransactions.

Step 2: Configure Multi-Level Sorting

Click anywhere inside tblRentalTransactions.
On the Ribbon, go to Data > Sort.
Set the primary sort level: Sort by Property Location, Sort On Cell Values, Order A to Z.
Click Add Level. Set the secondary sort: Then by Total Revenue, Sort On Cell Values, Order Largest to Smallest.
Click OK. Verify that property records are cleanly grouped alphabetically by location and ranked by revenue within each market.

Step 3: Enable Dynamic Total Row

Click inside the table and open the Table Design tab.
Check the Total Row box.
In the Total row for Total Revenue, select Sum from the dropdown.
In the Total row for Nights Booked, select Sum.
In the Total row for Nightly Rate, select Average.

Step 4: Apply Multi-Criteria Filters

Click the filter dropdown arrow on the Rental Tier column header.
Uncheck (Select All) and check only Premium and Deluxe.
Click the filter dropdown on the Nightly Rate column header. Go to Number Filters > Greater Than Or Equal To... and enter 250.
Notice how the Total Row subtotals automatically update to reflect only the currently visible, filtered records.

Step 5: Number Formatting & Save Workbook

Ensure all revenue and fee columns display as Currency ($#,##0.00).
Confirm that column widths are auto-fitted and no cells display ### errors.
Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Multi-Level Sort	Table sorted by Location (A-Z) and Total Revenue (Largest to Smallest).
Dynamic Subtotals	Total Row configured with `SUBTOTAL` functions that react to active filters.
Compound Filtering	Multi-column filters isolating high-value tiers and minimum nightly rates.
Presentation Polish	Proper currency formatting and clean layout without column truncation.