Out, damn’d gridlines! Out, I say!

So after doing some incredibly complicated formula challenges and ninja-grade VBA, I thought I’d turn my hand to something simple: finally changing the default template that loads when Excel starts or when I create a new workbook or worksheet so that gridlines are turned off. Because if there’s one thing I hate about Excel, it’s those gridlines: they make everything look like it was done in Excel. And if there’s one thing I do as soon as I open a new file, it’s turn those gridlines off. Tables and PivotTables give me all the borders I need, thank you very much.

Boy, what a battle. I have saved my changed book as an XLTM to just about everywhere I can think of.

  • I’ve tried saving it to C:\Users\Samsung\AppData\Roaming\Microsoft\Excel\XLSTART but when I start Excel, I have gridlines.
  • I’ve tried creating a new file at C:\xlStart with the template in it, and told Excel via Options>Advanced to open files in that folder but when I start Excel, I have gridlines:

    Options

  • I’ve saved it to C:\Users\Samsung\Documents\Custom Office Templates but when I start Excel, I have gridlines

I’ve gone from feeling like I’ve mastered to Excel, to feeling like I’m a complete idiot. Anyone care to tell me that I’m not?

How the heck the average user is supposed to know how to do this stuff is beyond me. Why isn’t there simply a button on the ribbon or backstage that says:
Give all future workbooks the settings of this one.

I’m using Excel 2013 365. But I’m thinking of doing a complete 180.

15 thoughts on “Out, damn’d gridlines! Out, I say!

  1. I tried xltx. Still no joy. The weird thing is that I have a vague memory of it working a few times early on. So maybe Excel has got confused with all my playing around. Have done a complete restart, to no effect.

  2. What filename did you use? Should be Book.xltm IIRC.
    Perhaps the extension needs to match your default filetype when you save-as a freshly started Excel file?
    I just tried on my Excel 2010 and it worked.

  3. I haven’t done this for decades, but I think filename Book.(template suffix) works for File-New, and Sheet.(template suffix) for New worksheet. And that it needed translation til local language, wich is Bok and Ark here in Norway.

  4. Jeff… In Excel 2013, try using Ctrl+N to create a new workbook.

    Back in Excel 2003, there was a “New” icon on the Standard toolbar and a “New…” command in the File menu. “New” would load book.xlt. Using “New…” and selecting blank workbook would not. For people who love this trick, “New”=Good. “New…”=Evil. I have no idea when this original bug with the “Blank Workbook” icon on the “New…” screen was introduced. It has been there forever, and it used to not matter because seeing this screen was not part of the normal workflow.

    Starting in Excel 2007, the default Excel interface no longer offered “New”. The thing called “New” on the Office button was really “New…”. To open book.xltx, you either had to use Ctrl+N, or customize the QAT, find “New” (instead of “New…”) and add it to the QAT. Same story in Excel 2010, choose File, New(…), Blank Workbook and you would not get book.xltx. But simply launching Excel 2010 would give us book.xltx so it was fine.

    Now, in Excel 2013, when you launch Excel, you are taken directly to the “Start screen”, which is actually the evil “New…” icon. When you click Blank Workbook, you are *not* getting book.xltx. So, this mildly annoying bug introduced in Excel 1850 is now forced upon us, front and center every time we open Excel 2013.

    I can only think of three options:
    (a) that I recommend to people who want to use Book.xltx that they will have to go to Excel 2013 File, Options, and uncheck “Show the Start screen when this application starts”, or
    (b) when Excel 2013 opens, press Esc to close the evil screen and get book.xltx
    (c) the Excel team makes the “Blank Workbook” icon respect book.xltx, correcting a decades-old bug.

  5. So, maybe heavy-handed, but you could using the Application.NewWorkbook event in a class module in the Personal workbook. That should allow you to create a new workbook with any application settings you want.

  6. @ Bill Jelen, good suggestions and history. Debra has a post on this at http://blog.contextures.com/archives/2013/01/17/open-excel-2013-with-a-blank-template/.

    Jeff, note that if you ever get this working, you also need a “Sheet.xltx” to keep those nasty gridlines off added sheets. Interestingly, the Sheet.xltx doesn’t apply to the starting sheet(s) in a new book, so you need both.

    Love the line, “they make everything look like it was done in Excel.”

  7. Aha! Oho! It was my own damned fault. As Jan says, filename should be Book.xltm not Book1.xltm. So that confirms my suspicion…I am indeed an idiot.

    That said, it strikes me that changing the default workbook easily is something that other idiots would want to do too. So Microsoft…how ’bout you give us idiots a fool-proof way of doing it that doesn’t involve us finding where some startup folder that we otherwise would never visit lives, and doesn’t involve us saving the current file with some extension that we would otherwise never use, and doesn’t involve us having to change the name of that template to some name that we never see when Excel starts?

  8. One time out of ten I do want gridlines. So what I’ve just done is added the New… icon to my QAT so it sits right next to the New icon. Now if I want gridlines, I click the New… icon which takes me to the backstage view where I can click on ‘New Workbook’ and get one with gridlines. Otherwise I use the New icon or push Ctrl + N (which I’m more likely to do, because – to reuse a gag from a previous post – I’m a Ctrl freak).

    You say bug? I say feature!

  9. There’s always the Registry. Fire up REGEDIT, navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options (change the 14.0 as needed), and add 4040 hex to the Options3 value.

  10. This post and the comments solve the problem of new blank workbooks only, and I often work in workbooks from others. My personal solution is to add the ‘toggle gridlines’ button to the QAT, so I just press Alt+6 whenever I open Excel. Way less fancy than the other suggestions, but it does the trick for me :-)

  11. @Scott Trapping the Workbook event works pretty well:


    Option Explicit

    Private WithEvents mxlApp As Excel.Application

    Private Sub Class_Initialize()
    Set mxlApp = Excel.Application
    End Sub

    Private Sub Class_Terminate()
    Set mxlApp = Nothing
    End Sub

    Private Sub mxlApp_WorkbookActivate(ByVal Wb As Workbook)
    ActiveWindow.DisplayGridlines = False
    End Sub

  12. Oh, thanks for this post and the comments. The gridlines were nagging me for a while.
    I knew I’ve changed it before, but both my solution and my memory were gone with a new laptop at work.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.