Excel Styles and The Format Painter

Cell Formatting

In Microsoft Excel, there are several ways to change cell formats like color, size or font. Here we will compare two of them, Styles and the Format Painter.

When applying formats in Microsoft Excel, select the cell (or cells) to be formatted, then apply the desired format using the button from the Home tab ribbon. For example, to format a cell in bold print click on the cell to select it, then click on the [B] button to change the format of the cell contents to bold.

You can apply other formats this way as well.  With that cell still selected, change the alignment to Centered by clicking the [Center] button…

… and change the number formatting under the Format Cells box to “Currency”.

Format Painter

This method of quickly copying formats works well, but it can be cumbersome to use if you are attempting to uniformly apply several individual formats to multiple cells. Since it requires a lot of steps it can be easy to accidentally miss one of them, which makes it an ideal situation to use the Format Painter.

The Format Painter button looks like a paintbrush, and it is used to copy a set of formats from one cell and “paint” those formats onto other cells. Because the formats are copied as a group, this tool can be particularly useful if you have several formats to copy. In the previous example, the Bold, Centered and Currency formats can be applied as a group to other cells with a single action by using the Format Painter button.

Select the cell whose formatting you wish to copy. Click the Format Painter button to copy the formats, then select the destination cell or cells to which you would like to apply them. A single click of the Format Painter button allows you to apply the copied set of formats once, or to a range of cells by clicking and dragging to select the range. Double clicking the Format Painter button keeps the tool active and allows you to apply the copied formats to multiple other cell ranges one after another, until the tool is turned off by clicking it again.

Styles

Styles are an alternative to the Format Painter. Styles are built-in or user-defined named collections of formats that can be applied to cells. For example, the Calculation Style is made up of individual formats for Font, Font Color, Cell Border and Cell Fill Color, and these formats are applied to a cell or cells as a group by clicking the Cell Styles button on the ribbon and selecting the Calculation Style from the Style Gallery. As with the Format Painter, the entire collection of formats that make up the Style is applied at once.

Unlike the Format Painter, Styles don’t need to be copied and pasted from another location in the worksheet, since the formats are already assigned to that Style in the gallery. A more important difference, however, is that format changes made to a Style itself will be applied to all the cells that have the Style applied in the future and to all the cells in a workbook that already have that Style applied. This behavior is different than using the Format Painter (or most other formatting methods), where the cells need to be selected first and the formatting applied afterwards.

The Format Painter and Styles are both methods of applying multiple formats to Excel cells simultaneously but they do have some differences. The information in this article should help you choose the one that best meets your needs.