# excel format: scale is 2 if value is larger than 1000

is there a way to define cell format for a number value, such that if it is more than 1000, then show only 2 digits on the right side of the decimal point, otherwise shows 4?

e.g. if the value is 1.259738, shows as 1.2597; if the value is 14579.9978, shows as 14,579.00

i'd like to have a solution via format, not modifying the cell formula as that would disturb computation.

Use this Custom Format:

[>1000] 0.00;[<1000]0.0000

Yes you can.

You need to use conditional formatting and multiple rules. I put the test data in J7 and J8 of 1.22345 and 10222.354 respectively. I then selected the range J7 to J8 and with the active cell being J7 I entered the following formula in to conditional formatting:

=J7<10000   'Condition 1

and then

=J7>=10000  'Condition 2

In for each condition I selected the format button

From the numbers tab I selected Number in the category window and adjusted the number of decimal places to the desired amount. If you wanted a different format browse for something that suits your needs or built your own custom format.