Use Conditional Formatting For Text In Mac Excel

Is there a way to use conditional formatting in the following manner in Excel? (For those who are curious, I am using Excel 2010 for Mac) I want Cell B2 to be green when A2 says 'John likes green apples' and B2 also says 'John likes green apples', yellow if A2 says 'John likes green apples' and B2 says 'Bill likes blue apples', and red if A2. To format as Text, select your data range and bring up the Format Cells dialog box. (Use the keyboard shortcut Control+1 on Windows or Command+1 on a Mac) Click on the Number tab, select Text in the Categories pane, and click OK.

Microsoft Excel is a vibrant tool that is used for both business and personal tasksHow To Use Microsoft Excel To Manage Your LifeHow To Use Microsoft Excel To Manage Your LifeIt's no secret that I'm a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities....Read More. One robust feature is called conditional formatting. This feature can come in handy for everyday situations as well as work-related ones. But, it can also be overwhelming if you have never used it.

Here are some general uses for conditional formatting, how to set them up, and of course, what exactly this feature is capable of doing.

Conditional Formatting Explained

This wonderful Excel feature applies formatting to cells based on the data populated. Whether you enter a number, percentage, letter, formula16 Excel Formulas that Will Help You Solve Real Life Problems16 Excel Formulas that Will Help You Solve Real Life ProblemsThe right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them.Read More, or word, the formatting will be applied automatically per the rules you set up.

For instance, if you want the cell to be highlighted in red every time you enter the letter A, this is a simple setup.

The tool has a variety of settings, options, and rules that can save time in spotting important items. And, for those who are familiar with the feature, it can be used quite extensively. However, it can also be advantageous for everyday tasks for school, home, work, and personal activities and for those who have never used it before.

Conditional Formatting For Text

For School: See Things Simpler

Whether in high school or college, you may keep track of your assignments, due dates, and grades in Excel. With a couple of convenient conditional formatting options, you can quickly see important items at a glance.

For due dates, you can apply formatting in a variety of ways to highlight what is past due, due tomorrow, or due in the next month. It just just takes a couple of steps:

Select the cells where you will be adding the due dates.
Click Conditional Formatting on the Home tab.
In the Highlight Cells Rules section, pick A Date Occurring.
Then, choose your criteria such as In the last 7 days with Red Text and Tomorrow with Yellow Fill.

This will help you see those important dates in a fast and easy way. Assignments that are past due or due tomorrow will pop right out.

For grade tracking and spotting your lower grades easily, you can apply formatting for both letter and number grades that include a range.

Select the cells where you will be adding the grades.
Click Conditional Formatting on the Home tab.
In the Highlight Cells Rules section, select Between.
Then, add your values such as between C and F or 0 and 2 with Red Fill.

This gives you a nice and simple way to see how well your classes are going and where your grades are lower.

For Home: Highlight What’s Important

Excel’s conditional formatting can be just as helpful if you use the application at home. For finances, home projects, and task lists10 Amazingly Useful Spreadsheet Templates to Organize Your Life10 Amazingly Useful Spreadsheet Templates to Organize Your LifeIs your life a mess of missed deadlines, forgotten shopping, and broken commitments? Sounds like you need to get organized.Read More, make those important items pop with a few simple steps.

If you use it for your monthly budget, for example, you can spot trouble quickly. Whether you have created your own spreadsheet or are using a handy template, applying formatting to negative numbers automatically is useful.

Select the cells where you will be entering the numbers or formulas.
Click Conditional Formatting on the Home tab.
In the Highlight Cells Rules section, select Less Than.
Then, add 0 with the highlight you would like. In this example, we selected Custom Format with Yellow Fill to make those cells really stand out.

Conditional Formatting Text Color

This provides a fast method for seeing negative numbers within your budget. And remember, you can apply whatever type of formatting makes it more noticeable to you from highlight to text to border color.

Excel is a popular tool for to-do lists as well and there is no better way to see items you have and have not completed than with automatic formatting. With this approach, any task that is not marked completed with an X will be highlighted.

Select the cells where you will be entering the completed indicator (X).
Click Conditional Formatting on the Home tab.
In the Highlight Cells Rules section, scroll down to More Rules.
In the pop-up window choose Format only cells that contain.
At the bottom under Edit the Rule Description, select Blanks from the drop-down box.
Then, click Format to pick the highlight you would like to apply, for instance, fill the cell with orange.

Applying this formatting takes only a minute and can be very helpful for keeping track of tasks that you have yet to complete.

For Personal: Make Goals Pop

Many people keep careful track of their personal goals. From workout sessions to weight maintenance to calorie counting10 Excel Templates To Track Your Health and Fitness10 Excel Templates To Track Your Health and FitnessRead More, you can see how well you are meeting those goals with cool formatting options.

For counting and tracking calorie intake, apply formatting to see when you go over and under your goal or limit for the day. In this instance, we will apply two separate rules.

Excel Conditional Formatting Match Text

Select the cells where you will be entering the number of calories.
Click Conditional Formatting on the Home tab.
In the Highlight Cells Rules section, select Greater Than.
Then, add the calorie goal number with the highlight you would like. In this example, we selected 200 with Red Fill. This will show when we go above the calorie limit.

Next set up the rule for going below the calorie limit.

Select the same cells as in the first rule.
Click Conditional Formatting on the Home
In the Highlight Cells Rules section, select Less Than.
Then, add the calorie goal number with the highlight you would like. In this example, we selected 200 with Green Fill.

Keep in mind that in this example, if you enter the exact number 200 for the calorie count, no formatting will be applied. This is because the number falls into neither rule.

For Business: Bring Out the Boldness

For those who work remotely or as independent contractors, Excel may be used for such tasks as time tracking or project estimates.

With a convenient way to keep track of hours worked as well as see an overview of the week or month, formatting with data bars can be quite useful.

Select the cells where you will be entering the number of hours.
Click Conditional Formatting on the Home tab.
In the Data Bars section, select either Gradient Fill or Solid Fill, depending on your preference.

Once you enter numbers into the designated cells, you will see the data bar adjust. The lower the number, the smaller the bar and vice versa. You can also apply the formatting to totals you have calculated at the bottom. This is a nice way to see your heaviest and lightest work days, weeks, or months.

Another great use of conditional formatting for business is for managing projects10 Powerful Excel Project Management Templates for Tracking10 Powerful Excel Project Management Templates for TrackingFree project management templates are the best. Get these key Microsoft Excel spreadsheet templates for tracking any project.Read More. Using indicator shapes and highlighting for items such as priority and effort can be beneficial, especially when sharing the data with others.

For setting priorities using numbers with 1 as the highest priority in red and 3 as the lowest in green, you can apply shape formatting.

Select the cells where you will be entering the numbers.
Click Conditional Formatting on the Home tab.
In the Icon Set section, select the shapes you would like to use.

Now as you enter the numbers 1, 2, and 3, you will see the shapes adjust their color3 Crazy Excel Formulas That Do Amazing Things3 Crazy Excel Formulas That Do Amazing ThingsExcel formulas have a powerful tool in conditional formatting. This article covers three ways to boost productivity with MS Excel.Read More. Keep in mind that if you use more than three numbers, which equals the three shapes, they will change accordingly. For instance, 1 will remain red, while 2 and 3 will turn yellow, and 4 will be green. You can also reverse the order of the shapes and assign specific numbers by selecting Conditional Formatting > Icon Sets > More Rules.

For setting the effort of a project or task, you can apply formatting using a specific word such as small, medium, or large.

Select the cells where you will be entering the effort word.
Click Conditional Formatting on the Home tab.
In the Highlight Cells Rules section, select Text That Contains.
Type in the word you want highlighted, such as Small and then choose the format, such as Yellow Fill. You can do the same for medium and large to give them each a different color.

Excel Conditional Formatting Multiple Words

With this type of rule set up, all you have to do is type in a word and the cell will be automatically formatted for you. This makes viewing your project or task efforts with a glance extremely easy.

Are You Ready to Apply Conditional Formatting?

Have you tried conditional formatting for everyday activities? Or are you ready to start having some fun with this feature?

Share your thoughts with us in the comments below!

Explore more about: Microsoft Excel, Microsoft Office Tips.

Conditional formatting quickly highlights important information in a spreadsheet. But sometimes the built-in formatting rules don’t go quite far enough. Adding your own formula to a conditional formatting rule gives it a power boost to help you do things the built-in rules can’t do.

Create conditional formatting rules with formula

For example, let’s say a doctors' office wants to track their patients’ birthdays to see whose birthday is coming up and then mark them as having received a Happy Birthday greeting from the office.

In this worksheet, we see the information we want by using conditional formatting, driven by two rules that each contain a formula. The first rule, in column A, formats future birthdays, and the rule in column C formats cells as soon as “Y” is entered, indicating that the birthday greeting has been sent.

To create the first rule:

Select cells A2 through A7. Do this by dragging from A2 to A7.
Then, click Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
Under Format values where this formula is true, type the formula: =A2>TODAY()
The formula uses the TODAY function to see if the dates in column A are greater than today (in the future). If so, the cells are formatted.
Click Format.
In the Color box, select Red. In the Font Style box, select Bold.
Click OK until the dialog boxes are closed.
The formatting is applied to column A.

To create the second rule:

Select cells C2 through C7.
Repeat steps 2 through 4 above, and enter this formula: =C2='Y'
The formula tests to see if the cells in column C contain “Y” (the quotation marks around the Y tell Excel that this is text). If so, the cells are formatted.
In the Color box, select White. In the Font Style box, select Bold.
Click the Fill tab and select Green.
The formatting is applied to column C.

Try it out

You can copy the following table to a worksheet in Excel – be sure to paste it into cell A1. Then, select cells D2:D11, and create a new conditional formatting rule that uses the formula below.

=COUNTIF($D$2:$D$11,D2)>1

When you create the rule, make sure it applies to cells D2:D11. Set a color format to be applied to cells that match the criteria (that is, there is more than one instance of a city in the D column – Seattle and Spokane).

First	Last	Phone	City
Annik	Stahl	555-1213	Seattle
Josh	Barnhill	555-1214	Portland
Colin	Wilcox	555-1215	Spokane
Harry	Miller	555-1216	Edmonds
Jonathan	Foster	555-1217	Atlanta
Erin	Hagens	555-1218	Spokane
Jeff	Phillips	555-1219	Charleston
Gordon	Hee	555-1220	Youngstown
Yossi	Ran	555-1221	Seattle
Anna	Bedecs	555-1222	San Francisco

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

excredlohua1987’s diary