The positive, negative and zero have been formatted just as the way your boss wanted! Custom Formatting – Example (download file) Press Ok and see the result in the excel file
IFTEXT IN CUSTOM FORMATTING EXCEL CODE
So our revised code looks like – $ 0.0 “Mn” $ (0.0) “Mn” įinally Code for zeros We don’t need to see zeros, instead we want a hypen or a dash so our revised quote looks like $ 0.0 “Mn” $ (0.0) “Mn” – Thumb Rule 5) When applying color to any part of the code, make sure the color is written first in square brackets So we will write $ 0.0 “Mn” Ĭode for negative numbers Now negative number need to be in red and in brackets, rest is the same as positive number coding And go to cell formatting (Ctrl+1) and click custom and start writing the code (by replacing the General)Ĭode for positive numbers For excel to format any profit/sales number in one decimal prefixed with a $ sign and suffixed with a Mn, we need a variable for sales/profit number.Ġ is a variable for any number.All zeros should be replaced with a hypen –Īccording to the rules our code will follow this sequence positive number code negative number code zero code.All negative (profit) numbers should appear this way in red color- $ (14.0) Mn.Stacey (even if you are not Stacey ? ), all the positive sales/profit numbers should appear this way – $ 173.0 Mn.
a simple case!Ĭonsider this Sale/Profit data over 6 months Thumb Rule 4) Put the text in the code in double quotes, example “Mn” Remember the separator is a semicolon ( ) not a comma. Thumb Rule 3) Always separate the parts of codes with a semicolon. I mean you cannot write the code for a text first and then for a negative number… it wont work!! So the sequence for all 4 parts of the code will be Positive Number Code Negative Number Code Zero Code Text Code Thumb Rule 2) You have to follow the sequence while writing the code. Three parts – 1st formats the +ve numbers, 2nd formats the -ve numbers, 3rd formats the zeros.Writing only first and second part – then first part formats +ve numbers and zeros and second part formats the -ve numbers.If you writing only the first part – then it applies to all numbers (+ve, -ve and zeros).Excel will assume the following in case you choose to omit one or more parts of the code You don’t necessarily need to write the code for all 4 parts.
Thumb Rule 1) The custom formatting code can consist of 4 parts
IFTEXT IN CUSTOM FORMATTING EXCEL HOW TO
We’ll discuss how to make a code in while but let’s discuss some ground rules first!! Structure & Thumb Rules of Writing a Custom Formatting code You customize formatting of your data with a code you write in the Format Cells dialogue box (shortcut Ctrl +1) in the custom formatting section shall we? And the I think the best way to start is to tell you that custom formatting does not actually change the underlying data, but only changes the way how it looks! (kept in bold because that is really important and true in most cases) Ok!! So where is Custom Formatting in Excel ? Guys get your eyes off the picture and lets get started.
not a slimming course or anything but how custom formatting can change the way how your data looks like!! ?Ĭustom formatting is like a make-up of your data This picture speaks miles about what we are going to discuss today. Is this related to Excel ? Yes Mam it is ? !!