Create your own Excel template

Q. By far, my preferred number format is this one shown in the screenshot below. How do I make it the default format for my worksheets?

A. You can easily make this format your “default” in Excel by creating a template using this format and choosing that template when you open a new document in Excel. Every time you open the template, that format will already be applied.

You can make templates for all sorts of preferred “defaults.” Creating personal templates can have many uses, such as designing an invoice template specifically for your business, setting up a standard financial statement template, and creating a budget template unique to your expenses.

To create an Excel template, open a blank Excel document and add the information or formatting you would like to be included in the template. Or, if you already have a workbook that contains the information or formatting, open the workbook. For our example, open a new, blank Excel workbook. Click the Select All button in the upper left corner of the spreadsheet to the left of all columns and above all rows (as shown in the screenshot below).

This will select all cells in the spreadsheet. Right-click anywhere in the cells and choose Format Cells. Under Category:, select Number; choose 2 for Decimal Places:; put a checkmark next to Use 1000 Separator (,); and select (1,234.10) under Negative numbers:. The Format Cells window should now look like the screenshot. Click OK.

If this is the first time a personal template has been created on this device, you will need to define the path where these templates will be stored. To do that, go to File, Options, and choose Save. Under Save workbooks, there is a line that says Default personal templates location:. The path defined is usually C:\Users\[UserName]\Documents\Custom Office Templates. You will need to do this step only once.

Save your template by going to File, Save As, and choose the path that is defined in the step above. Name the template and save as an Excel Template (*.xltx). Close the template.

When you want to use your template, go to File, New. You will see the words Office and Personal about halfway down the page. Choose Personal and then select the template you would like to use. Once you have selected this template, it will be pinned right next to the Blank Workbook option that you see when you click File. At this point, it is just as easy to select your template as it is to select a new workbook, but you don’t have to start from scratch.

You can view the video demonstration for creating this template below.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

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.