In Excel documents, in order to visualize the data more visually, we often use charts to describe. However, the routine is to use Excel's own chart graphics. This method is cumbersome and inconvenient to personalize. In fact, in addition to charts, we can also use fonts instead of charts to make simpler and more personalized font charts.

How to change the font? REPT function analysis

Friends who are familiar with Excel functions know that Excel has a function named REPT. Its main function is to repeatedly display texts according to the defined number of times, similar to manually copying texts. In the text, there are many similar "|, ▲, ■, ♥" symbols are also a class of fonts, so we can use the REPT function to fill these fonts, in order to achieve replacement of the font fonts, to create more personalized font charts.

So in actual use, how can we use the REPT function to character chart?

How much data, at a glance

In Excel, we often use data bars for labeling in order to visualize how much data is available. Now with the "REPT function + font symbols" can easily achieve the stripe effect.

First of all, choose a suitable font symbol. Click "Insert → Symbol → Symbol" in Excel. Select "Normal Text" in the font list of the open symbol, select "Other Symbols" in the subset, and you will see a lot of symbol fonts. Here, you can select “solid square” as the data bar marker. After selecting this character, click “Insert” and insert it into the blank cell for backup (Figure 1).

Figure 1 Various graphic fonts in Excel

hint:

You can see here the "symbol", in fact, a variety of Unicode characters, we can also follow the prompts below the font, enter the corresponding area code, and then press Alt + X to complete the input. For example, type "25a0" and press Alt+X to complete the entity square input.

Go to C1 and enter the formula "=REPT("â– ",B1/$B$4*20)" to populate the input formula with a different number of squares (Figure 2).

Figure 2 character chart

hint:

Here is the use of REPT function to fill the square symbol, the symbol can be inserted directly or according to location shortcut key method input, and need to use half-width double quotation marks. B1/$B$4 indicates that the number of input symbols is the ratio of B1 value to B4 (that is, the sum), where $B$4 is used as a relative reference, and adding more products does not affect the reference to the sum. 20 indicates that if the ratio is 1, 20 repeated characters are filled, otherwise the filling is performed according to the proportion. To make the data bar more intuitive, the value here needs to be selected according to the actual situation. If the original data is larger, then the multiple should be set larger.

Simple transformation, the chart settings are more casual

We all know that using conditional formatting can also be very convenient to add strips to cells, but unlike the above methods, character strips can be very convenient to change the strip size, color, shape, etc., because these changes In fact, only the corresponding changes have been made to the fonts in the cells. For example, in the above example, in order to highlight the smallest number of data, you can change the font in the data bar to red, and adjust the font size and tilt appropriately to complete the change of the data bar (Figure 3).

Figure 3 changes to the data bar can be achieved by changing the font

Of course, we can also make more changes to the chart by adding more settings to the font. For example, for the two parts of the same model, the above-mentioned function can be used to create a left-right contrast data map. For example, a comparison chart of FIG. 4 needs to be created now, so that the left and right sides of the data can be filled (FIG. 4).

Figure 4 need to produce renderings

Locate the E2 cell, enter the formula "=REPT("â– ",B2/$B$4*20)", fill it down with its font set to "right-aligned", and enter the formula "=REPT()" in G2 as well. â–  ",C2/$C$4*20)", set its font to left-justified (Figure 5).

Figure 5 Make a left-right chart

Nested Functions to Generate More Personality Charts

In addition to transforming fonts, the REPT function can be combined with other functions to generate more useful charts. For example, when rating a customer's consumption level, we often use ★ to indicate the customer's level. If you simply use the above method, the REPT function can only accurately distinguish the customer's star rating when the machine is filled with the ratio. For example, it is necessary to add 1 to 3 star marks according to the customer's consumption points 0→3, 4→7, 8→10, respectively. At this time, integrate the IF function to determine the points, and then use the REPT function to fill in. .

Positioning to C2, enter the formula "=IF(B2<=3,REPT("★",1),IF(B2<=7,REPT("★",2),REPT("★",3))) ”, and then fill the formula down, so that the user's points meet the requirements will be in the C column corresponding cells filled with the corresponding asterisk. Press the Enter key to automatically return the specified number of “★” symbols according to the score in the F2 cell (see Figure 6).

Figure 6 fills the corresponding star icon

hint:

Here, three IF decision statements are used to judge the integral of B2. If the score is less than or equal to 3, the corresponding cell is filled with an asterisk. If it is less than or equal to 7, it is filled with 2 asterisks. Otherwise, it is filled with 3 asterisks. In this way, the original REPT fill can be accurately filled according to the actual integration.