Since you want to learn how to create an Excel custom number format, it seems fair to assume that you already know how to find and locate the built-in number formats provided by Excel. Show
But we’d rather not take that for granted. Let’s cover the basics quickly, then dive into the nitty-gritty. What is a number format?A number format controls how cells with numeric data appear in Excel. Numeric data can mean dates, time, money, or anything else that looks like a number. The most important thing to understand about number formats is that they only affect how the number looks — they don't change the actual value of the number that Excel uses in calculations. You can find a list of built-in formats on the Home tab of the Excel ribbon in the Number command group. A dropdown field in that group displays the format of the active cell and allows you to change the number format of the active cell or highlighted cells by selecting another format from the dropdown list. The ‘Text’ number format is available by scrolling down and is used when you want to treat numbers as text.Download your free practice file! Use this free Excel file to practice along with the tutorial. Number format shortcutsAs an alternative to selecting from the dropdown list, you can also use the following keyboard shortcuts to apply number format changes to selected cells. These shortcuts work using the numbers above the letters on a Windows or Mac keyboard.
Excel defaults to the ‘General’ format unless it thinks that it recognizes the type of number you’re entering as currency, date, time, or something else. Justification for additional number formatsWith so many number formats available, why do we need to go adding to them by customizing number formats? Well, if you take a minute to think about it, it’s easy to see how even within each category above, there are multiple date, currency, and even percentage formats. For example, a date may be written as 09-Jan-2023, 1/9/23, or even Monday, January 9, 2023. Currency may be dollars ($), euros (€), yen (¥), and so on. In the example below, the value 1250.5 was entered in cell A1. If we open the Format Cells dialog box using Ctrl+1 and select the Number category, we can see that we can increase or decrease the number of decimal places that will be displayed in this cell. We can also use a checkbox to control whether a comma is displayed for values greater than or equal to 1000. Further below, we can select the format for negative values (a leading dash, red font, parentheses, or red font in parentheses). In some situations, there may be other types of number formats that are specific or unique to a given data set. Examples of this may include telephone numbers, account numbers, government ID numbers, etc.It makes sense that numbers of the same type should look consistent, and therefore may require a format not created by Excel programmers. I’m sure you can think of more examples like these. This is why custom number formats are so useful, and in some cases necessary, for accurate representation of various types of numeric data. Number format codesBefore we can go about building our own number formats, we need to be able to speak to Excel in a way that it understands. Number formats in Excel carry four sections of code. These sections are used to determine the formats for:
in that order, and are separated by semicolons. Skipping sections in format codeNot all sections are required. When only one format is provided, Excel will use that format for all values. If you create a format with two sections, the first section is used for positive values and zeros, and the second section for negative values. To skip the first, second, or third sections, enter the semicolon where that section would be without entering a format. Those values will be hidden from the display. To keep the default for any section, type General in that section, then enter the code for the section you want to change. For example, to keep the default display for positive and negative numbers, and display a dash for zero values, enter: General; -General; "-" Codes for formatting numeric dataThe table below shows symbols that are used to control the appearance of strictly numeric data.
How to create a custom number formatCreate a custom number format by doing the following:
Note that this does not change the format which you selected. It creates a new custom format that is added to that worksheet for future use. Still unsure of how to apply those codes to create a custom number format? Let’s do a simple example together, step by step. ScenarioIn the worksheet shown below, we’ve recorded the daily low in Celsius. The Date format was automatically applied to Column A by Excel, and the default General format was applied to Column B. We want to apply the following format to Column B:
removing all the extra characters. For the example above, we’ve entered spaces after the semicolons for readability, but they are optional.
The Column B display is updated. Other formatting codesThe following symbols may be used to format numbers as well as text.
Custom date and time formattingTo control the way dates and times appear, use the following codes.
It’s important to note the following:
Display literal characterThe symbols we’ve used so far are meant to tell Excel how to treat the values we input into the cell. But there are times when we want those literal characters to be displayed as a part of the format. For instance, if we want the hash symbol (#) or question mark displayed, we would need a special way to indicate that. There is. We use a backslash as an escape character to display the character which follows. In the following example, we want to create a code where numeric values are rounded to three decimal places and are followed by the letter m (for meters). Since we don’t want Excel to interpret “m” in the code as “month” or “minutes” we precede the code with a backslash.0.000\m The display is updated to show the letter m after the selected values, even though the number entered has not changed.When the following characters are used in the format code, they will be displayed as entered without the use of a backslash or double quotation marks.
How to edit or delete a custom number formatExisting number formats (whether built-in or custom) cannot be “edited” in the usual sense of the word. If you make a change to an existing number format, a new one is created which will appear in the Custom Category list for that workbook. You can delete a custom format you no longer need by single-clicking on the format, then pressing the Delete button. If the Delete button is grayed out, that means the format selected is a built-in format and cannot be deleted. Once you delete a number format, you cannot ‘undo’ that command. The format has to be re-created. Color formattingYou can change the default black font color to any of the following eight colors by enclosing them in square brackets in the respective code section.
Display text without converting cell to text formatIf you type text along with numeric values in a cell, Excel automatically converts that cell to a text format, making it unusable for formulas and other mathematical calculations. If you want a specific text displayed next to a number without changing the number to a text format, you may want to consider adding the text value to the number code. For example, to add the text “Profit margin is ” you would use the following code: “Profit margin is ”General% If this is the only code entered, any negative values entered would have a minus sign before the text display. To avoid this, you may want to add the negative code section. Your custom format would look something like this: "Profit margin is "0.0%;[Red]"Profit margin is "-0.0% ConclusionYou won’t be able to learn all the possible code combinations in this short read, but you’re probably a lot more knowledgeable about how Excel custom number formats work now. Hopefully, this will help you get around some of those pesky display problems you’ve been having. Learn more about how you can become an Excel ninja with GoSkills Excel courses! We recommend the Excel - Basic and Advanced course. Level up your Excel skills Become a certified Excel ninja with GoSkills bite-sized courses Start free trial How can you format data in cells to use a currency in Excel?Select the cells that you want to format and then, in the Number group on the Home tab, click the down arrow in the Number Format box. Choose either Currency or Accounting.
How would you place a format on a cell?Select the cells with the Excel Style. Right-click the applied style in Home > Cell Styles. Select Modify > Format to change what you want.
What is custom Number Format in Excel?Custom number formats can control the display of numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can do things like format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red.
|