Excel 2020: Use Default Settings for All Future Workbooks
February 12, 2020 - by Bill Jelen
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.
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.
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.
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 as shown below. Click the Format button and choose a different input color.
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.
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.
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.
If you get tired of pressing Esc, go to File, Options, General and deselect the checkbox Show the Start Screen When This Application Starts.
Title Photo: Payton Ferris at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.