Forum Discussion
Negitive numbers
I'm sure it's simple but I just can't figure it out?
I've imported transactions from an account that has two columns labeled as Debit and Credit however the Debit column in not formatted as negative numbers how do I format the column so that any numbers in column D are negative?
Thank you in advance for your wisdom and insight.
Simply select the desired column and use below formatting to convert numbers as negative.
You may also refer to the attached file for more clarity.
Thanks
Tauqeer
10 Replies
- After much consternation I found the solution based on mathetes recommendation I added a helper column with the following formula =IF(D6="","",(D6*-1)) which is less than ideal due to the fact I now have to revise each page in the workbook and recreate a couple of pivot tables, but at least I know for the next time.
You could also just use =ABS(D6) and copy that formula down. That takes any number, positive or negative, and makes it positive.
As I said in my first post, I find it a lot more meaningful to just know which fields are debits and which are credits and deal with them accordingly in any mathematical calculations. To show a debit as a negative is in itself kind of a double negative (given that the word means, in a way, that it's a negative).
By the way, why do you have multiple sheets? In my own workbook for tracking income and expenses, I have a single transactional database, incorporating the statements from every bank account and every credit card. It does mean that I have to do some operations on some of them to ensure a standard way of dealing with debits and credits--there is no industry standard--but once done, a single Pivot Table summarizes them all, income and expenses, by month and by category.
Change the way negative numbers are displayed
You can display negative numbers by using the minus sign, parentheses, or by applying a red color (with or without parentheses).
Select the cell or range of cells that you want to format with a negative number style.
If you're using Windows, press Ctrl+1. If you're using a Mac, press ++1
In the Category box, click either Number or Currency.
Under Negative numbers, select an option for negative numbers.
Where's the parentheses option? If you're missing the parentheses option for negative numbers, it may be due to an operating system setting. This article explains how to fix this.
Create and build a custom numeric format to show your numbers as percentages, currency, dates, and more. To learn more about how to change number format codes, see Review guidelines for customizing a number format.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
Thanks for a quick response!
I am not sure I am reading your pro tip correctly or if I didn't convey my dilemma appropriately?
I have 3838 entries that have been imported as $100.00 and I need to change them all to ($100.00).
I formatted the cells to Currency and selected the option $100.00 (in red) and then changed all the numbers to red hoping this would do the trick.
I am trying to avoid changing all 3838 cells manually.Negative numbers aren't showing with parentheses in Excel
If you're using Excel and negative numbers aren't displaying with parentheses, you can change the way negative numbers are displayed. But if that doesn't work, or if the parentheses option ($1,234.10) isn't available, it's likely because an operating system setting isn't set properly. If you're using a Mac, make sure you use the App Store and update to the latest version of macOS. If you're using Windows, use one of the following solutions to change the negative number format. Keep in mind that changing the setting will affect all programs on your computer, not just Excel.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Simply select the desired column and use below formatting to convert numbers as negative.
You may also refer to the attached file for more clarity.
Thanks
Tauqeer
- Thank you
