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

enter image description here


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

enter image description here

In for each condition I selected the format button

enter image description here

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.

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.