Mar 16 2021 11:42 AM
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.
Mar 16 2021 12:00 PM
Solution
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
Mar 16 2021 12:00 PM
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)
Mar 16 2021 12:33 PM
Mar 16 2021 12:45 PM - edited Mar 16 2021 12:50 PM
Part of the problem in communicating your desire is that you're referring to it as a "formatting" problem, which is missing the main point. You don't just want (for example) to change the color, because that is just changing the format, a very VERY superficial matter.
You understandably want to avoid changing them all manually. When I have that happen--probably in the same situation that you're encountering here, importing data from a bank or credit card company--I use the solution I outline in the next paragraph. For what it's worth, I happen to be bothered more by the practice of having numbers appear as negatives. I didn't "spend negative $100 on that purchase; I spent $100!!" In other words, I'd rather deal with them in terms of their absolute values, and just know that debits are debits and credits are credits and deal with them accordingly when it comes to such things as calculating current balance.
My solution, which is what I'd recommend, is the temporary use of a "helper column" to convert the entire column of 3838 cells from positive numbers to negative numbers and then Copy and Paste Special (values only) all 3,838 numbers, now negated, back onto the original column. All you need to do to turn positive numbers into negative is multiple them by -1. (For that matter, the same multiplier will turn negative numbers into positives.)
Mar 16 2021 01:10 PM
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)
Mar 16 2021 01:52 PM
Mar 16 2021 03:07 PM
Mar 16 2021 06:14 PM
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.
Mar 16 2021 12:00 PM
Solution
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