Large numbers are often rounded to millions, or simply the number with the letter "M" or the word of "million". For example, 4,569,885 is 4.57 million or 4.57 M. Please check how to format Axis labels as millions if you work with a chart.
1. To Add "M"
If you want to change a large number to "Number + M", you can use the formula below.
=TEXT(A2,"#.00,, \M")
Where "#.00" is to keep the result 2 digits.
The formula is to convert a number to the million with adding the letter "M" at the end with the general rounding rule. When the hundred thousands digit is 5 or larger, the number rounds up, and when the hundred thousands digit is less than 5, the number rounds down.
2. Combine with "Million" or "M"
Another way is to round the large numbers into nearest million first, then to combine with the work of "Million" with the CONCAT function with the formula below.
=TEXT(A2/1000000,"#,###,###.##")&" Million"
or to change "Million" to "M" with the following formula.
=TEXT(A2/1000000,"#,###,###.##")&" M"
Please note that when the number is small, the number will be rounded out and the result returns to ".Million". To avoid this situation, please use the IF function to condition it out. For example, if you want to keep 2 digits, 5,000 will round to be 0.01M. Any number less than 5,000 will be ignored.
If you work with Excel 2016 or earlier, you can use the IF function as below:
=IF(A2<5000,"",IF(A2>=5000,TEXT(A2/1000000,"#,###.##")&" M"))
If you work with Excel 2019 and after, you can use the IFS function as below:
=IFS(A2<5000,"",A2>=5000,TEXT(A2/1000000,"#,###.##")&" M")
