Zoom to 125 Percent – Excel 2011 Default Workbook

by Gregory on August 29, 2011

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, 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. If you have several worksheets in the Workbook Template file, you may have to set the zoom for each sheet.

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%.

My 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.

Related Posts Plugin for WordPress, Blogger...
Joseph October 20, 2011 at 7:30 am

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.

Gregory October 20, 2011 at 7:46 pm

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.

Joseph October 21, 2011 at 4:23 am

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.

Gregory October 21, 2011 at 6:36 pm

Again, I’ve got my workbook default set to one worksheet. Adding a new worksheet will give me the 125% view if there is a Sheets template.

David Stein October 27, 2011 at 1:15 pm

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.

Gregory October 27, 2011 at 8:19 pm

If you’ve set the General Preferences to open all files at startup in the same directory, then I’m at a loss as to why it doesn’t work.

Sigve Berge Hofland November 28, 2011 at 6:31 am

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.

Gregory November 28, 2011 at 6:39 am

Good to know, Sigve. I didn’t think about localization when I wrote the post. Thanks for the tip.

Darryl Kuhn November 23, 2011 at 7:38 am

This tutorial was very clear and helpful – worked like a charm. Thanks!!

Gregory November 23, 2011 at 10:57 am

Good to know!

Sigve Berge Hofland November 28, 2011 at 7:24 am

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/

Gregory November 28, 2011 at 10:18 am

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.

Sigve Berge Hofland November 28, 2011 at 10:31 am

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.

Gregory November 28, 2011 at 12:20 pm

Again, good to know. My setup works for me, but, as you’ve stated, not for everyone.

Thanks for the tip.

Barry December 15, 2011 at 2:23 pm

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

Gregory December 19, 2011 at 7:56 pm

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.

Barry Forman December 29, 2011 at 10:54 am

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!

Gregory December 30, 2011 at 4:08 pm

@Barry, I’m not sure how you would go about limiting the number of Styles available in Excel. Sorry!

Pat Murphy January 30, 2012 at 3:52 am

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.

Arthur March 2, 2012 at 12:10 pm

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.

Gregory March 2, 2012 at 6:26 pm

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.

Arthur March 3, 2012 at 3:42 pm

Thank you! Glad my comma thing helps, I really think it should be the standard.
P.S. not sure where to submit this but could you also help with another annoying issue?

http://answers.yahoo.com/question/index?qid=20120303151034AAqvlqt

Gregory March 3, 2012 at 5:47 pm

The Quick Search Bar above the Ribbon is there to stay in Excel 2011. It can’t be removed by customizing the menu or toolbars. Best to embrace it.

You can submit questions to me on the Contact page.

Gregory

Mike Minh April 10, 2012 at 10:32 am

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 !!

Gregory April 10, 2012 at 4:03 pm

@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.

Mike Minh April 11, 2012 at 2:58 am

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.

Gregory April 11, 2012 at 1:56 pm

@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?

drs April 22, 2012 at 8:04 am

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!

Brad September 28, 2012 at 12:15 pm

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???

Brad September 28, 2012 at 12:34 pm

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.

Gregory October 1, 2012 at 9:00 am

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.

Meg November 2, 2012 at 12:17 pm

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.

Gregory November 2, 2012 at 1:53 pm

I don’t know why they make it so hard. I just wish Windows and Mac teams a t Microsoft would compare notes before they published software.

Dan May 7, 2013 at 10:37 am

Oh thank you, this has been bugging me forever. Worked perfectly.

Steve Williams June 9, 2013 at 12:14 pm

Hello Greg,

Thanks for your solution, it works perfectly in my case.

-steve w.

Comments on this entry are closed.

Previous post:

Next post: