Display descriptive words instead of numbers with Custom Format in #Excel

Sometimes we want to add a descriptive like “Good Job” for figures above target; “Work Harder” for those below… like the following screenshot:

Excel Tip - Turn Numbers into Descriptives

To many people, the top-of-mind solution should be using IF function.

=IF(A2>0, "Good Job", IF(A2=0, "Just Met","Work Harder"))

This formula does the job fairly easy! 🙂

One drawback is, we will need a helper column to achieve this.  In some cases, we want the descriptives only; showing the numbers is not necessary.  For this case, Custom Format should be the go-to approach.

Format Cells –> Number Tab –> Custom –> Input the following into Type:  

Excel Tip - Turn Numbers into Descriptives1

Here’s the result:

See the values in formula bar vs. the cell contents?!

 

That is the result from the following custom format:

"Good Job"; "Work Harder"; "Just Met";@

The custom format comes with four portions: Positive Number; Negative Number; ZeroText (separate by ; ) 

So the above custom format means:

  • For positive number –> Display “Good Job”
  • For negative number –> Display “Work Harder”
  • For zero –> Display “Just Met”
  • For text –> Display whatever input (as represent by the @ symbol)

Note: We need double quote “” for text to be displayed

As simple as this.

You may download a Sample File to follow along.

 

Want some fun with your colleague?

"Silly"; "Silly"; "Silly"; "Silly"

No matter what the user input into cells with this custom format, s/he will only see the word “Silly”.

OR

You may put secret messages to someone whom you wish to express your special feelings to:

"Love you"; "Think of you"; "Miss you"; "???? You"

Disclaimer: Do it at your own risk. 😛

For more about custom format, you may refer to this page from support.office.

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Format and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.