Home
Technology Articles
Software Articles
Microsoft Products Articles
Excel Articles

Creating Custom Excel Templates

By: 
David H. Ringstrom
|
Updated:  
2025-04-21 13:16:57
|
From The Book:  
Microsoft 365 Excel For Dummies
Microsoft 365 Excel For Dummies
Microsoft 365 Excel For Dummies book cover
Explore Book
Microsoft 365 Excel For Dummies
Microsoft 365 Excel For Dummies book coverExplore Book
Buy On Amazon

Excel templates are the unsung heroes of efficient spreadsheeting — like meal prepping, but for your data. Why start from scratch every time, when you can have a perfectly formatted, mistake-proof starting point? Plus, using templates saves you from the inevitable “Oops, I just saved over my clean master file” moment.

Windows users can create custom Excel worksheet templates that are ready to be dropped in anywhere, while both Windows and macOS users can craft custom workbook templates and even tailor Excel’s sacred default template — the very one it uses to spawn new workbooks.

Carving out worksheet templates

Worksheet templates let you drop in prebuilt worksheets on the fly — a huge timesaver when you frequently add the same formatted sheet. But here’s the catch: this feature only exists in Excel for Windows. Mac users, you’re out of luck with this one.

Here are the steps:

  1. Create a workbook that has the worksheet(s) that you want to save as a template.
  2. Choose File→Save As→Browse.
  3. Navigate to Excel’s hidden template folder:
    1. Type %appdata% into the File Name field, then press Enter.
    2. Scroll down and double-click on Microsoft.
    3. Scroll down and double-click on Templates.
  4. Type a meaningful name for your template in the File Name field, and then click Save.
  5. Choose File→Close or press Ctrl+W or Ctrl+F4 to close the template workbook.

To put your nifty template to use:

  1. Right-click any worksheet tab, and then choose Insert.
    The Insert dialog box opens.
  2. Select your template from the General tab, and then click OK.
    Bingo! A perfectly formatted worksheet appears, no copy-pasting required. Even better, this saves you from accidentally moving key worksheets from one workbook to another when you meant to copy them.

Now you can insert polished, preformatted sheets into any workbook without fear of dragging your data into oblivion.

Establishing workbook templates

A workbook template is a preconfigured Excel file that serves as a starting point for new workbooks, preserving formatting, formulas, and structural elements while preventing accidental overwrites. Good news, macOS users — this one’s not Windows-only! You can create and use workbook templates just like Windows folks:

  1. Build out your Excel workbook with all the bells and whistles. This can include custom formatting, formulas, headers, footers, maybe even a motivational quote in cell A1.
  2. Save the workbook:
    • Windows: Choose File → Save As, then select Excel Template as the file type.
    • macOS: Choose File→Save as Template.
  3. Name your template, and then click Save. You now have a pristine copy of your workbook that can only be affected when you open the template workbook, which isn’t easy to do.

To create a new workbook based upon your template:

  1. Choose File→New→Personal (Windows) or File→New From Template (macOS).
  2. Pick your carefully crafted template.
    A new workbook opens with a preassigned file name based upon your template and a numeral, such as YourTemplate1.
  3. Choose File→Save As, select Excel Workbook (.xlsx) as the file type, and give it a real name.

Rest assured, even if you reflexively mash Ctrl+S (Windows) or Cmd+S (macOS), you can’t save over the original template — it’s safely tucked away in a templates folder, far from your impulsive keystrokes.

Tailoring Excel's default workbook

Why settle for Excel’s default when you can start every new workbook exactly the way you like it? From font choices to default sheet count, Excel gives you some basic customization options. Windows users even get to pick a default view (Normal View, Page Break Preview, or Page Layout) because, apparently, Microsoft thinks macOS users are fine with whatever they get.

To tweak these basic settings:

  • Windows: Choose File→Options→General, and then modify the When Creating New Workbooks section.
  • macOS: Choose Excel→Preferences→General, and then set your preferences.

But let’s be honest — the real magic happens when you customize the default workbook template. Want every new workbook to have your preferred formatting, column widths, headers, footers, and whatever else sparks joy? This is where you make it happen.

Creating a custom default workbook for Windows

Follow these exact steps to create a masterpiece that most (but not all) new workbooks will be based on:

  1. Set up a new workbook and make it your own. Anything is fair game — fonts, column widths, number of worksheets. You’re in control.
  2. Choose File→Save As→Browse. The Save As dialog box opens.
  3. Select Excel Template from the Save As Type field.
  4. Navigate to another hidden folder:
    1. Type %appdata% into the File Name field, then press Enter.
    2. Scroll down and double-click on Microsoft.
    3. Scroll down and double-click on Excel.
    4. d) Double-click on XLSTART. If XLSTART doesn’t exist, create a new folder named XLSTART and then double-click it (because sometimes Excel likes to test your perseverance).
  5. Type Book in the File Name field, then click Save.

    If you name the file anything other than Book (like Book1, My Awesome Template, or Y’all Watch This), Excel still opens it automatically when you launch the program (assuming you saved the workbook in the XLSTART folder), but all new workbooks ignore your changes and stick with the factory settings like an overprotective parent.

  6. Choose File→Close or press Ctrl+W or Ctrl+F4 to close the template workbook.
  7. Choose File→Options→General, clear the Show the Start Screen When This Application Starts checkbox, and then click OK.

Going forward, when you launch Excel, a blank workbook that has all your customizations appears automatically. Unlike macOS users, you now get to have your cake and eat it too:

  • Creating standard workbooks: Choose File →New Blank Workbook or File →New →Blank Workbook to start with a fresh slate.
  • Initiating customized workbooks: Press Ctrl+N to summon your custom masterpiece, no magic wand required.

If you change your mind about the template, use the File Explorer to navigate to %appdata%\Microsoft\Excel\XLSTART then delete the Book.xltx workbook.

Customizing the default workbook for macOS

Here’s how to tailor your default workbook to your liking:

  1. Create a new workbook and tweak it to perfection. Adjust fonts, styles, margins, custom headers and footers, or even hide an Easter egg in cell Z100 — go wild.
  2. Choose File → Save As Template. The Save As dialog box opens.
  3. Erase the Save As field, then navigate through the following folder structure:
    1. Type ~ to display the Go To dialog box.
    2. Type /Lib and then press Tab to navigate to the Library folder.
    3. Type Group and then press Tab to navigate to the Group Containers folder.
    4. Type UBF and then press Tab to navigate to the UBF8T346G9 folder.
    5. Type O and then press Tab to add the .Office extension.
    6. Type /User and then press Tab to navigate to the User Content.localized folder.
    7. Type Start and then press Tab to navigate to the Startup.Localized folder (no slash this time).
    8. Type E and then press Tab to navigate to the Excel folder (no slash here either).
    9. Press Enter to return to the Save As dialog box. Give yourself a high five — you made it!
  4. Type Book in the Save As field, and then click Save.

    Book is the only valid name — Book1, My Template, or anything else won’t work.

  5. Choose File→Close or press Cmd+W to close the workbook. Going forward, when you launch Excel or create a new workbook using File→New or Cmd+N, your custom template becomes the new default.

To go back to the original blank workbook, delete the custom template from: ~/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized /Excel.

Want more helpful, time-saving Excel tips? Check out the Microsoft 365 Excel For Dummies Cheat Sheet.

About This Article

This article is from the book: 

Microsoft 365 Excel For Dummies

About the book author:

David H. Ringstrom, CPA, is president of Accounting Advisors, Inc., a spreadsheet and database consulting firm. He is a sought-after speaker on the subject of Microsoft Excel, QuickBooks, and QuickBooks Online. He is also the co-author of the previous edition of QuickBooks Online For Dummies and two previous accounting reference books.

This article can be found in the category: 

Excel