Office Hacks: Data Variety: Play Excel cell custom format

We often enter data in Excel in different formats to express different meanings, such as percentages are often used to express changes in trends, integral figures to express different levels and so on. However, relying on traditional numbers does not visually express the actual changes, so we need to be flexible with a good cell custom format, so that the numbers have a more visual effect.

Cell phone number custom segmentation display

Usually when we enter the employee’s cell phone number, in order to facilitate the identification of often use the form of segmentation, but the segmentation of the manual addition of spaces, both inconvenient and will destroy the original form of data, so the following teaches you a way to set a custom segmentation display.

Select the cell where you need to enter the card number, right-click and select “Set cell format”, select “Custom” in the category list, and then enter “000 0000 0000” under the right type “, that is, the cell phone number segmentation is displayed as a similar “139 1234 5678” form (Figure 1).


Figure 1 custom display

Later we will enter the cell phone number in the cell, after the completion of input each number will be automatically displayed as the above example of the three-paragraph format, more convenient for us to identify the cell phone number (Figure 2).


Figure 2 Mobile phone number segmentation display

Tip: For ID card numbers, bank card numbers and other longer numbers, Excel will be displayed in scientific notation, which we can only set to text format, and then manually enter the interval.

Numerical unit custom display

Daily statistics for some of the larger numbers, in order to facilitate verification, you can use a custom unit of value to display, assuming that a table of values are more than 100,000 sales, you can customize the value of the unit for “million”, which is more convenient to see sales intuitively.

First select the sales data column, and then right-click to enter the cell custom settings, in the custom format to add a “0!.00,” million “” category (note that the outer Chinese quotation marks are not included, the numerical units inside the half-quote mark). After setting, the sales value we enter will be automatically added to the unit display of “million”, which is more convenient to view (Figure 3).


Figure 3 Adding custom units to the display

It should be noted that the unit value “ten thousand” is an arbitrary text, so when you use this kind of numerical units must be set according to the actual value, for example, you can set it to “hundred thousand”, “million “, whatever is set is displayed. Using the same method you can add characters in front of the value, for example, add “South China market” before the sales, will be displayed as “South China market 560,000” words (Figure 4).


Figure 4 Other custom displays

Custom code to turn values into a level display

For example, a mobile company divides monthly consumption data into three classes: 0 to 50, 51 to 99 and over 100, and defines them as ordinary customers, intermediate customers and VIP customers respectively, providing different services for different classes. Once the monthly consumption data of each customer is aggregated, users in different data ranges can be quickly converted to the above levels with the help of custom data.

Select the consumption data column, right-click to enter the cell custom settings, in the custom format to add the following format.

[>99] “VIP Customers”; [<50] “Normal Customers”; “Intermediate Customers”

Here use “[]” to set the conditions, the statement is written similar to the IF function, but do not have to consider what nested (Figure 5).


Figure 5 custom formatting code

After using the above custom format, the original value will automatically be displayed at the corresponding level according to the code, although the actual number can still be seen after positioning to the cell, such display will not affect the original data statistics, sorting and other applications (Figure 6).


Figure 6 numbers are displayed as levels

Of course Excel cells support many codes, we can use other codes to achieve more customization according to their actual needs, and this code format can be filled or copied, for example, you can directly drop down to fill the format, or use the format brush to quickly convert data for similar needs (Figure 7).


Figure 7 Using the Format Brush

“Data color change + arrows” to make the rise and fall more intuitive

We often use numbers to represent trends, such as percentages to show growth rates. Let’s try the “Color Change + Arrows” method to make it more perfect.

Here we use the up and down arrows to express the growth rate, and the negative growth figures into the red display. First in any cell using the input method of special symbols to enter the shape of “↑” and “↓”, the same as above into the custom format settings, in the custom format enter the following code.

[black] “↑” year-on-year increase of 0.00%; [red] “↓” year-on-year decrease of 0.00%

So that when we enter the percentage growth rate values in the cell, the positive growth rate will be added before the black up arrow and the year-on-year increase, and vice versa, the negative growth rate will be represented by a red down arrow, so that the data will be more intuitive (Figure 8).


Figure 8 Custom display “Data Color Change + Arrows”

Leave a Comment