Excel Conditional Formatting based on Another Cell | Highlight Cells

MITutorials

13 Feb 201801:30

Summary

TLDRIn this tutorial, the host demonstrates how to use conditional formatting in Excel to highlight cells based on their content. They show viewers how to create a new rule using a formula to identify and format cells in column A where column B contains the word 'invalid'. The cells are formatted in red font to make them stand out. The host guides through each step, from setting up the formula to applying the formatting rule, ensuring that the process is clear and easy to follow.

Takeaways

🎥 The video is a tutorial focused on teaching Excel skills.
📊 The specific task is to highlight cells based on the content of another cell.
📋 A sample dataset is used for demonstration, with a focus on columns A and B.
🚩 The tutorial aims to highlight cells in column A where column B says 'invalid'.
🖥 The process begins by accessing the 'Conditional Formatting' feature in Excel.
🆕 A new rule is created using a formula to determine which cells to format.
🔒 The formula uses '$B1' to reference the cell and freeze the column for the condition.
🔍 The condition checks for the text 'invalid' in column B.
🎨 The cells that meet the condition are formatted in red and the font is adjusted.
🔄 After setting the rule, it's necessary to manage and apply it to the desired range.
👍 The presenter encourages viewers to like, subscribe, and ask for help if needed.

Q & A

What is the main topic of the tutorial video?
-The main topic of the tutorial video is teaching viewers how to highlight cells in Excel based on the content of another cell.
Which column is used as the reference to highlight cells?
-Column B is used as the reference to highlight cells where it says 'invalid'.
What is the purpose of using a dollar sign ($) before B1 in the formula?
-The dollar sign ($) is used to freeze the column reference, ensuring that the formula applies to all rows in column B.
What is the formula used to determine which cells to format?
-The formula used is '=$B1="invalid"' to determine which cells to format based on the content in column B.
What color is chosen to highlight the cells that contain the word 'invalid'?
-The cells containing the word 'invalid' are highlighted in red.
How does the user apply the conditional formatting to the desired range?
-The user applies the conditional formatting by going to 'Manage Rules', selecting the range, and clicking 'Apply'.
What happens if the user does not select the correct range in 'Manage Rules'?
-If the user does not select the correct range, the conditional formatting will not be applied to the intended cells.
What is the significance of the 'Format' and 'Font' selections in the tutorial?
-The 'Format' selection is used to choose the color of the cell background, and the 'Font' selection is used to potentially change the font style, although in this case, it is not modified.
What is the final outcome of following the tutorial?
-The final outcome is that all cells in column A are highlighted in red wherever column B contains the word 'invalid'.
What are the additional resources offered by the tutorial creator?
-The tutorial creator offers help with Excel, SQL, or VBA and encourages viewers to comment if they need assistance.
How can viewers show appreciation for the tutorial?
-Viewers are encouraged to hit the like and subscribe button if they found the tutorial helpful.

Outlines

00:00

📊 Highlighting 'Invalid' Cells in Excel

In this tutorial video, the presenter starts by welcoming viewers to their channel and introduces the topic of the video: teaching how to highlight cells in Excel. They provide a sample dataset and aim to highlight all cells in column A where the corresponding cell in column B says 'invalid'. The presenter demonstrates how to use conditional formatting to achieve this. They guide viewers to click on 'Conditional Formatting', then 'New Rule', and use a formula to determine which cells to format. They enter a formula using a dollar sign to freeze the column and specify the condition as 'invalid'. They then format the cells with red color and right-aligned font. After setting up the rule, they explain that initially, nothing seems to change, so they go back to 'Conditional Formatting', 'Manage Rules', and specify the range of cells to which the formatting should apply. Finally, they apply the rule and demonstrate that column A is now highlighted where column B contains 'invalid'. The presenter concludes by encouraging viewers to like, subscribe, and ask for help in the comments if needed.

Mindmap

Keywords

💡Channel

A channel, in the context of online video platforms like YouTube, refers to a user's personal or brand's space where they upload and manage their videos. In the script, the speaker is addressing their audience on 'my channel,' indicating that they are the host of a YouTube channel where they share tutorials.

💡Tutorial

A tutorial is a teaching session or a set of instructions designed to teach someone how to do something, like a specific skill or technique. The video script mentions a 'tutorial' which implies that the content is educational and aims to guide viewers on how to accomplish a task.

💡Highlight

To highlight means to make something noticeable or prominent, often by changing its appearance. In the script, the tutorial's goal is to teach viewers how to 'highlight' cells in a spreadsheet, indicating a visual change to make certain cells stand out.

💡Cell

In spreadsheet terminology, a cell refers to the individual box that can contain data in a table. The script discusses 'highlighting cell' which is a common task in spreadsheet software like Excel, used to draw attention to specific data points.

💡Dataset

A dataset is a collection of data, often organized in a structured format like a table. The script mentions a 'sample dataset' which is the raw material that the tutorial will be working with to demonstrate the process of highlighting cells.

💡Column

A column in a spreadsheet is a vertical sequence of cells. The script specifies 'column B' where the condition for highlighting cells is based, indicating that the tutorial focuses on a particular column to apply the formatting.

💡Invalid

In the context of the script, 'invalid' refers to a specific condition or value within cells that triggers the highlighting. The tutorial aims to highlight cells where the word 'invalid' appears, serving as a criterion for the conditional formatting.

💡Conditional Formatting

Conditional formatting is a feature in spreadsheet software that allows cells to be automatically formatted based on their values or conditions. The script describes using 'conditional formatting' to change the appearance of cells containing the word 'invalid'.

💡Formula

A formula in spreadsheets is an equation that performs calculations. The script instructs viewers to use a 'formula' to determine which cells to format, suggesting a method of applying conditional formatting based on cell values.

💡Dollar Sign

In spreadsheets, the dollar sign ('$') is used to create absolute cell references. The script mentions typing 'dollar sign and b1' to freeze the column reference, ensuring that the formula applies to the correct column regardless of where it is copied.

💡Format

To format in spreadsheets means to define the appearance of cells, such as font, color, or borders. The script describes selecting 'red' as the format for highlighting, indicating a visual change to make cells with 'invalid' stand out.

💡Manage Rules

In the context of conditional formatting, 'manage rules' likely refers to a feature that allows users to adjust or edit the rules that determine cell formatting. The script suggests going to 'manage rules' to ensure the formatting applies to the desired range of cells.

Highlights

Introduction to a tutorial on how to highlight cells based on another cell's value.

The tutorial uses a sample dataset to demonstrate the process.

The goal is to highlight cells in column A where column B says 'invalid'.

The process starts by clicking on 'Conditional Formatting'.

A new rule is created using a formula.

The formula uses the cell reference '$B1' to check for the value 'invalid'.

The dollar sign '$' is used to freeze the column reference.

The format is set to red to highlight the cells.

The font is also set to red for emphasis.

After setting the rule, nothing changes immediately, indicating the need for further steps.

The 'Manage Rules' option is used to specify the range for the conditional formatting.

The range is selected where the conditional formatting should be applied.

The rule is applied, and the cells are highlighted as expected.

The tutorial concludes with a call to action for likes and subscriptions.

The presenter offers help in Excel, SQL, or VBA in the comments section.

The presenter thanks the viewers for watching.