Default Template
By Richard Rost
2 years ago
Setup Default Template for New Workbooks in Excel
In this Microsoft Excel tutorial, I will teach you how to set up a default template so that when you create a new workbook, it will automatically be set to whatever settings you prefer, including font, font size, column widths, row heights, background colors, and so on. I'll show you how to set up a Book.XLTX file. This way, you don't have to keep making changes every time you create a new spreadsheet.
Ellen from Gulf Shores, Alabama (a Platinum Member) asks: Microsoft just updated Office and they set the default font to this new Aptos, which I don't like. How can I get back Calibri without having to change it for every new document I create? This is annoying. Help.
Notes
- Create a Book.XLTX file (or XLTM with macros)
- Put in: C:\Program Files\Microsoft Office\root\Office16\XLSTART\
- Your folder may be different depending on Excel version
- Find at: File - Options - Trust Center - Trusted Locations - Excel Startup
- Remember to Disable Start Screen (File - Options - General - at bottom)
Links
Recommended Courses
Want More Excel?
Most of what I do is Microsoft Access, so if if you want to see me post more Excel videos, make sure to comment below: "I want more Excel!"
Keywords
TechHelp Excel, excel 2021, excel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #techhelp, default template Excel, workbook settings, font customization, Book.XLTX, row height adjustment, column width, background color, Trust Center, Trusted Locations, startup folder, change default font, template creation, start screen configuration, file save options, version differences
Intro
In this video, I will show you how to set up a default template file for new workbooks in Microsoft Excel, including how to change the default font, adjust font size and column widths, customize row colors, and save your changes as a template file. You will learn how to locate the correct Excel startup folder, copy your template to that folder, and deal with administrator permissions in Windows. I will also cover how to disable the Excel start screen so your new custom template loads by default each time you open Excel.
Transcript
Today, I'm going to show you how to set up a default template file for new workbooks in Microsoft Excel. Yesterday's video was about Microsoft Word. I had a question from one of my Platinum members, Ellen, and she said that Microsoft just updated Office and they set the new default font to Aptos, which she doesn't like, and she wants to be able to go back to Calibri. So, how can you create a new Word document every time and have these settings involved? Yesterday's video was all about setting up a normal document template in Microsoft Word.
Now in Excel, you can do kind of the same thing, but it's a little more complicated. So go watch this video if you're curious how to do it in Word. For Excel, let's go ahead and start up Excel. And just create a blank workbook file, a brand new document, and you can see here you got Aptos Narrow is the new one, and that's not too bad. I actually think this looks better in spreadsheets than Calibri or Tahoma did, which were my two previous favorite ones.
What you're going to do now is set up this sheet just as you want it for new blank workbooks. New blank sheet. So I'm going to select all of the cells, and I'm going to set it to something. Let's go old school again. Let's go Arial. Remember Arial from the 90s? Maybe you like it a little bit bigger. Maybe you like 12 point. Maybe you're always taking this first column and making it wider, and the first row, and you're always making that a different color. Let's just go with green for now. And you want all of your new workbooks that you create to start off like this.
All right, what we're going to do is we're going to save this. Click on more options and then more options again. That will bring up the actual save dialog box. I like working with this thing. I'm old school. All right, it's going to take you to whatever your default document folder is for saving workbooks. Now down here, we're going to switch this to book.xltx, a template file. Now if you've got macros in here, you can do it with macros too. Save it as this one. With Word, you always want to save it as a macro-enabled document. With Excel, you don't have to. I'm just going to pick this one because I don't have any macros.
Now it's going to switch the document folder on you. It's going to take you to this folder here. Don't worry about this. We're not going to save this file in this folder anyway. I don't know why they bring you here. This is the wrong folder. This is where it assumes you're going to save all of your custom templates. No, go back to your regular folder. In my case, it's my drive and then spreadsheets. Where are you, spreadsheets? That's where I save all my spreadsheet files. I'm just going to call this book.xltx. Save it in your default folder.
All right, it's saved. We're good to go. Now, the location that you save this in is going to be different for lots of different people. It could be in one folder for some and a different folder for others. It depends on what version of Excel you have. Here's how you find what the right folder is for you. It's not as easy as it was with Word, where you can just type in that app data trick that I showed you.
Alright, here we're going to go to File and then come down to Options, and you're going to go to your Trust Center, Trust Center Settings, Trusted Locations, and I know it's a pain, locations and I don't think and find your Excel startup folder; that's the folder where you have to put this template. By a lot c program files microsoft office root office sixteen for me the version I have okay and then excel start but can you change this year you can change it but I would just leave it here I just take a note of that copy it when we had a weekend copy of your ticket just take a note of it. Actually, you can copy if you double click right there because you can change it by that. So just take this here and just copy this to your clipboard. Ctrl+C.
Okay, now close Excel. Open up your file manager. Alright, we're going to click up here in the address bar, and we're going to paste in that thing that we copied, we're going to hit Enter. That will take us right to that folder. Program Files, Microsoft Office, root, Office 16, Excel, Start. Okay?
Now, we're going to open up another copy of our file manager or another window, however you want to do it. All right, there we go. There's my spreadsheets folder. There's that book.xltx that I saved. We're going to take that guy, click and drag, and drop it into here. You're going to get a warning up because you need administrator permission to put it in there. So if you're not someone that's got administrator permission on the machine that you're on, go get someone who is, and then hit Continue. And it will copy that file into that folder. There it is.
Okay? All right. Close down the File Manager. Let's go back into Excel. Come on. All right. Open up a blank workbook and... Oh, wait. Oh, no. No, I still got Aptos Narrow. What's going on here? Man. All right. This drove me nuts until I figured it out. When you start up Excel, let me do it again, this screen here, right, the start screen has you pick a blank workbook. This blank workbook is based on the default template that's built into Excel, not the one you just created. So the easiest way to get around this is to go into options and then go to general all the way at the bottom down here and turn off "*Show the start screen when this application starts*" and then hit OK. Now, close Excel, open it again, and it'll come right in to your template. Boom. See that? I'm in Arial, and I got that green first row and that wide first column. See that? That's how you do it.
The tough part is finding that folder, but you've got to go into your Trust Center to see where it is because it's different for every version of Excel, well for a lot of versions. Then you have to know to disable that start screen. Okay? And that's pretty much it. Now you got this as your default every time you start up Excel. And you can go back to old school Arial and Times New Roman if you want. And be cool like the old Gen X kids.
So there you go. That's how you set up your default template for Microsoft Excel. If you're curious about how to do something like this with Microsoft Access or PowerPoint, I'm going to talk about that in another video a little bit later today. So stay tuned, or check for a link down below. I'll post it there when it's done. But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS
Setting up a default template file for new workbooks in Microsoft Excel
Changing the default font style in an Excel workbook
Adjusting font size in an Excel workbook
Modifying column width in an Excel workbook
Changing the background color of row headers in an Excel workbook
Saving an Excel workbook as a template file (book.xltx)
Navigating to the default document folder for saving workbooks
Using the Excel 'Save As' dialog options to save a template
Locating the correct folder to save Excel template files
Navigating to the Excel Trust Center to find Trusted Locations
Determining the Excel startup folder path
Copying the Excel template file to the startup folder
Adjusting file manager settings for transferring the Excel template
Obtaining administrator permission for file transfers in Windows
Troubleshooting issues with the default template not loading on Excel startup
Disabling the Excel start screen to ensure the custom template loads by default
Identifying differences in template file setup between Excel versions
Closing and restarting Excel to confirm the default template is in use
Customizing Excel startup behavior to bypass the start screen
Confirming successful application of the default Excel workbook template
Quiz
(Q1) Which font was set as the new default by a Microsoft Office update according to the video?
(A) Times New Roman
(B) Calibri
(C) Arial
(D) Aptos Narrow
(Q2) When setting up a default template in Excel, what is the first thing you should create?
(A) A macro-enabled template
(B) A blank workbook file
(C) A text document
(D) A custom formulas sheet
(Q3) Which step is NOT part of customizing a new blank workbook in Excel?
(A) Selecting all cells and setting the font style and size
(B) Changing the width of the first column
(C) Saving the file in the custom templates folder
(D) Coloring the first row a different color
(Q4) What file format should you use to save the template for new workbooks in Excel?
(A) .xlsx
(B) .xlsm
(C) .docx
(D) .xltx
(Q5) Where should you NOT save the template file for Excel?
(A) In the custom templates folder suggested by Excel
(B) In your default document folder
(C) In the Excel startup folder
(D) On the desktop
(Q6) How can you find the correct folder for Excel startup templates?
(A) Through the File Explorer search bar
(B) By observing the default save path in the Excel options
(C) By using the Trust Center settings to locate Trusted Locations
(D) By typing in the "app data trick" mentioned for Word
(Q7) What is the purpose of copying the startup folder path to the clipboard?
(A) To create a new folder in the File Manager
(B) To navigate directly to that folder in the File Manager
(C) To share it with others through email
(D) To paste it into your browser
(Q8) What must you do if you receive a warning while copying the .xltx file into the startup folder?
(A) Ignore the warning and proceed
(B) Obtain administrator permission to copy the file
(C) Delete the original template file
(D) Restart your computer
(Q9) After creating and saving your custom Excel template, why might you still see the default font like Aptos Narrow when opening a new blank workbook?
(A) Because a custom template cannot override default settings
(B) Because the template file is corrupt
(C) Because the blank workbook option on the start screen uses Excel's built-in default template
(D) Because you didn't save the template properly
(Q10) What setting should you change in Excel to bypass the start screen and use your custom template upon startup?
(A) Enable macros
(B) Turn off "Show the start screen when this application starts"
(C) Set the custom template as the default save option
(D) Change the default font back to Calibri
Answers: 1-D; 2-B; 3-C; 4-D; 5-A; 6-C; 7-B; 8-B; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Excel Learning Zone is all about creating a default template file for new workbooks in Microsoft Excel. Yesterday, I covered how to do this in Word for those of you who prefer Calibri over the new default font, Aptos, since Microsoft recently changed that in an Office update. Today though, we're shifting focus to Excel and I'll walk you through the process step by step.
First, open Excel and start a new blank workbook. Right away, you'll see that Aptos Narrow is the new default font. Personally, I think it looks better for spreadsheets than some of the older options like Calibri or Tahoma, though everyone has their preference. If you'd rather use something else, for example Arial in 12 point size, you can set that up. Maybe you always prefer your first column to be wider, or you like setting a specific color for the first row. Go ahead and make those changes in your new blank sheet just the way you like them.
Once your sheet looks how you want all new workbooks to start out, it's time to save this setup as a template. When you save, make sure to select the option for a template file, specifically book.xltx. If you use macros in your workbooks, there's an option to save as a macro-enabled template file as well, but in this example we're just using the standard template.
Here's where it gets a bit more complicated compared to Word. Excel may direct you to save templates in a certain folder, but you shouldn't necessarily use the suggested folder. Instead, you need to find Excel's startup folder, as each version of Excel may have a different default location for templates. The proper way to locate this folder is through the Excel Trust Center. Go to File, select Options, then navigate to the Trust Center, and open Trust Center Settings. From there, look for Trusted Locations and find the folder path used for Excel startup files. Copy this path to your clipboard.
After you've copied that folder path, close Excel and use your file manager to go to that directory. In the address bar, paste in the folder location and press Enter. In a separate file manager window, find the book.xltx file that you just saved. Then, drag and drop this file into the startup folder. Windows may prompt you for administrator permission to complete this action. If you don't have admin rights, you'll need someone who does to help you proceed.
Now, after moving your template file, open Excel again. You might notice that when you create a new blank workbook from the start screen, it doesn't pick up your custom template. This is because the default blank workbook used by the start screen is hardcoded to use the built-in template, not your own. To solve this, go back into Excel's options, scroll down in the General section, and disable the "Show the start screen when this application starts" option. Once this setting is off, restart Excel. The program should now launch directly into a new workbook made with your custom template. You'll see your chosen font, colors, and column widths in the new workbook, just as you set them.
This setup process can be tricky due to the differences in folder locations and user privileges across various versions of Excel and Windows. The key steps include correctly modifying your workbook to your preferences, saving it as book.xltx, finding and accessing the right startup folder, making sure you have the necessary permissions, and finally disabling the start screen so your template loads by default.
You now have a way to customize the appearance and basic format of every new Excel workbook you create, going back to your preferred classic Office look or whatever style you fancy. If you're interested in doing something similar with Microsoft Access or PowerPoint, I'll be covering those in another video soon, so keep an eye out for the link when it's ready.
For complete step-by-step video instructions on everything we talked about today, visit my website at the link below. Live long and prosper, my friends.
Topic List
Setting up a default template file for new Excel workbooks
Changing the default font in an Excel workbook
Adjusting font size for all worksheet cells
Modifying column width in the template
Changing the background color of the first row
Saving a workbook as a book.xltx template file
Navigating to the Save As dialog in Excel
Locating the correct Excel startup folder
Finding the Excel startup folder via Trust Center
Copying the template file to the startup folder
Responding to administrator permission prompts in Windows
Disabling the Excel start screen on application start
Ensuring Excel uses the custom default template on startup
Verifying default template changes after restarting Excel
|
