Class 6: Data Validation and Conditional Formatting
Assignment 1: Implement Data Validation
Task: In a spreadsheet, apply data validation to allow only dates in the current month to be entered in the "Date of Sale" column (A1
).
Solution:
- Select A1
.
- Go to Data Validation.
- Set the validation criteria as Date and select "between" the start and end dates of the current month.
- Set an error message that says, "Please enter a valid date within this month."
Assignment 2: Highlight Top Performers
Task: Use conditional formatting to highlight the top 3 performers in a list of students' scores.
Solution:
- Select the range of students' scores.
- Go to Conditional Formatting > Top/Bottom Rules > Top 10 Items.
- Change the number to 3, and select a fill color (e.g., green) to highlight the top 3 scores.
Assignment 3: Apply Color Scales
Task: Use color scales to differentiate between low, medium, and high salaries in a dataset.
Solution:
- Select the salary data range.
- Go to Conditional Formatting > Color Scales.
- Choose a 3-color scale (e.g., red for low, yellow for medium, green for high salaries).
Assignment 1: Data Validation - Drop-Down List for Employee Department
Task: Create a drop-down list for employee departments (Sales, HR, IT, Finance) in cells A2:A10.
Steps:
- Select the Range:
- Highlight cells A2:A10 (where you want the drop-down list).
- Open Data Validation:
- Go to the Data tab in Excel or Google Sheets, then click Data Validation.
- Set the Criteria:
- In the Allow field (Excel) or Criteria field (Google Sheets), select List.
- In the source field, type the values: Sales, HR, IT, Finance.
- Click OK:
- After entering the values, click OK. Now, each cell in the range A2:A10 will have a drop-down list where users can select one of the departments.
Solution:
After applying these steps, you should be able to choose from the list of departments in each cell of the selected range.
Assignment 2: Data Validation - Restrict Age to Between 18 and 60
Task: Set up a rule to allow only ages between 18 and 60 in the range B2:B10 (where you will enter employee ages).
Steps:
- Select the Range:
- Highlight cells B2:B10.
- Open Data Validation:
- Go to Data > Data Validation.
- Set the Validation Rule:
- In the Allow field, select Whole Number (Excel) or Number (Google Sheets).
- Set the Minimum to 18 and Maximum to 60.
- Set an Error Alert:
- Choose a custom error alert (optional). For example, display an error message: "Please enter an age between 18 and 60."
- Click OK:
- After applying these settings, you’ll only be able to enter ages between 18 and 60 in cells B2:B10.
Solution:
Any number outside the range 18–60 will trigger an error message, ensuring only valid ages are entered.
Assignment 3: Conditional Formatting - Highlight Sales Above Target
Task: Highlight all sales amounts greater than $10,000 in the range C2:C10 using conditional formatting.
Steps:
- Select the Range:
- Highlight cells C2:C10 (which contain the sales data).
- Open Conditional Formatting:
- Go to the Home tab (Excel) or Format menu (Google Sheets), and click Conditional Formatting.
- Set the Rule:
- Choose Highlight Cells Rules > Greater Than (Excel), or Greater than (Google Sheets).
- In the box, enter 10000.
- Choose Formatting Style:
- Set the format (e.g., a light green fill or bold text).
- Click OK:
- Now, all sales greater than $10,000 will be highlighted with the selected format.
Solution:
Cells containing sales data greater than $10,000 will be highlighted, making it easy to identify high-performing sales.
Assignment 4: Conditional Formatting - Apply Data Bars to Revenue
Task: Apply data bars to visually represent monthly revenue in the range D2:D12.
Steps:
- Select the Range:
- Highlight cells D2:D12 (where monthly revenue is listed).
- Open Conditional Formatting:
- Go to Conditional Formatting (Excel) or Format > Conditional Formatting (Google Sheets).
- Choose Data Bars:
- In Excel, select Data Bars from the menu.
- In Google Sheets, use a custom number format or select Color Scale to simulate data bars.
- Choose Bar Color and Style:
- Pick a color for the bars (e.g., blue). You can also adjust whether the bars are gradient-filled or solid.
- Click OK:
- The data bars will now appear, with longer bars representing higher revenue.
Solution:
The data bars will display inside the cells, visually indicating the relative value of each cell’s revenue data.
Assignment 5: Conditional Formatting - Color Scale for Exam Scores
Task: Use a color scale to show exam scores (in the range E2:E12) where high scores are green and low scores are red.
Steps:
- Select the Range:
- Highlight cells E2:E12 (which contain the exam scores).
- Open Conditional Formatting:
- Go to Conditional Formatting > Color Scales.
- Choose a Color Scale:
- Select a 3-color scale:
- Green for the highest values.
- Yellow for middle-range scores.
- Red for the lowest scores.
- Click OK:
- The scores will now be color-coded, with green indicating high performance and red indicating low performance.
Solution:
This scale will allow you to quickly assess the distribution of scores, visually indicating who performed well and who did not.
Assignment 6: Data Validation with Custom Error Message
Task: Create a data validation rule that restricts the entry in the "Order Quantity" column (range F2:F10) to values between 1 and 50. Display a custom error message if an invalid entry is made.
Steps:
- Select the Range:
- Highlight cells F2:F10.
- Open Data Validation:
- Go to Data > Data Validation.
- Set Validation Criteria:
- Choose Whole Number (Excel) or Number (Google Sheets) and set the minimum value as 1 and the maximum as 50.
- Set Custom Error Alert:
- Enable the Error Alert option and set the message to: "Please enter a valid quantity between 1 and 50."
- Click OK:
- If a user tries to enter a number outside the specified range, they will receive the custom error message.
Solution:
By applying this, only quantities between 1 and 50 can be entered. Invalid entries will prompt an alert with your custom message.
Assignment 7: Conditional Formatting - Highlight Duplicate Entries
Task: Identify and highlight any duplicate entries in the "Employee ID" column (G2:G15).
Steps:
- Select the Range:
- Highlight cells G2:G15 (where employee IDs are listed).
- Open Conditional Formatting:
- Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Set Formatting Style:
- Choose how you want duplicates to be highlighted (e.g., with red text or a light red fill).
- Click OK:
- Any duplicate employee IDs in the range will be highlighted.
Solution:
All duplicate values in the selected range will be highlighted, making them easy to spot.
Conclusion:
These assignments cover practical applications of Data Validation and Conditional Formatting, helping you gain a deeper understanding of how to control data input and visualize information in a spreadsheet. Each task is designed to reinforce key concepts through real-world scenarios.
