So, Mr. Excel, has an old Podcast on creating default footers in excel. Yet, the episode is featuring Excel 97 and the process is very different in Excel 2007. So here, we go. With full inspirational credit to Mr. Excel, I give you “How to make a Default Footer in Excel 2007“.
So, you need a set of formats or text in every single excel workbook or spreadsheet you create. You need to take a few simple steps to get this done.
- Create a template workbook.
- Create a template worksheet.
- Point Excel to the location of the template files.
Creating Template Files in Excel
For any report that I create, I need to include the file path, the page number and my name. It’s a pain because I have to enter in three sets of information on each worksheet. So instead, I want to create a template in excel 2007 that will give me a standard, default footer across all tabs and in any new tab I create. There’s a difference!
Step 1: Create Footers Across All Tabs
In Excel, the standard workbook has three tabs. You need to make your changes across all three tabs. For my default footer, I needed to add &[File]&[Path] | &[Page] / &[Pages] | WJohnson. That’s standard for me.
Now that I’ve got my footers taken care of I need to save the file as an Excel Template file (.xltx). Save this file in a folder that is dedicated solely to your startup files (I’d suggest in your documents, make an Excel 2007 Template folder and a sub-folder for your default files). Save it as Book.xltx.
Step 2: Create a Template Spreadsheet
So with a workbook created, you need to create a default spreadsheet. From the same Book.xltx, delete all but one of the tabs. Save this as Sheet.xltx. The names are important. Book and Sheet templates override Excel 2007’s default behavior.
Point Excel to the Templates
You saved your default footers in Book and Sheet.xltx in a specific folder. Now it’s your job to get Excel to open the template files when you open excel.
- Start off by going to Excel Options.
- From there you select the Advanced Tab.
- Scroll to the bottom of the menu.
- Get the file path of your default template folder created in the previous steps.
- Paste the file path into the “At Startup” box (see picture below).
That’s it! Now, any new workbook you create will have your default footers. Any new tab you add will also have the new footers! So incredibly useful!
The One Issue: The spreadsheet template doesn’t increment the sheet number. When you add a sheet, it normally goes sheet1, sheet2, sheet3, … sheetn. Instead it will just copy the name of the tab – if you named the tab in the spreadsheet “Sheet”, it would output Sheet(1), Sheet(2), Sheet(3),… Sheet(n). I’ll update if I can figure out the incrementing! Maybe, Mr. Excel has the answer.