Excel 2007 : Cell Format

**HangMan** · 06-22-2012, 02:21 PM

Cell A1 displays the output of various different cells on a worksheet, however the information in the other cells can be formatted as either a number or as a percentage, e.g. Cell C1 may = 23,452, where as Cell D1 may = 79.23%.

How can I maintain the correct format for the figure displayed in Cell A1, i.e., when A1 displays the contents of C1, I want it to display as a number and when A1 displays the contents of D1, I want it to display as a percentage without having to change the cell format for cell A1? I want cell A1 to display 79.23% and not 0.7923!

**dilipandey** · 06-22-2012, 02:26 PM

Hi HangMan,

You can use Text function here... when A1 displays the content of c1, use =TEXT(c1,"00,000") and when A1 displays the content of d1, use =TEXT(F8,"0.00%")... this kind of formulation can be triggered using the logic which triggers the use of A1 and C1. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**HangMan** · 06-22-2012, 02:32 PM

Hi DILIPandey,

I'm not sure I follow. Doesn't this mean you would constantly have to change the format of Cell A1? I want to keep one format for Cell A1 but somehow allow it to display either numbers or percentages e.g. a number would be dispalyed as 23,462 and a percentage as 79.32% (rather than as 0.7932). I don't know if this makes sense?

Can you elaborate a little as to how to use the Text function, as I'm not sure how this would be implemented?

**dilipandey** · 06-22-2012, 02:45 PM

Hi HangMan,

That is perfectly making sense... and that is where I have suggested Text function to control the format appearances :-

You can use text function as :- =TEXT(reference,"0.00%") to display the result in percentage - as an example.. for other formats available and how to use them, suggest you to see the help feature of Ms excel on this function

Regards,
DILIPandey

<click on below 'star' if this helps>

**Pete_UK** · 06-22-2012, 02:52 PM

Hangman,

you can apply this custom number format to cell A1:

[>1] #,##0;[<=1]##0.0%

so that anything larger than 1 is displayed as a number with no decimal places, whereas anything less than 1 is shown as a percentage with 1 dp. The attached file shows this in action - just type something in A1 like =C1 or =D1 or even just a number to see the effect. You can use the Format Painter icon to apply that format to other cells.

Hope this helps.

Pete

**HangMan** · 06-22-2012, 04:09 PM

DILIPandey,

Many thnaks for your input. I couldn't see how your solution would work in practice as it seemed to me that I would have to constantly change the format of cell A1, but many thanks for your input and help. Pete_UK's solution is exactly what I was looking for...