Excel Conditional Formatting based on Another Cell | Highlight Cells
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
π 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
π‘Tutorial
π‘Highlight
π‘Cell
π‘Dataset
π‘Column
π‘Invalid
π‘Conditional Formatting
π‘Formula
π‘Dollar Sign
π‘Format
π‘Manage Rules
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.
Transcripts
hey guys welcome back to my channel and
my tutorial in this particular video I'm
going to teach you how to highlight cell
based or another cell I have a sample
dataset over here and I want to
highlight all the cells in column and
wherever it says invalid in column B so
let's get started I'm going to click on
conditional formatting I'm going to
click on new rule use a formula to click
on user formula to determine which cells
to format and over here I'm going to
type in dollar sign and b1 this is where
the value is valid or invalid is going
to be picked up the dollar sign is to
freeze the column and I'm going to type
in is equals to in quotes I am going to
type in invalid and I'm going to click
on format and select red here because
that's what I want it to be formatted in
and the font as right I'm going to click
on OK and I'm going to click on OK once
again so as you can see nothing has
changed so we need to go to conditional
formatting again click on manage rules
and in applies to section I'll have to
go and select the range wherever you
want the color in to be applied or
conditional formatting to be applied I'm
going to click on apply and click on ok
so as you can see anywhere it says
invalid in the column B the column a is
highlighted so I hope this video has
helped you please hit the like and
subscribe button if you want to see more
such tutorials and you can also comment
down below if you need any help in Excel
sequel or VBA I'll be happy to help you
thank you so much for watching
Browse More Related Video
Google Sheets - Conditional Formatting
SAP ABAP EΔitimi 55 - OO ALV Cell Style
Belajar Excel dari Nol episode 1 | Tutorial Excel Pemula
Word: Formatting Text
Curso de Excel 2023 - Aula de Apoio da Aula 1 [Resolvendo caso da Função SE Aninhada no Excel]
TLE 7- MATATAG CURRICULUM LESSON (1ST QTR)- CREATING SPREADSHEETS W/CONDITIONAL FORMATTING
5.0 / 5 (0 votes)