MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Use Default Settings for All Future Workbooks


March 01, 2019 - by Bill Jelen

Excel Use Default Settings for All Future Workbooks. Photo Credit: Payton Ferris at Unsplash.com

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 Height set to Automatic and Width set to 1 Page.

In the Scale to Fit group on the Page Layout tab, change Width to 1 Page, but leave Height at Automatic.

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 in the footer.

Click the dialog launcher below the Print TItles icon. In the Page Setup dialog, click the tab for Header/Footer. Click Custom Footer.
Type a left footer of Highly Confidential. A Center footer of Page &[Page] of &[Pages]

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.

In the Margins tab of Page Setup, choose 0.25 for Top, Left, Right, Footer. Type 0.5 for Bottom. In the Center on Page section, choose Horizontally.

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

The first icons on the Page Layout tab of the Ribbon offer dropdowns for Themes, Colors, Fonts, and Effects.

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.

In the Pivot Table Themes gallery, right-click your favorite and choose Set As Default.

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 as shown below. Click the Format button and choose a different input color.

The Cell Style for Input is a bright orange. Right-click that style and choose Modify.

I‘ve just shown you some of my favorite 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 it 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, and you can 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.

The default path for templates is C:\Users\[your name]\App Data\Roaming\Microsoft\Excel\XLStart

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. Use Save As again and save the workbook in the same folder but use Sheet plus the same extension as the name.

Two files are shown in the C:\XLStart folder: Book.xltx and Sheet.xltx.

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. 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 loaded the Book template. Everything was great.

The File menu offered a New... option, but hardly anyone used it 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.

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 loads.

The File, New panel offers a big tile called Blank Workbook. The figure advises you to press the Esc key instead of clicking this tile.

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

In Excel Options, there is a category called Start Up Options. Unselect Show The Start Screen When This Application Starts. Every time you open Excel, you will be at a blank workbook based on your Book.xltx.

Title Photo: Payton Ferris at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.