Workbook Defaults

July 20, 2017 - by Bill Jelen

Start with a blank workbook with a single sheet

Do you have favorite worksheet settings in Excel? I do. There are things I do to every new workbook I create.

In a few minutes, you can teach Excel your favorite settings. Then, every time you create a new workbook with Ctrl + N or insert a new worksheet, the worksheet will inherit all of your favorite settings.

The key step is to save the workbook as a template into a specific folder with two specific names.

Start with a blank workbook with a single worksheet.

Apply all your favorite settings. There are dozens of possibilities. Here are a few that I use:

On the Page Layout tab, change the Scale to Fit so the Width is 1 page. Leave the Height set to Automatic and width set to 1 Page.

Create a custom header or footer. Use the dialog launcher in the bottom right of the Page Setup group. Go to the Header/Footer tab. Choose Custom Footer. Type whatever is your company standard is in the footer.

Create custom margins. I like narrow margins – even more narrow than the built-in Narrow margin settings. I’ve been using 0.25-inch margins since the 1990s, and they're automatically set for me because I've added that to my template.

Choose a theme. I like the colors from Slipstream, but I prefer the Effects from Office 2007-2010.

The next settings seems a little over the top. When you set a pivot table default theme, it only applies to the current workbook. Excel never saves your preference. Create a tiny two-cell data set. Create a pivot table. Change the default formatting. Delete the pivot table and the data set. The template will remember the setting.

Would you use Cell Styles more often if they weren’t so ugly? Do you hate that input cells are orange? Go to Cell Styles, right-click Input and choose Modify. Click the Format button and choose a different input color.

I've just shown you some of my habitual settings. I'm sure you have your own favorites. Maybe you always set up a name to define the tax rate. Add it to your template, and you will never have to set this up again. Turn off gridlines. Do whatever you always do.

Once you've finished customizing your workbook, you need to figure out which file type you use most often. For people who never use macros, this is often XLSX. But I always use macros, so my default file type is XLSM. Maybe you want workbooks to open faster, and you use XLSB. There is a template format related to each of these file types – just change the extension as needed. So, for me, I save the workbook as XLTM. You might save it as XLTX.

As soon as you choose one of these file types, the Save As dialog box moves to a templates folder. You need to save the workbook in a different folder.

In the folder bar, type %AppData% and press Enter to get to the AppData\Roaming\ folder on your computer. From there, navigate to Microsoft\Excel\XLSTART.

Save the workbook with the reserved name Book plus the appropriate extension.

Save As again and save the workbook in the same folder but use Sheet (plus the same extension) as the name.

Of course, you only have to set this up once. After you do it, any time you use Ctrl+N to create a new workbook, the new workbook will inherit all of the settings from your template named Book.

Why did you have to also save templates named both Book and Sheet? Any time you insert a new worksheet into an existing workbook, Excel uses the Sheet template.

My Rant About New and New...

I’ve been using Book.xltm for 20 years. Back in all versions of Excel from Excel 95 up through Excel 2003, the Excel Standard toolbar had an icon called "New". Click that icon, and Excel load the Book template. Everything was great.

The File menu offered a different icon called "New…". Hardly anyone used "New…" because it was half as many clicks to simply click the "New" icon on the Standard toolbar. "New" respects your custom settings in the Book template. "New…" does not.

Things fell apart in Excel 2007. When Microsoft moved to the ribbon, the good "New" icon was missing from the ribbon. People had to open the Office icon and choose the evil "New…". Microsoft allegedly studied the Excel 2003 SQM (pronounced skwim, the result of the Customer Experience Improvement Program) data to figure out what belonged in the ribbon. But somehow, they massively screwed up and left "New" out of the Ribbon. The problem remained in Excel 2010.

Then, in Excel 2013, someone on the Office team decided that Excel should start by showing you a pane that has recent files and a bunch of templates. This is called the Start screen. Now, for the first time, Microsoft is shoving the Blank Workbook template in your face every time you open Excel. This is the evil Blank Workbook template that you would have had to use File, New… to get to in the past.

This stupid template does not inherit settings from Book.xltx and Sheet.xltx. This stupid template is built in. There is no way to change it. Microsoft had a great but obscure feature, and they’ve made it harder to use by shoving this useless Blank Workbook template down your throat when you open Excel 2013 or Excel 2016.

If you’ve set up custom Book and Sheet templates, do not click the Blank Workbook template. Simply dismiss this opening screen by using the Esc key, and your custom Book template will load.

If you get tired of pressing Esc, go to File, Options, General. Uncheck Show the Start Screen When This Application Starts.

Thanks to Jo Ann Babin for an idea similar to this one.

Watch Video

Title Photo: Myrians-Fotos / pixabay