Practical Excel skills sharing: various ways to play format brush!

Format Painter is very simple. With one click, you can copy the format to other cells, graphics, and text. But the usage of Format Painter is not just that. It can also quickly fill alternate rows with color, hide alternate rows, and achieve "lossless" merging of cells, etc.

In excel, the format brush located on the left side of the start menu can be used to brush text (font, font size, etc.), color (text color, unit Cell color, etc.), borders (cell borders, shape borders, etc.), etc., can quickly apply existing formats, so the frequency of use is still very high.

#Today we will take a look at various ways to play with excel format brushing!

How to play 1: Single use format brush

Everyone knows it, needless to say.

How to play 2: Use the Format Painter multiple times

Select the cell range with the format you like, double-click the Format Painter, and then select the cells one by one to apply the format area is enough. Please see the demonstration effect! ! !

Warm reminder:

(1) The double-click here is to click the left mouse button twice quickly and continuously. It is not a double click, but a single click. Click twice to select and cancel the format painter;

(2) Double-click the format painter not only to apply the format to other cells in the current worksheet, but also to other worksheets and even other workbooks.

How to play 3: Copy every other row to achieve fast color filling

Drag the format brush to copy the style "every other row" or "every n rows" to achieve fast color filling of the table Fill and beautify quickly. Interlaced copying is actually a "small" manifestation of the format painter's ability to copy and apply the style of the entire table to other cells.

In fact, the "interlaced" copy mentioned here is inaccurate. In this example, Format Painter did not actually copy alternate rows, but the padding of the 1st and 3rd rows it copied was "None", which made people mistakenly think that the 1st and 3rd rows were not copied.

How to play 4: Hide alternate rows

When there is less data, we can manually hide alternate rows one by one. However, when the amount of data is large, if we still manually hide one by one, it will be Very inefficient. Let's take a look at the following case: If you only want to retain the data for the first month of each quarter, how to operate?

Operation method one:

First select rows 3 and 4, hide rows 3 and 4, and then press and hold Ctrl-click the row number "2" to select row 2, and then click "Format Painter". Release the ctrl key and select other lines (select the line number).

Operation method two:

First hide the 3rd and 4th rows, then enter 2:4 in the name box, and click Enter, excel will Automatically select rows 2 to 4, then click "Format Painter" and select other rows.

How to play 5: Copy row height and column width

First select column A where the column width has been set, and then double-click the format painter , and finally click on other columns to be set, such as column B and column D, so that the columns after clicking become the same width as column A.

The method of copying row height is similar to this.

How to play 6: "Lossless" merge cells

Recently encountered problems in processing data: In order to beautify the cells, the cells were merged, but the table data was affected search. As shown in the following animation:

#The reason for the error is that cell merging only retains the value of the upper-left corner cell in the merged area. If we merge cells through Format Painter, we can get rid of this problem.

Still in the above case, first in the blank area of ​​the worksheet, column I, select an area with the same size as the area where the cells need to be merged to merge, then select the merged area in column I, click the format painter, and then Select column E of the data area that needs to be merged to complete the merge. Finally, try the data search again, and the result is completely correct. See the animation below!

Here, the format brush is used to merge cells to achieve "lossless" - the value of each cell is not lost, so it is both beautiful and does not affect the normality of the data. Search and use, it can be said that it kills two birds with one stone.

Finally, let me share two ways to play cool games in a seemingly useless format.

How to play 7: Copy the area content and format

Select the area to be copied, double-click the format brush, and then brush to another area, and find that the format is copied first, When I press the Enter key again, a miracle occurs and I find that the content has been copied.

When you get tired of using Ctrl C and Ctrl V, you can try this trick to deal with some scenarios!

How to play 8: Copy the format of the found cell

Use the format brush together with the search function, and different sparks can pop out. As shown in the table below, we now need to apply the B2 cell format to highlight the scores below 60 points.

Operation steps:

(1) First press Ctrl F to open the "Find and Replace" dialog box. Enter "*" in the box and click "Find All";

(2) Sort the column of "Value" in ascending order in the search results display box and click the first row;

(3) Click cell B2, and then double-click the format painter;

(4) Hold down the shift key to return to the "Find and Replace" dialog box, click on the last value lower than 60 points, and find The format is copied to all found cells.

As shown in the animation below:

At this point, have you discovered that the function of Format Painter is particularly powerful, but there are also times when Format Painter is willful and disobedient, we You have to pay attention too.

Finally, let’s play the trap game.

As shown in the figure below, column A is text and column B is numerical value.

We select column A, click the format painter, click all the values ​​in column B, and find that it appears to be text, but column B is actually still numbers. If you want to actually turn it into text, how can you finally achieve it? Think about it, everyone, and also hope to tell us your answers in the message area!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: various ways to play format brush!. For more information, please follow other related articles on the PHP Chinese website!