Excel Tip: Simple Custom Numbers Formatting Based on Their Sign

[zaittworks]

With the Method shown in the video, you do not need to resort to conditional formatting.

Just go to Format Cells :: Custom and input your formatting rules:

i.e.: [blue] 0,00 " kg" ; [red] (0) " km" ; [green] "Nothing"

A detailed explanation is shown in the video below

https://youtu.be/47GuemDj8T4

[#2]

@zaittworks 

I'll have to say that I started viewing this with a lot of skepticism: "I mean, conditional formatting isn't all that difficult!!"

But I ended realizing that for some situations that could indeed be useful...I hadn't realized that custom formats could handle text mixed in with numbers, to say nothing of the colors. So I'll keep that in my bag of tricks.

I do have a question: Your example dealt only with positive, negative and zero values; can the custom formatting handle different colors based on values in the given cell compared with other cells? For example, a date within 30 days of another date in green, but greater than 30 days displays in black? Or does that kind of user-defined condition still require conditional formatting?

[#3]

@mathetes 

Hi there, thanks for the question. For that you DO need conditional formatting, at least as far as I know.

Your example is a perfect candidate for conditional formatting :)

The thing about this hack is that you can quickly type it in. Wehn more complex formats are required, I would turn to conditional formatting.

[#4]

@zaittworks 

Interesting.

So custom formatting hacks can do simple things with complexity.

While conditional formatting is required to do complex things simply.

I like the symmetry!!

[#5]

@mathetes 

:smiling_face_with_smiling_eyes: You hit it on the nail