If it is negative it is printed with the “0” numeric format only this time the text “Neg” will be printed before it. If the number is positive it is printed with the “0” numeric format with the text “Pos” before it. This topic will be explained using examples.Īssume the same values as the previous example are printed in column A:Īs you can see the custom formatting has 3 parts: Section 3: Formatting in this section will apply to zero values only. Section 2: Formatting in this section will apply to negative numbers only. Section 1: Formatting in this section will apply to positive numbers only A section before the first semicolon, a section between the 2 semicolons and a section after the second semicolon. If an sign is NOT used in the last section then the following applies: If two semicolons are used then the custom formatting is split in 3 sections. For rows 6 and 7 which were numeric the “Pre” formatting was applied. It can be seen that for the first 5 rows (which were numeric) the 0.000 has been applied. If the data is text use the “Pre” formatting.If the data is numeric, use the 0.000 formatting.We will use the following custom formatting: I will explain this topic using an example.Īssume the following data is in column A: Section 2: The custom formatting in this section will apply to text values. Section 1: The custom formatting in this section will only apply to numeric values. The section before the semicolon and the section after the semicolon. If an sign is used in the second section the following will apply:
Similar to the previous section if one semicolon is used, then the custom formatting will be split in 2 sections. If the number is negative it will be printed with 3 decimal places. If the number input in the cell is a positive number or zero, it will be printed with 1 decimal place. In this example the following custom format is used:
If the number is negative then it is printed in red using the “0” numeric format.
If the number is positive or zero it is printed in blue using the “0” numeric format. If the number is negative then it will be printed with a “Neg” text before it with the “0” numeric format.Īlso this is another example with the same date in column A. If the number in column A is positive or zero then it will be printed with a “Pos” text before it using the “0” numeric format. The section before the semicolon applies to positive numbers and zero, while the section after the semicolon applies to negative numbers. We will be applying the following custom formatting to the cells in column A:Īs you can see the custom formatting has 2 sections, the section before the semicolon: The values below are printed in column A: I will explain this topic using examples. Section 2: The custom formatting in this section will apply to negative numbers.įor more information about the sign please see the article below: Section 1: The custom formatting in this section will only apply to positive numbers and zeros. The section before the semicolon and the section after the semicolon. If an sign is NOT used in the second section the following will apply: If one semicolon is used, then the custom formatting will be split in 2 sections.