Excel for Office 365 for Mac Excel 2019 for Mac Excel 2016 for Mac Excel for Mac 2011You can use number formats to change the appearance of numbers, including dates and times, without changing the actual number. The number format does not affect the cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar.Excel provides several built-in number formats. You can use these built-in formats as is, or you can use them as a basis for creating your own custom number formats. When you create custom number formats, you can specify up to four sections of format code. These sections of code define the formats for positive numbers, negative numbers, zero values, and text, in that order. The sections of code must be separated by semicolons (;).The following example shows the four types of format code sections.Format for positive numbersFormat for negative numbersFormat for zerosFormat for textIf you specify only one section of format code, the code in that section is used for all numbers.
![]()
If you specify two sections of format code, the first section of code is used for positive numbers and zeros, and the second section of code is used for negative numbers. When you skip code sections in your number format, you must include a semicolon for each of the missing sections of code. You can use the ampersand (&) text operator to join, or concatenate, two values. Create a custom format code.On the Home tab, click Number Format, and then click More Number Formats.In the Format Cells dialog box, in the Category box, click Custom.In the Type list, select the number format that you want to customize.The number format that you select appears in the Type box at the top of the list.In the Type box, make the necessary changes to the selected number format.Format code guidelines. You can use number format codes to control the display of digits before and after the decimal place.
Use the number sign (#) if you want to display only the significant digits in a number. This sign does not allow the display non-significant zeros. Use the numerical character for zero (0) if you want to display non-significant zeros when a number might have fewer digits than have been specified in the format code. Use a question mark (?) if you want to add spaces for non-significant zeros on either side of the decimal point so that the decimal points align when they are formatted with a fixed-width font, such as Courier New.
If you use Microsoft Excel on your Mac, you can save the spreadsheets you create and open them in Numbers, Apple’s spreadsheet app. It’s a handy feature to use in case you can’t access.
![]() ![]()
You can also use the question mark (?) to display fractions that have varying numbers of digits in the numerator and denominator.If a number has more digits to the left of the decimal point than there are placeholders in the format code, the extra digits are displayed in the cell. However, if a number has more digits to the right of the decimal point than there are placeholders in the format code, the number is rounded off to the same number of decimal places as there are placeholders. Use the following keyboard shortcuts to enter the following currency symbols in the Type box.To enterPress these keys¢ (cents)OPTION + 4£ (pounds)OPTION + 3¥ (yen)OPTION + Y€ (euro)OPTION + SHIFT + 2The regional settings for currency determine the position of the currency symbol (that is, whether the symbol appears before or after the number and whether a space separates the symbol and the number). The regional settings also determine the decimal symbol and the thousands separator. You can control these settings by using the Mac OS X International system preferences.To display numbers as a percentage of 100 — for example, to display.08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format.To display numbers in scientific notation, use one of the exponent codes in the number format code — for example, E–, E+, e–, or e+. If a number format code section contains a zero (0) or number sign (#) to the right of an exponent code, Excel displays the number in scientific notation and inserts an 'E' or 'e'.
The number of zeros or number signs to the right of a code determines the number of digits in the exponent. The codes 'E–' or 'e–' place a minus sign (-) by negative exponents. The codes 'E+' or 'e+' place a minus sign (-) by negative exponents and a plus sign (+) by positive exponents.
Create a custom number format.On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.In the Format Cells dialog box, under Category, click Custom.In the Type list, select the built-in format that most resembles the one that you want to create. For example, 0.00.The number format that you select appears in the Type box.In the Type box, modify the number format codes to create the exact format that you want. For example, 000-000-0000.Your changes will not alter the built-in format.
Instead, your changes create a new custom number format.When you have finished, click OK.Apply a custom number format.Select the cell or range of cells that you want to format.On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.In the Format Cells dialog box, under Category, click Custom.At the bottom of the Type list, select the built-in format that you just created. For example, 000-000-0000.The number format that you select appears in the Type box.Click OK.Delete a custom number format.On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.In the Format Cells dialog box, under Category, click Custom.In the Type list, select the custom number format, and then click Delete. Create a custom number format.On the Home tab, under Number, on the Number Format pop-up menu, click Custom.In the Format Cells dialog box, under Category, click Custom.In the Type list, select the built-in format that most resembles the one that you want to create. For example, 0.00.The number format that you select appears in the Type box.In the Type box, modify the number format codes to create the exact format that you want. For example, 000-000-0000.Your changes will not alter the built-in format. Instead, your changes create a new custom number format.When you have finished, click OK.Apply a custom number format.Select the cell or range of cells that you want to format.On the Home tab, under Number, on the Number Format pop-up menu, click Custom.In the Format Cells dialog box, under Category, click Custom.At the bottom of the Type list, select the built-in format that you just created.
For example, 000-000-0000.The number format that you select appears in the Type box.Click OK.Delete a custom number format.On the Home tab, under Number, on the Number Format pop-up menu, click Custom.In the Format Cells dialog box, under Category, click Custom.In the Type list, select the custom number format, and then click Delete.
![]() Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |