Conditional Formatting in Templates

Is there a way to save the conditional formatting settings of a template and it transfer over to the new sheet?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It should save. When you select the option to use the template, be sure that all of the options are checked off.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Angie M Graham

    Not sure I follow.

    If you save a sheet to a template, it will save the conditional formatting and include it for each sheet you create from the template.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Have a fantastic day & Happy New Year!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Angie M Graham
    Angie M Graham ✭✭✭✭

    I think I know what is happening but don't know how to work around it.

    I created a sheet of payables that export to excel from Quick Books and then import into SS from Excel.

    I customized it using conditional formatting and changing the preferences on some columns (making checkboxes, and drop down options - spend quite a bit of time on this formatting).

    I saved the Sheet. Then I saved as New Template.

    When I open a new sheet using that Template, I have to clear the data to be able to import the new data from Excel the next week. I think when I clear the data, it's also clearing my formats because when I import the new data, the formats are gone.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How exactly are you importing the data? If you are using the "Import from Excel" feature, then it is creating a new sheet entirely and not saving to the sheet you created from the template.

  • @Paul Newcome Related Question - The conditional formatting saves in the template, but once I import data, then the conditional formatting disappears and I need to redo on each sheet. Thoughts?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @CWibben How exactly are you importing data? When you choose the "Import" function, you are actually creating a new sheet (no formatting or anything). If you want to use a template, you would need to copy/paste your data into the sheet saved as new from the template or you would need to use Data Shuttle.

  • Thanks @Paul Newcome We created a custom template w/ conditional formatting, then imported from Excel and MS Project. Once we imported data, the conditional formatting was lost. When I copy/paste from either, the conditional formatting is there, but all parent/children (e.g. indents) are now gone and we'd need to go line by line to adjust. Would be nice to have both. :) Thanks for your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @CWibben Yes. You are experiencing the "expected" functionality. Importing creates a new sheet (new sheets do not have conditional formatting, automation, etc.), and copy/paste into an existing sheet will remove the hierarchy.

    I have not yet tested ALL scenarios, but I have found that Data Shuttle can bring data in while preserving hierarchy, but it does have its limitations and may not necessarily be too helpful in your particular situation. If you already have access to Data Shuttle, it would certainly be worth doing some testing to see if you can get it to work for you.