Custom Table Styles

Whenever I look at the default Table Style that Excel spits out:
 
Default Table
 
 
…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:
 
Default Styles
 
 

…but there are very few that are Few-worthy, let alone sponge-worthy. This one is getting close:
 
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:
 
Custom Table
 
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:
 
Stairway
 
 

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

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:
 
New Table

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:
 
Denied2
 

No stairway denied

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

Let’s ask Microsoft where our beautifew new Custom Table Style is.
 
Have you seen this Style2
 
 
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…

Excellent
 
NOT!

How is the average user going to manage this, eh?
 
Suck
 
 
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:
 
Startup Path

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:
 
Save as 2
 

     

  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.  

  3. 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:
    No Macros
  4.  

  5. 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:
    Wrong place
  6.  

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

8 thoughts on “Custom Table Styles

  1. Overwayned and underspocked: Genius! Yeah, I’ll see if I can whip up a post on Boolean Logic or something as a commemoration of Spock’s untimely demise.

  2. Hi, Dick

    Surprisingly, even setting by default and saving the template, it doesn’t work. I use Excel 2013 under Win 8.1 64.

  3. Hi Mike. I actually wrote this post, not Dick. I suspect Dick ain’t quite the Waynes World fan that I am. I’m picking he’s more of a “The Wire” kind of guy.

Leave a Reply

Your email address will not be published. Required fields are marked *