Format Painter in Excel for table layout

The formatting can take a lot of time, especially if you need to create many similar tables in one or several sheets. Some cells have a percentage format, others ones – financial format. You also need to select the fill colors for the background of the tables, the type and size of the fonts, the style and color of the borders, etc.

Usually the user does not remember all the colors and formats, but the style needs to be adhered to otherwise the documents will look unprofessional. To solve this problem, Excel developers created the excellent tool.



The application of the tool Format Painter tool

The «Format Painter» tool is used to copy and paste of formats without changing the values (a text, a number, etc.). To learn how to effectively use this most useful tool, you need to perform the following task. Before you make the format on the model, create similar tablets as in the picture:

There are two tablets: the left table is formatted, and the right one should correspond to the format of the left:

  1. Select to the columns C2:D7 and click on the tool with the brush icon «Format Painter». The selected area will be displayed with the animated copy border, and next to the mouse pointer there will be a brush.
    2. two tablets.
  2. Select the range G2:H7 with the mouse.
    3. Format Painter.

Formats are copied, but the width of the columns is different. Naturally, they can be corrected manually, but if we want the column width to be identical, we need to copy the format a little differently.

Press the key combination CTRL + Z several times to return the original view of the two tables: on the left with the format, and on the right without one.

To copy the width of the columns (or row height), we do the following:

  1. Select to the columns C:D and choose «Format Painter».
  2. Select the columns G:H with the mouse.
copy the width of the columns.

As you can see, along with the formats, the sizes of the columns were copied, and the data in the tables did not change. In the same way, the height of the lines is also copied, only you need to select columns rather than columns.



Multiple Copy Formats

The «Format Painter» button is intended for one-time and reusable copying of cell formats. Very often you have to copy table formats several times in a row. For example, if ones are located on many sheets. Let's say we have such plates on each sheet. For the comfortable solution of this task, we perform the following actions:

  1. Highlight the columns B:H, but this time we do not just choose, but make a double click on the button with the brush tool to copy the formats.
  2. Periodically we proceed to each sheet and highlight the columns B:H
Multiple Copy Format.

Notice, that by double-clicking the button after each selection of cells (copy formats), the tool remains active. Probably, therefore, the format on the sample Excel does not have hot keys. This is indicated by a brush near the mouse pointer. It will not disappear until the tool is disabled by the user.

All Classes

en ru