Class 6: Data
Validation and Conditional Formatting
Objective:
Learn how to implement
data validation rules and use conditional formatting to manage and visualize
data efficiently in a spreadsheet.
1. Data Validation
Data Validation is a feature in spreadsheet software that
restricts or controls the type of data that can be entered into a cell or range
of cells. This is useful for ensuring data accuracy and consistency.
Key Concepts of
Data Validation:
- Setting Validation Rules: Data validation rules define what type of
data can be entered into a cell. Common rules include:
- Number Range: Allow only numbers within a specified
range (e.g., between 1 and 100).
- Date Range: Ensure that the data entered is a valid
date and falls within a specific range.
- Text Length: Limit the length of text entered in a
cell (e.g., no more than 10 characters).
- Drop-Down Lists: A drop-down list allows users to select
an option from a predefined list rather than entering data manually. This
ensures that only valid options are selected.
Steps to Apply Data
Validation:
- Select the Range:
- Highlight the cells where you want to
apply the validation rule (e.g., A1
).
- Open the Data Validation Dialog Box:
- In Excel/Google Sheets, go to the Data
tab and click Data Validation.
- Set the Validation Criteria:
- Choose the validation criteria. For
example, select "Whole Number" and set the range between 1 and
100.
- Add an Input Message (Optional):
- You can display a message that informs
users of the validation rule.
- Set an Error Alert:
- Customize the error message that appears
if someone enters invalid data.
Example: Creating a
Drop-Down List
- Select the range of cells where you want
the list.
- Go to Data > Data Validation.
- Under Allow, select List.
- Enter the values for the list, separating
them by commas (e.g., "Option 1, Option 2, Option 3").
2. Conditional
Formatting
Conditional
Formatting allows you to
format cells based on specific conditions or rules. It can visually highlight
important data, making it easier to analyze trends or spot errors.
Key Concepts of
Conditional Formatting:
- Highlight Cells Rules: Use these rules to highlight cells based
on specific conditions, such as:
- Cells greater than a specified value.
- Cells containing specific text or dates.
- Duplicate or unique values.
- Data Bars: Data bars fill the background of a cell
with a gradient that represents the relative value of the data in that
cell. Higher values get longer bars, while smaller values get shorter
bars.
- Color Scales: Color scales apply a gradient of colors
across a range of cells, helping you visualize how data compares to other
data points. For example:
- Green for high values, yellow for medium,
and red for low values.
Steps to Apply
Conditional Formatting:
- Select the Range:
- Highlight the cells to which you want to
apply conditional formatting (e.g., B1
).
- Open Conditional Formatting Menu:
- In Excel, go to the Home tab, then
click Conditional Formatting in the Styles group.
- In Google Sheets, go to Format
> Conditional Formatting.
- Choose a Formatting Rule:
- Select from predefined rules like
"Greater Than," "Less Than," "Between,"
etc.
- Example: Highlight cells greater than 80.
- Customize the Format:
- Set the format that will apply when the
condition is met (e.g., cell color, font style).
- Add More Rules (Optional):
- You can apply multiple conditions to the
same set of data by adding more rules.
Example: Highlight
Cells Based on Values
- Select the range of cells you want to
format (e.g., A1
).
- Go to Conditional Formatting and
choose Highlight Cells Rules > Greater Than.
- Enter a value (e.g., 50) and select a
color to highlight cells with values greater than 50.
Example: Applying
Data Bars
- Select the range of cells.
- In Conditional Formatting, choose Data
Bars.
- Pick a color bar style. Cells will now
display data bars to visually represent their values.
Example: Using
Color Scales
- Select the range of cells (e.g., C1
).
- In Conditional Formatting, choose Color
Scales.
- Choose a color gradient (e.g., green for
high values, red for low values).
Exercise
- Task 1: Apply Data Validation to a Range
of Cells
- Create a drop-down list for a
range of cells to select from the following options: "High,"
"Medium," "Low."
- Restrict a range of cells to allow only
whole numbers between 1 and 100.
- Task 2: Use Conditional Formatting to
Highlight Cells
- Apply conditional formatting to highlight
sales numbers greater than 10,000 in a dataset.
- Use Data Bars to visualize the
monthly revenue data for the past year.
- Apply Color Scales to show the
temperature variations over a week.
