Practice 14: 3D Formulas & Multi-Sheet Consolidation

Key Topics & Core Concepts

3D Cell Referencing: Constructing 3D reference formulas spanning identical worksheets (e.g., =SUM('Quarter 1:Quarter 4'!B5)).
Multi-Sheet Grouping: Grouping multiple worksheets simultaneously to apply uniform structural formulas, labels, and formatting.
Summary Consolidation: Rolling regional or quarterly division performance into an executive summary tab.
Workbook Architecture: Maintaining sheet coordinate parity across multiple departmental branches.

Assignment Overview & Deliverables

In this practice lab, you will consolidate quarterly branch revenue streams across multiple worksheets for Perfect Vacation Rentals using 3D cell references and multi-sheet grouping techniques.

Starter Workbook: Download PerfectRentals-05-1.xlsx
Completed File Name: [your initials] Practice14_Consolidation.xlsx
Active Worksheets: Consolidation, Quarter 1, Quarter 2, Quarter 3, and Quarter 4

Step-by-Step Instructions

Step 1: Open Starter & Group Worksheets

Download and open PerfectRentals-05-1.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice14_Consolidation.xlsx.
Click the Quarter 1 tab. Hold down the Shift key and click the Quarter 4 tab.
Notice the title bar displays [Group], indicating that all four quarterly sheets are grouped.

Step 2: Enter Formulas Across Grouped Worksheets

With the sheets grouped, click cell B9 (Total Rental Revenue).
Type: =SUM(B5:B8) and press Enter.
Drag the fill handle from B9 across through cell E9 to total columns across all four quarters simultaneously.
In cell F5 (Total Category Revenue), type: =SUM(B5:E5) and copy down through cell F9.
Right-click any worksheet tab and select Ungroup Sheets. Click through each quarterly tab to verify that the formulas were applied identically.

Step 3: Construct 3D Reference on the Consolidation Sheet

Click the Consolidation worksheet tab.
Click cell B5 (Cabin Rental Revenue - Consolidated).
Type =SUM(, then click the Quarter 1 tab.
Hold down the Shift key and click the Quarter 4 tab.
Click cell B5 and press Enter.
Verify the formula reads: =SUM('Quarter 1:Quarter 4'!B5).
Copy the formula from cell B5 across through E5, and then down through row 8.

Step 4: Calculate Annual Consolidated Totals

In cell B9 of the Consolidation sheet, enter: =SUM(B5:B8) and copy across to column F.
In cell F5, enter: =SUM(B5:E5) and copy down through row 8.
Verify that cell F9 reflects the grand total across all property types and all four quarters.

Step 5: Apply Accounting Formats & Save Workbook

Format row 5 and row 9 across all sheets with Accounting format ($#,##0). Format inner rows 6 through 8 as Comma format (#,##0).
Apply a top border and double bottom border (Accounting Underline) to row 9 on the Consolidation sheet.
Auto-fit all column widths to prevent truncation 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-Sheet Grouping	Simultaneous formula entry across grouped worksheets (`[Group]`).
3D Referencing	`=SUM('Quarter 1:Quarter 4'!B5)` syntax correctly applied to consolidate data.
Workbook Integrity	Consolidation totals balance with individual quarter aggregations.
Presentation Polish	Consistent accounting number formatting and double underlines.