Session 5: Working with Ranges and Basic Data Validation
Objective: Teach students how to work with cell ranges effectively, introduce basic
data validation techniques, and provide practice with sorting and filtering data.
Duration: 1 hour
1. Review of Session 4 (5 minutes)
- Quick recap of key points from the previous session.
- Answer any lingering questions.
2. Working with Cell Ranges (15 minutes)
- Selecting Ranges:
- How to select a single range of cells using the mouse or keyboard shortcuts
(Shift + Arrow keys).
- Selecting non-adjacent ranges using Ctrl + Click.
- Naming Ranges:
- How to create named ranges for easier reference.
- Example: Naming the range A1:A10 as "SalesData".
- Using the Name Box to create and navigate named ranges.
- Using Named Ranges in Formulas:
- How to use named ranges in formulas instead of cell references.
- Example: `=SUM(SalesData)` instead of `=SUM(A1:A10)`.
3. Basic Data Validation (20 minutes)
- Introduction to Data Validation:
- Explanation of data validation and its importance in ensuring data accuracy.
- Setting Up Data Validation:
- Step-by-step guide to setting up basic data validation rules:
1. Select the cell(s) where you want to apply data validation.
2. Go to the "Data" tab and click on "Data Validation".
3. In the "Settings" tab, choose the validation criteria (e.g., whole number,
decimal, list, date, time, text length).
4. Set specific conditions (e.g., between, greater than, less than).
- Examples of Data Validation:
- Restricting entries to whole numbers between 1 and 100.
- Creating a drop-down list for selecting predefined options.
- Custom Error Messages:
- How to set up input and error messages to guide users:
1. In the "Input Message" tab, enter a title and message that will appear when
the cell is selected.
2. In the "Error Alert" tab, enter a title and message that will appear if invalid
data is entered.
- Clearing Data Validation:
- How to remove data validation rules from cells.
4. Sorting and Filtering Data (15 minutes)
- Sorting Data:
- How to sort data in ascending or descending order.
- Sorting by multiple columns.
- Example: Sorting a list of names alphabetically and then by age.
- Filtering Data:
- Using the AutoFilter feature to filter data based on specific criteria.
- Example: Filtering a list of sales records to show only those above a certain
amount.
- Clearing filters to view all data again.
5. Hands-on Practice (10 minutes)
- Create a new worksheet and enter sample data (e.g., employee details with
columns for Name, Age, Department, Salary).
- Apply data validation to ensure the Age column only accepts numbers between
18 and 65.
- Create a drop-down list for the Department column with options like "HR",
"Sales", "IT", "Finance".
- Sort the data by Department and then by Salary.
- Apply filters to show employees in the "Sales" department with a salary above
$50,000.
6. Detailed Steps and Demonstrations:
1. Selecting Ranges:
1. Click and drag to select a range with the mouse.
2. Use Shift + Arrow keys to extend the selection.
3. Use Ctrl + Click to select multiple non-adjacent ranges.
2. Naming Ranges:
1. Select the range you want to name.
2. Click on the Name Box (next to the formula bar), type a name, and press Enter.
3. To use the named range in a formula, type the name instead of the cell
references (e.g., `=SUM(SalesData)`).
3. Setting Up Data Validation:
1. Select the cell(s) for data validation.
2. Go to the "Data" tab, click "Data Validation", and choose the criteria.
3. For a whole number range, select "Whole number" and set the conditions (e.g.,
between 1 and 100).
4. For a drop-down list, select "List" and enter the options separated by commas.
5. Add input messages and error alerts to guide users.
4. Sorting Data:
1. Select the data range you want to sort.
2. Go to the "Data" tab and click "Sort".
3. Choose the column to sort by, and select ascending or descending order.
4. To sort by multiple columns, click "Add Level" and specify additional criteria.
5. Filtering Data:
1. Select the data range and go to the "Data" tab.
2. Click "Filter" to enable filtering.
3. Use the drop-down arrows in the column headers to set filter criteria.
4. To clear filters, click "Clear" in the "Data" tab.
6. Hands-on Practice:
- Enter sample data for employees:
A1: Name
A2: John Doe
A3: Jane Smith
A4: Emily Davis
B1: Age
B2: 25
B3: 35
B4: 42
C1: Department
C2: Sales
C3: HR
C4: IT
D1: Salary
D2: 55000
D3: 48000
D4: 62000
- Apply data validation for Age and Department columns as described.
- Sort data by Department and then by Salary.
- Apply filters to show specific criteria (e.g., Sales department with Salary >
$50,000).
