Templates for new workbooks and worksheets

I have been having a 1st World problem.  For as long as I can remember, I create a new worksheet and the default cell vertical alignment is set to bottom which I hate.  There it is.  It’s no more than that.  I can’t believe I am publicly complaining about only this.  Many times over several years I would think damn is today’s the day I am going to fix this annoying default now and forever and invest up to 2 minutes to find the solution.  How many times every few months would I repeat going to the Excel Options (File \ Options \ General) to the section entitled “When creating new workbooks” only to find it still has those same 4 options as you see here below (wht so few Microsoft?) and Alignment is not one of them.   Was I the only one in the world who wants cell text and top aligned by default perhaps?

Anyway I could just fix this with two clicks, select Worksheet then use the Ribbon Top Align tool and stop whinging.   This is a nothing problem.

But then another day comes along and the same problem.   A steely resolve came over me today and I knuckled down to fix this once and for all.  And I discovered, having such a simple setting as default alignment was quite hard to do.

So first thing I discovered was somewhere over the years I had forgotten to use my own template.  I mean, the templates for new workbooks and worksheets,  Maybe 2010 and the Ribbon and QAT came along and I just forgot to develop one.  So OK great, I quickly set up my new template defaulting to one sheet with Top Aligned chosen and surely this was the solution to my long running, annoying problem.   And really I can do more than just fix this minor inconvenience –  I can develop new styles, new Pivot Table styles and new Table styles to draw from in my template.

File New TemplatesBut my joy was short lived.  Yes the first sheet of a new workbook was my template.  But add a new sheet using Insert on the Home Group on the Ribbon or using the plus icon at the bottom left added a new sheet but cell alignment is Bottom Aligned.  Now that surprised me.

So some googling and one of the most trusted sites out there Ron de Bruins Excel Automation shed light on this confusing problem.

There are two folders from which Excel draws templates.

  1. When you first create a template and save as an .xltx or xltm file it is saved here and then this folder is used when you choose File New on the Ribbon to open your template…
    C:\Users\John\Documents\Custom Office Templates  (obviously replace John with your Username)
  2. But when you insert a worksheet using:
    Ribbon Home \ Insert \ Insert Sheet or the plus sign at bottom left Excel this then pulls a completely different template to that in the Custom Office Templates folder…

No wonder the confusion.   But then of course when you think about it there would be different folders, one folder is for Workbooks and the other for Worksheets.  You potentially can’t insert a workbook into a workbook right, so it needs a different folder so it makes sense.

But, I can use my Workbook template as a Worksheet template because I have set the default number of sheets to just one.  So if I copy your template created in the Custom Office Templates folder to this folder:
C:\Users\John\AppData\Roaming\Microsoft\Templates
and I change how you insert new sheets to:

  1. Right click in the tabs area at the bottom
  2. Insert
  3. Then find and choose your template that you have copied there.

then finally, I have achieved my goal!  When I insert new worksheets choosing this template I get my Alignment and whatever other time savers such as new Table styles or commonly used VBA subs and functions I can now incorporate within this template (in the latter case if I save as .xltm).

OK it wasn’t quite as euphoric as this scene but it solved a long running problem and got me thinking about templates again.