Conditional Custom Formatting in #Excel

Got the following question:

This gives me £28.8 K (if 28800 is input) and £28.0 K if 28000 is input. I was rather hoping that if there was no decimal after the thousand, ie £28 K, this would end up as £28 K, and not £28.0 K, but I guess that is not possible.

Here’s a screenshot illustrating the question:

Is it not possible????? Nothing is impossible, especially during Christmas.  🙂

Before we dive into the steps to achieve it, let’s see the custom formats for $28.0 k and $28 k first: 

$#,##0.0, k --> $28.0 k

$#,##0, k --> $28 k

See the tiny difference?  

Let’s put them into action.  You may download a to Sample File – Conditional Custom Format to follow along.

1. Apply custom format for $28.0 k

Select the range –> Ctrl+1 to open the Format Cells –> Number tab –> Custom –> Type $#,##0.0, k –> OK

Tip: The negative part in the custom format is optional here.  If omitted, Excel will assume the format for negative number be the same as that for positive number.  Want to know more about Custom Format?  Matthew Kuo of mbaexcel has a detailed post on it: http://www.mbaexcel.com/excel/how-to-use-excel-custom-number-formatting/

Now we should get the following:

However it has not taken care of the whole thousand numbers yet.

No worries.  Conditional Formatting to rescue.

2. Set the Condition Formatting

Select the range (A1:A6 in our example) –> Home Tab –> Conditional Formatting –> New Rules…

The trick is to set the rule to identify if the number is in whole thousands.  To do that, we use the formula:

=mod(A1,1000)=0  
'this formula checks if the residue by dividing 1000 is equal to 0

Note: A1 is a relative reference.  Also ensure A1 is the active cell of the range selected.

When the condition is fulfilled, then apply the custom format for $28 k (no decimal).

3. Now we’ve got the results.

Isn’t it a Christmas miracle? 🙂