Skip to main content

How to display different text than the value in same cell?

Normally, the entered data will be displayed in formula bar as same as the cell as the below first screenshot shown, but, have you ever tried to display a different text than the value in the same cell? It means, the text in cell is different from the value displayed in formula bar as the second screenshot shown. To deal with this job in Excel, this article will provide an easy method.

doc different text than value 1

Display different text than the value in same cell with Conditional Formatting

Make the displayed text equal the actual value with Kutools for Excel


arrow blue right bubble Display different text than the value in same cell with Conditional Formatting

For example, I want the cell C1 display a word Monday but has a value of 1 in formula bar, C2 displays a word Tuesday but has a value of 2, etc as following screenshot shown:

doc different text than value 2

1. Select the cell values that you want to display them with different text, and then click Home > Conditional Formatting > New Rule, see screenshot:

doc different text than value 3

2. Then in the New Formatting Rule dialog box, click Use a formula to determine which cells to format under the Select a Rule Type list box, and then enter this formula:=c1=1 (C1 is the first cell of the selected cells that you want to use, and the number 1 is the cell value that you want to be displayed with a different text in formula bar.) into the Format values where this formula is true text box, see screenshot:

doc different text than value 4

3. Then click Format button to go to the Format Cells dialog box, and then click Number tab, and select Custom from the left Category pane, then enter the text that you want to be displayed in the same cell, this example, I will enter “Monday” into the Type text box, see screenshot:

doc different text than value 5

Note: The text should be surrounded with the double quotes.

4. And then click OK > OK to close the dialogs, and the number 1 in the cells has been displayed as the text Monday as following screenshot shown:

doc different text than value 6

Note: To display other cell values with different text, please repeat the formula into the Conditional Formatting dialog box, and enter the specific text you need to custom the cells in the Format Cells dialog, as below shown:

Conditional formula Format (Number: Custom)
=C1=2 "Tuesday"
=C1=3 "Wednesday"
=C1=4 "Thursday"
=C1=5 "Friday"
=C1=6 "Saturday"
=C1=7 "Sunday"

arrow blue right bubble Make the displayed text equal the actual value with Kutools for Excel

Sometimes, you just need to make the displayed text equal the actual value, how could you finish it in Excel?

If you have Kutools for Excel, with its To Actual feature, you can solve this task with one click.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. 

After installing Kutools for Excel, please do as this:

1. Select the cells, that you want to make the displayed text equal the actual value, and then click Kutools > To Actual, see screenshot:

2. And the displayed text have been converted to the actual values at once, see screenshot:

doc different text than value 8

Click to Download Kutools for Excel and free trial Now!

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I can make this work if I copy your example. However, if I decide that I want the new values to appear in Col D and the original values to remain in Col C, I can get Col D to show all correct formatting except the value it self. It will show borders, fill color, etc., but not the hard coded "custom" value. Ideas?
This comment was minimized by the moderator on the site
You then need to use the -> "Use a formula to determine wich cells to format" Then use the =IF(C1=1;true;false) and then do the same in Format... as you was doing before.
This comment was minimized by the moderator on the site
Its amazing I just wished the displayed text would stay even in dropdown lists
This comment was minimized by the moderator on the site
Thanks, works perfectly except for TRUE and FALSE? Any ideas why?
This comment was minimized by the moderator on the site
This is awesome. Thanks
This comment was minimized by the moderator on the site
This is amazing. Thanks
This comment was minimized by the moderator on the site
How else can I make the displayed text equal the actual value if I don't have Kutools?
This comment was minimized by the moderator on the site
Awesome. Thanks for this, it exactly solved what I needed to do.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations