Microsoft Excel: Modify Excel’s default blank workbook

Q. Is it possible in the Office 365 edition of Excel 2016 to create new blank workbooks that are automatically formatted to include headers and footers with date, time, file name, and file path information?

A. Yes, it is possible to modify Excel’s default blank workbook; you can even modify the default new worksheets that you insert into your existing workbooks. Here’s how to do this in the Excel 2016 Office 365 edition; the procedures are roughly the same for all editions of Excel, although the file paths may be slightly different.

1. Locate your XLSTART folder. Launch a File Explorer window and search your entire computer for the XLSTART folder. You may have more than one XLSTART folder, and if so, use the XLSTART folder associated with your username. Make a note of this folder location. For example, my XLSTART folder associated with my username was located at C:\Users\CarltoncCollins\AppData\Roaming\Microsoft\Excel\XLSTART.

2. Modify a blank workbook. Open a new blank Excel 2016 workbook, and make all the modifications you desire for your new default blank workbook. For example, to add headers or footers to your workbook, from the Page Layout tab, select the dropdown arrow in the bottom right corner of the Page Setup group to launch the Page Setup dialog box (indicated by the blue arrow in the screenshot below). Select the Header/Footer tab, click the Custom Header button (indicated by the green arrow in the same screenshot), and then in the resulting Header dialog box (indicated by the orange arrow) position your cursor and click the File, Path, Date, and Time icons (circled in red also in the screenshot below) to insert those fields into the header (as depicted by the red arrows). (In the example pictured, I inserted the file name and path fields into my header to the left, and the date and time fields in my header to the right.) Perform similar steps to add Page Numbers to the Custom Footer and then click OK, OK.

3. Save the file. Save the modified Excel workbook to your XLSTART folder as an Excel Template (file type) named Book. This action creates a new custom blank Excel workbook template containing your specific default settings. (Note: Depending on your computer’s permission settings, you may not be allowed to save the new blank Excel template named Book directly to your XLSTART folder from within Excel. If so, save the file elsewhere, and then copy and paste the file into your XLSTART folder, and answer Continue if prompted to complete this action using administrator rights.)

4. Edit your Excel options. To complete this process, disable the Excel Start screen as follows. From Excel’s File tab, select Options, General, and in the Start up options section, uncheck the box labeled Show the Start screen when this application starts, and then click OK (indicated in the screenshot below).

At this point, your newly created blank Excel template containing your modifications should launch automatically whenever you launch Excel, or whenever you create a new blank workbook using Excel’s File, New menu option.

To ensure that new worksheets you insert into your workbooks also include the modifications you desire, follow these instructions.

1. Open the new blank workbook you just created and delete all worksheets except one.

2. Save the single worksheet workbook as an Excel Template in the XLSTART folder named Sheet. (Again, if your computer’s permissions do not allow you to save the new blank Excel template directly to your XLSTART folder from within Excel, save the file elsewhere, and then copy and paste that file into your XLSTART folder, and answer Continue if prompted to complete this action using administrator rights.)

3. Thereafter, newly inserted worksheets will contain all the modifications included in your Sheet.XLTX template. Your XLSTART folder should now appear as pictured at the bottom of the page.

Some of the default modifications I like to make to my blank Excel workbook and worksheet templates are as follows:

1. Custom headers and footers. Custom headers and footers described above are almost always a good idea.

2. Remove gridlines. I think gridlines clutter the screen; therefore, as a personal preference I like to turn off the workbook gridlines by default. Later, if I want gridlines in specific parts of my workbook, such as to highlight tables, then I can add them using the border formatting tools.

3. PivotTable defaults. I recommend that you create a quick PivotTable in Excel, and then adjust the default PivotTable options as follows:

• Preserve formats and column widths. Right-click the PivotTable and select PivotTable Options. On the Layout and Format tab, check the box labeled Preserve cell formatting on update and uncheck the option labeled Autofit column widths on update. Then click OK (as indicated in the screenshot below).

These adjustments will ensure that your PivotTable’s formatting and column widths won’t change each time you refresh or adjust your PivotTable.

• Sort order. Right-click the PivotTable and select PivotTable Options. On the Display tab, select the radio button labeled Sort A to Z, and then click OK (shown below).

This adjustment will ensure that your PivotTable sorts alphabetically, rather than in whichever text order your source data happen to occur.

• Disable the Generate GetPivotData tool. Select the PivotTable, and from the PivotTable Tools menu, select Analyze, click the Options dropdown arrow (circled in blue below), and then uncheck Generate GetPivotData, circled in red in the screenshot below.

This setting change will allow you to more easily reference PivotTable data in formulas created outside a PivotTable, without generating the complicated GetPivotData formula—which is my personal preference.

• Remove PivotTable. Once you have made the PivotTable modifications you desire, delete the PivotTable and PivotTable data by highlighting and deleting the columns containing the PivotTable and data; the PivotTable modifications you made will remain intact as default settings when you create new PivotTables in the future.

4. Other default setting changes. Please note that I typically make other changes to my default workbook as well. For example, I like to set my default font and font size to Calibri 12 point, which is Microsoft’s newer font designed to be more readable as both text and numerals, and on smaller handheld devices. However, this default setting, as well as many others, can be applied more easily from the File, Options menu. I covered seven recommended Excel default adjustments in my March 2012 Technology Q&A article “Ready, Set, Excel!“ You can see a video of this topic at youtu.be.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.