Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.

Reader Rolando J. wins a copy of the book Excel for the CEO for his question: “How do you display numerical values in thousands while retaining the numerical values? For presentation purposes you may want to display the dollars in thousands (or in millions).”

This used to be easier in Excel. Actually, it hasn’t gotten any more difficult to select thousands or millions. The issue is that it has become far easier to select other number formats.

Starting in Excel 2007, Microsoft offered this dropdown menu from the center of the Home tab of the ribbon. The dropdown makes it so easy to choose from a few formats that you might forget that there are hundreds of other formats in the old Format Cells dialog box.

To get to the complete list of number formats, either choose More Number Formats… from the bottom of Figure 1 (below) or simply type Ctrl 1 to access the Format Cells dialog box.

The Format Cells dialog box offers categories such as Number, Currency, and Accounting. Choose the closest category. Choose your currency symbol, decimals, and/or thousands separator as appropriate.

Fig. 1

In Figure 2 (below), the numbers would be formatted with no decimal places and a thousands separator.

Fig. 2

After choosing a number format, select the Custom entry from the bottom of the Category list in the Format Cells dialog box. A new “Type:” box will appear in the dialog box. This box will start out with the somewhat cryptic characters that are used to represent your selected format. In Figure 3 (below), the number format is “#,##0.”

Fig. 3

You are allowed to click on the Type box and add new characters to the format. Every comma you type at the end of the number format will cause the number to be displayed divided by one thousand. Type a single comma at the end of the number format like this: “#,##0” to display numbers in thousands (see Figure 4 below).

Fig. 4

Type two commas: “#,##0,,” to display numbers in millions. After adding commas to the custom number format, click OK. Note that these settings do not change the actual number in the cell.

Your calculations will continue to work as before. This custom number format will only change the way the numbers are displayed.

Figure 5 (below) shows a section of a balance sheet in dollars, thousands, and millions. The bottom example uses #,##0.0,, to show one decimal place along with the millions. This allows readers to see data in hundreds of thousands. You might note in the report heading that the numbers are in (000) or you might want to add an abbreviation such as K, M, or MM after each value. If you use “K” as your thousands abbreviation, then a number format of #,##0,K will work fine. If you want to use M or MM as an abbreviation, then you must either surround the M with quotes or precede it with a backslash in the number format. The examples in Figure 6 (below) show several custom number formats and their results.

Fig. 5

Fig. 6

Bill Jelen is the author of 32 books, including Excel Gurus Gone Wild, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.