How To Show Negative Numbers In Excel

Excel has a lot of built-in number formats, but sometimes you need something specific. Whether you're representing a piffling-used currency, tracking in-stock units, or want to color code profits and losses, you are in need of a an Excel custom number format. Number formatting in Excel is pretty powerful merely that means it is also somewhat circuitous. This is the definitive guide to Excel's custom number formats…

Using Custom Number Formats in Excel

By default, each prison cell is formatted as "General", which means it does not have any special formatting rules. When yous enter data in a prison cell, Excel tries to gauge what format it should have. When information technology doesn't guess correctly, you demand to change the format. Excel has a few pre-set formatting options attached to buttons in theDomicile carte, but if those don't meet your needs, you demand to employ the full options bachelor in theFormat Cells carte.

To access this card, look for the Number section of the Dwelling house menu tab. Click the arrow in the lower right corner of the Number section.

It will bring upward the Format Cells carte du jour in the Numbers tab:

Underneath the pre-defined number formats for common items like currency and percentage, there is a category called Custom. The format types in this section are different from the pre-set options. They are filled with symbols and codes:

A number format lawmaking is entered into the Type field in the Custom category. These codes are the key to creating any custom number format in Excel. Beginning, even so, we need to empathise how they work…

Agreement the Number Format Codes

Number format codes are the string of symbols that define how Excel displays the information you store in cells. We volition get into the means to draw the formats in a minute, only first we need to go over how Excel interprets those symbols. Each number format code is made up of as many as 4 sections separated by a semi-colon (;).

These sections control formatting for 1 or more parts of the number line, including positive numbers, negative numbers, and zeros. They tin can also control formatting for sub-sets of these parts, like all numbers greater than 100 and text-based data. What each section controls depends on how many sections there are in the number format lawmaking. A total number format code volition exist entered as follows:

          Section1;Section2;Section3;Section4        

The behavior of different parts of the number line volition be equally follows:

Every bit indicated above, when there is just one section provided, it describes the format for all numbers. With two, the first section describes the format of positive, naught, and text values, while the second department describes the format of negative values, etc.

Y'all can choose to skip formatting for whatsoever of the middle sections by entering General instead of other format data. For case, if y'all only want to bear on positive numbers and text, y'all can enter a number format code with this arrangement:

          Section1;Full general;General;Section4        

General strips all formatting from the data entered, so exist conscientious how you lot use it. Negative numbers with the General format code will not display the minus sign in front of their number.

Important note: Using a single department number format lawmaking does not ever have the aforementioned result equally expanding the same rules to all sections. For instance:

Section1

Is not the same as:

Section1;Section1;Section1;Section1

Look at the examples below to see examples of the difference…

Now that we understand what a number format code is, what tin can we do with information technology?

Changing Font Color with Number Format Codes

1 of the simplest things yous can exercise with number format codes is modify the colour of the font in the affected cells. The syntax for doing so is simple:

[Color Name]

Just choose the section that corresponds to the office of the number line you want to modify color, and provide the color in brackets. The color options are equally follows (the background is gray for dissimilarity in the tabular array, but backgrounds are non affected by the number format code):

As an example, we tin provide a separate color lawmaking for each part of a number format lawmaking:

[Cherry]General;[Blue]Full general;[Magenta]Full general;[Cyan]Full general

The General message just tells Excel to correspond the numbers equally entered by the user. The output of this number format code looks like this:

Notation that the negative number in row 3 does not automatically get a negative sign (-) in front of information technology. Nosotros are overriding the default format of negative numbers in the cell. As well notice that the color format is not affecting annihilation well-nigh the presentation; the number of decimal places stays the same, equally does the alignment of the information to the left or right of the cells.

Adding Text with Number Format Codes

You lot can add text effectually numbers with number format codes by inserting the text in a department one of two ways:

Single Characters

For single characters (similar an @ symbol before a number), type a backslash (\) followed by the symbol. The number format lawmaking:

\@Full general

Results in the following output:

Annotation that the minus sign even so precedes the negative number. Also notation that the Text value is non affected past the @ symbol improver.

Importantly, this is unlike from the result if we expanded our format guideline to each section of the number format lawmaking:

\@General;\@General;\@General;\@General

Results in the post-obit output:

Note that the minus sign is gone from the negative number and the Text value now receives the @ symbol.

Text Strings

To add an entire text string to a number (like adding "units" to the end of a number), we surround the text cord in quotation marks (" "). The number format lawmaking:

General" units"

Results in the post-obit output:

Once once again, this is unlike from:

General" units";Full general" units";General" units";General" units"

Which results in:

Special Characters

In addition to these ii methods, in that location is a set of special symbol characters that practise non need a leading backslash or quotes to exist included in the number format code. The list is equally follows:

Excel will also accept well-nigh other non-mathematical symbols, such a not-dollar currency symbols, copyright/trademark symbols, and Greek letters. These symbols are not bachelor on nigh standard keyboards, simply they can exist entered by property down the ALT key while typing in a iv-digit number. Some of the well-nigh useful ones are below:

A full list of ANSI character codes tin be found on Wikipedia here.

Changing Decimal Places, Pregnant Digits, and Commas

Adding symbols and colors is useful, but well-nigh of the work you lot'll probable need to do with custom number formats is change the way Excel displays the numbers it stores. Number format codes utilize a set of symbols to represent how the data should announced in the cell. Here is a summary of the symbols:

Let's review them each in turn…

Zip (0)

Zeros in the number format lawmaking represent a forced digit. That ways that whether or not the digit is relevant to the value, it will be shown. A great case of this is the standard dollars and cents notation that is used to represent prices in the United States: $0.00. Even if there are no extra cents in the corporeality, the two zeroes are still shown in the notation.

Hither is an case of the zero code in action. The post-obit examples are using this number format lawmaking:

0.00

Question Mark (?)

Question marks in the number format lawmaking represent an alignment digit. This means that when the number existence shown doesn't need the digit in question, a blank space of the aforementioned size is used. This is used to align decimal and comma places for more easy ranking of values, etc.

Here is an case of the question mark lawmaking in action. The following examples are using this number format code:

0.??

Pound Sign (#)

Sometimes called a hash mark, the pound sign in the number format code represents an optional digit. This means that when the number being shown doesn't demand the digit in question, it will be omitted from the displayed number. This is virtually often used to stand for numbers in their nigh easily readable form.

Here is an example of the pound sign code in action. The following examples are using this number format lawmaking:

#.##

Period (.)

The period in the number format lawmaking represents the location of the decimal betoken in the number being displayed. When paired with the comma code, it tin can show numbers in thousands or millions, changing 1,200 to 1.2, for example. Information technology is similar to the text format codes higher up in that it is always displayed when information technology is function of the number code, even when number being displayed does not straddle the decimal indicate. Run across the comma, pound sign and question marking examples above for useful illustrations of the menstruum in utilise.

Comma (,)

The comma in the number format code represents the thousands separators in the number existence displayed. It allows you to describe the beliefs of digits in relation to the thousands or millions digits.

Here is an example of the comma code in activeness. The following examples are using this number format code:

$??,???.00

Asterisk (*)

The asterisk in the number format code represents the repeating grapheme modifier. It is used along with a character to brandish a repeating digit that fills the empty infinite in a jail cell.

Hither is an case of the asterisk code in action. The post-obit examples are using this number format lawmaking:

*=0.##

Underscore (_)

The underscore in the number code represents the space character modifier. Information technology is used along with a character to brandish a blank space equal in size to the specified character. It tin be used, for case, to properly align positive and negative numbers when parentheses are used in only the negative case.

Hither is an example of the underscore code in action. The following examples are using this number format code:

_(#.##_);(#.##)

Using Fractions, Percentages, and Scientific Annotation

Certain types of notation crave that symbols be used to betoken the format change, including fractions, percentages, and scientific notation. Here is a summary of the symbols for each:

Nosotros'll examine each in item…

Fractions (/)

Fractions are special, since they require a change in units. The number 0.23 is represented as 23/100, but 0.25 can be simplified t0 ane/iv or shown as 25/100. Similarly, 1.25 can be shown equally 1 1/iv or the improper fraction 5/4. Which manner Excel displays the number depends on how you construct the number format code.

Fractions effectively round values to the nearest possible fraction. They besides take the guidelines of the pound sign and question mark symbols they are paired with.

Integer with Reduced Fractions

A fairly typical representation for fractions is to keep the whole numbers contained from the fraction balance. The representation for this is relatively straightforward and can be done with pound signs and question marks to slightly dissimilar issue…

Using question marking (?) notation, the post-obit number format lawmaking:

# ???/???

Produces a fraction residue with upwardly to three digits:

The alignment of the fraction bar is preserved regardless of the number of digits in use. If nosotros limit the number of digits on each side of the fraction to 2, Excel will round the number to the nearest fraction value. The post-obit number code format:

# ??/??

Changes the representation of 0.23 from 23/100 to 3/13.

If you don't wish to preserve the alignment effectually the fraction bar, you can use a similar fraction number format code that uses pound signs.

Using pound sign annotation, the following number format code:

# ###/###

Produces a more readable fraction remainder that can be justified or centered in the cell:

Improper Fractions

If you'd rather bundle the whole number portion of a value into the fraction itself, you can specify every bit much in the number format code.

Using pound sign note, the following number format code:

###/###

Produces an improper fraction with up to three digits:

Fixed Base of operations Fractions

It is as well possible to strength Excel to round fractions to a specific denominator by specifying it in the number format code.

Here is an example of a stock-still base code in action. The following examples are using this number format code:

# ##/15

The outcome is a rounded fraction balance that goes to the nearest number of 15ths.

Percentages (%)

Much similar fractions, percentages are controlled past the number format codes that accompany them. A bones percentage can be achieved with a pound sign symbol in the number format code:

#%

Results in the following output:

You can also specify fractional percentages, as shown with this number format code:

# #/#%

Results in unmarried-digit fractions in the percentages where needed:

Finally, as always, you can specify the number of significant digits with decimal places:

#.0%

Results in a 10th place aligned decimal:

Scientific Notation (E)

It's difficult to read extremely small and extremely large numbers conventionally because of all the leading and trailing zeroes. Scientific notation fixes that by moving the decimal to the relevant digits, so 0.0000001 can go ane x x^-7 . Excel uses the E note for this, so that same number would be 1E-07. So, every bit you'd expect, the capital letter E signals scientific notation in number format code.

Otherwise, scientific notation in Excel is controlled by the aforementioned number codes every bit percentages and fractions. It needs a number format code in front of the E to describe the relevant digits and a plus (+) and another number format code behind to describe the handling of the exponential digit.

Here is an example of a scientific note code in activeness. The following examples are using this number format lawmaking:

#E+#

Yous tin can also achieve more than consistent notation with zeros. The following examples are using this number format code:

0.00E+00

Note that in this instance, the decimal and exponent are both constrained to 2 significant digits, regardless of whether they are necessary. The trade-off is, it keeps the output far more than consistent, with a predictable string length.

Dates and Times

Dates and times in Excel are a special example. For a detailed discussion of how Excel uses them, delight review the Definitive Guide to Using Dates and Times in Excel.  The number format codes work identically to the format_text input for the TEXT command, and they can be reviewed here.

Go the latest Excel tips and tricks past joining the newsletter!

Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you primary it.

Bring together the newsletter to stay on summit of the latest manufactures. Sign upward and you'll become a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

Source: https://www.exceltactics.com/definitive-guide-custom-number-formats-excel/

Posted by: baileywarailut.blogspot.com

Share this post