Contents

HOW TO USE CONDITIONAL FORMATTING BASED ON TEXT IN EXCEL

INTRODUCTION

Whenever we prepare any report in excel, we have two constituents in any report.

The Text portion and the Numerical portion.

But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.

Many functions are provided by the Excel which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.

THIS WAS AN EXCERPT FROM THE FIRST ARTICLE OF THIS SERIES MANIPULATING TEXT IN EXCEL – PART I

In this articles , we will continue learning many more techniques about the manipulation of text in Excel.

 

 

HOW TEXT IS HANDLED IN EXCEL?

TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI].
Text comprises of the individual entity character which is the smallest bit which would be found in Excel.
We can perform the operations on the strings[Text] or the characters.
Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the later part of the article.
 
TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]
If we need to make anything inactive, such as Date to be non responding to the calculation, we put it as a text. Similarly if we want to avoid any calculations for a number it needs to be put as a text.
 
 
 
 
 

CONDITIONAL FORMATTING BASED ON TEXT

CONDITIONAL FORMATTING is the process of formatting in Excel on the basis of the conditions. We can put many conditions in the cell and program the Excel to make the formatting , as desired, if the particular condition is met. Formatting comprises of the foreground color, background color, font, size etc. which are the properties of the text.

It makes the results more readable.

IT IS RECOMMENDED TO LEARN THE BASICS ABOUT CONDITIONAL FORMATTING, IF NOT VERY COMFORTABLE WITH THIS TOPIC.

CLICK HERE TO LEARN ABOUT CONDITIONAL FORMATTING.

Maximum times, we apply the conditional formatting on the basis of values present in the cells. Let us now learn the way by which we can apply the conditional formatting using the Text.

HIGHLIGHTING THE TEXT EQUAL TO SOME VALUE:

Let us find out the cell which contains a text value equal to some SPECIFIED TEXT.

For the example let us take this block of text values in Excel.

HELLOHOWARE YOURED
GREENYELLOWBLUEGREYWHITE
BIGBUFFALOZEBRAXYLOPHONESMALL
BLACKAPPLEMANGOGUAVAONION
PUMPKINOKRAKIWIPOTATOSPRING

 Let us try to highlight the cells containing HELLO.

It can be done in two ways.

1. Using the predefined options.

2. Using custom formula.

 STEPS to highlight cells using the predefined option:

  •  Select the complete table.
  • Go to HOME TAB>CONDITIONAL FORMATTING>HIGHLIGHT CELL RULES>EQUAL TO
 
 
CONDITIONAL FORMATTING USING TEXT
HIGHLIGHTING A CELL CONTAINING A SPECIFIC WORD
  • Click EQUAL TO.
  • Following dialog box will open.
  • Enter the value in the field as shown in the picture.
  • Select the format of the cells which satisfy the condition.
  • and Click OK.
ENTER THE VALUE AND SET THE FORMAT
  • After clicking OK, we will see the result as the cell with HELLO will be in pink. [The option doesn’t differ the cases. Both of upper and lower cases will be taken as same.]
  • Similarly we can find other values too.
CONDITIONAL FORMATTING IN TEXT
ENTERING THE VALUES AND RESULT

 

2. Using custom formula.

 STEPS to highlight cells using the custom formula:

  •  Select the complete table.
  • Go to HOME TAB>CONDITIONAL FORMATTING>NEW RULE.
  • A dialog box as shown in the picture below will open.
  • Click the last option: use a formula to determine which cells to format.
CUSTOM FORMULA CONDITIONAL FORMATTING IN TEXT
CUSTOM FORMULA FOR CONDITIONAL FORMATTING IN EXCEL
  •  Enter the formula in the field as =E6=”HELLO”.
  • Set the format as per your choice. We set the fill color as green.[Don’t forget to change the format otherwise difference won’t be visible.]
  • Click OK.
  • The result will be highlighted as shown in the picture below.
 

For the concept OF CONDITIONAL FORMATTING CLICK HERE.

EXAMPLE 2:STEPS TO  HIGHLIGHT THE TEXT MATCHING THE GIVEN TEXT [EXACT MATCH]

In this example, let us find out the way to highlight the cells which contains the text same as some specified text exactly with the same content and with the same case.
 
GENERALIZED FORMULA TO HIGHLIGHT THE EXACT TEXT:
=EXACT(FIRST CELL OF SELECTION, “TEXT TO BE MATCHED”).
For our example, we will highlight the cell containing the text “Yellow”.
Follow the steps.
  • Select the complete table.
  • Go to HOME TAB>CONDITIONAL FORMATTING>NEW RULE.
  • A dialog box as shown in the picture below will open.
  • Click the last option: use a formula to determine which cells to format.
  • Enter the formula =EXACT(E6,”Yellow”).
  • Choose the format of your choice, which will appear when some cell fulfill the condition.
  • Click OK.
MATCH ANY TEXT AND CONDITION FORMATTING IN EXCEL
HIGHLIGHTING A CELL CONTAINING A SPECIFIC WORD

For the concept OF CONDITIONAL FORMATTING CLICK HERE.

EXAMPLE 3: STEPS TO  HIGHLIGHT THE CELLS IF TEXT CONTAIN A SPECIFIC CHARACTER

Suppose we want to create a condition when we want to highlight the cell if the cell contains any specific character say # for our example.
GENERALIZED FORMULA TO HIGHLIGHT THE CELLS WHICH CONTAIN A SPECIFIC CHARACTER
  • Select the complete table.
  • Go to HOME TAB>CONDITIONAL FORMATTING>NEW RULE.
  • A dialog box as shown in the picture below will open.
  • Click the last option: use a formula to determine which cells to format.
HIGHLIGHTING THE CELLS WHICH CONTAINS THE TEXT COMPRISING OF A SPECIFIC CHARACTER

THERE’S ALWAYS ROOM FOR IMPROVEMENT.

KINDLY GIVE YOUR VALUABLE SUGGESTIONS.

Leave a Reply

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

KINDLY SPEND FEW SECONDS HERE....

-We might be compensated for the visits or purchases you make by clicking the ads on this website .  They help us to keep bringing excellent quality articles for you.
-Click on the pictures if you can’t read. All of them are High Resolution .
-If you can’t find what you were looking for, kindly message in the chat box.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW