Numbers in Disguise

It is possible to get a cell to display one thing, but store another.
Create a Custom Format by right-clicking the cell, select Format Cells…, then from the Number tab select Custom.
In the Type box use whatever text you want displayed, but be sure to enclose it in “double-quotes”.

Disguised Number 1

I admit, this is a pretty dirty trick, but it may have legit uses.
Data Validation cell dropdowns also use this format. This makes it possible to select a text value from a list, but it will store the number instead!

Disguised Number 2

There’s probably a limit to the number of custom formats. I’m not sure what that limit is, but I reckon it’s big, because in many of the spreadsheets I’ve worked on there are way, way too many.

Posted in Uncategorized

15 thoughts on “Numbers in Disguise

  1. This trick has some very interesting uses I’ve used before. Unfortunately the limit of custom formats is 256 and has not been increased in 2007 like so many other limits. Maybe in 2010? haven’t checked yet.

  2. A1 is 1, A2 is 2, A3 is 3, A4 is 4, A5 is 5
    Custom Format for A1 is “Apple”, A2 is “Orange”, A3 is “Pear”, A4 is “Banana”, A5 is “Strawberry”
    A simple list dropdown using Data Validation, using A1:A5 as the source. Excel does the rest.

    It makes sense. It’s like how you can have a dropdown list of percentages, you select 50%, but it stores 0.5 instead.

  3. Another interesting behaviour

    a1 : custom format is [>10]”big”;[>-10]”OK”;”small”

    we get
    A1 = 15 see : big
    A1 = 5 OK
    A1 = -5 -OK ???
    A1 = -15 -small ???

    but when custom format is [>10]”big”;[<-10]”small”;”OK”

    we get
    A1 = 15 see : big
    A1 = 5 OK
    A1 = -5 -OK ???
    A1 = -15 small

    Is it property or bug?

  4. @Petrak

    It’s math!
    In your first scenario, only 15 is > 10, thus “big”
    Both 5 and -5 are > -10, so “ok”
    -15 does not meet any criteria, thus “small”

    Similarly, in your second scenario,
    only 15 is > 10, thus “big”
    only -15 is < -10, thus “small”
    5 and -5 do not meet either criteria, thus “ok”.

  5. @JKP – you jest surely! Why would you just not store the list and do a MATCH to get its index if you want an index value?

    This strikes me as a mildly amusing trick that has no real practical use, nothing that cannot be achieved more easily and clearly in other ways.

  6. The only time I’ve ever used this was in a Calendar to disguise the basis date. Beside that, I think it’s dirty, but worth of knowing.

  7. I haven’t used it to hide values but to format formula results, ie:

    “Answer is” #### “dollars”

  8. I use this when I want the user to visualize a boolean and the model I’m building is checking only whether the custom-formatted cell is blank. I use a capital X in quotes as the format. Occasionally I use it for April Fool’s day fun.

    Brett

  9. @ Luke M, Petrak has no problem with the maths. He has a question with what is displayed when the different formats are applied.
    a1 : custom format is [>10]”big”;[>-10]”OK”;”small”

    we get
    A1 = 15 see : big
    A1 = 5 OK
    A1 = -5 -OK ??? this displays “-OK”
    A1 = -15 -small ??? this displays “-small”

    but when custom format is [>10]”big”;[<-10]”small”;”OK”

    we get
    A1 = 15 see : big
    A1 = 5 OK
    A1 = -5 -OK ??? this displays “-OK”
    A1 = -15 small

    Is it property or bug?

Leave a Reply

Your email address will not be published. Required fields are marked *