Broad Strokes
Number codes are worth the time to memorize; knowledge is power
Number formatting codes can be your best friend, particularly if you’re copying tabulated data directly from Excel into another Office application. This syntax is particularly useful to know when using the TEXT() formula (which formats a numerical value as text, based on the number code you provide).
There are several features of number codes that make them extremely convenient to use. Number formatting carries through to charts (or can be applied in chart formatting). This feature makes charts more intuitive by showing relevant units on each axis. Number formatting also carries through clipboard operations – when you copy a chart into another Office application, the formatting will be retained in the pasted values.
Before we get to the good stuff, the keyboard shortcut for Excel’s number formatting dialog box is ctrl-1. It can also be accessed by clicking the bottom right corner of the Home>Number tab.
In the number formatting dialog, choose the “Custom” category from the selection box on the left. The text box labelled “Type:” allows you to enter codes. These codes are necessary when using the TEXT() function, which returns a string displaying a value based on the number code that you provide.
The Good Stuff
The following table lists my go-to codes. The codes on this list are intuitive and worth your time to memorize – importantly, logical combinations of these codes can produce very flexible results. Knowledge is Power!
| Feature | Code | Description |
|---|---|---|
| Integer | 0 | This is as simple as it gets – Excel interprets this to display any value rounded to the nearest integer with no other formatting. |
| Decimal Places | 0.00 | Adding a decimal followed by zeros to the number code will display the value with the number of decimal places corresponding to the number of zeros in the code. This code would display the value rounded to two decimal places. |
| Leading Zeros | 000 | This code specifies the minimum number of leading zeros that Excel will display. For example, this code would display 1 as 001. For numbers larger than 999, it would show all digits. |
| Thousands Separator | #,##0 | This is the code to specify that numbers should appear with commas separating thousands. Using 0 instead of # would indicate for Excel to show leading zeros, as shown above. |
| Units in Thousands, Millions, etc. | 0,, | This code rounds all numbers to the nearest thousand/million/billion/etc.. The units displayed are powers of 1000 corresponding to the number of commas at the end of the number code. This code would display numbers in millions (i.e. 1000^2). I recommend using custom text (i.e. “M”) to make these units easier to interpret, as described for custom text. |
| Currency | $0 | Using a dollar (pound/euro/etc.) sign shows numbers with the specified units |
| Custom Text | 0” units” | Adding quotation marks allows you to display numbers with any additional text you might like. This is useful both to keep your sheets organized (by making displayed data more descriptive) and make chart axes display units. These formats will carry over if you paste a chart into another application. |
| Day | D | This code will return the day corresponding to a date value (i.e. this only works for dates that are stored in the date data format). D and DD return the numerical day of the month (without and with a leading zero respectively); DDD and DDDD return the day of the week (abbreviated and in full respectively). |
| Month | M | As one might expect, M follows the same logic as days when applied to a date value; that is, M and MM return the numerical representation for the month; MMM and MMMM return the abbreviated and full month name. |
| Year | Y | The year number code comes in two versions – YY and YYYY, which give the two-digit and four-digit representations of the year, respectively. |