"Excel Conditional Formatting Mastery: Answers to Common Queries"

 

Excel Conditional Formatting Mastery

Answers to Common Queries 

Assignment 1: Highlighting Sales Figures

Objective: Practice applying Highlight Cell Rules to highlight specific values in a dataset.

Instructions:

1.   Download or create a dataset of sales figures for a company over several months. Include columns such as Month, Sales, and Target Sales.

2.   Apply conditional formatting to highlight the following:

o    Sales Greater Than Target: Use a Green fill to highlight cells where sales exceed the target.

o    Sales Less Than Target: Use a Red fill to highlight cells where sales are below the target.

3.   Ensure the formatting is applied to the Sales column only.

Dataset Example:

Month	Sales	Target Sales
January	1500	1200
February	1000	1200
March	1800	1500
April	800	1000

Solution:

1. Dataset Creation:

First, create or download a dataset that includes columns such as Month, Sales, and Target Sales. Here is an example dataset:

Month	Sales	Target Sales
January	1500	1200
February	1000	1200
March	1800	1500
April	800	1000

2. Applying Conditional Formatting:

To apply conditional formatting to highlight sales figures based on their comparison to the target:

Step 1: Select the Range of Cells (Sales Column)

• Highlight the Sales column (e.g., B2:B5 in the example dataset).

Step 2: Apply Conditional Formatting for Sales Greater Than Target (Green Fill)

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• Select Highlight Cell Rules and then choose Greater Than.
• In the dialog box that appears:
• Type =C2 (since C2 represents the Target Sales for January).
• In the drop-down list next to the text box, choose Custom Format.
• Select Fill, choose a Green color, and click OK.
• Click OK to apply the rule.

Now, cells where Sales are greater than the Target Sales will be highlighted with a green fill.

Step 3: Apply Conditional Formatting for Sales Less Than Target (Red Fill)

• With the Sales column still selected, go back to Conditional Formatting in the Home tab.
• Select Highlight Cell Rules and choose Less Than.
• In the dialog box that appears:
• Type =C2 again (for the comparison with Target Sales).
• In the drop-down list, choose Custom Format.
• Select Fill, choose a Red color, and click OK.
• Click OK to apply the rule.

Now, cells where Sales are less than Target Sales will be highlighted with a red fill.

3. Reviewing the Result:

After applying both conditional formatting rules, your Sales column will look like this:

Month	Sales	Target Sales
January	1500	1200
February	1000	1200
March	1800	1500
April	800	1000

Final Notes:

• The conditional formatting helps visually distinguish the months where Sales exceed the Target (green) and where Sales are below the Target (red).
• You can modify the formatting (e.g., choose different colors) based on personal preferences or specific requirements.

 

 

 

 

Assignment 2: Top/Bottom Performers

Objective: Apply Top/Bottom Rules to find the best and worst performers in a list of students' scores.

Instructions:

1.   Create a dataset with Student Name and Score columns.

2.   Apply conditional formatting to:

o    Highlight the Top 3 Scores in Green.

o    Highlight the Bottom 3 Scores in Red.

3.   Ensure the formatting is dynamic, so if scores change, the top/bottom performers are updated automatically.

Dataset Example:

Student Name	Score
John	85
Alice	92
Bob	78
Claire	88
David	95
Emily	72

Solution for Assignment 2: Top/Bottom Performers

1. Dataset Creation:

First, create a dataset with Student Name and Score columns. Here’s an example dataset:

Student Name	Score
John	85
Alice	92
Bob	78
Claire	88
David	95
Emily	72

2. Apply Conditional Formatting for Top/Bottom Scores:

Step 1: Select the Range of Cells (Scores Column)

• Highlight the Score column (e.g., B2:B7 in this case).

Step 2: Apply Conditional Formatting for Top 3 Scores (Green Fill)

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• Select Top/Bottom Rules, and then choose Top 10 Items....
• In the dialog box that appears:
• Change the number 10 to 3 (to highlight the top 3 scores).
• In the drop-down next to "with," select Green Fill with dark green text or any format of your choice.
• Click OK to apply the formatting.

Now, the Top 3 Scores will be highlighted in green.

Step 3: Apply Conditional Formatting for Bottom 3 Scores (Red Fill)

• With the Score column still selected, go back to Conditional Formatting in the Home tab.
• Select Top/Bottom Rules, and then choose Bottom 10 Items....
• In the dialog box that appears:
• Change the number 10 to 3 (to highlight the bottom 3 scores).
• In the drop-down next to "with," select Red Fill with dark red text or any format of your choice.
• Click OK to apply the formatting.

Now, the Bottom 3 Scores will be highlighted in red.

3. Reviewing the Result:

After applying the conditional formatting, your dataset should look like this:

Student Name	Score
John	85
Alice	92
Bob	78
Claire	88
David	95
Emily	72

• Alice and David have the top 3 scores and will be highlighted in green.
• Bob and Emily have the bottom 3 scores and will be highlighted in red.

Final Notes:

• The conditional formatting rules are dynamic, meaning that if the Scores change, the top and bottom performers will be automatically updated.
• The Top 3 Scores will always be highlighted in green, and the Bottom 3 Scores will always be highlighted in red, regardless of how the dataset is updated.

Assignment 3: Data Bars to Represent Sales Progress

Objective: Practice using Data Bars for visual representation of data.

Instructions:

1.   Create a dataset that tracks the progress of sales throughout the year. Include columns like Month and Sales.

2.   Apply Data Bars to the Sales column to visually represent the relative values of each month. Use a blue gradient for the bars.

3.   Ensure the data bars are clear, with higher values having longer bars.

Dataset Example:

Month	Sales
January	1000
February	1200
March	1400
April	1100
May	1300

Solution for Assignment 3: Data Bars to Represent Sales Progress

1. Dataset Creation:

First, create a dataset that tracks the Sales progress throughout the year. Here's an example dataset:

Month	Sales
January	1000
February	1200
March	1400
April	1100
May	1300

2. Apply Data Bars to the Sales Column:

Step 1: Select the Range of Cells (Sales Column)

• Highlight the Sales column (e.g., B2:B6).

Step 2: Apply Data Bars

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• From the dropdown, select Data Bars and then choose More Rules... at the bottom.

Step 3: Customize the Data Bars (Blue Gradient)

• In the New Formatting Rule dialog that appears:
• Under Select a Rule Type, choose Format cells based on their values.
• Under Format Style, choose Gradient Fill.
• Choose Blue under Minimum and Maximum.
• For Minimum, select Lowest Value (or Automatic) to set the shortest bar for the lowest sales value.
• For Maximum, select Highest Value (or Automatic) to set the longest bar for the highest sales value.
• In the Bar Appearance section, ensure that the Fill Color is set to a blue gradient.
• Optionally, you can adjust the bar direction and border settings based on your preferences (this is optional).

Step 4: Apply the Rule

• Click OK to apply the Data Bars to the Sales column.

3. Reviewing the Result:

After applying the data bars, your dataset should look visually enhanced with bars representing the relative sales values. Here's what the visual representation would look like:

Month	Sales	Data Bars (Visual Representation)
January	1000	▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
February	1200	▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
March	1400	▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
April	1100	▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
May	1300	▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓

• March will have the longest data bar because it has the highest sales value (1400).
• January will have the shortest data bar because it has the lowest sales value (1000).
• The bars will be blue in color, with a gradient to indicate the relative sales performance.

Final Notes:

• The Data Bars visually represent the sales progress, with the length of each bar proportional to the sales value in each month.
• The data bars automatically adjust if the sales data changes, ensuring that the relative values remain accurate. Higher sales will have longer bars, and lower sales will have shorter bars.

Assignment 4: Color Scales for Profit Distribution

Objective: Apply Color Scales to show the distribution of profits.

Instructions:

1.   Create a dataset that tracks profits for a company in different regions. Include columns like Region and Profit.

2.   Apply Color Scales to the Profit column to visually represent the profit distribution.

o    Use a Red-Yellow-Green scale, where red indicates low profits and green indicates high profits.

3.   Ensure that cells with higher profits are shaded green, and cells with lower profits are shaded red.

Dataset Example:

Region	Profit
North	5000
South	12000
East	8000
West	3000

Solution for Assignment 4: Color Scales for Profit Distribution

1. Dataset Creation:

First, create the dataset that tracks profits for a company in different regions. Here's an example dataset:

Region	Profit
North	5000
South	12000
East	8000
West	3000

2. Apply Color Scales to the Profit Column:

Step 1: Select the Range of Cells (Profit Column)

• Highlight the Profit column (e.g., B2:B5).

Step 2: Apply Conditional Formatting with Color Scales

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• From the dropdown, select Color Scales.
• Choose the Red-Yellow-Green Color Scale. This scale will automatically color the cells from red (low values) to green (high values), with yellow representing mid-range values.

Step 3: Customizing the Color Scale (Optional)

To fine-tune the color scale:

• After selecting Red-Yellow-Green Color Scale, click on Manage Rules.
• In the Conditional Formatting Rules Manager:
• Select the rule you just applied.
• Click Edit Rule.
• Here, you can adjust the Minimum, Midpoint, and Maximum values if you want a more customized scale. For example, you can set:
• Minimum: Red (set to the lowest value, e.g., 3000)
• Maximum: Green (set to the highest value, e.g., 12000)
• Midpoint: Yellow (could be set to the average or median value of profits, e.g., 8000).

Step 4: Apply the Rule

• Click OK to apply the color scale to the Profit column.

3. Reviewing the Result:

After applying the color scale, your dataset will visually represent the profit distribution using a gradient from red to green. Here's how the result would look visually:

Region	Profit	Color Scale (Visual Representation)
North	5000	🟥🟥🟥🟨🟩
South	12000	🟩🟩🟩🟨🟥
East	8000	🟨🟨🟨🟩🟥
West	3000	🟥🟥🟥🟨🟩

• West would be shaded red (indicating the lowest profit of 3000).
• South would be shaded green (indicating the highest profit of 12000).
• East and North would be shaded in varying shades of yellow and green depending on their position relative to the highest and lowest profit.

Final Notes:

• Red represents low profits, and green represents high profits.
• The Yellow represents values in between, giving a clear visual cue of where each region's profit stands.
• The color scale dynamically updates as profits change, making it easy to track the profit distribution across regions.

Assignment 5: Icon Sets for Financial Performance

Objective: Use Icon Sets to represent performance indicators.

Instructions:

1.   Create a dataset for a financial report, with columns Month, Revenue, and Profit.

2.   Apply an Icon Set to the Profit column, using a Traffic Light icon set (red, yellow, green) to represent:

o    Green: Profit greater than 20%.

o    Yellow: Profit between 10% and 20%.

o    Red: Profit less than 10%.

3.   Adjust the thresholds to match your profit ranges.

Dataset Example:

Month	Revenue	Profit
January	50000	18%
February	60000	25%
March	55000	8%
April	65000	15%

Solution for Assignment 5: Icon Sets for Financial Performance

1. Dataset Creation:

Create the dataset with the columns Month, Revenue, and Profit. Here’s an example:

Month	Revenue	Profit
January	50000	18%
February	60000	25%
March	55000	8%
April	65000	15%

2. Apply Icon Set to the Profit Column:

Step 1: Select the Range of Cells (Profit Column)

• Highlight the Profit column (e.g., C2:C5).

Step 2: Apply Conditional Formatting with Icon Sets

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• From the dropdown menu, select Icon Sets.
• Choose the Traffic Light (3 Icons) option, which displays three icons: Red, Yellow, and Green.

Step 3: Customize the Icon Set (Adjust the Thresholds)

To make the traffic light icons reflect the profit ranges:

1.   After applying the Traffic Light icon set, click on Conditional Formatting again and select Manage Rules.

2.   In the Conditional Formatting Rules Manager, find the rule you just applied and click Edit Rule.

3.   In the Edit Formatting Rule dialog box, you’ll see options to modify the icon set:

o    Type: Set this to Number for defining the percentage thresholds.

o    Green Icon (Profit > 20%): Set the Value to 20%.

o    Yellow Icon (Profit between 10% and 20%): Set the Value to 10%.

o    Red Icon (Profit < 10%): Set the Value to 0%.

Ensure that the "Show Icon Only" option is unchecked so that the actual values are displayed along with the icons.

Step 4: Apply the Rule

• Click OK to apply the icon set and adjust the thresholds.

3. Reviewing the Result:

The dataset will now visually represent the profit performance for each month using traffic light icons (Red, Yellow, and Green):

Month	Revenue	Profit	Performance Icon
January	50000	18%	🟨
February	60000	25%	🟩
March	55000	8%	🟥
April	65000	15%	🟨

• February has 25% profit, which is greater than 20%, so it's highlighted with a Green icon (🟩).
• January has 18% profit, which is between 10% and 20%, so it's highlighted with a Yellow icon (🟨).
• March has 8% profit, which is less than 10%, so it's highlighted with a Red icon (🟥).
• April has 15% profit, which is between 10% and 20%, so it's highlighted with a Yellow icon (🟨).

Final Notes:

• The Traffic Light icon set now visually shows the performance of each month based on the profit percentage.
• The icons will update automatically if the Profit values change, making it easy to track financial performance over time.
• This visual representation helps stakeholders quickly identify which months had strong, moderate, or poor profit performance.

Assignment 6: Duplicate Value Detection

Objective: Practice using conditional formatting to find duplicate values.

Instructions:

1.   Create a dataset that includes a list of Employee IDs in a company.

2.   Apply conditional formatting to highlight any duplicate Employee ID in the list using a light red fill with dark red text.

3.   Make sure that only the duplicate values are highlighted and not the first occurrences.

Dataset Example:

Employee ID
101
102
103
104
101
105

Solution for Assignment 6: Duplicate Value Detection

1. Dataset Creation:

Create a dataset with the Employee ID column. Here’s an example:

Employee ID
101
102
103
104
101
105

2. Apply Conditional Formatting to Detect Duplicates:

Step 1: Select the Range of Cells (Employee ID Column)

• Highlight the Employee ID column (e.g., A2:A6).

Step 2: Apply Conditional Formatting for Duplicates

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• From the dropdown menu, choose Highlight Cells Rules.
• Select Duplicate Values.

Step 3: Customize the Formatting for Duplicates

• In the Duplicate Values dialog box, make sure the Duplicate option is selected.
• In the drop-down menu next to with, select the Custom Format option to customize the highlight style.

Step 4: Set the Format for Duplicates

• In the Format Cells window, go to the Fill tab and choose a light red fill color.
• Go to the Font tab and choose dark red text.
• Click OK to apply the formatting.

Step 5: Reviewing the Result

Now, the duplicate values will be highlighted with a light red fill and dark red text.

Here’s how the data will look after applying the formatting:

Employee ID
101
102
103
104
101
105

• The Employee ID 101 appears twice, so both instances of 101 will be highlighted with the custom formatting, while the first occurrence of 101 will not be highlighted.
• The rest of the IDs will remain unaffected.

Final Notes:

• Conditional Formatting will highlight only the duplicate values, and the first occurrence of each duplicate value will not be highlighted.
• If you add more Employee IDs or change any existing ones, the formatting will automatically update to reflect any new duplicates.

Assignment 7: Formula-Based Conditional Formatting

Objective: Use formulas for more complex conditional formatting rules.

Instructions:

1.   Create a dataset of employee Name, Hours Worked, and Overtime Hours.

2.   Apply conditional formatting based on the following:

o    Highlight the Overtime Hours column in Green if the Hours Worked is greater than 40 (normal working hours).

o    Use a formula to apply this conditional formatting. The formula for the rule will be: =B2>40 where B2 refers to the Hours Worked cell.

3.   If overtime hours exceed 10 hours, highlight it in Yellow.

Dataset Example:

Name	Hours Worked	Overtime Hours
John	45	5
Alice	38	0
Bob	42	4
Claire	46	11

Solution for Assignment 7: Formula-Based Conditional Formatting

1. Dataset Creation:

Create a dataset with Employee Name, Hours Worked, and Overtime Hours columns. Here’s an example:

Name	Hours Worked	Overtime Hours
John	45	5
Alice	38	0
Bob	42	4
Claire	46	11

2. Apply Conditional Formatting Using Formulas:

Step 1: Select the Overtime Hours Column

• Highlight the Overtime Hours column (e.g., cells C2:C5).

Step 2: Conditional Formatting for Overtime Hours Based on Hours Worked > 40

• Go to the Home tab in Excel.
• In the Styles group, click on Conditional Formatting.
• Select New Rule.
• Choose Use a formula to determine which cells to format.

Step 3: Enter the Formula for Overtime Hours (Green)

• In the formula field, enter the formula:
  =B2>40
• This formula checks if the Hours Worked (Column B) is greater than 40.
• If true, the rule will be applied to the Overtime Hours (Column C).

Step 4: Set Format to Green

• Click on the Format button, go to the Fill tab, and select a green color.
• Click OK to apply the formatting.

Step 5: Apply Conditional Formatting for Overtime Hours > 10 (Yellow)

• While the Overtime Hours column (cells C2:C5) is still selected, go back to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• Enter the formula:
  =C2>10
• This formula checks if the Overtime Hours (Column C) exceeds 10 hours.

Step 6: Set Format to Yellow

• Click on the Format button, go to the Fill tab, and select a yellow color.
• Click OK to apply the formatting.

3. Review the Results:

The conditional formatting will be applied as follows:

• For John (Hours Worked: 45, Overtime Hours: 5):
• Since John’s hours worked (45) are greater than 40, the Overtime Hours (5) will be highlighted in green.
• For Alice (Hours Worked: 38, Overtime Hours: 0):
• Since Alice’s hours worked are not greater than 40, no formatting will be applied to Overtime Hours (0).
• For Bob (Hours Worked: 42, Overtime Hours: 4):
• Since Bob’s hours worked (42) are greater than 40, the Overtime Hours (4) will be highlighted in green.
• For Claire (Hours Worked: 46, Overtime Hours: 11):
• Since Claire’s hours worked (46) are greater than 40, the Overtime Hours (11) will be highlighted in green.
• Since Claire’s overtime hours (11) exceed 10, the Overtime Hours (11) will also be highlighted in yellow.

Final View of the Dataset:

Name	Hours Worked	Overtime Hours
John	45	5 (Green)
Alice	38	0
Bob	42	4 (Green)
Claire	46	11 (Yellow & Green)

Notes:

• The green fill highlights the Overtime Hours when the Hours Worked are greater than 40.
• The yellow fill highlights Overtime Hours that exceed 10 hours, regardless of the Hours Worked.
• The conditional formatting will update automatically if the values in the dataset change.

Assignment 8: Using Conditional Formatting for Dates

Objective: Apply Date-Based Conditional Formatting.

Instructions:

1.   Create a dataset that tracks important dates (e.g., Meeting Dates, Due Dates).

2.   Apply conditional formatting to:

o    Highlight dates that are within the next 7 days in Yellow.

o    Highlight dates that are older than today in Red.

3.   Ensure the date-based rules are dynamic and update as the date changes.

Dataset Example:

Task	Due Date
Project Deadline	12/18/2024
Team Meeting	12/20/2024
Client Feedback	12/22/2024
Code Review	12/15/2024

Solution for Assignment 8: Using Conditional Formatting for Dates

1. Dataset Creation:

Create a dataset that includes tasks and their due dates. Here’s an example:

Task	Due Date
Project Deadline	12/18/2024
Team Meeting	12/20/2024
Client Feedback	12/22/2024
Code Review	12/15/2024

2. Apply Conditional Formatting Based on Dates:

Step 1: Highlight Dates Within the Next 7 Days (Yellow)

• Select the "Due Date" column (e.g., cells B2:B5).
• Go to the Home tab.
• In the Styles group, click Conditional Formatting.
• Select New Rule.
• Choose Use a formula to determine which cells to format.
• In the formula box, enter the following formula to highlight dates that are within the next 7 days:

scss

Copy code

=AND(B2>=TODAY(), B2<=TODAY()+7)

• TODAY() returns the current date.
• B2>=TODAY() checks if the due date is today or in the future.
• B2<=TODAY()+7 checks if the due date is within the next 7 days.
• Click on the Format button, go to the Fill tab, and choose a yellow fill color.
• Click OK to apply the formatting.

Step 2: Highlight Dates Older Than Today (Red)

• Select the "Due Date" column (e.g., cells B2:B5) again.
• Go back to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• In the formula box, enter the following formula to highlight dates that are older than today:

scss

Copy code

=B2<TODAY()

• B2<TODAY() checks if the due date is before the current date.
• Click on the Format button, go to the Fill tab, and select a red fill color.
• Click OK to apply the formatting.

3. Review the Results:

After applying the rules, the conditional formatting will be dynamic and automatically update as the current date changes. Here's what you should expect:

• Project Deadline (12/18/2024): If the current date is earlier than 12/18/2024 and within the next 7 days, it will be highlighted in yellow.
• Team Meeting (12/20/2024): If the current date is earlier than 12/20/2024 and within the next 7 days, it will be highlighted in yellow.
• Client Feedback (12/22/2024): If the current date is earlier than 12/22/2024 and within the next 7 days, it will be highlighted in yellow.
• Code Review (12/15/2024): If the current date is later than 12/15/2024, it will be highlighted in red.

Final View of the Dataset:

Task	Due Date
Project Deadline	12/18/2024 (Yellow)
Team Meeting	12/20/2024 (Yellow)
Client Feedback	12/22/2024 (Yellow)
Code Review	12/15/2024 (Red)

Notes:

• The yellow fill highlights tasks whose due dates are within the next 7 days from today.
• The red fill highlights tasks whose due dates are earlier than today.
• The conditional formatting will update automatically as the current date changes. For example, if the current date moves closer to the 12/18/2024, the Project Deadline will be highlighted in yellow.

Assignment 9: Conditional Formatting for Age Groups

Objective: Use conditional formatting based on logical conditions.

Instructions:

1.   Create a dataset of Name and Age of employees in a company.

2.   Apply conditional formatting to:

o    Highlight employees under the age of 30 in Blue.

o    Highlight employees between 30-50 in Orange.

o    Highlight employees over the age of 50 in Red.

Dataset Example:

Name	Age
John	25
Alice	35
Bob	60
Claire	40

---

Solution for Assignment 9: Conditional Formatting for Age Groups

1. Dataset Creation:

Create a dataset that includes employee names and their ages. Here’s an example dataset:

Name	Age
John	25
Alice	35
Bob	60
Claire	40

2. Apply Conditional Formatting Based on Age Groups:

Step 1: Highlight Employees Under the Age of 30 (Blue)

• Select the "Age" column (e.g., cells B2:B5).
• Go to the Home tab.
• In the Styles group, click Conditional Formatting.
• Select New Rule.
• Choose Use a formula to determine which cells to format.
• In the formula box, enter the following formula to highlight employees under the age of 30:

Copy code

=B2<30

• B2<30 checks if the age is less than 30.
• Click on the Format button, go to the Fill tab, and choose a blue fill color.
• Click OK to apply the formatting.

Step 2: Highlight Employees Between the Ages of 30-50 (Orange)

• Select the "Age" column (e.g., cells B2:B5) again.
• Go back to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• In the formula box, enter the following formula to highlight employees whose age is between 30 and 50:

scss

Copy code

=AND(B2>=30, B2<=50)

• AND(B2>=30, B2<=50) checks if the age is between 30 and 50, inclusive.
• Click on the Format button, go to the Fill tab, and select an orange fill color.
• Click OK to apply the formatting.

Step 3: Highlight Employees Over the Age of 50 (Red)

• Select the "Age" column (e.g., cells B2:B5) again.
• Go back to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• In the formula box, enter the following formula to highlight employees over the age of 50:

Copy code

=B2>50

• B2>50 checks if the age is greater than 50.
• Click on the Format button, go to the Fill tab, and select a red fill color.
• Click OK to apply the formatting.

3. Review the Results:

After applying the rules, the conditional formatting will automatically highlight employees according to their age group. Here’s what you should expect:

• John (Age 25): Will be highlighted in blue since he is under 30.
• Alice (Age 35): Will be highlighted in orange since she is between 30 and 50.
• Bob (Age 60): Will be highlighted in red since he is over 50.
• Claire (Age 40): Will be highlighted in orange since she is between 30 and 50.

Final View of the Dataset:

Name	Age
John	25 (Blue)
Alice	35 (Orange)
Bob	60 (Red)
Claire	40 (Orange)

Notes:

• The blue fill highlights employees under the age of 30.
• The orange fill highlights employees whose age is between 30 and 50.
• The red fill highlights employees over the age of 50.
• The conditional formatting will update automatically as the ages change.

Assignment 10: Conditional Formatting Based on Multiple Criteria

Objective: Apply conditional formatting based on multiple conditions.

Instructions:

1.   Create a dataset that includes Product Name, Sales, and Stock columns.

2.   Apply conditional formatting to the Stock column based on the following:

o    If the Sales are greater than 500, highlight the Stock in Green.

o    If the Sales are less than 200, highlight the Stock in Red.

o    If the Sales are between 200 and 500, leave the Stock without formatting.

3.   Ensure that all formatting is applied dynamically as sales data changes.

Dataset Example:

Product Name	Sales	Stock
Product A	600	50
Product B	150	200
Product C	350	120
Product D	450	300

---

Solution for Assignment 10: Conditional Formatting Based on Multiple Criteria

1. Dataset Creation:

Create a dataset that includes the Product Name, Sales, and Stock columns. Here’s an example dataset:

Product Name	Sales	Stock
Product A	600	50
Product B	150	200
Product C	350	120
Product D	450	300

2. Apply Conditional Formatting Based on Multiple Conditions:

Step 1: Highlight Stock in Green If Sales Are Greater Than 500

• Select the "Stock" column (e.g., cells C2:C5).
• Go to the Home tab.
• In the Styles group, click Conditional Formatting.
• Select New Rule.
• Choose Use a formula to determine which cells to format.
• In the formula box, enter the following formula to check if the sales are greater than 500:

Copy code

=B2>500

• B2>500 checks if the sales in column B are greater than 500.
• Click on the Format button, go to the Fill tab, and choose a green fill color.
• Click OK to apply the formatting.

Step 2: Highlight Stock in Red If Sales Are Less Than 200

• Select the "Stock" column (e.g., cells C2:C5).
• Go back to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
• In the formula box, enter the following formula to check if the sales are less than 200:

Copy code

=B2<200

• B2<200 checks if the sales in column B are less than 200.
• Click on the Format button, go to the Fill tab, and select a red fill color.
• Click OK to apply the formatting.

Step 3: Leave Stock Without Formatting If Sales Are Between 200 and 500

• Select the "Stock" column (e.g., cells C2:C5).
• Since the default behavior for sales between 200 and 500 is no formatting, you do not need to apply any formatting for this condition. Simply skip this step.

3. Review the Results:

After applying the rules, the conditional formatting will automatically update based on the sales data. Here’s the expected behavior:

• Product A (Sales 600): The stock will be highlighted in green because sales are greater than 500.
• Product B (Sales 150): The stock will be highlighted in red because sales are less than 200.
• Product C (Sales 350): No formatting will be applied because sales are between 200 and 500.
• Product D (Sales 450): No formatting will be applied because sales are between 200 and 500.

Final View of the Dataset with Formatting Applied:

Product Name	Sales	Stock
Product A	600	50 (Green)
Product B	150	200 (Red)
Product C	350	120 (No formatting)
Product D	450	300 (No formatting)

Notes:

• Green fill highlights the stock when sales are greater than 500.
• Red fill highlights the stock when sales are less than 200.
• No formatting is applied to the stock when sales are between 200 and 500.
• The formatting is dynamic, so it will update automatically if the sales data changes.

This solution ensures that the stock values are conditionally formatted based on the sales data, and the rules are applied dynamically as sales data changes.

Assignment 11: Heat Map for Sales Data

Objective: Apply Color Scales to create a heat map for sales data analysis.

Instructions:

1.   Create a dataset of Salesperson and Sales Amount for each salesperson.

2.   Apply Color Scales (such as a Red-Yellow-Green scale) to the Sales Amount column.

o    Green for the highest sales.

o    Yellow for medium sales.

o    Red for the lowest sales.

3.   The colors should represent the relative sales amount in a visual manner.

Dataset Example:

Salesperson	Sales Amount
John	2000
Alice	4500
Bob	1200
Claire	3500
David	5000

---

Solution for Assignment 11: Heat Map for Sales Data

1. Dataset Creation:

Create a dataset that includes the Salesperson and Sales Amount for each salesperson. Here's the example dataset:

Salesperson	Sales Amount
John	2000
Alice	4500
Bob	1200
Claire	3500
David	5000

2. Apply Color Scales to Create a Heat Map:

Step 1: Select the Sales Amount Column

• Select the Sales Amount column (e.g., cells B2:B6).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• In the Styles group, click on Conditional Formatting.
• Hover over Color Scales, and select the Red-Yellow-Green Color Scale. This color scale is commonly used to represent the lowest values in red, medium values in yellow, and the highest values in green.

Step 3: Review the Color Scale:

• The highest sales (David with 5000) will be colored green.
• The lowest sales (Bob with 1200) will be colored red.
• The medium sales (Alice with 4500, Claire with 3500, and John with 2000) will be shaded in yellow with varying intensity based on the sales amount.

Final View of the Dataset with the Heat Map:

Salesperson	Sales Amount
John	2000 (Yellow)
Alice	4500 (Yellow)
Bob	1200 (Red)
Claire	3500 (Yellow)
David	5000 (Green)

Explanation of Results:

• David (5000) has the highest sales, so his cell will be green.
• Bob (1200) has the lowest sales, so his cell will be red.
• The remaining sales figures will be shaded yellow based on their relative position between the highest and lowest values, with the intensity of yellow changing based on the sales amount.

Notes:

• The Red-Yellow-Green color scale allows you to quickly analyze the sales performance, with the color representing how each salesperson performed relative to the others.
• The formatting is dynamic, so if any sales amount changes, the heat map will update automatically.

Assignment 12: Custom Formula for Discount Calculation

Objective: Use a Custom Formula for conditional formatting to calculate discounts.

Instructions:

1.   Create a dataset with Customer Name, Purchase Amount, and Discount columns.

2.   Apply conditional formatting to the Discount column using a formula that highlights:

o    Green if the Purchase Amount is greater than 5000 and the discount should be 10%.

o    Yellow if the Purchase Amount is between 2000 and 5000 and the discount should be 5%.

o    Red if the Purchase Amount is less than 2000 and there is no discount.

3.   Use a custom formula to apply the discount condition: =B2>5000 for Green, =AND(B2>=2000, B2<=5000) for Yellow.

Dataset Example:

Customer Name	Purchase Amount	Discount
John	7000
Alice	4500
Bob	1500
Claire	8000

Solution for Assignment 12: Custom Formula for Discount Calculation

1. Dataset Creation:

Create a dataset with Customer Name, Purchase Amount, and Discount columns. Here's the example dataset:

Customer Name	Purchase Amount	Discount
John	7000
Alice	4500
Bob	1500
Claire	8000

2. Apply Conditional Formatting Using Custom Formula:

Step 1: Select the Discount Column

• Select the Discount column (e.g., cells C2:C5).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• In the Styles group, click on Conditional Formatting.
• Select New Rule from the dropdown.

Step 3: Apply the Green Formatting (10% Discount for Purchases > 5000)

• In the New Formatting Rule window, select Use a formula to determine which cells to format.
• Enter the formula:

Copy code

=B2>5000

This formula checks if the Purchase Amount is greater than 5000. If true, the discount should be 10%, and the cell will be highlighted in green.

• Click on Format, choose the Fill tab, and select Green. Press OK.

Step 4: Apply the Yellow Formatting (5% Discount for Purchases between 2000 and 5000)

• Create another rule by selecting New Rule again.
• Choose Use a formula to determine which cells to format.
• Enter the formula:

scss

Copy code

=AND(B2>=2000, B2<=5000)

This formula checks if the Purchase Amount is between 2000 and 5000. If true, the discount should be 5%, and the cell will be highlighted in yellow.

• Click on Format, choose the Fill tab, and select Yellow. Press OK.

Step 5: Apply the Red Formatting (No Discount for Purchases < 2000)

• Create another rule for the No Discount condition.
• Select New Rule again, and choose Use a formula to determine which cells to format.
• Enter the formula:

Copy code

=B2<2000

This formula checks if the Purchase Amount is less than 2000. If true, the discount will be 0%, and the cell will be highlighted in red.

• Click on Format, choose the Fill tab, and select Red. Press OK.

Step 6: Finalizing

• Once you've created all three rules, click OK to apply the conditional formatting.
• The Discount column will be highlighted according to the formulas you've set based on the purchase amounts.

3. Resulting Dataset with Conditional Formatting:

After applying the conditional formatting, the Discount column will automatically show the appropriate discount and be highlighted as follows:

Customer Name	Purchase Amount	Discount	Highlight Color
John	7000	10%	Green
Alice	4500	5%	Yellow
Bob	1500	0%	Red
Claire	8000	10%	Green

4. Explanation of the Formatting:

• Green (10% Discount): Applied to John and Claire since their purchase amounts are greater than 5000.
• Yellow (5% Discount): Applied to Alice since her purchase amount is between 2000 and 5000.
• Red (No Discount): Applied to Bob since his purchase amount is less than 2000, so no discount is given.

Assignment 13: Find and Highlight Duplicate Entries

Objective: Use Conditional Formatting to find and highlight duplicates in a list.

Instructions:

1.   Create a list of Email IDs in an Excel sheet.

2.   Apply conditional formatting to highlight duplicate email addresses in the list using a light red fill.

3.   Ensure that only duplicate email addresses are highlighted, and the first occurrence remains unformatted.

Dataset Example:

Email ID
john@example.com
alice@example.com
bob@example.com
john@example.com
claire@example.com
alice@example.com

Solution for Assignment 13: Find and Highlight Duplicate Entries

1. Dataset Creation:

Create a dataset with Email IDs. Here's the example dataset:

Email ID
john@example.com
alice@example.com
bob@example.com
john@example.com
claire@example.com
alice@example.com

2. Apply Conditional Formatting to Highlight Duplicate Email IDs:

Step 1: Select the Email ID Column

• Select the range of cells where you have the email addresses (for example, A2:A7).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• In the Styles group, click on Conditional Formatting.
• Select Highlight Cells Rules and then choose Duplicate Values from the dropdown.

Step 3: Choose the Formatting Style

• In the Duplicate Values dialog box, make sure the first dropdown is set to Duplicate.
• In the second dropdown, choose Custom Format.
• Click Format, go to the Fill tab, and select Light Red as the fill color.
• Press OK to apply the formatting.

Step 4: Finalize

• Click OK to close the dialog and apply the conditional formatting.

3. Resulting Dataset with Conditional Formatting:

After applying the conditional formatting, any duplicate email addresses (except the first occurrence) will be highlighted in light red. Here's the resulting dataset:

Email ID	Highlight Color
john@example.com	(No Highlight)
alice@example.com	(No Highlight)
bob@example.com	(No Highlight)
john@example.com	Light Red
claire@example.com	(No Highlight)
alice@example.com	Light Red

4. Explanation of the Formatting:

• The first occurrence of each email address remains unformatted.
• The duplicate entries (john@example.com and alice@example.com) are highlighted in light red, while their first occurrence remains unchanged.

5. Dynamic Updates:

• This conditional formatting will dynamically highlight new duplicates if any email ID is added or removed from the list.

Assignment 14: Gantt Chart for Project Timeline

Objective: Use Conditional Formatting to create a Gantt chart for project management.

Instructions:

1.   Create a dataset that includes Task Name, Start Date, and End Date.

2.   Create a project timeline where the rows represent tasks and the columns represent dates.

3.   Apply conditional formatting to highlight the cells between the Start Date and End Date for each task using a blue fill.

4.   Ensure that the timeline adjusts dynamically when the Start Date or End Date changes.

Dataset Example:

Task Name	Start Date	End Date	Day 1	Day 2	Day 3	Day 4	Day 5
Task A	12/18/2024	12/20/2024
Task B	12/19/2024	12/22/2024
Task C	12/20/2024	12/22/2024

---

Solution for Assignment 14: Gantt Chart for Project Timeline

1. Dataset Creation:

Create a dataset that includes Task Name, Start Date, and End Date. Below is the example dataset:

Task Name	Start Date	End Date	Day 1	Day 2	Day 3	Day 4	Day 5
Task A	12/18/2024	12/20/2024
Task B	12/19/2024	12/22/2024
Task C	12/20/2024	12/22/2024

2. Create the Project Timeline:

• For this Gantt chart, the rows represent the tasks, and the columns represent the dates.
• Add the days in the top row (e.g., "Day 1", "Day 2", etc.) and adjust the Start Date and End Date for each task.

3. Apply Conditional Formatting:

To highlight the cells between the Start Date and End Date for each task with a blue fill, follow these steps:

Step 1: Select the Range

• First, select the range of cells in the timeline where you want the conditional formatting to apply. In this case, select the cells from Day 1 to Day 5 (e.g., D2:H4 if you have the dataset in columns D to H).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• Click Conditional Formatting.
• Select New Rule from the dropdown.

Step 3: Use a Formula for Conditional Formatting

• In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
• Enter the following formula to check if the date falls between the Start Date and End Date for each task:

excel

Copy code

=AND(D$1>=$B2, D$1<=$C2)

Here:

• D$1 refers to the date in the top row (e.g., Day 1, Day 2, etc.).
• $B2 refers to the Start Date for the task.
• $C2 refers to the End Date for the task.

Step 4: Format the Cells

• After entering the formula, click the Format button.
• Choose a blue fill for the highlighted cells.
• Click OK to apply the conditional formatting rule.

Step 5: Drag to Apply the Rule

• After applying the rule, drag the formula down to cover all the tasks in the timeline. Excel will automatically apply the conditional formatting to each row based on the start and end dates for the respective task.

4. Final Gantt Chart:

After applying the formatting, your Gantt chart should look like this:

Task Name	Start Date	End Date	Day 1	Day 2	Day 3	Day 4	Day 5
Task A	12/18/2024	12/20/2024	Blue	Blue	Blue
Task B	12/19/2024	12/22/2024		Blue	Blue	Blue	Blue
Task C	12/20/2024	12/22/2024			Blue	Blue	Blue

5. Explanation of the Formula:

• =AND(D$1>=$B2, D$1<=$C2) checks if the date in Day 1, Day 2, etc. is greater than or equal to the Start Date and less than or equal to the End Date for each task.
• If the condition is true, the cell is filled with the blue color you chose.

6. Dynamic Updates:

• The formatting will adjust dynamically if you change the Start Date or End Date for any task, as the formula checks these values automatically.

Assignment 15: Conditional Formatting for Age Group Segmentation

Objective: Use Conditional Formatting to categorize age groups.

Instructions:

1.   Create a dataset of Employee Name and Age.

2.   Apply conditional formatting to the Age column:

o    Highlight employees under 30 years old in Green.

o    Highlight employees between 30 and 50 years old in Yellow.

o    Highlight employees over 50 years old in Red.

3.   Use a formula for the formatting: =B2<30, =AND(B2>=30, B2<=50), and =B2>50.

Dataset Example:

Employee Name	Age
John	25
Alice	45
Bob	55
Claire	35

---

Solution for Assignment 15: Conditional Formatting for Age Group Segmentation

1. Dataset Creation:

First, create a dataset for Employee Name and Age. Below is the example dataset:

Employee Name	Age
John	25
Alice	45
Bob	55
Claire	35

2. Apply Conditional Formatting:

To categorize employees based on their age using conditional formatting, follow these steps:

Step 1: Select the Age Column

• Highlight the Age column (in this case, the cells in column B that contain the age values, e.g., B2:B5).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• Click on Conditional Formatting.
• Select New Rule from the dropdown menu.

Step 3: Use Formulas for Conditional Formatting

We will apply conditional formatting based on specific conditions for the Age values.

Step 4: Apply Rule for Ages Under 30 (Green Highlight)

• In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
• Enter the formula for highlighting ages under 30:

excel

Copy code

=B2<30

• Click on the Format button and choose a Green Fill.
• Click OK to apply this formatting rule.

Step 5: Apply Rule for Ages Between 30 and 50 (Yellow Highlight)

• Again, click on Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter the formula for ages between 30 and 50:

excel

Copy code

=AND(B2>=30, B2<=50)

• Click on the Format button and choose a Yellow Fill.
• Click OK to apply this rule.

Step 6: Apply Rule for Ages Over 50 (Red Highlight)

• Click on Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter the formula for ages over 50:

excel

Copy code

=B2>50

• Click on the Format button and choose a Red Fill.
• Click OK to apply this rule.

3. Final Appearance:

After applying the conditional formatting, the Age column will be formatted based on the following criteria:

• Green for ages under 30.
• Yellow for ages between 30 and 50.
• Red for ages over 50.

For the given dataset:

Employee Name	Age
John	25
Alice	45
Bob	55
Claire	35

4. Explanation of the Formulas:

• =B2<30: This formula checks if the value in B2 (Age) is less than 30. If true, the cell is formatted with a Green fill.
• =AND(B2>=30, B2<=50): This formula checks if the value in B2 (Age) is between 30 and 50 (inclusive). If true, the cell is formatted with a Yellow fill.
• =B2>50: This formula checks if the value in B2 (Age) is greater than 50. If true, the cell is formatted with a Red fill.

5. Dynamic Updates:

• The formatting will adjust dynamically if you update the Age values. The colors will change based on the updated age ranges.

Assignment 16: Trend Analysis with Color Scales

Objective: Use Color Scales to identify trends over time.

Instructions:

1.   Create a dataset with Month and Revenue columns.

2.   Apply a Color Scale to the Revenue column, where higher revenues are green and lower revenues are red.

3.   Ensure that the dataset reflects a positive or negative trend, and apply color scales to visualize the trend of revenues over months.

Dataset Example:

Month	Revenue
January	1500
February	2500
March	3200
April	4200
May	2900

---

Solution for Assignment 16: Trend Analysis with Color Scales

1. Dataset Creation:

First, create the dataset with Month and Revenue columns. Below is the example dataset:

Month	Revenue
January	1500
February	2500
March	3200
April	4200
May	2900

2. Apply Color Scale:

To apply color scales to the Revenue column to visually represent the trend of revenues over the months, follow these steps:

Step 1: Select the Revenue Column

• Highlight the Revenue column (the cells in column B containing the revenue values, e.g., B2:B6).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• Click on Conditional Formatting.
• Select Color Scales from the dropdown menu.

Step 3: Choose a Color Scale

• Choose the Green-Yellow-Red Color Scale (or Green to Red Color Scale depending on your Excel version). This scale uses green for higher values (representing good performance or growth) and red for lower values (representing poor performance or a decline).

Step 4: Ensure Correct Visualization

The Green-Yellow-Red Color Scale will automatically apply colors based on the following:

• Green represents the highest values (e.g., the month with the highest revenue, which is April with 4200).
• Yellow represents middle values.
• Red represents the lowest values (e.g., the month with the lowest revenue, which is January with 1500).

3. Final Appearance:

After applying the color scale, the Revenue column will have a gradient of colors based on the revenue values:

Month	Revenue
January	1500
February	2500
March	3200
April	4200
May	2900

4. Explanation of the Color Scale:

• Green: Represents the highest revenue value (April with 4200).
• Yellow: Represents intermediate revenue values, with the medium-range values like February, March, and May.
• Red: Represents the lowest revenue value (January with 1500).

5. Dynamic Trend Visualization:

• As you update the Revenue values, the colors will adjust automatically. Higher values will be colored in green, and lower values will be in red, helping to visualize the revenue trend over the months.

Assignment 17: Use Icons for Attendance Status

Objective: Use Icon Sets for tracking attendance.

Instructions:

1.   Create a dataset of Student Name and Attendance Percentage.

2.   Apply a Traffic Light icon set to the Attendance Percentage column:

o    Red icon for attendance less than 50%.

o    Yellow icon for attendance between 50% and 80%.

o    Green icon for attendance greater than 80%.

3.   Ensure the icons change dynamically based on the attendance percentage.

Dataset Example:

Student Name	Attendance Percentage
John	45%
Alice	90%
Bob	60%
Claire	80%

---

Solution for Assignment 17: Use Icons for Attendance Status

1. Dataset Creation:

First, create the dataset with Student Name and Attendance Percentage columns. Below is the example dataset:

Student Name	Attendance Percentage
John	45%
Alice	90%
Bob	60%
Claire	80%

2. Apply Traffic Light Icon Set:

To apply the Traffic Light Icon Set to the Attendance Percentage column, follow these steps:

Step 1: Select the Attendance Percentage Column

• Highlight the Attendance Percentage column (the cells in the Attendance Percentage column, e.g., B2:B5).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• Click on Conditional Formatting.
• Select Icon Sets from the dropdown menu.
• Choose the Traffic Light (3 Arrows) icon set (or the Traffic Light option depending on your Excel version). This set will display red, yellow, and green icons based on specific rules.

Step 3: Customize the Icon Set

To set the icon rules based on the specified attendance percentages, follow these steps:

1.   Click on "Manage Rules": This is found under the Conditional Formatting menu.

2.   Edit the Rule: Select the rule for the Traffic Light icon set and click on Edit Rule.

3.   Set the Icon Conditions:

o    Red Icon: Set the condition for the red icon to trigger when the attendance percentage is less than 50%. (e.g., "Number" -> "less than" -> 50).

o    Yellow Icon: Set the condition for the yellow icon to trigger when the attendance percentage is between 50% and 80%. (e.g., "Number" -> "between" -> 50 and 80).

o    Green Icon: Set the condition for the green icon to trigger when the attendance percentage is greater than 80%. (e.g., "Number" -> "greater than" -> 80).

Step 4: Ensure Dynamic Updates

• Once the icons are applied and the conditions set, the icons will update dynamically based on the attendance percentages.
• For example, if a student's attendance percentage changes, the icon will automatically update to reflect the new status (red, yellow, or green).

3. Final Appearance:

After applying the icon set and customizing the conditions, the Attendance Percentage column will show the corresponding icons for each student:

Student Name	Attendance Percentage	Icon
John	45%	🟥 (Red)
Alice	90%	🟩 (Green)
Bob	60%	🟨 (Yellow)
Claire	80%	🟨 (Yellow)

4. Explanation of the Icons:

• Red Icon: Indicates attendance is less than 50% (John's attendance is 45%).
• Yellow Icon: Indicates attendance is between 50% and 80% (Bob's attendance is 60% and Claire's attendance is 80%).
• Green Icon: Indicates attendance is greater than 80% (Alice's attendance is 90%).

5. Dynamic Updates:

• Whenever the Attendance Percentage changes for any student, the icon will automatically adjust to reflect the updated value, following the rules set for red, yellow, and green icons.

Assignment 18: Highlight Late Payments

Objective: Use Date-Based Conditional Formatting to highlight late payments.

Instructions:

1.   Create a dataset that includes Customer Name, Invoice Date, and Payment Due Date.

2.   Apply conditional formatting to the Payment Due Date:

o    Highlight payments overdue (past today's date) in Red.

o    Highlight payments that are due within the next 3 days in Yellow.

o    Highlight payments that are on time (future dates) in Green.

3.   Ensure the formatting updates dynamically.

Dataset Example:

Customer Name	Invoice Date	Payment Due Date
John	12/01/2024	12/15/2024
Alice	12/05/2024	12/10/2024
Bob	12/07/2024	12/14/2024
Claire	12/10/2024	12/12/2024

Solution for Assignment 18: Highlight Late Payments

1. Dataset Creation:

First, create the dataset with the Customer Name, Invoice Date, and Payment Due Date columns. Here's an example:

Customer Name	Invoice Date	Payment Due Date
John	12/01/2024	12/15/2024
Alice	12/05/2024	12/10/2024
Bob	12/07/2024	12/14/2024
Claire	12/10/2024	12/12/2024

2. Apply Date-Based Conditional Formatting:

To apply conditional formatting based on the Payment Due Date, follow these steps:

Step 1: Select the Payment Due Date Column

• Highlight the Payment Due Date column (e.g., cells in C2:C5).

Step 2: Open Conditional Formatting

• Go to the Home tab in the Excel Ribbon.
• Click on Conditional Formatting.
• Choose New Rule from the dropdown.

Step 3: Apply Conditional Formatting Rules

1.   Highlight Late Payments (Red): Payments overdue (past today's date)

o    Choose Use a formula to determine which cells to format.

o    Enter the formula:

scss

Copy code

=C2<TODAY()

o    Click Format and choose a Red fill color. Press OK.

2.   Highlight Payments Due in the Next 3 Days (Yellow): Payments due within the next 3 days

o    Choose New Rule again.

o    Choose Use a formula to determine which cells to format.

o    Enter the formula:

scss

Copy code

=AND(C2>=TODAY(), C2<=TODAY()+3)

o    Click Format and choose a Yellow fill color. Press OK.

3.   Highlight On-Time Payments (Green): Payments that are on time (future dates)

o    Choose New Rule once more.

o    Choose Use a formula to determine which cells to format.

o    Enter the formula:

scss

Copy code

=C2>TODAY()+3

o    Click Format and choose a Green fill color. Press OK.

Step 4: Ensure Dynamic Updates

• The conditional formatting will automatically update based on the current date. For example, if today’s date changes, the formatting will adjust the colors accordingly:
• Red for overdue payments.
• Yellow for payments due within the next 3 days.
• Green for payments due after the next 3 days.

3. Final Appearance:

After applying the rules, the Payment Due Date column will dynamically reflect the formatting rules:

Customer Name	Invoice Date	Payment Due Date	Formatting
John	12/01/2024	12/15/2024	Green
Alice	12/05/2024	12/10/2024	Red
Bob	12/07/2024	12/14/2024	Red
Claire	12/10/2024	12/12/2024	Yellow

4. Explanation:

• Red: Payments that are overdue (due before today's date).
• Yellow: Payments due within the next 3 days (between today's date and 3 days ahead).
• Green: Payments that are due more than 3 days from today.

 

Some More ...

Assignment 1: Highlighting Top and Bottom 10%

Question:

1.   Create a dataset with a Sales column.

2.   Apply conditional formatting to the Sales column to highlight the top 10% in green and the bottom 10% in red.

Solution:

1.   Dataset:

Sales
500
700
300
900
400
800
1000

2.   Steps:

o    Select the Sales column.

o    Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10%.

§  Choose green color.

o    Similarly, go to Bottom 10%, choose red color.

3.   The top 10% values (900, 1000) will be highlighted in green, and the bottom 10% values (300, 400) will be highlighted in red.

---

Assignment 2: Color Scale for Exam Scores

Question:

1.   Create a dataset with Student Name and Exam Score.

2.   Apply a color scale to the Exam Score column with the following rule:

o    Red for the lowest score.

o    Yellow for the middle score.

o    Green for the highest score.

Solution:

1.   Dataset:

Student Name	Exam Score
John	45
Alice	85
Bob	70
Claire	90

2.   Steps:

o    Select the Exam Score column.

o    Go to Home > Conditional Formatting > Color Scales.

o    Choose the Red-Yellow-Green color scale.

3.   Result:

o    Red will highlight the lowest score (John - 45).

o    Yellow will be for the middle score (Bob - 70).

o    Green will be for the highest score (Claire - 90).

---

Assignment 3: Apply Icon Set for Sales Performance

Question:

1.   Create a dataset with Employee Name and Sales Performance (in dollars).

2.   Apply an Icon Set to the Sales Performance column:

o    Green for high performance (greater than $1000).

o    Yellow for medium performance (between $500 and $1000).

o    Red for low performance (less than $500).

Solution:

1.   Dataset:

Employee Name	Sales Performance
John	2000
Alice	800
Bob	300
Claire	1200

2.   Steps:

o    Select the Sales Performance column.

o    Go to Home > Conditional Formatting > Icon Sets > 3 Arrows (Green-Yellow-Red).

o    Modify the icon set rules:

§  Green for greater than 1000.

§  Yellow for between 500 and 1000.

§  Red for less than 500.

3.   Result:

o    John and Claire will have the green icon.

o    Alice will have the yellow icon.

o    Bob will have the red icon.

---

Assignment 4: Highlight Dates Due Within 7 Days

Question:

1.   Create a dataset with Task Name and Due Date columns.

2.   Apply conditional formatting to the Due Date column to highlight:

o    Red if the date is within 7 days of today.

o    Yellow if the date is overdue (earlier than today).

Solution:

1.   Dataset:

Task Name	Due Date
Task A	12/20/2024
Task B	12/17/2024
Task C	12/23/2024

2.   Steps:

o    Select the Due Date column.

o    Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

o    For Red (within 7 days): =AND(A2>=TODAY(), A2<=TODAY()+7).

o    For Yellow (overdue): =A2<TODAY().

o    Apply formatting as per the conditions.

3.   Result:

o    Task B will be highlighted in yellow (overdue).

o    Task A will be highlighted in red (due within 7 days).

---

Assignment 5: Highlight Values Greater Than a Threshold

Question:

1.   Create a dataset with Employee Name and Salary columns.

2.   Apply conditional formatting to the Salary column to highlight:

o    Green for salaries greater than $5000.

Solution:

1.   Dataset:

Employee Name	Salary
John	4000
Alice	6000
Bob	5500
Claire	3000

2.   Steps:

o    Select the Salary column.

o    Go to Home > Conditional Formatting > New Rule > Format only cells that contain.

o    Set the rule to greater than 5000.

o    Choose green color for formatting.

3.   Result:

o    Alice and Bob's salaries will be highlighted in green.

---

Assignment 6: Highlight Duplicates

Question:

1.   Create a dataset with Email IDs.

2.   Apply conditional formatting to highlight duplicate email IDs.

Solution:

1.   Dataset:

Email ID
john@example.com
alice@example.com
bob@example.com
john@example.com
claire@example.com

2.   Steps:

o    Select the Email ID column.

o    Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

o    Choose the format (light red fill).

3.   Result:

o    The second occurrence of john@example.com will be highlighted.

---

Assignment 7: Highlight Cells Based on Text Match

Question:

1.   Create a dataset with Product Name and Stock Level columns.

2.   Apply conditional formatting to highlight:

o    Red for products with low stock (less than 10).

o    Green for products with sufficient stock (10 or more).

Solution:

1.   Dataset:

Product Name	Stock Level
Product A	5
Product B	20
Product C	8
Product D	12

2.   Steps:

o    Select the Stock Level column.

o    Go to Home > Conditional Formatting > New Rule > Format only cells that contain.

o    Set the rule for less than 10 for red and greater than or equal to 10 for green.

3.   Result:

o    Product A and Product C will be highlighted in red (low stock).

o    Product B and Product D will be highlighted in green (sufficient stock).

---

Assignment 8: Highlight Sales Target Achievements

Question:

1.   Create a dataset with Employee Name and Sales Target columns.

2.   Apply conditional formatting to highlight:

o    Green for employees who achieve or exceed their target (Sales >= Target).

o    Red for employees who do not meet their target (Sales < Target).

Solution:

1.   Dataset:

Employee Name	Sales	Target
John	5000	4000
Alice	3000	4000
Bob	6000	6000
Claire	4500	5000

2.   Steps:

o    Select both Sales and Target columns.

o    Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

o    For Green (sales >= target): =B2>=C2.

o    For Red (sales < target): =B2<C2.

3.   Result:

o    John and Bob will be highlighted in green (achieved or exceeded target).

o    Alice and Claire will be highlighted in red (did not meet target).

---