What's Hot at TechRepublic
American Express Company, its subsidiaries and affiliates ('American Express') require that all the visitors to our site(s) on the World Wide Web that provide travel related products, services and content (the 'Travel Site') adhere to the following terms and conditions. How to Change Dropbox Account Settings and Preferences. You can access different levels of Dropbox account settings depending on which platform you are using your account. On any platform, access the 'Settings' or 'Preferences' menu, then.
Large numbers are a bit laborious to read, especially if there are many of them. To improve readability, you might want to reduce the number of digits, without losing the number's scale. For example, the sheet below shows a number of large numbers, some in the millions. 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.)
LEARN MORE: Office 365 Consumer pricing and features
Now, let's try a custom format and see what happens:
- Select the data range and press [Ctrl]+1 to display the Format Cells dialog.
- From the Category list, choose Custom.
- In the Type control, enter the $#,' M'; format string. The pound sign combined with the two comma characters displays a character in the millions position, if one exists. The ' M' component displays a literal M character, to denote millions.
- Click OK.
The results look good, but a couple of values disappeared! That's because those numbers are less than one million. You could add a few pound signs, but you might not get what you expect. For instance, repeat the instructions above and use the format string $#,###, ' M';. (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. 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 millions.
We have two formatting problems. 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. This format retains the scale, without losing any values and without misrepresenting the values!
The two comma characters retain the millions scale. The period character forces the value to truncate, while the last two pound signs display the appropriately rounded digits.
Microsoft Weekly Newsletter
Be your company's Microsoft insider with the help of these Windows and Office tutorials and our experts' analyses of Microsoft's enterprise products. Delivered Mondays and Wednesdays
![Esl questions about preferences Esl questions about preferences](/uploads/1/2/5/5/125519043/981893872.jpg)
Also see
![How do you usually express likes and dislikes How do you usually express likes and dislikes](/uploads/1/2/5/5/125519043/663763385.png)
- How to add a drop-down list to an Excel cell (TechRepublic)
- Tap into the power of Excel's data validation feature (free PDF) (TechRepublic)
- Six clicks: Excel power tips to make you an instant expert (ZDNet)
- A cheatsheet of Excel shortcuts that make inserting data faster (TechRepublic)
- You've been using Excel wrong all along (and that's OK) (ZDNet)
- Five ways to take advantage of Excel list features (TechRepublic)
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.