Setting a custom table style as the default table style is a little tricky. You need to create an Excel template that contains the custom style, and then make sure Excel uses the template when it starts up. Let’s walk through the process. First, create a new blank workbook. As a first step, I recommend that you highlight cell A1 in yellow. After you set up Excel to use a custom template, this makes it easy to verify that Excel is indeed using the template. Next, copy and paste a table with the custom style applied into the new workbook. The moment you paste the table, the custom style will be added to the workbook. Inside the Table Styles menu, right-click and set the custom style as the default style. Next, we need to save the workbook to a known location. Although Excel has a start up directory deep in the windows file system, I prefer to keep templates in my own template directory, in a location I know and control. In this case, I’ve created a folder called “templates” on the desktop and I’m going to save this file to that directory. You can create a similar directory anywhere you like. To use this file as a default template, I need to save the workbook as an Excel template with the name “book” and the extension “xlxt”. When you save a workbook as a template, Excel might change the directory path, so be sure to confirm the path after you set the file type. Now, before you save the file, make sure you copy the full path. You can do this in the file explorer. Then save the file. Back in Excel, navigate to Options in the File menu. Then visit the Advanced area, and, under General, near the bottom, you’ll see an option for startup files, with a box to specify a path. Paste the path to your templates directory here. Then press OK to exit the options area. To verify the template is working, quit and relaunch Excel. After Excel is running again and you create a new workbook, you should see that cell A1 is highlighted. To test the default table style, I’ll paste in some data and create a table. Sure enough, my custom style is used by default. To make changes to the default template, just open and edit the file and save as a template again in your templates directory.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
