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
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.