Line 2 of Input Data (2.75 ) Does Not Contain Exactly 1 Value. Please Try Again]

This tutorial explains the nuts of the Excel number format and provides the detailed guidance to create custom formatting. Yous will larn how to bear witness the required number of decimal places, change alignment or font color, display a currency symbol, circular numbers by thousands, evidence leading zeros, and much more.

Microsoft Excel has a lot of born formats for number, currency, percentage, accounting, dates and times. But in that location are situations when yous demand something very specific. If none of the inbuilt Excel formats meets your needs, you tin can create your own number format.

Number formatting in Excel is a very powerful tool, and once yous acquire how to apply information technology property, your options are almost unlimited. The aim of this tutorial is to explicate the about essential aspects of Excel number format and set up y'all on the right track to mastering custom number formatting.

• How to create an Excel custom number format
• Understanding Excel number format
• Excel formatting tips and guidelines
  • Show the desired number of decimal places
  • Display a thousand separator
  • Round numbers by thousands
  • Add text to number formats
  • Brandish currency symbols
  • Show leading zeros
  • Display numbers as percentages
  • Turn decimal numbers into fractions
  • Create a custom Scientific Notation format
  • Show negative numbers in parenthesis
  • Display zeros every bit dashes or blanks
  • Add indents
  • Alter font colour
  • Repeat characters
  • Change alignment
  • Create conditional number formats
  • Appointment and fourth dimension formats

How to create a custom number format in Excel

To create a custom Excel format, open up the workbook in which you want to utilize and shop your format, and follow these steps:

1. Select a cell for which yous want to create custom formatting, and press Ctrl+1 to open the Format Cells dialog.
2. Under Category, select Custom.
3. Type the format code in the Type box.
4. Click OK to save the newly created format.

Washed!

Tip. Instead of creating a custom number format from scratch, you choose a congenital-in Excel format shut to your desired result, and customize it.

Wait, wait, but what practise all those symbols in the Blazon box mean? And how practice I put them in the right combination to display the numbers the way I want? Well, this is what the rest of this tutorial is all about :)

Agreement Excel number format

To exist able to create a custom format in Excel, it is important that yous sympathize how Microsoft Excel sees the number format.

An Excel number format consists of four sections of code, separated past semicolons, in this order:

POSITIVE; NEGATIVE; Nil; TEXT

Hither'south an example of a custom Excel format code:

1. Format for positive numbers (display ii decimal places and a thousands separator).
2. Format for negative numbers (the same as for positive numbers, only enclosed in parenthesis).
3. Format for zeros (display dashes instead of zeros).
4. Format for text values (display text in magenta font colour).

Excel formatting rules

When creating a custom number format in Excel, delight remember these rules:

1. A custom Excel number format changes only the visual representation, i.eastward. how a value is displayed in a cell. The underlying value stored in a cell is not changed.
2. When you are customizing a built-in Excel format, a copy of that format is created. The original number format cannot be changed or deleted.
3. Excel custom number format does not have to include all four sections.

   If a custom format contains just 1 section, that format will be applied to all number types - positive, negative and zeros.

   If a custom number format includes ii sections, the first section is used for positive numbers and zeros, and the 2d department - for negative numbers.

   A custom format is applied to text values only if it contains all 4 sections.

4. To apply the default Excel number format for any of the middle sections, type Full general instead of the corresponding format code.

   For case, to brandish zeros as dashes and show all other values with the default formatting, use this format code: General; -General; "-"; General

   Note. The General format included in the two^nd section of the format code does non brandish the minus sign, therefore nosotros include it in the format code.

5. To hibernate a certain value blazon(s), skip the corresponding code department, and only type the catastrophe semicolon.

   For example, to hide zeros and negative values, use the following format code: General; ; ; General. As the result, zeros and negative value volition appear but in the formula bar, simply will not be visible in cells.

6. To delete a custom number format, open up the Format Cells dialog, select Custom in the Category list, find the format you want to delete in the Type list, and click the Delete push.

Digit and text placeholders

For starters, permit'southward learn 4 basic placeholders that you lot can use in your custom Excel format.

Code	Clarification	Instance
0	Digit placeholder that displays insignificant zeros.	#.00 - always displays 2 decimal places. If you blazon 5.5 in a cell, it volition display as 5.50.
#	Digit placeholder that represents optional digits and does not display actress zeros. That is, if a number doesn't need a certain digit, information technology won't exist displayed.	#.## - displays up to 2 decimal places. If you type 5.5 in a jail cell, it will brandish as five.v. If you blazon five.555, it will display as 5.56.
?	Digit placeholder that leaves a space for insignificant zeros on either side of the decimal point just doesn't display them. It is oft used to align numbers in a column by decimal point.	#.??? - displays a maximum of 3 decimal places and aligns numbers in a cavalcade by decimal point.
@	Text placeholder	0.00; -0.00; 0; [Red]@ - applies the cerise font color for text values.

The following screenshot demonstrates a few number formats in action:

Equally you may have noticed in the above screenshot, the digit placeholders behave in the following manner:

• If a number entered in a cell has more digits to the right of the decimal betoken than in that location are placeholders in the format, the number is "rounded" to as many decimal places every bit in that location are placeholders.

  For example, if you blazon ii.25 in a cell with #.# format, the number will display every bit 2.3.

• All digits to the left of the decimal point are displayed regardless of the number of placeholders.

  For example, if y'all type 202.25 in a cell with #.# format, the number will display as 202.3.

Below yous volition find a few more examples that volition hopefully shed more light on number formatting in Excel.

Format	Clarification	Input values	Display as
#.000	Always display 3 decimal places.	two 2.5 0.5556	2.000 ii.500 .556
#.0#	Display a minimum of i and a maximum of 2 decimal places.	2 2.205 0.555	two.0 2.21 .56
???.???	Display up to 3 decimal places with aligned decimals.	22.55 2.5 2222.5555 0.55	22.55 2.5 2222.556 .55

Excel formatting tips and guidelines

Theoretically, at that place are an infinite number of Excel custom number formats that yous tin can make using a predefined set of formatting codes listed in the table beneath. And the following tips explain the well-nigh mutual and useful implementations of these format codes.

Format Code	Clarification
General	General number format
#	Digit placeholder that represents optional digits and does not display actress zeros.
0	Digit placeholder that displays insignificant zeros.
?	Digit placeholder that leaves a space for insignificant zeros simply doesn't display them.
@	Text placeholder
. (period)	Decimal betoken
, (comma)	Thousands separator. A comma that follows a digit placeholder scales the number past a thousand.
\	Displays the character that follows it.
" "	Brandish any text enclosed in double quotes.
%	Multiplies the numbers entered in a prison cell by 100 and displays the pct sign.
/	Represents decimal numbers equally fractions.
Eastward	Scientific notation format
_ (underscore)	Skips the width of the adjacent graphic symbol. It's ordinarily used in combination with parentheses to add left and right indents, _( and _) respectively.
* (asterisk)	Repeats the grapheme that follows it until the width of the cell is filled. It's often used in combination with the space character to change alignment.
[]	Create conditional formats.

How to control the number of decimal places

The location of the decimal point in the number format code is represented by a period (.). The required number of decimal places is divers past zeros (0). For example:

• 0 or # - brandish the nearest integer with no decimal places.
• 0.0 or #.0 - display ane decimal place.
• 0.00 or #.00 - display 2 decimal places, etc.

The deviation between 0 and # in the integer part of the format code is every bit follows. If the format code has simply pound signs (#) to the left of the decimal indicate, numbers less than 1 begin with a decimal point. For example, if you type 0.25 in a cell with #.00 format, the number will display as .25. If you lot apply 0.00 format, the number will display as 0.25.

How to testify a thousands separator

To create an Excel custom number format with a thousands separator, include a comma (,) in the format code. For example:

• #,### - display a thousands separator and no decimal places.
• #,##0.00 - display a thousands separator and 2 decimal places.

Round numbers by thousand, meg, etc.

As demonstrated in the previous tip, Microsoft Excel separates thousands by commas if a comma is enclosed by whatsoever digit placeholders - pound sign (#), question mark (?) or nil (0). If no digit placeholder follows a comma, information technology scales the number by m, two sequent commas scale the number past one thousand thousand, so on.

For instance, if a cell format is #.00, and you type 5000 in that jail cell, the number v.00 is displayed. For more examples, please see the screenshot below:

Text and spacing in custom Excel number format

To display both text and numbers in a cell, do the following:

• To add together a single character, precede that character with a backslash (\).
• To add together a text string, enclose it in double quotation marks (" ").

For example, to indicate that numbers are rounded by thousands and millions, you tin add \K and \M to the format codes, respectively:

• To display thousands: #.00,\M
• To display millions: #.00,,\M

Tip. To make the number format ameliorate readable, include a space between a comma and astern slash.

The following screenshot shows the to a higher place formats and a couple more variations:

And hither is another example that demonstrates how to brandish text and numbers within a single jail cell. Supposing, you want to add the word "Increase" for positive numbers, and "Decrease" for negative numbers. All you lot have to do is include the text enclosed in double quotes in the appropriate section of your format code:

#.00" Increase"; -#.00" Subtract"; 0

Tip. To include a space betwixt a number and text, type a space grapheme subsequently the opening or before the endmost quote depending on whether the text precedes or follows the number, like in "Increase ".

In addition, the following characters can be included in Excel custom format codes without the utilise of backslash or quotation marks:

Symbol	Description
+ and -	Plus and minus signs
( )	Left and right parenthesis
:	Colon
^	Caret
'	Apostrophe
{ }	Curly brackets
< >	Less-than and greater than signs
=	Equal sign
/	Forward slash
!	Exclamation point
&	Ampersand
~	Tilde
	Infinite graphic symbol

A custom Excel number format can likewise accept other special symbols such as currency, copyright, trademark, etc. These characters tin can exist entered by typing their iv-digit ANSI codes while holding downward the ALT key. Here are some of the near useful ones:

Symbol	Lawmaking	Description
™	Alt+0153	Trademark
©	Alt+0169	Copyright symbol
°	Alt+0176	Caste symbol
±	Alt+0177	Plus-Minus sign
µ	Alt+0181	Micro sign

For example, to brandish temperatures, you can utilise the format code #"°F" or #"°C" and the result volition await similar to this:

You can likewise create a custom Excel format that combines some specific text and the text typed in a prison cell. To do this, enter the boosted text enclosed in double quotes in the 4^th section of the format code before or after the text placeholder (@), or both.

For example, to proceed the text typed in the cell with some other text, say "Shipped in", utilize the following format code:

General; Full general; Full general; "Shipped in "@

Including currency symbols in a custom number format

To create a custom number format with the dollar sign ($), simply type it in the format code where appropriate. For case, the format $#.00 will display v as $five.00.

Other currency symbols are not available on most of standard keyboards. But y'all tin can enter the popular currencies in this way:

• Turn NUM LOCK on, and
• Use the numeric keypad to type the ANSI code for the currency symbol you lot want to brandish.

Symbol	Currency	Code
€	Euro	ALT+0128
£	British Pound	ALT+0163
¥	Japanese Yen	ALT+0165
¢	Cent Sign	ALT+0162

The resulting number formats may wait something similar to this:

If y'all want to create a custom Excel format with some other currency, follow these steps:

How to display leading zeros with Excel custom format

If you try entering numbers 005 or 00025 in a cell with the default General format, you would observe that Microsoft Excel removes leading zeros considering the number 005 is same as 5. But sometimes, we do want 005, not v!

The simplest solution is to use the Text format to such cells. Alternatively, you lot can type an apostrophe (') in front of the numbers. Either way, Excel will sympathize that you want any cell value to exist treated as a text string. As the result, when you type 005, all leading zeros volition be preserved, and the number will bear witness up as 005.

If y'all want all numbers in a column to contain a certain number of digits, with leading zeros if needed, then create a custom format that includes only zeros.

Equally you lot call back, in Excel number format, 0 is the placeholder that displays insignificant zeros. So, if you lot need numbers consisting of 6 digits, use the following format code: 000000

And now, if you type 5 in a prison cell, it will appear as 000005; 50 will appear as 000050, and so on:

Tip. If you lot are entering phone numbers, zip codes, or social security numbers that comprise leading zeros, the easiest way is to apply one of the predefined Special formats. Or, you tin create the desired custom number format. For example, to properly display international seven-digit postal codes, use this format: 0000000. For social security numbers with leading zeros, utilize this format: 000-00-0000.

Percentages in Excel custom number format

To display a number every bit a percentage of 100, include the per centum sign (%) in your number format.

For example, to display percentages as integers, use this format: #%. Every bit the result, the number 0.25 entered in a cell will appear equally 25%.

To display percentages with ii decimal places, use this format: #.00%

To display percentages with 2 decimal places and a thousands separator, use this i: #,##.00%

Fractions in Excel number format

Fractions are special in terms that the same number can be displayed in a variety of ways. For case, 1.25 can be shown as 1 ¼ or five/5. Exactly which way Excel displays the fraction is determined past the format codes that yous use.

For decimal numbers to appear as fractions, include frontwards slash (/) in your format code, and divide an integer part with a infinite. For example:

• # #/# - displays a fraction remainder with up to 1 digit.
• # ##/## - displays a fraction residue with up to ii digits.
• # ###/### - displays a fraction remainder with up to iii digits.
• ###/### - displays an improper fraction (a fraction whose numerator is larger than or equal to the denominator) with upward to 3 digits.

To round fractions to a specific denominator, supply it in your number format code after the slash. For example, to display decimal numbers as eighths, utilise the post-obit fixed base fraction format: # #/eight

The following screenshot demonstrated the above format codes in action:

As you probably know, the predefined Excel Fraction formats align numbers by the fraction bar (/) and display the whole number at some distance from the remainder. To implement this alignment in your custom format, utilise the question mark placeholders (?) instead of the pound signs (#) similar shown in the following screenshot:

Tip. To enter a fraction in a cell formatted every bit General, preface the fraction with a nil and a space. For instance, to enter 4/8 in a cell, you lot blazon 0 four/8. If y'all blazon 4/8, Excel will assume you are entering a date, and change the prison cell format accordingly.

Create a custom Scientific Notation format

To display numbers in Scientific Annotation format (Exponential format), include the upper-case letter alphabetic character E in your number format code. For instance:

• 00E+00 - displays 1,500,500 equally 1.50E+06.
• #0.0E+0 - displays 1,500,500 every bit 1.5E+six
• #Eastward+# - displays ane,500,500 every bit 2E+half dozen

Evidence negative numbers in parenthesis

At the starting time of this tutorial, we discussed the iv code sections that make up an Excel number format: Positive; Negative; Goose egg; Text

Most of the format codes nosotros've discussed and then far independent merely i department, meaning that the custom format is applied to all number types - positive, negative and zeros.

To make a custom format for negative numbers, y'all'd need to include at least 2 code sections: the kickoff volition be used for positive numbers and zeros, and the second - for negative numbers.

To bear witness negative values in parenthesis, simply include them in the second section of your format code, for example: #.00; (#.00)

Tip. To line up positive and negative numbers at the decimal point, add an indent to the positive values section, e.g. 0.00_); (0.00)

Display zeroes every bit dashes or blanks

The built-in Excel Accounting format shows zeros as dashes. This can also be done in your custom Excel number format.

As y'all remember, the zero layout is determined past the 3^rd section of the format code. So, to force zeros to announced every bit dashes, type "-" in that section. For instance: 0.00;(0.00);"-"

The above format code instructs Excel to display 2 decimal places for positive and negative numbers, enclose negative numbers in parenthesis, and turn zeros into dashes.

If you don't want any special formatting for positive and negative numbers, blazon General in the one^st and ii^nd sections: General; -General; "-"

To turn zeroes into blanks, skip the third section in the format code, and only type the ending semicolon: General; -General; ; General

Add indents with custom Excel format

If you don't want the cell contents to ride upward right against the cell border, yous can indent information within a cell. To add an indent, utilise the underscore (_) to create a space equal to the width of the grapheme that follows it.

The commonly used indent codes are as follows:

• To indent from the left edge: _(
• To indent from the correct edge: _)

Most often, the right indent is included in a positive number format, so that Excel leaves infinite for the parenthesis enclosing negative numbers.

For example, to indent positive numbers and zeros from the correct and text from the left, you lot can employ the post-obit format code:

0.00_);(0.00); 0_);_(@

Or, you tin can add indents on both sides of the cell:

_(0.00_);_((0.00);_(0_);_(@_)

The indent codes move the cell data by one character width. To motility values from the cell edges by more than i grapheme width, include 2 or more sequent indent codes in your number format. The following screenshot demonstrates indenting cell contents by 1 and 2 characters:

Change font colour with custom number format

Changing the font color for a certain value type is one of the simplest things y'all can do with a custom number format in Excel, which supports viii main colors. To specify the color, only blazon ane of the post-obit colour names in an appropriate department of your number format code.

[Black] [Green] [White] [Blue]

[Magenta] [Yellowish] [Cyan] [Cherry-red]

Note. The color code must exist the commencement detail in the section.

For example, to leave the default Full general format for all value types, and change only the font colour, use the format code similar to this:

[Green]General;[Red]General;[Black]General;[Blue]General

Or, combine color codes with the desired number formatting, eastward.g. brandish the currency symbol, 2 decimal places, a thousands separator, and bear witness zeros as dashes:

[Blue]$#,##0.00; [Red]-$#,##0.00; [Black]"-"; [Magenta]@

Repeat characters with custom format codes

To echo a specific graphic symbol in your custom Excel format and so that information technology fills the cavalcade width, blazon an asterisk (*) before the character.

For example, to include enough equality signs afterward a number to fill the cell, use this number format: #*=

Or, you can include leading zeros by adding *0 before any number format, due east.g. *0#

This formatting technique is unremarkably used to change jail cell alignment every bit demonstrated in the next formatting tip.

How to change alignment in Excel with custom number format

A usual way to alter alignment in Excel is using the Alignment tab on the ribbon. However, you tin can "hardcode" cell alignment in a custom number format if needed.

For case, to align numbers left in a prison cell, type an asterisk and a space after the number lawmaking, for example: "#,###* " (double quotes are used but to evidence that an asterisk is followed past a space, y'all don't need them in a existent format code).

Making a pace further, you could have numbers aligned left and text entries aligned right using this custom format:

#,###* ; -#,###* ; 0* ;* @

This method is used in the congenital-in Excel Accounting format . If you lot employ the Bookkeeping format to some jail cell, then open the Format Cells dialog, switch to the Custom category and look at the Type box, y'all will see this format code:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

The asterisk that follows the currency sign tells Excel to echo the subsequent space character until the width of a cell is filled. This is why the Accounting number format aligns the currency symbol to the left, number to the right, and adds as many spaces as necessary in between.

Use custom number formats based on weather condition

To have your custom Excel format applied only if a number meets a certain condition, blazon the condition consisting of a comparison operator and a value, and enclose information technology in square brackets [].

For example, to displays numbers that are less than 10 in a red font colour, and numbers that are greater than or equal to 10 in a light-green color, apply this format code:

[Red][<10];[Green][>=10]

Additionally, you can specify the desired number format, e.k. show 2 decimal places:

[Red][<10]0.00;[Green][>=ten]0.00

And here is some other extremely useful, though rarely used formatting tip. If a cell displays both numbers and text, yous can make a conditional format to show a noun in a singular or plural course depending on the number. For example:

[=1]0" mile";0.##" miles"

The above format code works as follows:

• If a cell value is equal to one, it will display as "1 mile".
• If a cell value is greater than i, the plural form "miles" will show up. Say, the number iii.5 will display as "iii.v miles".

Taking the instance farther, you can display fractions instead of decimals:

[=i]?" mile";# ?/?" miles"

In this instance, the value 3.five volition announced as "three i/ii miles".

Tip. To utilize more than sophisticated conditions, employ Excel'southward Conditional Formatting characteristic, which is specially designed to handle the job.

Dates and times formats in Excel

Excel date and times formats are a very specific case, and they take their own format codes. For the detailed information and examples, delight check out the post-obit tutorials:

• How to create a custom date format in Excel
• How to create a custom fourth dimension format in Excel

Well, this is how you tin can change number format in Excel and create your own formatting. Finally, here'southward a couple of tips to quickly apply your custom formats to other cells and workbooks:

• A custom Excel format is stored in the workbook in which it is created and is non available in any other workbook. To utilise a custom format in a new workbook, you can salve the electric current file as a template, and then use it as the basis for a new workbook.
• To apply a custom format to other cells in a click, save it as an Excel style - just select any cell with the required format, go to the Home tab > Styles group, and click New Prison cell Style….

To explore the formatting tips further, you tin can download a copy of the Excel Custom Number Format workbook we used in this tutorial. I thanks for reading and hope to encounter you again side by side week!

You may too be interested in

mascarenasahme1941.blogspot.com

Source: https://www.ablebits.com/office-addins-blog/custom-excel-number-format/comment-page-1/

Bagikan Artikel ini