Excel’s custom format function allows you to personalize the data in cells to make it more in line with your needs, while also making your tables more beautiful and easy to read.
What are Excel custom format codes?
Excel custom format codes are a set of special characters and symbols. Through these codes, you can control the way data in cells is displayed, such as the format of numbers, the display of dates, the alignment of text, and so on.
Composition of custom format codes
A complete custom format code usually consists of multiple parts, each separated by a semicolon (;). These parts correspond to different number formats, negative number formats, zero value formats, and text formats.
- Positive number format: The format used to display positive numbers.
- Negative number format: The format used to display negative numbers.
- Zero value format: The format used to display zero values.
- Text format: The format used to display text.
Common custom format code examples
- Currency Format: Displays numbers in currency format, with a dollar sign and two decimal places.
$#,##0.00;($#,##0.00) - Percentage format: Display the number as a percentage.
0% - Date Format: Displays numbers in the year-month-day date format.
yyyy年m月d日 - Time format: Display numbers in the time format of hours: minutes: seconds.
h:mm:ss - Custom text format: Display numbers as specified text.
"文本"
Special characters in custom format codes
- 0: Display the number. If the number is insufficient, fill it with 0.
- #: Display the number. If the number is not enough, it will not be displayed.
- ?: Display numbers. If there Country Email List are not enough digits, fill them with spaces.
- .: Display decimal point.
- ,: Use commas as thousands separators.
- $: Display the currency symbol.
- @: Display text.
How to apply custom formatting
- Select the cells that need to be formatted.
- Right-click and select Format Cells.
- In the Number tab, select the Custom category.
- Enter the custom format code in the Type box.
- Click OK.
Application scenarios of custom formats
- Financial reports: Format Phone Number list numbers as currency to display financial data more intuitively.
- Date and Time: Format numbers into date and time formats for better management of time data.
- Sequence Number: Add a prefix or suffix to the data to make it more readable.
- Conditional formatting: Set different formats for cells based on different conditions .
Summarize
Excel custom format codes provide users with powerful tools to flexibly format data, thereby improving the readability and aesthetics of data. By mastering the commonly used custom format codes and special characters, you can easily create a format that meets your needs.
SEO keywords: Excel, custom format, format code, number format, date format, time format, text format, financial report, conditional format
Further reading:
- Microsoft official documentation: Details the syntax and usage of custom format codes.
- Excel Tutorials website: Provides a wealth of custom formatting examples and techniques.
I hope this article can help you better master the Excel custom format function!
