Practice 2: Formulas, Functions & Sheet Management

Key Topics & Core Concepts

Basic Mathematical Formulas: Constructing arithmetic formulas using addition (+), subtraction (-), multiplication (*), and division (/).
Standard Aggregate Functions: Utilizing SUM, AVERAGE, MIN, and MAX across continuous cell ranges.
Worksheet Management: Renaming sheets, inserting new tabs, applying tab color accents, and reordering worksheet positions.
Number & Table Formatting: Applying Accounting and Currency formats, zero-decimal adjustments, and total row underlines.

Assignment Overview & Deliverables

In this practice exercise, you will practice entering standard mathematical operations, calculating summary statistics across ranges, and organizing multi-sheet workbooks.

Starter Workbook: Download starter_data.xlsx
Completed File Name: [your initials] Practice02_Formulas.xlsx
Worksheets Evaluated: Summary, Transactions, and DepartmentData

Step-by-Step Instructions

Step 1: Open Starter & Save Workbook

Download and open starter_data.xlsx in Excel.
If Protected View is enabled, click Enable Editing.
Save your file as [your initials] Practice02_Formulas.xlsx.

Step 2: Enter Basic Calculations (Transactions Sheet)

Click the Transactions worksheet tab.
In cell E4 (Extended Total), enter a formula multiplying Quantity by Unit Price: =C4*D4.
Double-click or drag the fill handle from E4 down through cell E25 to copy the formula.
In cell F4 (Discounted Total), apply a 5% discount: =E4*(1-0.05) and copy down through F25.

Step 3: Calculate Statistical Summaries (Summary Sheet)

Click the Summary worksheet tab.
In cell B5, calculate the total sales: =SUM(Transactions!E4:E25).
In cell B6, calculate the average transaction: =AVERAGE(Transactions!E4:E25).
In cell B7, calculate the highest transaction: =MAX(Transactions!E4:E25).
In cell B8, calculate the lowest transaction: =MIN(Transactions!E4:E25).

Step 4: Worksheet Management & Tab Formatting

Right-click the Sheet1 tab (if present), rename it to DepartmentData, or ensure tabs are organized in order: Summary, Transactions, DepartmentData.
Right-click the Summary tab, click Tab Color, and choose Dark Blue (CU Denver Navy accent).
Right-click the Transactions tab and set Tab Color to Gold.

Step 5: Apply Number Formatting & Clean Layout

On the Summary sheet, format cells B5:B8 as Currency ($#,##0.00).
On the Transactions sheet, format columns D, E, and F as Currency.
Ensure column widths are auto-fitted so no text or numbers are truncated (no ### display errors).
Save your workbook.

Practice exercises are formative and prepare you for homework submissions.

Key Skills Evaluated

Skill Area	Verification Target
Basic Operators	Multiplication and percentage discount formulas correctly structured.
Summary Functions	`SUM`, `AVERAGE`, `MIN`, and `MAX` correctly reference cross-sheet ranges.
Workbook Structure	Tab names, order, and tab colors configured cleanly.
Number Formatting	Currency format with 2 decimal places applied to all monetary outputs.