I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells.

It seems as though this only works within a sheet: Cell reference with colour formatting. This would be the perfect option, except I am unsure how I can reference the master sheet's key.


7 Answers 7


May be an old question but its still being looked up - so here is the workaround:

Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data from- right click the sheet tab n click 'copy to'

  • when the browse file window pops up select the sheet you plan to use the importrange formula on.

  • once you get the successful copy message - open that sheet - press ctl A and press the delete key

  • then in cell A1 type your importrange formula - your data will now display with all conditional formatting rules that have been set in the original sheet which you are importing from.

check out my post on "importrange while preserving conditional formatting rules" https://productforums.google.com/forum/#!topic/docs/-KnnBPq4d24

  • I would suggest changes in this procedure, in order to avoid showing the original information in the history of the only-view-tab spreadsheet. First duplicate the sheet holding the format you want to transfer. Then follow step 3, and 2. Commented Apr 22, 2020 at 18:52
  • Good solution. But unfortunately, the formatting in my sheet comes from a script and any change in the formatting in the original sheet doesn't reflect the changes in the target sheet.😭😭 Commented May 12, 2020 at 15:11

Creating a template with formatting and then applying the import range seems to be working


At this time ImportRange only imports data; it doesn't import formatting. An alternative is to copy the sheet and move it to another spreadsheet.


You can also use "zero width space" character(s) at the beginning(or end) of any text, which doesn't change it's appearance. These act as invisible tags for conditional formatting to trigger on. Make a conditional format with a "Text starts with" condition and "​" (without the quotes) as the search character // There is 1 zerowidth space within those quotes.
You can look for 3 zerowidth prefix characters then 2 then 1 of those characters with three different conditional formula effects (Title, subtitle, text).


I know this is a year old, but for others who come here from a Google search and haven't found any good answers elsewhere:

Depending on your needs, of course, consider using Conditional Formatting. In my case, I wanted to keep the formatting of my section titles. So, I made all the headers contain the same trigger '---' as in '---Section 1 ---'. Then I created a Conditional Format that says, "If 'Text Contains' '---' then fill with Blue".

This is great if I add another line to a section on the source sheet because the title format follows the '---'. Hope that helps.


If you are willing to use an add-on for Google Sheets, try Sheetgo. It can import formatting upon its first import.

Helpful reference: How to import data from one spreadsheet to another without the formatting


The thing that worked best for me is to just copy the source sheet and paste the formatting only on the destination (Cmd+Option+V on Mac). This of course is only useful if the formatting of the source is rather static, otherwise you'll need to copy/paste the formatting again every time rows are added or formatting is changed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.