Shorten numbers & format to display thousands as "K" , millions as"M" but still remain as numbers

Peter Hui

Hello everyone,

Good day.

I'd like to ask your help regarding a task I'm trying to complete on Smartsheet. I'm trying to shorten the numbers in a table show thousands with "K" and millions with "M" after the first comma instead of showing the entire number. I found the thread below and it gave me the rounding and formatting results. However, there's also another requirement. I have to show the numbers in chart on a dashboard. The issue I have now is that the chart is not showing the values because they are no longer "numbers".

Is there a way to apply the rounding and formatting but keep the resulting value as a "number" so that the chart can pick them up?

Hope you could share your inputs.

Thank you.

Best regards,

Peter

https://community.smartsheet.com/discussion/25186/help-on-formula-format-numbers-and-shorten-to-k-thousand-m-million-b-billion

Mark Cronk

Hi @Peter Hui ,

Did you find a solution? I don't think there is one using just formatting. It would make a great enhancement suggestion, link below.

There's a workaround if you think the effort is worth it. You could use the formatting solution you found for your table so it has the clean appearance. Then create a separate linked sheet that either recalculated your table or removes the formatting, converting text back to numbers, and use that to drive your charts. One sheet for the table display and 1 sheet for charting.

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?ss_v=114.0.2

Help?

Mark

Peter Hui

Hello Mark,

Thank you for sharing your inputs.

My goal is to have the rounded/reformatted numbers appear in the chart (a bar graph). Due to the number of items being tracked, the chart looks so busy with all the data sets displayed in their original format. That is why I was looking for a way to apply the rounding/formatting but still keep them as numbers so the display in the chart can be simplified and the numbers don't overlap.

I will follow your advice and submit an enhancement request.

Thanks,

Peter

svenu

Thanks for opening this thread @Peter Hui.

I have raw data that is in thousands and millions which when plotted, makes the graph look super busy. This would be a super useful enhancement feature to make the data look concise.

Raw Data:

The graph doesn't pick up the values in "K" or "M" as they are text values:

Regards

Sowmya

Anna S

Is there any update on this?