Zoom to 125 Percent – Excel 2011 Default Workbook

I want to have Excel 2011 (for Mac) open to a blank worksheet that is zoomed to 125%, and it has taken a very long time to figure this out. It involves saving a template to a buried location, and then manipulating the file in Finder to remove the extension. Simple, once you know the trick.

The buried location is:

Macintosh HD:Users:

username

:Library:Application Support:Microsoft:Office:User Templates:My Templates:

Or you can check to see where the Excel startup files are located on your computer. Just to go Excel > Preferences then select General and click the Select… box for At startup, open all files in. This is where a modified template file should go.

Excel 2011 Preferences General

Change the Default Workbook in Excel 2011

  • Open Excel 2011
  • Choose View > Zoom, then select 125% and click OK
  • Choose File > Save As
  • In the Save As: box type in Workbook
  • Click the Format: drop-down button and choose Excel Template (.xltx)
  • Make sure you save it to the startup location (discussed above)
  • Click Save

Excel 2011 Save As Template

Note: Keep in mind that I have set Excel preferences to have my workbooks open with just one worksheet. You may have to set the zoom for each sheet if you have several worksheets in the Workbook Template file.

The Default Workbook Secret

The trick now is to open Finder and navigate to the startup folder, select the Workbook.xltx file you just created, and delete the .xltx portion of the file name. I did this by selecting the file, clicking once to highlight the name, then selected the .xltx extension and hit the delete key.

Excel will warn you that this may be dangerous but stay the course and click Remove.

Remove .XLTX Extension

Now open Excel 2011 and you’ll be looking at a worksheet that is zoomed to 125% in the default workbook.

One More Thing – New Worksheets

We’re not done just yet because we need to do the same thing once again, but this time save the file with the name Sheet, so that when you add a new sheet to a workbook the zoom will automatically be set at 125%.

Summary

The default files Excel uses for a new Workbook and a new Sheet are template files, with the extension removed, and located in the startup files location.

A common fallacy is that Excel for Mac uses the Normal Template. This belief took me in the wrong direction for quite some time. Hopefully, this post will set the record straight.

Obviously, you can set more preferences than having a 125% zoom view of the worksheet. All you have to do is open these template files, make the changes and save (overwrite) them as template files. Then make sure the file extension is removed.

35 thoughts on “Zoom to 125 Percent – Excel 2011 Default Workbook”

  1. Thanks for the tip!

    It seems that this works for the first worksheet. But Excel (I am using Office 2011) opens subsequent sheets with the numbers Worksheet1, Worksheet2, etc and they go back to the default 100%. Any thoughts? Thanks in advance.

    • I’ve changed my Excel Preferences > General to have only 1 sheet in a workbook. Then if I need another sheet, it will appear with at 125% zoom if I’ve added Sheet template file.

      Perhaps when you save your Template file you can try and make sure all of your sheets have 125% zoom. Otherwise, take my advice and go with a one sheet default.

      • Super, thanks!

        Any thoughts on what to do with Excel opening new books not just as Worksheet but as Worksheet1, Worksheet2, etc? It seems that the template only works for the first worksheet opened per session.

  2. Hmm – didn’t work. I followed the instructions to a T, and am now looking at a /{my username}/Library/Application Support/Microsoft/Office/User Templates/My Templates folder containing two items: “Workbook” and “Sheet” (with .xltx extensions removed). In fact, if I open either of these files, I’m presented with a new Excel sheet with a 125% zoom level. However, when I start Excel or ask to create a new worksheet, I’m presented with a sheet having the default 100% zoom level.

    • The names for workbooks and sheets are localized, and so are the filenames you have to use for templates that set the defaults, so “Workbook” and “Sheet” only work if your Office is in English. You have to use whichever name is the default for new workbooks or sheets in your language.

  3. Choosing the folder
    /Users/{user}/Library/Application Support/Microsoft/Office/{User Templates}/{My Templates}
    as the folder where files are opened at startup might not be ideal, since this is also the default folder for Normal template in Word, which cannot be opened by Excel.

    There exists a specific Startup-folder which might be a more suitable choice. The Startup folder is the following on my setup (where {Startup} is a language dependent variable):
    /Applications/Microsoft Office 2011/Office/{Startup}/Excel/

    • The Startup folder will only work when you open Excel. Not good when you create a new workbook or you create a new worksheet.

      These template files won’t affect the Normal Word template.

      • On my computer placing the Workbook and Sheet template files in the Startup folder works fine for new workbooks and sheets also when Excel already is open, when this folder is chosen in “Preferences > General > At startup, open all files in”.

        If I choose the My Templates folder there, Excel issues an error message that says that Normal.dotm cannot be opened because it has the wrong format. It doesn’t affect the file, but it is an error message that can be avoided if the “At startup, open all files in” folder only contains Excel files.

  4. Thanks for this!

    Any ideas if this will work for changing the defaults in Charts? I tried doing the same thing as you suggested for Sheet and saving it as Chart and it doesn’t work. I want to make new Charts appear as a new tab rather than an object in the sheet. Also getting rid of the default shading would be good.

    Thanks

    • Thanks for bringing this up, it completely slipped my mind. I haven’t investigated the default Chart and how to change it in Excel. If I get some time during the Christmas break I’ll take a look.

  5. this worked great to reset my preferred default workbook. How do I limit the number of options in the Style window. It list about three dozen and I only use about 6!

  6. Very Useful post. I wanted to change page setup to automatically include header & footer with Sheet name, date, time, page no etc. Worked a treat using the Startup folder. Many thanks.

  7. I had trouble with this for a while, but realized, you have to get rid of the extension by right clicking on the file, clicking “get info”, opening the dropdown “Name & Extension”, then editing “Workbook.xltx” to “Workbook”.
    (for some reason, the extension is not shown in just the finder window, so it is tempting to skip the deleting step until you realize it’s still secretly hidden in there). Also quitting excel completely every now and then between steps felt like a good safety move for me.

    P.S. THANK YOU! I have been looking for this for the past 6 months!!
    I also added the comma with no decimals to all cells. I have no idea why anyone would want to see a really long # with no commas when $, decimals, %, etc formatting can always be added later.

    • You can see the file extension in Finder if you do the following: Open Finder, chooser Finder > Preferences and select the Advanced tab (gear icon) and check the box to Show All Filename Extensions.

      I should probably add the comma with no decimals too, as this is something I use all the time and Excel does this so poorly.

  8. Thanks, this works nicely, but it is absolutely critical that the file name extension is really removed, not just hidden as in “Hide extension”. For best results do therefore check with CMD-I what the filename really is !!

    • @Mike, Excel can tell if the file has an extension, regardless if it’s shown or not. I got this trick some time ago sometime ago, and I don’t remember all the details, but I’m pretty sure the file extension has to be delete before Excel will use it as the default workbook/sheet. You can always try it without removing the file extension and prove me wrong. If so, let me know and I’ll update my post.

  9. This is why I wrote this, to emphasise the ‘hidden extension’ problem. Removing the extension just in the Finder did only hide it (on my Mac, SnowLeo). Only when I went via CMD-I the truth became visible and I could really remove it.

    • @Mike, I’m not having the same problem with removing the file extension that you are experiencing. When I wrote the post I was using Snow Leopard and now I’m using Lion. Regardless, when I remove an Excel file extension in Finder I get a warning that asks me, “Are you sure you want to remove the extension “.xlsx”? If I select Remove the extension is removed and that is confirmed by using CMD+I and looking in the file inspector window.

      I just verified this in Lion and am pretty sure the same thing happened in Snow Leopard when I created the original files.

      But you bring up a great way to remove a file extension, and that is to do so in the file inspector – using the keyboard shortcut CMD+I to launch it when having a file select in Finder. This is and easier way to remove the file extension. I tried this out and it still gives me the warning about removing the file extension, did you get a similar warning?

  10. Thanks so much for this. I did get an error message every time I opened Excel that said the normal.dot file is not valid and when I clicked OK, it opened correctly with the Workbook I had saved without the extension, as you recommended. So I went to the Finder and created an Excel Template folder in My Templates, put the Workbook template I had created in there, and then re-opened Excel and directed it to the new folder. Worked like a charm!

  11. Similar to David Stein’s post – this does not work for me.

    I also followed the directions to a “T”. One thing – when I remove the .xltx extension, it gives no warning of impending doom.

    If I double-click on the “workbook” file – it opens a new workbook with the desired new defaults. But if I just open Excel into a new workbook, it’s the same old 100% original default.

    What am I missing???

  12. One more thought and a question.

    So I did same the workbook into the Startup folder. That seems to work.

    However – when I open an existing excel file (like if somebody emails me their xls file) – it still opens in ridiculously small 100% view (and the window is also tiny).

    What a pain to maximize or expand the window AND change the view to 150%.

    Any remedy?

    I’m loving the Mac – but I recall being able to set a default view for new AND exiting spreadsheets with Office 2010 on Windows. Bummer.

    • Existing file will open at 100% until you change it to 125% or 150% and save the file. Then they open to the magnified screen. This seems to be Excel’s default.

      The column width is based upon the default font size. So maybe choosing a default font, in the default workbook, that is larger may fix this problem. I haven’t tried doing this as I’m not sure about dealing with another font.

  13. Thank you so much – I have been searching for the solution. The default alignment of “bottom” makes me insane and now it’s finally fixed! Why the heck does MS make it this crazily difficult to set defaults? How about a nice little checkbox on the Style format “save as default”. Geesh.

Comments are closed.