Apply conditional formatting automatically when new sheets are created

Question

Is there a function to automatically apply the workbook's conditional formatting rules when a new sheet is created? I have a workbook recording monthly attendance, with rules to highlight absences. At this point, when I need to create a new sheet for a new month, I copy and paste the previous sheet's content into the new sheet, which applies the formatting to the new sheet, then edit the new sheet to reflect the new month's data. However, I want to be able to hand the workbook over to somebody else and not have to leave instructions on how to use the workbook.

Answer 1

In a new workbook, set up a Sheet as you wish including formatting rules.

Delete all other sheets from that workbook.

Save the workbook as a Template named SHEET.xltx

Store that workbook in folder. . . . .

C:\Users\User\AppData\Roaming\Microsoft\Templates

To insert a new sheet in Excel, right-click on any sheetname and Insert. . . then select SHEET.xltx from the list.

Gord

Answer 2

Yes I did. I'm having trouble inserting the template into the new sheet in my workbook. I rightclick the sheet's tab, select insert, but my template does not appear as an option.

Answer 3

Thank you! Can you elaborate on the calender cell part of your response?

Answer 4

Hi Mary,

Have you tried Andreas' suggestion?

Regards,

Tisky

Answer 5

The most common solution is to create a template sheet.

This sheet contains all what you need, formulas, conditional formatting, etc.

And e.g. for a calendar, just one cell to setup which month should be used.

If you need a new sheet, copy the template sheet, fill the "month should be used" cell, done.

Andreas.