Conditional Formatting in Templates

Angie M Graham

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

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å

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, andree@workbold.com)

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

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!

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

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.

CWibben

@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

@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.

CWibben

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

@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.