Practical Excel Tips Sharing: The Magical “Custom Format” Function

In the previous article "Sharing practical Excel skills: batch creation/splitting of worksheets, batch renaming of worksheets", we learned two methods of setting up worksheets. Today we are going to learn the "custom format" function. After learning the custom format, we can deal with many problems without using functions.

In the default cell format of EXCEL software, it is usually a numerical value, date or text, etc. So is there any way to make the characters in the cell display in the format we want? Today I will introduce to you a very useful function: custom format.

1. How to open custom format

Select any cell, click the "Home" tab, and click on the right side of "Number Group" There is an extension button in the lower corner. Click to bring up the "Format Cells" dialog box.

You can also directly press the shortcut key Ctrl 1 to bring up the dialog box shown below. Click "Number" - "Custom". Under "Type" on the right is where we need to fill in the custom format code. After clicking OK, the characters in the cell will be displayed as we want.

2. Introduction to basic custom functions

The custom format code is divided into four areas Sections are separated by half-width semicolons (;). The code of each section acts on different types of values:

Positive number display format; Negative number display format; Zero value display format; text display format

In addition, it is also very simple to use custom formats to display colors for characters. Just add [red] and other words to the custom code. Characters display corresponding colors. The colors can display the following: [Black], [Green], [White], [Blue], [Magenta], [Yellow], [Teal], [Red].

3. Main application scenarios of custom formats

3.1 The percentage increase is shown

in As shown in the table below, the positive and negative numbers in the last column of data are marked with different colors, and the up and down arrows represent the positive and negative signs.

The custom format code is:

[Red]↑0%;[Green]↓0%;0%;

Analysis: There are four areas separated by semicolons. The first area represents the positive area and displays red; the second area represents the negative area and displays green. 0 is a numerical placeholder. , displays the value itself, followed by % to display the percentage; the third area is the zero value display format, and the color defaults to black; the last area is text, and if there is no text in the table, fill in nothing.

3.2 Add text display

In the table shown below, I want to set the number in the first column to the display format of the second column.

Usually everyone uses the text connector & to connect text. Here we directly use the custom format code to set it to: Part 0

Analysis: 0 represents a placeholder for a number, just add text before and after.

As shown below, if the numbers in the first column are Chinese numbers, they need to be set to be displayed in the format of the second column.

Then the custom format is set to: Part @

Analysis: @ represents the occupancy of the text Character.

3.3 Numbers are displayed in Chinese

If we need to display the numbers in column 1 as shown below in the Chinese style of columns 2 and 3, generally Chinese will be entered directly when typing. Of course, custom formats can also help you realize this function very conveniently.

Chinese lowercase custom format code is set to: [DBNUM1]

Chinese uppercase custom format code is set to: [DBNUM2]

3.4 Segmented display of numbers

At work, we also need to display values ​​of different lengths in segments, as shown below:

The custom format code for segmented display is set to:

0000 0000 0000

Analysis: Use 0 as a placeholder and add a space to display in segments.

The custom format code without adding 0 in front of the segmented display is set to:

??00 0000 0000

Analysis: If we don’t want 0 is automatically added to the front of the value, and if you want the value to be displayed in alignment, you can use ? to replace the 0 displayed previously, here? The number of depends on the maximum number of previously displayed 0s.

However, since EXCEL can fully display numbers within 11 digits; the number of numerical digits is greater than 11 digits but not more than 15 digits. EXCEL displays scientific notation as 9.03525E 13. You can use the above automatic Define the format code so that it is fully displayed; numbers over 15 will be displayed as 0, so the method used here does not work for values ​​over 15 digits. For values ​​exceeding 15 digits, we can only modify the format to text to display them all.

3.5 Date and time display

In the table shown below, the date and time in the first column need to be displayed in the format of the second column.

The custom format code settings in the table above are:

The first one: [DBNum1]yyyy"year"m"month"d"day " aaaa

The second one: d/mmm/yy,dddd

The third one: Today is aaaa

The fourth one: morning/afternoon h"point"m "minutes"s"seconds"

The fifth one:h:m:s am/pm

The sixth one:[m]minutes s seconds

Analysis: This The English letters inside are the English initials of year, month, day, hour, minute and second. The number of letters determines the number of characters in the display format.

In the date format, yy represents 18, while yyyy represents 2018; m and mm represent the months 7 and 07 displayed in numerical values, and mmm and mmmm represent the months Jul (abbreviation) and July (full name) displayed in English. ); In the same way, d and dd represent the dates 8 and 08 displayed in numerical values, and ddd and dddd represent the days of the week Sun (abbreviation) and Sunday (full name) displayed in English.

In the time format, if m needs to represent minutes, it needs to be displayed together with h hours, or [ ] is added to represent the number of minutes accumulated from zero o'clock on the day to the present, which means that hours are also converted into minutes.

If you need to express the day of the week in Chinese, you need to use the letter a. Aaaa displays Sunday, and aaa displays only the day.

4. Issues to note

It should be noted that custom formatting does not change the format of the cell itself, only changes it display mode.

In the table shown below, the uppercase Chinese numbers in column C are converted from the custom format code set by the numbers in column A. Let’s try to see if the Chinese numbers in column C can be summed?

Enter the formula in column D:

SUM($C$30:C30). Double-click to fill it down. You can see the cumulative summation results as follows.

It can be concluded that although the cells in column C are displayed in Chinese capitals, they are actually numerical values ​​and can be used for mathematical operations.

Of course, if we need to convert the custom format into an actual numerical format, we can use the clipboard to copy, and then copy the data in the clipboard to the cell.

#Or copy it into a word or txt document and then paste it back into the cell to complete the format conversion.

Okay, that’s all the introduction to EXCEL’s custom formats

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel Tips Sharing: The Magical “Custom Format” Function. For more information, please follow other related articles on the PHP Chinese website!