CODEWITHSUNDEEP
excelformatting
jsky
jsky

Reputation: 2217

How to format Microsoft Excel data labels without trailing decimal on round values?

(Not sure if this is the best stack for this question).

What is the format to have 14500 read as $14.5K but to have 3000 read as just $3K without the trailing decimal point? I can't seem to comprehend the correct expression from the manual.

enter image description here

Upvotes: 1

Views: 22906

Answers (2)

jsky
jsky

Reputation: 2217

To get this to work, I formatted the cell's of the data column 4 4 4 4 3.5 13.5, by either selecting the column and then right click and format cells or by right clicking on the chart and selecting format data labels. I formatted this with the regular expression $#K so that the data then shows as $4K $4K $4K $4K $4K $14K.
The consequence is that the number is rounded to not include the decimal.
Now, all i needed to do was separately format the individual cell's that had decimal values by right clicking on the individual cell's and changing their format to #.0K.
Thus we now have, $4K $4K $4K $4K $3.5K $13.5K

Also, the , can be used in the regex ($#,K for round numbers, $#.0,K for decimal) for data of thousands, like my original question.

This was an acceptable solution for me because there were only two such fractional data points.
For a larger dataset, you will need to use a conditional expression to determine all the cell's that have decimal values.

One way to do this, is like so: If your numbers are in column B, apply this formula for column C =B1=INT(B1)
This will show TRUE if the data is of INT data type (no decimal precision) and FALSE if not.

Now, select column C and select Data\Filter\Autofilter From the drop-down list in C1, select FALSE This will show only the decimal numbers and hide the whole numbers.
And now you can apply the relevent formatting as described above.

Upvotes: 0

Brett Wolfington
Brett Wolfington

Reputation: 6627

Excel does not appear to support this functionality using custom format codes. However, there is a way to simulate this. Create a new column that uses the function ="$"&A1/1000&"K".

Original   ="$"&A1/1000&"K"
$3,500     $3.5K
$3,000     $3K
$14,500    $14.5K
$13,300    $13.3K
$25,000    $25K
$250,000   $250K

Upvotes: 1

Related Questions