Excel 2011 for Mac – Installation, Preferences, and VBA

by Gregory on November 2, 2010

Office 2011 Icons

After reading all the great things about Microsoft Excel for Mac 2011 that were written before its release, I decided to purchase a copy. My primary goal was to have an Excel version on my MacBook Pro that allowed VBA macros, however the full Office software suite seemed to be a really great value.

I paid $174.99 USD for the Microsoft Office for Mac Home and Business 2011 – 1 Pack edition on amazon.com after pre-ordering and receiving a $25 discount from their price assurance guarantee.

Excel 2011 Software Installation

What you get with the Office 2011 Home and Business Edition:

  • Word 2011
  • PowerPoint 2011
  • Excel 2011
  • Outlook 2011
  • Microsoft Office Web App support
  • Messenger for Mac 8
  • Remote Desktop for Mac 2 (drive your Windows-based PC from your Mac)
  • Technical support 1 year

The installation took about 15 minutes. The program loaded, I entered the product key, activated and registered the software, then launched Excel, and got a welcome screen telling me all about the new features.

Whats New Excel 2011

And then I had to face the Excel Workbook Gallery, which is the default screen that appears when you open Excel 2011. Nice for all of 15 seconds.

At the bottom of this screen there’s a box you can check: Don’t show this when opening Excel, but I ignored it and went straightaway to a blank Excel workbook.

Excel 2011 Preferences

The first thing I wanted to check out was the Options, oops pardon my Windows speak, I mean Preferences, by using the menu selection Excel → Preferences or the keyboard shortcut Cmd+apostrophe ( ⌘ , ).

Excel 2011 Preferences

Within these preferences is standard stuff you’d find in a Windows version of Excel, but I want to review some of the settings for General, Edit, AutoComplete, Compatability, and Ribbon.

General Preference

In the General dialog box I unchecked the box beside: Open Excel Workbook Gallery when application opens so that Excel opens to a blank worksheet.

General Settings Change

Edit Preference

On the Edit dialog box, a new option for Excel 2011 is Automatically convert date system. As you can see in the Description box below, this option converts the date system of the source data to match the date system of the target workbook.

I’m not exactly sure how this preference option works and will be looking into it, but with Excel’s two different date systems (1900 & 1904) this setting becomes more important now that Excel 2011 and Excel 2010 can share workbooks on the web in Windows SkyDrive.

Excel 2011 Edit Dialog Box

AutoComplete Preference

In the AutoComplete dialog box the setting for Show the menu only after I’ve typed x letters (x set at 1, range 0-9) seems to be one that I’ll revisit soon. I’m not sure I want AutoComplete popping up after typing only 1 letter.

AutoComplete Dialog Box

CompatibilityPreference

On the Compatibility dialog box, under Transition, the Save files in this format: is defaulted to Excel Workbook (.xlsx) and can be set to a number of different things.

Excel 2011 Compatibility Dialog Box

Changing the default file format to Excel 97-2004 Workbook (.xls) might be prudent should you share files frequently with people using older versions of Excel.

Ribbon Preference

On the Ribbon dialog box, under Show or hide tabs, I noticed that you can drag them in the order you prefer. I added the Developer tab to the Excel Ribbon by checking the box for Developer.

Ribbon Dialog Box

The VBA Editor – A Quick Look

I had to peek at the VBA editor just to check it out. Going to the Developer tab and clicking Editor opened the VBA Editor, where I added a module and wrote a quick test macro, as you can see below.

Excel 2011 VBA Editor

Not a bad start.

Next I’ll compare the Ribbons of Excel 2011 and 2010.

Related Posts Plugin for WordPress, Blogger...
patricio February 4, 2011 at 2:34 pm

Please review the VBA IDE in xl11! Thanks and great site!!

Gregory February 4, 2011 at 3:11 pm

Good idea, I’ll get to work on that. It’s much different that the Windows version. Stay tuned.

Stephen Falbel August 31, 2011 at 12:14 pm

Have you noticed a problem with auto-complete, in that it does not work at all (at least for me)? I have it turned on, and it does nothing. From what I read, it is also not as elegant as it was in Office 2007 for Windows, in that it forces you to do an extra down-arrow keystroke. But I can’t even get that far.

Gregory August 31, 2011 at 5:07 pm

My auto-complete works, sort of. It’s not like Windows where an auto- complete word is highlighted entirely and you can hit enter to, well, enter the matched word. In Excel 2011 any match shows up in a list below the cursor and I have to hit the down arrow to select the word, then hit enter to enter the word. Too much work and very annoying.

Sometimes auto-complete won’t work if there are blank cells in the column of data, but I’ve not had the problem you’re describing. I just typed in “apple” in cell A1 and “brainiac” in cell A1, then in cell A3 typing a letter a or b gives me the auto-complete functionality.

Taylor September 5, 2011 at 5:01 pm

I have Office for Mac 2011 and I’m trying to make it so that the auto complete menu will be selected with the “tab” key or the “return” key. Currently I have to use the “down arrow key” to select it, then press “return” to go to the next cell and it’s slowing me down.

Anyone know how to change these settings?

Thanks,
Taylor

Gregory September 7, 2011 at 8:16 pm

Sorry for the delay, but I haven’t figured this one out yet. Truly a strange duck.

praktisches-wissen October 30, 2011 at 4:45 pm

I had the same problem, that autocomplete worked, and than no more.
And now it works again. (But unfortunately I can’t tell what I did that made it working.)

What I did, was I just rechecked all the options as above mentioned; worked through all functions keys (if that helped I do not know) – and “magically” the autocomplete refunctioned again.)

BTW: Personally I prefer the way Excel Mac 2011 offers the entries – it’s a better way to offer all possible entries and then being able to select them by arrow down and enter.
Othmar

praktisches-wissen October 30, 2011 at 4:48 pm

of course – after having selected an entry – pressing TAB works as well – and you are right on the next cell :-)

lynda November 21, 2011 at 3:15 pm

Is there a way to take my Excel 2010 PC files & convert them to the MAC 2011 version I have at home?

Gregory November 21, 2011 at 4:13 pm

Any Excel 2010 “PC” file will work with Excel 2011 on a Mac. No conversion necessary.

Heather December 3, 2011 at 1:24 pm

I have Excel 2010 for PC and I have sent a workbook to several people with a mac and Excel 2011. It opens up but does not let them use the drop down menus? The cells with formulas are hidden and protected, but all the other cells are not, and that is including the drop down menus. Can you help with this?

Gregory December 5, 2011 at 5:55 pm

Drop-down menus using data validation should work in each version. If the drop-down menus in Excel 2010 are ActiveX controls, they won’t work on a Mac in Excel 2011. You could change them to Form control menus, which would work in Excel 2011 on a Mac.

I have no idea why the formulas would be protected or hidden.

Without seeing the file or a sample of the issues you are facing its hard to determine the cause of any problems. If you would send me a sample file (using the form on the contact page) I could give you a better answer since I have a Mac and a PC with different versions of Excel.

Zak December 9, 2011 at 7:31 am

Will macros created in Excel 2007 on a Windows PC work in Office for Mac 2011? If so how?!

Gregory December 10, 2011 at 3:22 pm

Macros created in Excel 2007 on a Windows PC may, or may not work in Excel 2011 on a Mac. There are some minor differences in the code that may cause an error, but generally the code should work. The quickest way to find out is to run the macro and find out if there are any errors.

To run a macro in Excel 2011 on a Mac use the keyboard shortcut Fn+Option+F8 (or just Option+F8 if you’ve changed the Fn preference) to bring up the Macro dialog box.

You can also run a macro from the Developer tab on the Ribbon. If you don’t see the Developer tab, choose Excel > Preferences and click Ribbon, then select the check box before Developer and click OK.

Zak December 14, 2011 at 10:43 am

Thanks Gregory. The issue I am running into is that the file is operated by button controls, but when I press them, the nothing happens.

Gregory December 15, 2011 at 9:24 pm

Button controls in Windows are either ActiveX or Form controls. ActiveX controls don’t work in on a Mac so you have to replace them with Form controls.

Ashwin July 24, 2012 at 8:32 pm

Hi Gregory,
Facing same problem, How to replace these activeX buttons with Form controls?

Fred Mertz December 13, 2011 at 10:37 am

I am using Office for Mac 2011. Autocomplete is working on some of my Excel spreadsheets but not all. I have trashed the preferences, the Excel toolbar, re-installed Office for Mac 2011 but I can’t get it to work on some key spreadsheets

Gregory December 19, 2011 at 7:53 pm

This is one of my frustrations with Autocomplete: It only works with a continuous column of cells. It only recognizes cell contents below the last blank row.

Barring that little issue, make sure you check Excel > Preferences > AutoComplete to see if the preferences are correct.

Other than that, I’m out of suggestions.

Jim Hahn January 1, 2012 at 11:15 am

1) I know there is a way to change/add keyboard shortcuts for Excel for Mac 2011.

Is there a way to change/add keyboard shortcuts for the VBA side? There are just too many keys to hold down to do basic debugging. E.g., shift+command+I to “step into” from a breakpoint. I can’t see a way to change shortcuts.

2) Assuming, apparently incorrectly, that I could change VBA shortcuts, I made an exhaustive list of all command+letter shortcuts to see what might be available to use for debugging commands. While doing this, I found that command+J appears twice. Once for “list properties” and once for “bring to front”. How can one shortcut do two different things? Maybe it’s smart and context sensitive.

3) I am hung up on simple debugging, so bear with me. I found that there are special keyboards called ‘chorded keyboards’ which allow one to type all the keys on a standard keyboard with one hand on a keyboard with only a ‘few’ keys. I am not sure why anybody would want that, but that’s beside the point. Here’s my question: Does anyone know of a hardware keyboard with, say, a 4×4 row of fat buttons on it. Each button could be programmed to issue commands like “step into” or “step over” or whatever you wanted. The existing chorded keyboards I can find are weird shaped with buttons in odd places…not too useful for what I want.

Thanks for the great web site.
–jim

Gregory January 1, 2012 at 3:03 pm

@Jim

1) I wouldn’t know how to re-program the VBA shortcut menu.
2) Yes, the COMMAND + J keyboard is context sensitive. The second one would only work when an object is selected and you have more than one object.
3) I didn’t know about these “special keyboards”

If anyone can help answer Jim’s question, please comment.

Jim Hahn January 9, 2012 at 7:32 pm

Excel for Mac 2011 on Lion (and my brand new MacBook Pro) has a way WITHIN Excel to reprogram shortcuts. I was able to reprogram:

step into
step over
step out

which are important.

I was not able to reprogram Continue, a rather important debugging command. Although I used Excel’s functionality to reprogram it to COMMAND+K, it stubbornly still forces me to hit “fn+f5″. On the menu itself, it says COM+K. Annoying.
–jim

Gregory January 9, 2012 at 7:40 pm

Wow, that’s awesome! Any hints as to how you were able to do this?

Jim Hahn January 9, 2012 at 7:54 pm

My apologies: The functionality is at the Apple OS level, not Excel.

See: http://docs.info.apple.com/article.html?path=Mac/10.6/en/8564.html

Under Apple preferences – Keyboard

Just can’t change the Continue button….! So far….

Remember, this is in Lion. I have 10.7.2.

JB January 9, 2012 at 6:52 pm

I recently installed msoffice 2011 for Mac on my Mac which is running Lion OS.

On opening excel documents, I cannot see any entries in the cell. When I look at the ribbon line, I can see the entry, but it does not appear in the workbook. This is happening on all the files I try to open.

Any ideas?

Thanks

Gregory January 9, 2012 at 6:58 pm

Is the workbook protected?

JB January 9, 2012 at 7:05 pm

No.
I have been fiddling with the font and this appears to be the key.

If I select all on the worksheet and change the font to something like Arial Black, then everything shows up. SO…… I’m guessing that the default font is somehow an invisible ink or something that has to be swapped out for a normal view font. I”m trying to change this on the preferences.

Seen anything like that?

Gregory January 9, 2012 at 7:12 pm

Actually, yes I did see this one time, but it was only with one file. Good thinking on finding the font problems. Sometimes Excel has some weird stuff going that’s inexplicable.

Erin Conway February 2, 2012 at 8:30 pm

Hi there. I have a 15 sheet workbook in excel. However, for some reason only ONE sheet opens and there are no tabs for the other sheets within the workbook showing when I open the workbook on my mac pro. If I open the workbook on a pc all pages are showing. There was a time when all sheets were available when opened on my mac, but not any more.
Has anyone else had this issue? How do I get the WHOLE workbook to populate for me on my mac?
Erin

Gregory February 4, 2012 at 8:40 pm

In Excel on your Mac, choose Excel > Preferences and click View, then check to see if there is a check beside the box for Show Sheet Tabs. This box should be checked.

Pryce February 8, 2012 at 9:46 am

I have a MacBook Pro with Lion 10.7.3. I am also running Fusion 4.1.1. I have several spreadsheets created on the PC (Windows) Excel (older versions) that are workbooks with several tabs and Macros that have been set up to run various programs. Was considering buying 2011 Excel for Mac, but was worried about running the older excel programs with Macros on the Mac side. Was going to buy the 2010 Home and Student version for PC so I wouldn’t run into problems with the Mac side. I am sharing programs with PC users, so I need to be totally compatible with the PC when running and revising programs. Do you think the 2011 Mac version will be ok for me, or should I buy the PC version to be safe?

Gregory February 11, 2012 at 1:02 pm

I would get the PC version to be safe. Even though Excel 2011 (Mac) allows VBA code, it doesn’t allow any ActiveX Controls. You can use Form Controls in both versions, but you have to KNOW which control you have in the older Excel (windows) file because they look similar on first appearance. A drop-down combo list control, or a button could be either type unless you check the properties.

ActiveX controls can be replaced by Form controls, but this takes effort and sometimes there is a good deal of code in the ActiveX control that has to be moved to a module.

Stephen Falbel February 23, 2012 at 6:19 am

Gregory, I’ve finally figured out the autocomplete mystery. It seems to be tied to the preference to edit directly in cell. If you turn off the edit-in-cell preference (so that when you double-click on a formula it takes you to the referenced cells–which I do often), then autocomplete no longer works. Seems like a bug in the program to me.

Fred Mertz July 28, 2012 at 9:34 am

How do I turn on the edit-in-cell preference? I’m not finding it. Hopefully this does the trick.

This autocomplete bug makes me crazy. Worked fine on Excel 2008 but can’t get it to work in Excel 2011.

Fred Mertz July 28, 2012 at 9:38 am

Sorry, found it. It’s on and autocomplete still will not work. Trashed preferences, re-started. Toggled autocomplete options on and off. Can’t get it to work at all.

It’s Microsoft- figures. No wonder Apple is killing them. Apple actually has people to help. Microsoft will help you for $50. after your 90 second call warranty expires.

How’s that Zune working out for you?

Gregory July 28, 2012 at 9:40 am

Exactly ;)

Ken Coleman March 7, 2012 at 5:18 am

I have Excel Office 2011 files with VBA macros running on Mac notebook OS 10.6.8. When try to use on desktop with Lion OS the files won’t save. I can open them but cannot access the macros or save the file. This happens to any Excel file with a macro.

Gregory March 7, 2012 at 8:30 pm

I’m not exactly sure why this happens when you open a file with a macro. The Excel > Preferences > Security has one item you can check: Warn before opening a file that contains macros. If that is checked you get the option to Enable or Disable the macros when you open the file. If it’s not checked the macros open Enabled.

The only other thing I can think of is to check File > Properties and click the General tab and see if the Read Only Attribute is checked.

cesar March 19, 2012 at 1:38 pm

Greetings,

I’d like to know how to modify or add a new reference library to a Excel workbook with a VBA Project. I tried to do that like in Excel 2010 (Windows) (Tools ->References) but I couldn’t do it because the menu in Excel 2011 Mac is different.

Thanks,

Gregory March 19, 2012 at 2:13 pm

I haven’t added a reference library to Excel 2011, so I’m in the same boat that you are and don’t know the solution. Sorry!

Erica Rose Siegel April 14, 2012 at 3:48 am

Good morning! I too, am having the same problem with not being able to find my tabs and the show sheet tabs is checked. Does anyone have any other suggestions? Thanks so much!

Gregory April 16, 2012 at 9:38 pm

Go into the VBA Editor (Alt+F11) choose View > Project Explorer, and see if there are worksheets under the Microsoft Excel Objects. If so click on one of the sheets, then choose View > Properties Window and check the Visible Property. It will be one of three values:

-1 – XlSheetVisible
0 – xlSheetHidden
2 – xlSheetVeryHidden

You can change this property to the first one and the sheet will appear in the workbook. Any of the sheets that are xlSheetVeryHidden don’t show up in the Hidden sheet dialog box and will appear almost invisible to most users.

Andrew Aitchison June 3, 2013 at 12:42 pm

I have Office 2011 for Mac (word, excel and PowerPoint) downloaded and installed from the web.

Where are the vba help files. It’s driving me nuts not having them.

Thanks

Gregory June 3, 2013 at 9:20 pm

If you open the VBA Object Browser and hit the F1 key for Help, a pop-up box for VBA documentation gives the following link.

http://msdn.microsoft.com/en-us/office/ff688774.aspx

Apparently there is minimal documentation in the Excel program.

Patrick June 16, 2013 at 9:18 am

need help with macros they run great then they dissappear re appear stop working and now give me 1004 error messages about refresh backrounddata excel cannot find the file etc.
at this point Will pay for help
need excel for mac to prompt user to find a csv file open with certain presets especially certain columns formatted as text so we keep leading zeros copy the data to existing or new spreadsheet and be able to do that action in any new work book or template we use.
HAd it working now its stopped. so i think one more step is to never link bak to that data file once i paste it in excel i want the dat to stay static unit i chang sit.

Gregory June 27, 2013 at 7:53 pm

I don’t have the time to hire out for this type of work. You can find someone to bid on a job at freelancers.com.

Gregory January 9, 2012 at 8:30 pm

Good stuff. Thanks for sharing.

Gregory July 25, 2012 at 6:49 pm

Any code inside an ActiveX control will have to be put into a subroutine in a VBA module. Replace the ActiveX control with a Form control. They are found on the Developer tab. Next you link a macro to the Form control and away you go.

Comments on this entry are closed.

{ 2 trackbacks }

Previous post:

Next post: