Conditional Formatting in 10 Minutes.

Conditional formatting in Excel allows you to highlight cells based on specific criteria, making data analysis easier and more visually appealing. This guide will walk you through the basics and advanced techniques to effectively use conditional formatting.

Step 1: Basic Highlighting of Test Scores

1. Open Excel and enter your data, including names and test scores.

2. Select the range of test scores you want to format.

3. Go to the Home tab, click on Conditional Formatting, and choose Highlight Cells Rules.

4. Select Greater Than and enter 70 to highlight passing scores. Choose a green fill color and click OK.

5. Repeat the process for scores Less Than 70, selecting a red fill color for failing scores.

Step 2: Dynamic Conditional Formatting

1. To make the formatting dynamic, select the range again.

2. Go to Conditional Formatting > Highlight Cells Rules > Greater Than.

3. Instead of typing a number, select a cell (e.g., J4) that contains the pass threshold. This allows the formatting to adjust automatically when you change the value in J4.

4. For failing scores, repeat the same process using a cell reference for the fail threshold (e.g., J5).

Step 3: Highlighting Text

1. To highlight text, select the relevant cells.

2. Navigate to Conditional Formatting > Highlight Cells Rules > Text that Contains.

3. Input a keyword (e.g., "https") to highlight URLs, and choose a fill color (e.g., green) for completed tasks.

4. You can also highlight other text such as "Excel" or "Word" using the same method, selecting different colors as needed.

Step 4: Using Color Scales and Data Bars

1. For a visual representation of scores, select your data range.

2. Go to Conditional Formatting > Color Scales or Data Bars to apply a gradient or bar that reflects the values dynamically. This helps in quickly assessing performance across a range.

Step 5: Advanced Techniques with Formulas

1. For more complex conditions, you can use formulas within conditional formatting.

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

3. Enter a formula (e.g., =A1>70) to apply formatting based on specific criteria. This allows for highly customized formatting options.

By following these steps, you can effectively use conditional formatting in Excel to enhance your data visualization and analysis. Experiment with different rules and formats to find what works best for your needs!

For more essential tips and tricks on Microsoft 365 applications, be sure to check out That Office Guy’s YouTube Channel.