Excel has a number of features that make it easy to copy and paste formatting across different parts of your Excel workbook, whether it’s on the same page or across multiple pages within a workbook or even across workbooks. The ability to copy and paste formatting is a massive time saver and helps your reports look clean and professional.
There are two primary methods involved in copying and pasting formatting. The first is the format painter, allowing you to use the mouse to paint areas with new formatting. The second option is to copy and paste formatting using contextual menus.
We’ll explain both methods and talk through some additional best practices when working with formatting workbooks.
Let’s jump in!
Table of Contents
Understanding Formatting in Excel
Before diving into copying formatting, it’s essential to understand what formatting in Excel is and is not. Excel keeps values, formulas, images, and formatting (including conditional formatting) separately from each other. Because of this modular approach it means that you can copy and paste each individual piece of an Excel spreadsheet.
- Font Styles and Sizes – Changing the typeface and size of your text.
- Cell Colors – Applying different background or font colors.
- Borders – Adding lines around cells or groups of cells.
- Number Formatting – Displaying data as currency, percentages, etc.
- Conditional Formatting – Automatically applying styles based on cell values.
It’s these items that we’ll focus on when we talk about copying formatting in Excel.
Copying formatting in Excel is primarily done using the Format Painter, a tool that allows you to copy formatting from one part of your spreadsheet and paint it on another, or by copying and pasting formatting as special.
Let’s take a look at both.
Copying Excel Formatting the Format Painter
To use the Excel Format Painter, select the cell or cells that you want to copy the formatting of. Click the Format Painter button on the Home tab of the Excel Ribbon. Then select the cell or cells that you want to apply the copied formatting to.
Here’s how in more detail.
Step 1.) Locate the Format Painter Button
The Format Painter is conveniently located on the left side of the Home tab and is notated by a small paint brush icon.
Step 2.) Select Cell or Cells to Copy Formatting Of
Prior to clicking on the Format Painter button, select the cell or cells that you want to copy the formatting of. In our example below, we selected several cells from D5 through D9 to copy it to E5 to E9.
Step 3.) Select Cell or Cells to Copy Formatting to
After clicking the Format Painter, the selected cells with remain highlighted, then when you click or drag your mouse across a series of cells the formatting will appear as soon as you let go of the mouse button.
In our example below, the formatting from column D has been copied to column E.
To summarize how to use the Format Painter, these are the steps.
- Select the Cell with Desired Formatting – Click on the cell that has the formatting you want to copy.
- Activate Format Painter – On the Home tab, click the Format Painter icon (it looks like a paintbrush).
- Apply the Formatting: Click on the cell(s) where you want to apply the copied formatting to.
There is a reason that the Format Painter is prominently located on the Home tab of the Excel Home ribbon, it’s one of the most widely used tools and can be a massive time saver. It’s especially useful when you add a new column, new row, or even need to copy and paste formatting from one sheet of a workbook to another sheet or another workbook altogether.
Tips and Tricks for Using the Excel Format Painter
As you start using the Format Painter, there are several tips and tricks that will save you time.
Double Click the Format Painter to Keep it Active
When you use the Format Painter, it will normally let you click on one cell or set of cells and then disappear. If you want to copy and paste formatting to a series of cells that are non-continuous, you can double click the format painter the click on as many cells as you want.
After you apply formatting, click the escape key on your keyboard to leave format painting mode.
Copying Formatting Between Excel Worksheets
You can click the top left corner of a worksheet where there is a triangle in the corner of column A and row 1 to select an entire sheet, or you can press CTRL + A to select all.
Then click the format painter to activate it.
After making the selection, navigate to another worksheet in your workbook, or a different workbook and click on the same place. This will copy and paste the entire formatting of one worksheet to another.
Copying Formatting to Multiple Excel Worksheets at One Time
To copy and paste formatting from one sheet to multiple sheets in Excel, select a cell, multiple cells or full sheet, then click the Format Painter. Prior to applying the new formatting to a sheet, hold down CTRL or SHIFT to multi-select the tabs to apply formatting to. Apply formatting to one of the selected sheets, it will be applied to all selected sheets.
How to Multi-Select Sheets in Excel
When you hold down SHIFT on the keyboard and select a tab on the left, and a tab on the right all sheets in between will be selected. If you hold down CTRL while left clicking on individual sheets, you will select multiple tabs.
Once selected, the Format Painter will be applied to the cell or cells of any highlighted sheet.
Once the formatting is applied, you can click on any of the single sheets without holding a keyboard button down and Excel will go back to having only one sheet selected.
Copying Excel Formatting using Copy and Paste Formatting
After copying a cell or range of cells, Excel gives users the ability to paste different parts of the data. Users can paste values, or they can paste formatting. This method is often quicker than using the Format Painter because keyboard shortcuts can be used.
Here’s how in more detail.
Step 1. Select Cells and Copy Contents
You can either select a single cell, multiple cells, or the contents of an entire worksheet. This step actually copies the entire contents of the cells selected, but we will only paste the formatting portion of the copied cells.
Either select cells and click Copy on the Clipboard section of the Home tab on the Excel Ribbon, or press CTRL + C on your keyboard. This is the universal Windows keyboard shortcut to Copy.
After the contents are copied to the clipboard, we can paste the formatting.
Step 2. Paste Formatting Using Paste Special
Either select the cells you want to apply the formatting to, or select the starting point of a range of cells. Excel will automatically fill the formatting down to match the cells that have been copied.
When you Right Click the mouse, a contextual menu will appear with Paste Options. The icon of a paintbrush in front of a clipboard allows you to paste formatting.
After pasting formatting, the format of the starting cells will be applied to the new cell or set of cells.
Tip: Excel gives you a wide range of flexibility when copying and pasting. If you navigate to the Paste Special section, you can not only paste formatting, but you can paste comments, formatting excluding borders, column widths, formula and number formats etc.
Before you manually adjust different aspects of formatting, check out the paste special options. It may be possible to set it up once and re-use the pre-set formatting many times to avoid re-work.
Keyboard Shortcut to Copy and Paste Formatting in Excel
While there is no method to activate the format painter with the keyboard, you can copy and paste formatting to a cell range. Do this by selecting, then copying the cells with formatting using CTRL + C. Then move to the cells you want to apply formatting to and press ALT + E, S, T and ENTER.
Alt + E opens the Edit menu, S opens the Paste Special Dialog, and T selects Formats, ENTER closes the paste special dialog box and accepts the changes.
Excel is a highly flexible tool allowing users multiple ways to complete most tasks. Copying and pasting formatting is no exception. To quickly copy and paste formatting from one cell or cells, to multiple cells, or even multiple sheets in the same or different workbooks, the Format Painter can be used.
Try double clicking the Format Painter to apply formatting across non-continuous cells.
The second option is to perform a standard Copy using the Copy button, or CTRL + C keyboard shortcut. Then paste the formatting, or a special subset of the copied formatting to different cells. Pasting can also be done using the keyboard combination of ALT + E, S, T though this method is less commonly used.