Data label and Axis values are not in compact for Italy region

Question

I have created a waterfall chart with set of datapoints including positive and negative numbers and applied custom number format as below

Positive number Data format - [>999999999]$##.0,,,"B";[>999999] $##.0,,"M"; $##.0

Negative number Data format- $##.0,,"M"

With US region settings (dot as decimal separator) datapoints and axis values are in compact format. However, when we switch to Italy region settings (comma as decimal separator) datapoints and axis values are not showing in compact format for both positive and negative numbers.

Could someone explain why this inconsistency occurs and suggest how to ensure that all data labels and axis values in the chart consistently use the compact number format for other regions?
With US region settings:

With Italy region settings:

Refer the settings below:
US region settings:

Italy region settings:

Answer 1

Good day @Shivapriya Saravanan,

Thank you for your thoughtful question. I very much hope I can share a bit of my own perspective here, based on what I’ve learned and observed.

From what I understand, if in Excel you turn off “Use system separators” and manually enter your own decimal and thousand separators, Excel will strictly use the exact pair of symbols you specify, regardless of the regional settings applied to the system. When those manually defined separators do not match the logic used in the custom number format you typed, display issues can easily occur.

In other words, this is generally expected behavior and a known limitation of Excel. When you enter a custom format such as ##.0,,,"B", Excel saves that format string exactly as it is in the file. It does not automatically convert the dot to a comma or replace ,,, with another equivalent representation when you later switch the region. The format itself stays unchanged.

When you change the region to Italy, Excel only changes the way it reads and parses numbers based on the local rules. In the Italy locale, a comma is used as the decimal separator and a dot is used as the thousand separator. Because of that, a US-style format string containing dots and commas for scaling can be interpreted differently, or Excel may no longer recognize the scaling logic at all.

This becomes especially important because, in Excel custom formats, the thousand separator is not just a visual separator. It also acts as a scaling operator. For instance, a format like #,###.0, implicitly divides the value by 1,000. Since the character used for thousand separation and scaling depends on the regional settings, what works with commas in the US may stop working when you switch to Italy, where dots are expected instead. As a result, sequences like ,, or ,,, can be treated as ordinary characters rather than scale operators, which is why you might see a value such as 30988715226,,,B displayed instead of a compact label like $30,9B.

The most reliable workaround at the moment is to rewrite the custom format so that it matches the Italy regional conventions. That means using a comma for decimals and dots for scaling. For example, a format like [>999999999]$##,0..."B";[>999999] $##,0.."M"; $##,0 for positive numbers, and $##,0.."M" for negative numbers, aligns with how Excel expects to read numbers under Italy settings. 

I know this is not ideal, and I understand the expectation that Excel would handle this conversion automatically. Unfortunately, as this is a user-to-user support forum, I do not have the ability to influence or change how the product currently works. My intent is simply to share what I know and offer practical guidance based on experience and community knowledge.

If my response happens to be helpful or offers you a useful perspective on the issue, you may gently consider clicking “Accept Answer” for this post. Doing so helps highlight and surface this discussion for other users who might be experiencing the same concern, or for those who may have additional ideas or possible workarounds to share. It also reduces the chance that the thread is overlooked by community members who are interested and willing to contribute further insights.

Thank you very much again for your time and cooperation.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.