खाटू श्याम जी की, कृपा से
Introduction to Custom Number Formatting
Custom number formatting in Excel allows you to display numbers, dates, and text in a way that suits your needs, without altering the actual value of the data. This chapter will explore various custom number formats, including advanced conditional formatting and the use of wild characters.
1. Understanding Custom Number Formatting
Purpose:
Custom number formatting helps you present data in a specific format, such as displaying numbers with commas, formatting dates, or applying conditional formatting based on number values.
How It Works:
Excel uses format codes to customize the appearance of numbers. These codes consist of sections separated by semicolons. Each section defines how different types of values (positive numbers, negative numbers, zeros, and text) are displayed.
2. Basic Custom Number Formats
Number Format Codes:
- General: The default format. Displays numbers as they are entered.
- 0: Displays numbers with leading or trailing zeros. For example, 00000 will display 5 as 00005.
- #: Displays numbers without leading zeros. For example, ### will display 5 as 5.
- .: Defines decimal places. For example, 0.00 will display 5 as 5.00.
Examples:
- Currency: To format numbers as currency with two decimal places, use: $#,##0.00
- Percentage: To format numbers as percentages, use: 0.00%
- Date: To format dates as dd-mmm-yyyy, use: dd-mmm-yyyy
3. Advanced Custom Number Formats
Negative Numbers: You can specify how negative numbers should be displayed by adding a section in the format code:
- Positive and Negative: 0.00;[Red]-0.00
- Positive, Negative, and Zero: 0.00;[Red]-0.00;0.00
Text Formatting:
- Text Prefix/Suffix: Add text to numbers. For example, 0 “units” will display 5 as 5 units.
- Custom Text Formats: 0 “Sales” will display 100 as 100 Sales.
Examples:
- Phone Numbers: Format phone numbers with dashes: (000) 000-0000
- Social Security Numbers: Format SSNs: 000-00-0000
4. Conditional Formatting with Custom Formats
Custom number formatting allows for advanced conditional formatting using four sections:
- Positive Numbers: Defines how positive numbers are displayed.
- Negative Numbers: Defines how negative numbers are displayed.
- Zero: Defines how zeros are displayed.
- Text: Defines how text values are displayed.
Syntax:
positive;negative;zero;text
Examples:
- Highlighting Positive, Negative, and Zero Values:
- Format: 0;[Red]-0;[Blue]”Zero”
- Displays positive numbers normally, negative numbers in red, and zeros in blue with the text “Zero.”
- Displaying Currency and Percentages:
- Format: $0.00;[Red]-$0.00;0.00%;””
- Displays positive currency values with two decimal places, negative currency values in red, zeros as percentages, and text values blank.
- Text Prefix/Suffix with Conditions:
- Format: 0 “kg”;[Red]-0 “kg”;”Zero”;”Text”
- Displays positive numbers followed by “kg,” negative numbers in red followed by “kg,” zeros as “Zero,” and text values as “Text.”
Use of Wild Characters: Wild characters can be used in custom number formats to match patterns:
- ?: Represents a single character.
- *: Represents multiple characters.
- ****: Escapes special characters.
Examples:
- Phone Numbers with Wild Characters: “(###) ###-####”
- Text with Wild Characters: “Item ###”
5. Practice Assignment
- Conditional Formatting for Units:
- Create a list of numbers and use a custom number format to display values greater than 1 as “kg” and the value 1 as “1 kg”.
- Format code: 0 “kg”;[Red]-0 “kg”;”0″;”Text”
- Formatting with Wild Characters:
- Apply custom number formats using wild characters to format phone numbers and item codes.
- Advanced Conditional Formatting:
- Format a range to display positive numbers normally, negative numbers in red, zeros as blank, and text values with a specific tag.
Pro Tip: Custom number formatting is a versatile feature in Excel that enhances data presentation and readability. By understanding and applying various format codes and conditional formatting techniques, you can customize how your data is displayed without altering its actual values. For more advanced formatting techniques and practice exercises, Anjni Computer Education offers additional resources to support your Excel skills development.
