Copy formatting Google Sheets with IMPORTRANGE Formula

by Jul 20, 2018

Smart data organization is critical to the success of a company or project. Data needs to be well formatted so that people can actually understand and use it. Raw data might be great to feed into machines, but humans need data that is efficiently structured and designed. You can emphasize and differentiate data using different fonts or cell colors. Tables and borders can be used to isolate or highlight certain sections. You can use various date formats or bring in alternative number formats, such as percentages, represent different currency types, or embed images within a cell. The ways to represent data are limitless.

Good formatting helps users contextualize data. But what happens when you need to transfer data between files? All of that hard to create beautifully represented data can be undone in the click of a button.

Some users will manually transfer spreadsheet data, but that is a serious waste of time and can limit an organization’s ability to make important decisions or operate as fast as their industry does. There can also be problems with human error or keeping track of updates. People need to be able to automatically import data and copy formatting Google Sheets. So how can we do it? Let’s discuss IMPORTRANGE and Sheetgo—both the benefits and limitations.

IMPORTRANGE

IMPORTRANGE is a widely used formula to import data from one Google Sheet into another. Not between tabs, but between different files. Using IMPORTRANGE to copy formatting Google Sheets takes a bit of technical skill. Using formulas within spreadsheets can be a bit daunting for non-technical people. Here is a primer on IMPORTRANGE if you don’t know how to use it with instructions and tips.

If you are familiar with using formulas it is very easy and quick to apply. However, it faces some limitations regarding the transfer of formatting. Because of this, you cannot always maintain the visual look of the source sheet.

For instance, IMPORTRANGE cannot import font formatting. You will lose font weight, size, color, and background. IMPORTRANGE also does not import images. For people working with raw data, this will not be much of an issue. But people who create highly visual spreadsheets, or depend on images within certain cells, will encounter a lot of problems using IMPORTRANGE to transfer data. Figure 2 demonstrates a data transfer from Figure 1 using IMPORTRANGE and how it fails to import formatting. The font-weight cell size and image have been lost.

Figure 1: Example source formatting

Copy Formatting Google Sheets

Figure 2: Formatting import using IMPORTRANGE

Copy formatting Google Sheets: Formatting Import Using Importrange

Copy formatting Google Sheets

If you need to import data and copy formatting Google Sheets, then Sheetgo can help you. Sheetgo has an add-on for Google Sheets designed to help people transfer data quickly and conveniently with no technical expertise. Add-ons are powerful tools that increase the capabilities of Google Sheets and allow users to do so much more with their data. Install the Sheetgo add-on by clicking on the button below.

Once installed, a new Google Sheet will automatically be opened. Give this file a name to avoid confusion (in this example: “Import Formatting”). To start Sheetgo, go to the Menu bar > Add-ons > Sheetgo > Start. A sidebar will open on the right side of your screen. To create a connection, and thus import without losing the formatting of your files, click on the green + button at the bottom right (see snapshot below).

Starting Sheetgo Add-on to Preserve the Formats in Google Sheets

In the next step, you select the source file that you want to import data from. By default, your most recent accessed files will be shown. If you cannot find the file you need there, you can either click on Select file(s) to search for the file in your Google Drive or you can directly upload by clicking the Upload files button at the bottom. Select the file and source tab name that you wish to import from.

Copy Formatting Google Sheets

Once selected, you can give your connection an appropriate name (in this case: Import_Formatting). In the Settings tab,  enable the Transfer formatting button to have it import your file keeping the same formatting alive. You can also enable automatic updates, filter, but for this example, we will leave it as it is. Finally, click on SAVE CONNECTION to have your data imported with the source formatting preserved, as shown in Figure 3.

Figure 3: Successful import of the source formatting using Sheetgo

Copy Formatting with Google Sheets: Successful Import of the Source Formatting

Users that need to transfer data and copy formatting Google Sheets files find that Sheetgo is the best option to maintain the integrity of the visual representation of their data.  The differences between the functionalities of Sheetgo compared to those of IMPORTRANGE are listed in the following blog post.

Share This