excel query

shabani

I exported a report from sage to excel and as i expected lost some of the formatting.However i want to change the figures in some columns to negative numbers. Is there a way i can change positive numbers to negative without having to change each and every single one. To complicate matters i want to the negative numbers to have a opening and closing bracket instead of the minus sign. Please help!

Chippy_Minton

This is a 2 stage process:

1. Change positive numbers to negative

Type -1 in an empty cell and copy it.
Select the cells with the numbers you want to change, right-click and click Paste Special. Choose Values and Multiply as the operation and click OK.

2. Display negative numbers with surrounding brackets instead of the minus sign

This is simply a case of using the correct cell format. In the following I'm assuming that your Windows is set to UK regional settings [1].

Select the cells, right-click and click Format Cells.
On the Number tab, click Custom in the list of Categories.
In the Type box, type in #;(#) and click OK.

Excel remembers the format you typed in, so if you want to format another range of cells like this, click the Custom category and scroll down to the #;(#) format and click it.

[1] The negative number formats that Excel shows for the Number and Currency categories in Format Cells depends on your regional settings. By default the UK regional settings will show negative numbers as -1234 or in red with or without the minus sign. With US regional settings the (1234) format is available. Your regional settings for negative numbers and currency can be changed via Control Panel - Regional and Language Options. This changes them machine-wide.

shabani

works perfectly. You just saved me half a day of work. thanks
Do you have a site from where you get this info as i have been trying loads of books to no avail

Shrimp_Stu

A great websites for Excel users:
Mr Excel messageboards:
http://www.mrexcel.com/board2/viewforum.php?f=2&sid=f2543991cd9e14631608f71c208ada41

Chippy_Minton

shabani wrote:

works perfectly. You just saved me half a day of work. thanks
Do you have a site from where you get this info as i have been trying loads of books to no avail

You're welcome. The best sources of info are probably the Excel newsgroups and the Excel Forum.