The period character forces the value to truncate, while the last two pound signs display the appropriately rounded digits.The SUM function allows you to add 2 or more numbers together. The two comma characters retain the millions scale. SEE: 30 things you should never do in Microsoft Office (free PDF) (TechRepublic) This format retains the scale, without losing any values and without misrepresenting the values! How can we express millions and less than one million using the same custom format? Let’s try one more time using the format string $#.#,” M” –the character to the right of the pound sign is a period character, not a comma. You don’t want to imply that $21 thousand is actual $21 million, but that’s what this custom format does! In addition, someone might interpret $1,794 M as $1,794 million s. (The period after the semi-colon is grammatical and not part of the format string.) As you can see below, the results aren’t accurate. For instance, repeat the instructions above and use the format string $#,#, ” M”. You could add a few pound signs, but you might not get what you expect. The results look good, but a couple of values disappeared! That’s because those numbers are less than one million. The ” M” component displays a literal M character, to denote millions. The pound sign combined with the two comma characters displays a character in the millions position, if one exists. In the Type control, enter the $#,” M” format string.Select the data range and press +1 to display the Format Cells dialog.Now, let’s try a custom format and see what happens: LEARN MORE: Office 365 Consumer pricing and features We can reduce the number of digits using a custom format, but be careful you might get some unexpected results! (We’re specifically working with millions, but you can apply this technique to any scale.) For example, the sheet below shows a number of large numbers, some in the millions. To improve readability, you might want to reduce the number of digits, without losing the number’s scale. Large numbers are a bit laborious to read, especially if there are many of them. TechRepublic Premium editorial calendar: IT policies, checklists, toolkits, and research for download Get Microsoft Office Pro and lifetime access to 5 top apps for $59.99 8 best HR analytics tools and software for your business in 2023

Excel add commas in numbers