SOLVED

Negitive numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2214855%22%20slang%3D%22en-US%22%3ENegitive%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2214855%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20sure%20it's%20simple%20but%20I%20just%20can't%20figure%20it%20out%3F%3C%2FP%3E%3CP%3EI've%20imported%20transactions%20from%20an%20account%20that%20has%20two%20columns%20labeled%20as%20Debit%20and%20Credit%20however%20the%20Debit%20column%20in%20not%20formatted%20as%20negative%20numbers%20how%20do%20I%20format%20the%20column%20so%20that%20any%20numbers%20in%20column%20D%20are%20negative%3F%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20wisdom%20and%20insight.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2214855%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2214937%22%20slang%3D%22en-US%22%3ERe%3A%20Negitive%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2214937%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F999414%22%20target%3D%22_blank%22%3E%40PaddyMcPatrick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimply%20select%20the%20desired%20column%20and%20use%20below%20formatting%20to%20convert%20numbers%20as%20negative.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tauqeeracma_0-1615921041737.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264529iD02D1471B561BEAE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tauqeeracma_0-1615921041737.png%22%20alt%3D%22tauqeeracma_0-1615921041737.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20may%20also%20refer%20to%20the%20attached%20file%20for%20more%20clarity.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2214940%22%20slang%3D%22de-DE%22%3ESubject%3A%20Negitive%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2214940%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F999414%22%20target%3D%22_blank%22%3E%40PaddyMcPatrick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fchange-the-way-negative-numbers-are-displayed-104b47a6-8ae2-471f-8f73-c4b96e7bb146%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EChange%20the%20way%20negative%20numbers%20are%20displayed%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EYou%20can%20display%20negative%20numbers%20by%20using%20the%20minus%20sign%2C%20parentheses%2C%20or%20by%20applying%20a%20red%20color%20(with%20or%20without%20parentheses).%3C%2FP%3E%3COL%3E%3CLI%3E%3CP%3ESelect%20the%20cell%20or%20range%20of%20cells%20that%20you%20want%20to%20format%20with%20a%20negative%20number%20style.%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3EIf%20you're%20using%20Windows%2C%20press%20Ctrl%2B1.%20If%20you're%20using%20a%20Mac%2C%20press%20%2B%2B1%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3EIn%20the%20%3CSTRONG%3ECategory%3C%2FSTRONG%3E%20box%2C%20click%20either%20Number%20or%20Currency.%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3EUnder%20%3CSTRONG%3ENegative%20numbers%3C%2FSTRONG%3E%2C%20select%20an%20option%20for%20negative%20numbers.%3C%2FP%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSTRONG%3EWhere's%20the%20parentheses%20option%3F%3C%2FSTRONG%3E%20If%20you're%20missing%20the%20parentheses%20option%20for%20negative%20numbers%2C%20it%20may%20be%20due%20to%20an%20operating%20system%20setting.%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fnegative-numbers-aren-t-showing-with-parentheses-in-excel-682a1cc5-701a-4ce7-92db-cc9eebc5ffa0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EThis%20article%20explains%20how%20to%20fix%20this.%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20a%20custom%20number%20format%3C%2FA%3E%3C%2FP%3E%3CP%3ECreate%20and%20build%20a%20custom%20numeric%20format%20to%20show%20your%20numbers%20as%20percentages%2C%20currency%2C%20dates%2C%20and%20more.%20To%20learn%20more%20about%20how%20to%20change%20number%20format%20codes%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Freview-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EReview%20guidelines%20for%20customizing%20a%20number%20format%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

10 Replies
best response confirmed by PaddyMcPatrick (Occasional Contributor)
Solution

Hi @PaddyMcPatrick 

 

Simply select the desired column and use below formatting to convert numbers as negative.

tauqeeracma_0-1615921041737.png

You may also refer to the attached file for more clarity.

 

Thanks

Tauqeer

@PaddyMcPatrick 

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).

  1. Select the cell or range of cells that you want to format with a negative number style.

  2. If you're using Windows, press Ctrl+1. If you're using a Mac, press ++1

  3. In the Category box, click either Number or Currency.

  4. 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 a custom number format

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.

@PaddyMcPatrick 

 

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.)

 

 

@PaddyMcPatrick 

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)

Thank you
Thanks for the input.
How would you suggest I refer a similar problem in the future?
If I want column D to always display the same value anytime I enter a set of numbers would that not be formatting?
I'm still trying to get the correct lingo down.

Thank you for your time Nikolino!
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.

@PaddyMcPatrick 

 

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.