This Blog is for a favorite non-profit in Manchester, NH …

You have an existing workbook that you need to replace the old data in all the cells with “constant numbers” – maybe it is daily, weekly, monthly, quarterly – frequency doesn’t matter. How did you do this in the past?  Maybe you:

  • Opened the last workbook and saved it with a new name
  • Then you started typing in the new data

I did that years ago until one time when I was handed the new data to enter just as my boss was heading into the meeting where the workbook was needed. So I started entering the new data UNTIL the phone rang and I got distracted.  I thought I was done so I saved the workbook, printed it on an overhead, and took it into the meeting for my boss.  A few minutes later he came out asking WHY ARE LAST WEEKS NUMBER ON THE BOTTOM PART OF THIS REPORT?

Simple answer – I was distracted by the phone. So I promised I’d find a way so this would never happen again.

The SOLUTION – a Template. Below are steps you might want to do to prepare an existing workbook to be saved as a Template:

  1. Remove the data from the cells with Constant Numbers:
    GoTo (Ctrl + G or F5 key)
    Click Special Select Constants and remove the check mark in front of Text.
    Click OK. 
    Press the Delete key on the keyboard
  2. Conditionally Format the selected cells to make it clear which cells will need data:
    NOTE:  Do NOT click in any cells in the worksheet or the selected cells will no longer be selected!
    Click on Conditional Formatting in the Styles section of the HOME Ribbon
    conditional-formating-new-rule
    Click on New Rule
    Select Format only cells that contain
    Either change Cell Value to Blank or change between to equal to then type =”” (=QuoteQuote with no spaces) in the line at the right
    conditional-format-equal-to-equal-to-quote-quote
    Click Format and on the Fill tab, select any color of your choice (I won’t matter the color because the color will be gone once the cell has data in it)
    Click OK twice
  3. Click in the 1st cell you need to type in
  4. Excel 2007 – Click the Microsoft Logo in the upper right of Excel
    Excel 2010 + – Click the File tab
  5. Select Save As
  6. Change the Save as type: to Excel Template
  7. Enter an appropriate name for the template
  8. Check to see if Excel automatically selected the Template folder for you to save the template into.
    template-folder-for-templates
    Excel 2013 will create a folder in Documents called Custom Office Templates and save the workbook there.
    template-folder-for-2013-excel-templates
  9. Click Save and then close the workbook.