Excel number formats FAQ
The previous FAQ instalment considered font formats including the use of Excel Styles and Themes. This time we will consider issues around the formatting of numbers, explains Simon Hurst.
How do I display negative numbers in brackets?
There are two main ways to switch between using a minus sign and a set of brackets to indicate a negative number. The first involves a system setting outside of Excel. Windows Control Panel includes a set of Regional and Language options. The exact sequence varies according to the version of Windows, but there is a ‘Customize’ screen that includes tabs for Numbers, Currency, Time and Date. It seems that it is the Currency tab that controls the options available within Excel. There is a ‘Negative Currency Format’ dropdown in the Currency tab. If you choose the first of the bracketed options, this should ensure that Excel includes options for bracketed negatives in the Format, Cells, Number tab: Number and Currency categories:
Continued...
The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.
Registration is FREE and allows you to view all content, ask questions, comment and much more.
Or if you are already registered, login here
Rounding FAQ
An earlier FAQ dealt with rounding and the various rounding functions in some detail:
http://www.accountingweb.co.uk/topic/excel-faqs-rounding/493946
Keep Life Simple
With respect this posting does seem to overcomplicate the topic for use in the real world in which I believe I have been working for several decades.
_-* #,##0.00_-;(#,##0.00)
The above custom format will accommodate all sensible accountants' requirements + even align numbers by ref to their decimal place so as not to offend the eye. Copy from above + Paste it into your Format Cells; Custom; Type cell + see if it works for you.
In my view £ signs should always be dispensed with as they can be read as another number which is not there. How often do we deal with numbers which are not sterling so why use the symbols + why invest your ink or toner in printing them?
On the rare occasion of more decimal places being required then edit .00 to .00000 etc as needs be.
The existing Excel % format works absolutely fine so does not need any surgery - try it.
DAI