## 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? 🙂

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

### 6 Responses to #,##0, k or #,##0.0, k ? Why not both?

1. Jeff Weir says:

Cool hack!

Like

• MF says:

Thanks 😄

Like

2. xlor.la says:

Thanks for this christmas present! greetings from Lima, Perú!

Like

3. Yea thanks for this christmas present!, cheers from Lima Perú!

Like

4. Anne Broxton says:

Thank you so much for the Christmas miracle. This has worked exactly as I wanted. Wishing you and yours a very Happy Christmas and a peaceful New Year.

Like

• MF says:

You are welcome. Glad it helps 😀
Wish you a Merry Christmas 🎄 and Happy new year 🎊 too

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.