Copy formatting in Google Sheets with the IMPORTRANGE formula

Formatting data is extremely important to effectively organize data in spreadsheet programs such as Google Sheets, so that you can read and interpret it easily. We can leave the importing of data to technology, however, human input is better when it comes to formatting this data in a way to contextualize and optimize a business.

You can emphasize different data sets by using different fonts or cell colors, highlight different data using tables and borders, or even optimize data through number formatting, such as percentages or currency. The final result is an effectively formatted sheet of data that shows us exactly what we are looking for.

But what happens when we try to manually transfer this data to another file? We lose this beautifully organized formatting. Furthermore, manually transferring this data wastes precious time, and can also lead to a higher likelihood of human error.

All things considered, it’s clear that businesses need to be able to automatically transfer data between files whilst maintaining their formatting.

So how can we automatically transfer data with copy formatting in Google Sheets? Both IMPORTRANGE and the workspace automation tool Sheetgo provide an answer – let’s explore both options and their benefits and limitations.

Ready to streamline your data?

What is the IMPORTRANGE formula?

IMPORTRANGE is a widely used formula that can import data from one Google Sheet file to another. Using formulas within spreadsheets can be tricky for those with little experience. You can use this introductory article on IMPORTRANGE that includes instructions and tips on how to use this formula effectively.

If you are familiar with using formulas in Google Sheets, IMPORTRANGE is a quick and easy way to automatically transfer data from one file to another. However, it’s important to note the limitations of this formula when it comes to maintaining the visual formatting. With IMPORTRANGE, you can maintain the raw data format from the source sheet, however, you cannot transfer font weight, size, color, or background.

Let’s take a look at IMPORTRANGE’s copy formatting on actual imported data.

Figure 1: Example data – source sheet

copy formatting google sheets 1

Figure 2: Imported data using IMPORTRANGE

copy formatting google sheets 2

As you can see, IMPORTRANGE has successfully transferred all of the data formatting (such as currency and percentages number formatting), but has failed to maintain all of its visual formatting. Font weight, font size and cell color have been lost.

Overall, IMPORTRANGE is a great option for those who are confident in using formulas and only need to transfer raw data. However, if you are creating more visual data sets that require a lot of visual formatting, then you’ll need something like Sheetgo.

Connect, merge, filter or split your spreadsheets

Copy formatting Google Sheets with Sheetgo

Sheetgo automatically transfers data between files using connections. You can use Sheetgo as an add-on in Google Sheets to automatically transfer data quickly whilst maintaining the original formatting.

Unlike IMPORTRANGE, Sheetgo requires no technical expertise, so you can transfer data quickly and conveniently. More importantly, unlike IMPORTRANGE, Sheetgo can effectively transfer font weight, size, color, or background through copy formatting. This is perfect for those who need to transfer data that is visually formatted.

First, add Sheetgo as an add-on in Google Sheets

Follow these instructions to use Sheetgo as an add-on in Google Sheets.

  1. In your google sheets, click the Add-ons button
  2. Select Get add-ons
  3. Search for Sheetgo in the pop up
  4. Select the Sheetgo add on and click Install.

Or install the Sheetgo add-on directly from the Google Workspace Marketplace, by clicking the button below:

Once you have successfully installed Sheetgo as an add-on in Google Sheets, you can now use it whenever you need to transfer data.

How to use Sheetgo to transfer data with copy formatting

Let’s go through a step-by-step of how to use Sheetgo to transfer data with copy formatting:

  1. Start on the Google Sheet page where the data you would like to copy is located. Make sure you name this file clearly to avoid confusion.
  2. In the navigation menu, click on Add-ons and select the Sheetgo add-on. Press Start.
copy formatting google sheets 3
3. The Sheetgo add-on will appear as a sidebar on the right-hand side of your Google sheets. As you want to import data, click the Select data button for the Export data option.
copy formatting google sheets 2

4. Information on this file will come up. Under the File tab section, make sure you select the correct tab that has your data.

copy formatting google sheets 5

5. Click on the Settings menu. Enable the Transfer formatting options you wish to transfer to your new Google Sheets. Once you are sure you have entered the correct information, press Continue.

copy formatting google sheets 5

6. Now choose where you send your data. As we want to export our data to another Google Sheet, we click Google Sheets.

Choose whether you want to export your data to a new or existing file. Make sure you provide a clear File name and choose your Destination folder. Once you are happy with your destination, press Finish and save.

copy formatting google sheets 7

7. Your data is now connected. You should now have a new file with your exported data and copy formatting.

copy formatting google sheets

When comparing the above data to the original source sheet in Figure 1, it is clear to see that Sheetgo has successfully transferred all of the data, whilst maintaining all of the visual formatting from the original source. The font type, font size and cell color have successfully transferred to the new destination file in Google Sheets. Thus, Sheetgo’s ability to copy formatting makes it the best option to maintain the visual integrity of your data.

⇒ Watch the video below and learn how to copy formatting in Google Sheets with the IMPORTRANGE formula.

How to copy formatting in Google Sheets with IMPORTRANGE formula

Copy formatting with IMPORTRANGE

And there you have it! With Sheetgo, you can import any formatted data automatically between Google Sheets quickly and effectively, maintaining all of the original visual formatting.

Take a look at the following blog post if you would like to learn more about the differences between the functionalities of Sheetgo compared to those of IMPORTRANGE.

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

Connect everything and automate entire business processes

Ready to streamline your spreadsheet data?

You may also like…