Copy Formatting From One Sheet To Another In Google Sheets (2 Ways)

Knowing how to copy formatting comes in handy especially when you have multiple worksheets or spreadsheets that have a similar data layout. It will save a surprising amount of time. Thus, in this article, we will look at the different ways to copy formatting from one sheet to another in Google Sheets.

Table of Contents

2 Ways to Copy Formatting From One Sheet to Another in Google Sheets

To show our two examples, we have the two following worksheets; one formatted and the other unformatted.

Formatted Dataset

Unformatted Dataset

1. Using Paste Special

The first method we will talk about is the Paste Special option of Google Sheets. We are all familiar with copy and paste, CTRL+C CTRL+V options, but Paste Special allows its users to specify what and how they want the data to be pasted (customizable pasting).

For example, we will Copy the entire dataset from the Main worksheet.

Right-click on the top-left cell (first cell) of the other dataset to find the Paste special option, and eventually the Paste format option.

Keyboard Shortcut: CTRL+ALT+V

Limitation:

The Paste Special option can only paste the same range that was copied. Any extra data cells will not be formatted.

2. Using the Format Painter

The next option we will discuss is the Format Painter. It is often overlooked but can make copying formatting to different sheets very easy.

To use the Format Painter, follow these simple steps:

Step 1: Select the format range you want to copy.

Step 2: Click on the Paint format button. You can find it in the Toolbar.

Step 3: Simply click (left click) on the first cell of the dataset.

The format should be pasted.

The steps may be simple, but it is easy to get wrong.

Limitations:

Like Paste Special, if you have multiple formats copied, only the copied range will be pasted. However, if a single type of format is copied, you can apply it to a range of cells.
It can be finicky to work with.

Copy Formatting to a Different Spreadsheet in Google Sheets

Copying formatting to a separate spreadsheet comes with some extra barriers. One is that we cannot use the Format Painter to copy the formatting.

So, the only thing remaining is the Paste Special. Which seems to work fine.

Even then, the border formatting is not copied over completely.

border formatting is not copied over to another spreadsheet

The borders are not copied over

Special Case: Copying Conditional Formatting

Most complex or conditional formatting of data in Google Sheets is done using the Conditional Formatting feature.

Since this type of formatting is dependent on external variables, copying them may not seem as simple. But it is otherwise.

Here we have a dataset that has conditional formatting applied to it:

Lucky for us, we can use both the methods we have discussed earlier, Paste Special and Format Painter, to copy the conditional formatting to another worksheet in the same spreadsheet.

using format painter to copy conditional formatting from one sheet to another in google sheets animated

Copying Conditional Formatting with Format Painter

However, you cannot directly copy the conditional formatting to another spreadsheet. To do so, you will first have to export the worksheet that contains your desired conditional formatting to the other spreadsheet first.

Simply right-click or click on the drop-down icon beside the worksheet name from the worksheet tab and this menu will appear:

New spreadsheet: Creates a new spreadsheet where the current worksheet is copied to
Existing spreadsheet: Lets you choose an existing spreadsheet where you want to copy your conditional formatting.

After either of the options is selected, you can copy the conditional formatting from the copied worksheet in the other spreadsheet by using any of the two methods mentioned above.

Final Words

That concludes all the ways we can copy formatting from one sheet to another in Google Sheets. We hope that the methods we have discussed come in handy for your spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.