Hi - Dave here.
Happy Friday!
This week I got a good question from a reader named Mike:
"How can I get my future spreadsheets to remember my preference of custom headers and footers?"
This is one of those things that seems like it should be a simple setting in Excel, but it isn't. The answer is a
default template: a custom template with a specific name that Excel uses automatically every time you create a new workbook. It's an old-school tip, but if you find yourself making the same changes to every new workbook, it will save you a lot of clicks.
A default template can store many settings, including:
- Page setup options, including margins, orientation, and print area
- Headers and footers
- The document theme (theme colors, fonts, and effects)
- Font formatting, cell styles, and number formats
- Column widths and row heights
- Display options and zoom settings
- The number of sheets in new workbooks
The tricky part is
where to put the template. Officially, it goes in a hidden "startup folder" buried deep in the file system, and the location varies by platform and version.
However, I recommend you skip this complicated step and tell Excel to use a folder that
you create, in a location that makes sense to you. I just updated the article below with this simpler approach, with steps for both Windows and Mac:
[
Read the full article here]
The article also covers the settings you can save in a template, how to find the official startup folder with a clever VBA trick, a simple way to test that your template is actually being used, and how to set a default template for new
sheets as well.
Note: Default templates are a long-standing feature, so this tip also works in older versions of Excel, although some option names and locations vary slightly.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.