change number format in cell depending on value in another cell

Copper Contributor



Using Excel for Mac 2011. I want to change the number format of a cell depending on the value of text in an adjacent cell. So for example, in cell A i have the a drop down list of options "%" , "M2" , "Unit" etc, and in cell B, if cell A is "%", then I want the number format to be percentage, but if Cell A is M2, I want it to be a whole number, as per example below

     A            B



I looked in conditional formating, but in Mac  2011, the only options are for colour highlights, font changes etc, not number formats. Am I missing something, or is there another way to achieve this without using macros? If not in excel Mac 2011, is this feature avaialble in later editions, I am well due an upgrade!




many thanks



1 Reply

Hello @maxgolaz,


That could be done with a series of IF() and TEXT() functions. For example, to search for "%" or "M2" as a unit would be:

=IF($A1="%",TEXT($B1,"0%"),IF($A1="M2",TEXT($B1,"0"),"NO SUCH FORMAT"))

 The series of IF() statements could be extended by replacing "NO SUCH FORMAT" with additional IF() statements.