The Daily Dose

Whenever I look at the default Table Style that Excel spits out:
 

 
 
…I think: Nice structure. Pity about the finish. That formatting is a bit eyestrain-inducing , if not migraine-producing. I’m going to have to sand that sucker back to the wood, and repaint it.

So I look through the default styles for something that I can use in the spreadsheet that I’ll later be sending Stephen Few:
 

 
 

…but there are very few that are Few-worthy, let alone sponge-worthy. This one is getting close:
 

 
…but that huge contrast in the header row between pitch-black fill and white writing is really Tufte on my eyes, not to mention that dark grid makes this data look like it comes from Excel. Stephen won’t like that at all.

So I create my own style:
 

 
Ahh, that’s better…it lets the data – and our eyes – breathe a little easier. It uses pretty minimal formatting so that the data is front-and-centre, rather than the table itself.

In fact, I’m going to assign a custom name to my beautifew new custom style, befitting of it’s ability to help me get one step up the ladder of visual enlightenment:
 

 
 

And now I’ll save little Stairway so that it’s the default Table style used whenever I create any Table ever again:
 

 
 

And with that done, now I can dappily go to a new workbook, and – while happily humming Bohemian Rhapsody – create a new table using my beautifew new Custom Table Style:
 

What the? Why am I NOT in that list? And why doesn’t that list have my beautifew new Custom Table Style applied to it? Ah well, can’t be good at Excel and lucky in lust, I guess. And anyways, at least I saved that Custom Table Style to the Table Styles gallery earlier. Let’s just apply my style manually from there:
 

 

That’s right, Wayne. No Stairway. Denied, indeed.

Let’s ask Microsoft where our beautifew new Custom Table Style is.
 

 
 
Well, er…um…you see, the thing is…when you save a Table Style, it only gets saved in the particular workbook you’re working in.

What? Really? You went to all the effort to allow users to create new Table Styles, but you didn’t give them a way to reuse those anywhere else?

It turns out, the only way we can make our beautifew new Custom Table Style permanently stick around is by:

1. Copying a Table that uses our new Table Style into a new blank workbbook
2. Setting that Table Style as the Default Table Stlye, like I did earlier
3. Deleting that Table
4. Saving the workbook as an Excel Template in the Startup folder, so that Excel will use this workbook – and our beautifew new Custom Table Style – as a template whenever we create a new document.

 
Well that is just…

 
NOT!

How is the average user going to manage this, eh?
 

 
 
That’s right, Wayne…it’s pretty tricky. Here’s a couple of tips that might help.

Firstly, before you save that template, make things (slightly) easier on yourself and find your Startup folder location by typing ?application.StartupPath in the immediate window of the VBE:
 

You can then copy that path, so that later on you can paste it into the Save As dialog box.

And there’s a few things you need to note about the Save As dialog box:
 

 

 
1. You want to change the name of the workbook from Book1 to just Book. (Excel will add the 1 or 2 or whatever automatically when it opens a copy of the template)

 

2. You might as well go all-out and save it as an Excel Macro Enabled Template, so that you never again get this pesky message:
   

 

3. You want to paste that Startup folder location in after you’ve selected that Excel Macro Enabled Template option from the Save as type dropdown, not before. Why? Because otherwise Excel inexplicably overwrites your previous directory choice as soon as you choose to save a file as a Template with this location:
   

 

Well, hopefully they’ll make this easier for us in Excel 2016.