Excel provides many ways to format text and values in a worksheet. For saving time we can copy the similar formatting to another area, but every Excel user should know how to copy formatting in Excel from one area to another?

A professionally-formatted worksheet—through adding appropriate symbols, aligning decimals, and using fonts and colors to make data stand out—makes finding and analyzing data easier.

We can easily save a huge time to copy formatting from a cell or a range of cells (maybe enriched formatting) to another location. As a result, the target cells have been copied with the formatted style such as 

➟ Cell styles: contain a collection of formatting, such as font, font color, font size, fill, and borders. We can apply an Excel cell style to save formatting time).

➟ Merge and center labels: Type a text string in the left cell, select a range including the cell contains text string, and then click the Merge & Center to merge cells and center the label within the newly merged cell.

➟ Horizontal and Vertical cell alignment: The default horizontal alignment depends on the data entered, and the default vertical alignment is Bottom Align.

➟ Wrap text: Use the Wrap Text option to present text on multiple lines in order to avoid having extra-wide columns.

➟ Increase and decrease indent: To indicate the hierarchy of data or to offset a label, increase or decrease how much the data are indented in a cell.

➟ Borders and Fill colors: Borders and fill colors help improve the readability of worksheets.

➟ Number format: The default number format is General, which does not apply any particular format to values. Apply appropriate formats to values to present the data with the correct symbols and decimal alignment. For example, Accounting Number Format is a common number format for monetary values.

➟ Turn values into currency or percentages.

(01) HOW TO COPY FORMATTING IN EXCEL ➢ USING PASTE SPECIAL ‘FORMATS’ OPTION

In advanced Excel, it is the best practice to use the formats option in the Paste Special dialog box to copy all formatting from one area to another in Excel. This method can be applied for a single cell or range of cells with various formatting.

This method is helpful when we want to share a specific summary or particular data from the master workbook with others rather than sharing the entire master file.

• Read More: Paste Special in Excel Vs Break Link – Which one is Better?

METHOD 1: EXCEL COPY FORMATTING SHORTCUT (BY THE PASTE SPECIAL FORMATTING)

(i) Click a cell or select a range of cells containing formatting that we want to copy (i.e., the source).

(ii) Then copy the selected cell or range of cells by the Excel shortcut Ctrl+C.

(iii) Select the cell or range of cells to which the copied formatting should be applied (i.e., the destination).

(iv) Either press Alt+E+S+T (sequentially press Alt, E, S, T) or press Alt+Ctrl+V+T (hold  Alt+Ctrl+V, then press T) which will select the ‘Formats‘ option in the Paste Special dialog box. Then press Enter or click OK.

As a result, the selected cells have been copied with the formatted style.

Note: This method can be applied horizontally (row-wise) or vertically (column-wise). Similarly, this method can be applied to contiguous cells or non-contiguous cells. This method is also helpful for copying formatting to an entire column or row. 

• Read More: 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts

• Read More: 90+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts |

METHOD 2: EXCEL COPY FORMATTING BY THE PASTE SPECIAL VIA MOUSE RIGHT-CLICK

(i) Click a cell or select a range of cells containing formatting that we want to copy (i.e., the source).

(ii) Then copy the selected cell or range of cells by the Excel shortcut Ctrl+C.

(iii) Select the cell or range of cells we want to format (i.e., the destination).

(iv) Right-click on the selection and choose any of the following methods:

➟ WAY 1: Click Paste Special from the context menu and as a result, the Paste Special dialog box opens ➪ Select ‘Formats’ ➪ Click OK or press Enter. As a result, the targeted cells have been copied with the formatted style.

➟ WAY 2: After right-click, simply mouse over the Paste Special, an additional list will open ➪ Click Paste Special ➪ Select ‘Formats‘ option in the Paste Special dialog box ➪ Finally press Enter or click OK. As a result, the targeted cells have been copied with the formatted style.

■ Note: We had detail discussed on Paste Special in a separate tutorial, suggested you read this tutorial: Paste Special in Excel Vs Break Link – Which one is Better?

➟ WAY 3: After right-click, simply mouse over the Paste Special, an additional list will open ➪ Click the Formatting icon from the list. As a result, the targeted cells have been copied with the formatted style.

➟ WAY 4: After right-click, simply mouse over the Paste Special, an additional list will open ➪ Click the Keep Source Formatting icon from the list. As a result,the targeted cells have been copied with the formatted style.

METHOD 3: EXCEL COPY FORMATTING BY THE PASTE SPECIAL VIA THE RIBBON

(i) Click a cell or select a range of cells containing formatting that we want to copy (i.e., the source).

(ii) Then copy the selected cell or range of cells by the Excel shortcut Ctrl+C.

(iii) Select the cell or range of cells we want to format (i.e., the destination).

(iv) Then go to the Home tab ➪ Clipboard ➪ Click on Paste drop-down and choose any of the following methods:

➟ WAY 1: Click Paste Special which will open the Paste Special dialog box.

Select the ‘Formats‘ option in the Paste Special dialog box ➪ Finally, press Enter or click OK. As a result, the targeted cells have been copied with the formatted style.

➟ WAY 2: Click the Formatting icon from the list. As a result, the targeted cells have been copied with the formatted style.

➟ WAY 3: Click the Keep Source Formatting icon from the list. As a result,the targeted cells have been copied with the formatted style.

(02) HOW TO COPY FORMATTING IN EXCEL ➢ USING FORMAT PAINTER IN EXCEL

Format Painter is a feature found in most Office applications that enables us to quickly and easily copy all formatting from one area to another in Excel, Word, and PowerPoint. Format Painter in Excel is located in the Clipboard group on the Home tab (Home ➪ Clipboard ➪ Format Painter). The Format Painter button has an icon of a paintbrush. 

The Format Painter not only speeds up the process of applying the same formatting to other cells but also makes it easy to give our workbook a consistent look. 

METHOD 1: EXCEL COPY FORMATTING BY THE FORMAT PAINTER VIA THE RIBBON

(i) Select the cell or range of cells containing the formatting that we want to copy.

(ii). Choose Home ➪ Clipboard ➪ Format Painter. The mouse pointer is a white plus sign along with a paintbrush.

Single-click Format Painter if we want to copy the format to only one other selection. The format Painter turns off once the formatting has been applied.

If, however, we plan to copy the same format to multiple areas, double-click Format Painter. Then, to turn off Format Painter, either press Esc(ape) key or click the Format Painter tool.

(iii). Click or drag across the target cells we want to format. Excel immediately applies formats such as font, size, colors, borders, and alignment.

To quickly copy the width of one column to another column, select the heading (column address) of the first column, click the Format Painter tool, and then click the heading of the column where we want to apply the column width.

METHOD 2: EXCEL COPY FORMATTING SHORTCUT (BY THE FORMAT PAINTER SHORTCUT)

(i) Select the cell or range of cells containing the formatting that we want to copy.

(ii) Press Alt+H+F+P (sequentially press Alt, H, F, P) which will select the format painter in Excel.

(iii). Click or drag across the target cells we want to format. Excel immediately applies formats such as font, size, colors, borders, and alignment.