FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
January 28, 2016

Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)Sorry my newsletter is a day late. I was helping my daughter Stephanie move furniture yesterday. She was supposed to be moving today (Friday) but because of a forecasted snowstorm for Friday night we had to move it up one day.

That picture of me on the right was taken last Sunday but it has rained since then and most of that snow is gone. Tonight's storm is supposed to bring lots of snow and rain so it probably won't be good snowshoeing weather.

In today's newsletter I'm trying something a little different that I hope will help you. At the beginning of each tip (Thursday only) I will give a brief summary of what the tips is about. If you already know the tip or can get the gist of it from the summary, then you won't have to waste your time reading the long version of the tip. I will be doing this with just Thursday's tips, since Tuesday's 'Excel in Seconds' tips are already very brief. Please let me know if this helps.

Again, thank to everyone for sharing my newsletter.

Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com





If you missed my last newsletter, you can click here to view it online.


TheExcelAddict.com Quote of the Day

“Diligence is excellence over time, with discipline."

-- Dave Ramsey --

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Display Negative Numbers with Red Font and Parentheses

Summary
If
you would like to show negative numbers as a red font and in parenthesis and that format is not listed in your number formats, you can create your own custom negative number format.

#,##0.00_);[Red](#,##0.00)

Format negative numbers with parenthesis

Details
A common number format that many people use, which may not be included in the preset formats that come with Excel, is a format that displays negative numbers with a red font in parentheses rather than with a leading or trailing minus sign. If your version of Excel doesn't have a preset number format that shows parentheses you can easily create one.

Here's how you can create a custom number format that displays negative numbers with a red font and in parentheses to help them stand out from positive numbers.

1) Select the cells you want to apply this format to;

2) From the Home tab, click the dropdown menu at the top of the Number group and select More Number Formats;

3) In the Category section, select Custom;

4) Select a format on the right that contains [Red] in the code with a format similar to the one you want to use (e.g. with or without commas and currency symbol and the number of decimal places you need);

Format negative numbers with parenthesis

Number Format Codes

A number format can consist of four parts separated by semicolons;

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

1) a code for positive numbers;
2) a code for negative numbers;
3) a code for zero values (optional);
4) a code for text values (optional).

Most common number formats contain just two parts. Code for positive and negative numbers separated by a semi-colon.


5) In the Type field, between the zero and the semi-colon, type an underscore and a right parenthesis. I'll explain that later;

Align positive numbers with negative parentheses

6) To the right of the semi-colon, replace the minus sign (-) with a left parenthesis and, on the far right of the code, type a right parenthesis;

Align positive numbers with negative parentheses

In the samples above, the _) in the code of the first section tells Excel to put a blank space equal to the width of the parenthesis character to the right of all positive numbers. This is to ensure that the digits and decimals of the positive numbers align properly with the negative numbers in other cells.

As you can see from the code in the second section, [Red] tells Excel to use a red font for negative numbers. If you don't want your negative numbers to be red, omit the [Red] part from the format code.

An optional third section tells Excel to how to display zero values. If there is no format code specified for zero values, Excel will use the same format as positive numbers. However, if you want your zeros to be displayed as dashes, you need to type a semi-colon followed by a dash to the right of the negative number code. You can also choose to have zeros display as blanks by typing just a semi-colon following the negative number code.

Note: It's important to note that the formatting of a cell's value affects only its displayed value. It does not affect its underlying true value.



Thanks for supporting this newsletter and website


FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630