Good day,
This week, I'm going to show you how to make numbered-text in Excel. This allows you to combine text and numbers in a worksheet while allowing it to still function as a normal number.
(Let me know what you think in the comments on youtube!)
Can’t see the thumbnail? Click here to watch on YouTube |
Last Week's Tutorial: Scrollable Charts
[Tiny Tip] Use Formulas in Conditional Formatting
Did you know you can use formulas in Conditional Formatting to highlight cells or entire rows based on your own custom rules? This allows you to create much more powerful and dynamic formatting than the standard options.
The trick is that your formula must return TRUE or FALSE.
When the formula returns TRUE, Excel applies your chosen formatting. When it returns FALSE, the formatting is not applied.
Step 1: Create Your Sample Data
Imagine you have a list of orders and want to automatically highlight entire rows when an order has a High priority and its status is Pending.
Enter this sample data into cells A1:D5:
Order | Customer | Priority | Status |
|---|---|---|---|
1001 | Acme Co. | High | Pending |
1002 | Smith LLC | Normal | Pending |
1003 | ABC Inc. | High | Complete |
1004 | Jones Co. | High | Pending |
Step 2: Create the Conditional Formatting Rule
Select your data, excluding the headers, A2:D5.
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter this formula:
=AND($C2="High",$D2="Pending")
Click Format, choose a light red fill color, and click OK on the following windows to apply the rule.
That's it! Excel will highlight orders 1001 and 1004 because they meet both conditions.
Step 3: Understand How the Formula Works
The AND() function checks two conditions:
$C2="High" checks whether the priority in column C is High.
$D2="Pending" checks whether the status in column D is Pending.
If both conditions are TRUE, then the AND function returns TRUE, and Excel highlights the row. Otherwise, it returns FALSE, and the row is not highlighted.
You can also use the OR() function if you want to highlight rows when either condition is TRUE instead of requiring both.
Notice the $ before the column letters. This keeps the formula checking columns C and D, even when formatting other cells in the row. The row numbers aren't locked, so Excel automatically adjusts the formula for each row.
Quick Tip
You can use almost any Excel formula in Conditional Formatting, as long as it evaluates to TRUE or FALSE for the cells being checked.
For example, the simple formula =$D2="Complete" could highlight an entire row when the order status is Complete.
Once you understand the TRUE/FALSE concept, you can build your own custom formatting rules for almost anything!
Excel AI
Fix or add features to your macros with the click of a button using our new tool.
Courses
Cheers and see you next week!
Don