Show specific text based on value in Excel

Supposing, you have a list of values, now, you would like to show a specific text based on the value. For example, if cell value is greater than 1000, then “Increase” text is displayed in another column as below screenshot shown. To deal with this task, both the REPT and IF functions can do you a favor.


Show specific text based on value with REPT function

In Excel, the normal REPT function is used to repeat the characters a specified number of times. But, here, I will use the REPT function to display specified text based on cell value. The generic syntax is:

=REPT("message",logical test)
  • message: The specific text that you want to be displayed.
  • logical test: The logical criteria to display the text based on.

1. Please enter or copy the below formula into a blank cell:

=REPT("Increase",A2>1000)

Note: In the above formula, “Increase” is the specific text you want to display, “A2 > 1000” is the criteria to be based on, you can change them to your need.

2. Then, drag the fill handle down to the cells that you want to apply this formula, and the specific text will be displayed if meet the criteria, see screenshot:

Tips: This RETP function also can be applied for multiple criteria, if you want to use OR logic, you should combine multiple REPT functions with the & character, and the AND logic can be expressed with the * character.

For example, to display the text “Increase” if the cell value is greater than 1000, if the value is between 500 and 1000, “Normal” text is displayed, otherwise, a “Decrease” text is displayed. Please copy the below formula into a blank cell:

=REPT("Increase",A2>1000) & REPT("Normal",(A2>500)*(A2<=1000)) & REPT("Decrease",A2<=500)

And then, copy the formula to other cells you want to apply this formula, and you will get the result as following screenshot shown:


Show specific text based on value with IF function

In Excel, the IF function also can help you to solve this task. The generic syntax is:

=IF (logical_test, [value_if_true], [value_if_false])
  • logical_test: It is the condition that you want to test.
  • value_if_true: A specific value that you want returned if the logical_test result is TRUE.
  • value_if_false: A value that you want returned if the logical_test result is FALSE.

Type or copy the below formula into a blank cell, and then drag the fill handle down to the cells you want to use this formula, and the specific text will be displayed based on the criteria you designed.

=IF(A2>1000,"Increase","")

Tips: For more criteria, please apply the nested IF function:

=IF(A2>1000,"Increase",IF(A2>500,"Normal","Decrease"))


Relative functions used:

  • REPT:
  • The REPT function is used to repeat the characters a specified number of times.
  • IF:
  • The IF function is used to test for a specific condition, returns the corresponding value that you supply for TRUE or FALSE.

More articles:

  • Check If Cell Equals Any Value In List
  • For example, I have a list of text strings in column A, and now, I want to check if cell value in this column equals any value in another list of column E. If equals, display TRUE, otherwise, a FALSE value is displayed as below screenshot shown.
  • If cell contains text then display in Excel
  • If you have a list of text strings in column A, and a row of keywords, now, you need to check if the keywords are appear in the text string. If the keywords appear in the cell, displaying it, if not, blank cell is displayed as following screenshot shown.
  • Count Keywords Cell Contains Based On A List
  • If you want to count the number of keywords appears in a cell based on a list of cells, the combination of the SUMPRODUCT, ISNUMBER and SEARCH functions may help you to solve this problem in Excel.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.