Practice 8: Professional Table Formatting & Structured Referencing

Key Topics & Core Concepts

Excel Table Conversion: Converting standard cell grids into dynamic Excel Table objects (Ctrl + T).
Structured References: Writing formulas using named table column references (e.g., [@Quantity] * [@UnitPrice]) rather than relative cell addresses.
Table Styles & Banding: Applying table style presets, alternating banded rows, and highlighted first/last column accents.
Dynamic Total Row: Toggling the built-in Table Total Row and configuring subtotal functions (SUM, AVERAGE, COUNT).

Assignment Overview & Deliverables

In this practice exercise, you convert standard vacation rental transaction ranges into an official Excel Table object, utilize structured references for calculated columns, and format the table with custom banding styles and dynamic total aggregations.

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

Step-by-Step Instructions

Step 1: Open Starter & Convert to Official Excel Table

Download and open PerfectRentals-04.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice08_Tables.xlsx.
Click anywhere inside the transaction data range on the Rental Transactions worksheet (cells A4:F30).
Press Ctrl + T (or go to Insert > Table). Ensure "My table has headers" is checked and click OK.
On the Ribbon's Table Design tab, locate the Table Name box on the far left and rename the table to tblRentals.

Step 2: Apply Structured References in Calculated Columns

In cell G4, type the new column header: Gross Revenue and press Enter. Notice that the table automatically expands to incorporate column G.
In cell G5, type =[@Nights]*[@NightlyRate] and press Enter.
Observe that Excel automatically populates the formula down the entire column using calculated column automation.
In cell H4, add another header: Service Fee. In H5, calculate a 12% fee: =[@[Gross Revenue]]*0.12 and press Enter.

Step 3: Format Table Styles and Banding

Click anywhere inside tblRentals to activate the Table Design tab.
In the Table Styles gallery, choose Table Style Medium 2 (or a navy/blue medium accent style).
In the Table Style Options group, verify that Header Row and Banded Rows are checked.
Check the box for First Column to apply bold formatting to transaction ID identifiers.

Step 4: Enable and Configure Total Row

On the Table Design tab, check the Total Row checkbox.
Scroll to the bottom of the table. In the Total row for Gross Revenue (column G), click the dropdown arrow and select Sum.
In the Total row for Service Fee (column H), click the dropdown and select Sum.
In the Total row for Nights (column E), select Average.

Step 5: Number Formatting & Save Workbook

Select the revenue and fee columns (columns F, G, and H) and format as Currency ($#,##0.00).
Format the Nights column as Number with zero decimal places (and 1 decimal place on its average subtotal).
Double-click all column dividers to auto-fit widths and eliminate any text clipping.
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
Table Object Conversion	Data range converted to named table (`tblRentals`) with headers.
Structured References	Formulas built using `[@Column]` syntax and automatically calculated across rows.
Table Styling	Banded rows and First Column styles active with professional table theme.
Total Row Aggregations	Dynamic Total Row enabled with SUBTOTAL aggregations for Sum and Average.