Last Day

Exam Scope: Empowerment Technologies (4th Quarter)

The exam is a 60-item multiple-choice test divided into the following key competencies:

1. Data Validation (9 items): Configuring rules to ensure data integrity, guiding user input (e.g., dropdowns, date/text limits), and handling invalid entries.

2. Sorting Datasets (7 items): Using multi-level sorting, protecting headers, and understanding "Sort sheet" vs. "Sort range".

3. Filtering Datasets (10 items): Managing filters and filter views, applying specific conditions (e.g., "Text starts with"), and sequential filtering

4. Spreadsheet Functions (19 items): Utilizing text, date, calculation, and counting functions with correct cell referencing.

5. Pivot Tables (15 items): Creating and customizing tables using Rows, Columns, Values, and Filters to summarize large datasets.

Student Reviewer Highlights

1. Data Validation & Integrity

• Dropdowns: Use these to restrict choices to a specific list (e.g., 'Project Status').

• Error Handling: "Reject input" blocks invalid data entirely, while "Show a warning" allows the value but flags it with a red triangle.

• Help Text: You can provide instructions to users before they type by enabling "Show help text" in the validation sidebar.

• Criteria Examples: Setting text length (e.g., exactly 5 characters for ID numbers) or ensuring a date is not in the past.

2. Sorting & Filtering Data

• Sort Sheet vs. Sort Range: "Sort sheet" reorders every column in the tab, while "Sort range" only reorders selected cells.

• Multi-Level Sort: Use "Advanced range sorting options" to sort by one column (e.g., Department) and then another (e.g., Last Name).

• Filter Views: Use these to filter data for yourself without changing the view for other collaborators.

• Active Filters: A column has an active filter when its drop-down icon changes to a funnel icon.

3. Pivot Tables

• Editor Areas:

  • Rows: Lists items vertically on the left.

  • Columns: Lists items horizontally across the top.

  • Values: The data being calculated (SUM is the default for numbers, but can be changed to AVERAGE, COUNT, etc.).

  • Filters: Limits the data shown in the table.

• Data Preparation: Functions like TRIM are essential before pivoting to ensure categories like " John" and "John" aren't treated as different entries.

4. Essential Functions

• Text: =LEFT(text, num), =RIGHT(text, num), =LEN(text) (counts characters), =TRIM(text) (removes extra spaces), and =SPLIT(text, delimiter).

• Date: =YEAR(date), =MONTH(date), and =DAY(date) to extract specific parts of a date.

• Calculations: =SUM(range), =AVERAGE(range), =MIN(range), and =MAX(range).

• Counting: =COUNT(range) for numbers only; =COUNTA(range) for any non-empty cell.

• Referencing: Use absolute references (e.g., $B$1) to keep a specific cell fixed when dragging formulas.