I bought a MacBook Pro and installed Windows 7 with Boot Camp Assistant so I could access the “normal” Excel. This was important to me at the time because I was doing Excel development work in versions 2003, 2007 and 2010 on a Windows PC.

I love my MacBook Pro, which I consider my computer of the future. It’s great for dealing with Photos, Movies, Music, and all of my iOS devices.

I purchased the Mac versions of Excel (2008 and 2011) but they seem like a mixture of the three Excel versions mentioned above, and can be very frustrating at times. There are a few things missing in the latest Excel Mac version, like the Name Manager and the Evaluate Formula dialog box, to name just two.

Despite these shortcomings, I’m seriously thinking about selling my desktop PC and going exclusively with the Mac. That’s the crossroads I’m at right now. Letting go of the PC, but keeping the Windows versions of Excel.

My Hangups

BootCamp allows me to run Windows, and the Excel versions I need, but is very restrictive in the sense that I have to shut down my Mac, then restart with BootCamp to run Windows 7. This is a real pain in the you-know-what.

There are other problems with running Windows on a Mac. For instance, I haven’t been able to get my Apple Magic Mouse to work with Windows 7. (Go figure.) And using Dropbox was the best way to get Excel files between Windows 7 and Mac OS X.

My Saving Grace

All of those issues were solved when I purchased the Parallels Desktop software for Mac. Now I can switch to Windows without having to shut down Mac OS X Lion. That’s just plain awesome!

I’m still getting used to how Parallels works, but this software is easily worth the purchase price. And my Apple Magic Mouse, just works.

Parallels Desktop on my MacBook Pro

Here’s a screen shot of the Parallels Desktop open on my MacBook Pro. You can see that I have Microsoft 2010 (Windows) open, but I want you to notice that I also have the Finder dialog box open.

I dragged the Card Size Lookup.xlsx file from Finder onto the Excel 2010 (Windows) program and it opened. I then made a change to the spreadsheet and saved it. The file was saved back on my Mac from whence it came. This is the miracle of modern technology.

I still have lots of testing to do but it looks like the Parallels Desktop has made my life much, much easier. I know now which way to turn at the crossroads.

In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left.

This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select the very last row in the same column, then shoot up (Ctrl + Up Arrow) to find the last data cell.

Once you know the where the column heading and last data cell is in the current column, the range can then be selected.

The following macro will select the column of data if you start with the active cell at the column heading.

Over the past two days there has been talk of Microsoft coming out with a version of Office for the iPad in 2012. I for one would happily purchase the Excel part of this package, assuming the price point is similar to that of Apple’s Numbers app at $9.99 USD.

If, like me, you started with Excel and then tried Numbers. Well, it’s unique. And not Excel.

I have the Documents To Go Premium – Office Suite universal app for the iPad and iPhone that works with Excel files, but this Premium version is required to support file sharing and costs $16.99 USD. It works okay and I use it when necessary. But it’s not Excel.

So I have high hopes for an Excel app on the iPad that does lots of wonderful things.

We can always dream.

I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart.

Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations during these months isn’t a bad idea, so I’m going to look at pay periods for the next three years.

Add a Column of Dates

I’ll enter the first pay period, then create a formula that adds 14 days and copy it down to get my date range.

Since a PivotTable will “see” the underlying serial date, I’ll need to add another column for Month and give it a “Month-Year” format so the PivotTable will group similar Months together. For this I’ll use the TEXT formula. The “Pay Period” Date is used for the first value argument, and then “mmm-yy” for the format_text argument.

So the formula in cell B3 =TEXT(A3,”mmm-yy”)

As you can see in the screen shot, the months Dec-11 and Jun-12 have three pay periods. A PivotTable will quickly summarize more than one year and show the number of times a pay period happens each month.

Add a PivotTable

The steps to create a PivotTable in Excel 2011 are as such.

• Select a cell inside the data range
• Click the Data tab on the Ribbon
• Click the PivotTable drop-down arrow and select Create Manual PivotTable…

• On the PivotTable dialog box, click OK

You’ll get a new worksheet that shows an empty PivotTable Layout. There’s an introductory PivotTable popup box that has a link to Learn more about PivotTables, which brings up the Help system topic About PivotTables. Click the x to dismiss this help box.

The PivotTable Builder box is also shown. This object looks quite a bit different from the traditional Windows counterpart. My first reaction was that it looks funky. Nevertheless, it’s the functionality that counts.

Arrange the PivotTable Layout

Click and drag Month from the Field name area to the Row Labels area. Then click Month again and drag it to the Values area. (Yes that’s right, you’re dragging Month twice.)

In the Values area you should see Count of… and to see the rest, just click the i to bring up the field name list.

Sort the PivotTable

Click inside the Data area (like cell B5) of the PivotTable and then select Descending from the Sort icon drop-down list on the Toolbar.

The top of the list shows months with 3 pay periods. Just what I was looking for.

You’ll notice the descending sort doesn’t leave the Row Labels in ascending order. (Nov-12 doesn’t follow Jun-12, etc.)

A Better Formula

You can change the Month formula to =TEXT(A2,”yyyy-mm”) and the Row Labels will show up in year-month format in ascending order.

While this took some time to explain, the reality is when I do this it takes about two minutes. And the bulk of the time is generating the dates and adding the formula.

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column of data.

The situation is depicted below. Cell C2 is active, and has the formula =B2+A2. I want to copy it down to the rest of the column in this data range. However, cells B6 and B11 are empty, along with countless others below the visible table range. (Pretend this data table is huge.)

Here is some VBA code that will Fill Down the formula.

The key is setting the formula range (rngFormula). I take the entire region of data (rngData) and offset by 1 row because I don’t want the header row, then I resize the rows in the range by subtracting 1 from the total number of rows because I now need one less row in the range.

Next I offset the entire range by the row number of the active cell, but have to subtract one column because I offset from column 1, not column 0. And finally I resize the data range to 1 column, which gives me the single-column range I want with the formula in the top row.

The routine does no error checking and is restricted to using the active cell that has a formula. But it does the trick given those limitations.

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.

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

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.

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.

I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.

So here’s my list of things I do to “normalize” Excel 2003.

Full Menu’s

My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users know what menu commands are available if they’re hidden?”

I like to use “full menus” so you see ALL menu commands each time each time you click a menu. This is a standardized approach; you see the same thing each time you click a menu.

Here’s how it’s done:

Right click the toolbar
Click Customize…
Click the Options tab on the Customize dialog box
Check Always show full menus
Click Close

General Options

I have only two recommendations here: maximizing the recently used file list and minimizing the number of worksheets in a new file.

Recently used file list (9)

I like to set the recently used file list to the maximum number. In Excel 2003 that number is nine. This saves you time when searching for a recently used file.

Here’s how it’s done:

Choose Tools > Options then click the General tab on the Options dialog box. Change the Recently used file list to 9 and make sure there’s a check in the check-box.

Sheets in new workbook (1)

When creating a new spreadsheet file, how many sheets do you actually use? How many times have you looked over a spreadsheet file from someone else and clicked on those bank sheets to see if they contained anything?

Remember the days when a new file had 16 worksheets as the default? Excel 2003 has just three. That’s two to many. I like to set the number of worksheets in a new file to one. If I need another, they’re easily created.

Here’s how it’s done:

Choose Tools > Options then click the General tab on the Options dialog box. Change the Sheets in new workbookto 1.

Shortcuts on the Toolbar

These custom toolbar buttons are necessary when using Excel 2003: Paste Values, Freeze Panes, Current Region, Auto Filter, and Pivot Table. You can easily customize the toolbar to add these and more.

For each addition to the toolbar you’ll need to access the Customize dialog box. The long way is to choose View > Toolbars > Customize… or the short way is to right-click a toolbar and select Customize… from the pop-up menu.

Once the Customize dialog box is open, select the Commands tab. Now your ready. Here’s my favorite custom toolbar commands.

Paste Values

The very first custom toolbar command icon I put up. Indispensable. Well, almost.

Here’s how it’s done:

In the Categories pane select Edit. In the Commands pane scroll down and find Paste Values. Click and hold the left-mouse button down while dragging the icon to a toolbar.

Freeze Panes

Another indispensable toolbar command button shortcut icon for anyone who works with lots of data in proper tables.

Here’s how it’s done:

In the Categories pane select Window and Help. Scroll down until you find Freeze Panes. Click with left mouse button, hold and drag to the toolbar.

Select Current Region

Here’s a neat button that allows you to find the shape of a data region by selecting it for you automatically. Better than the keyboard shortcut Ctrl+Shift+asterisk (*). Reminds me of the CurrentRegion property in VBA.

Here’s how it’s done:

In the Categories pane select Edit. In the Commands pane scroll down to the bottom and find Select Current Region. Click and hold with the right-mouse button while dragging to a toolbar of your choice.

AutoFilter

A great button to save you some time when filtering tables. If the active cell is on the header row it simply turns on the filter. However, if you select a cell in the data that contains something you want to filter, clicking the AutoFilter will turn on AND filter that selection for you. A one step process that saves time.

Here’s how it’s done:

Select Data from the Categories pane. Click the AutoFilter command with the left mouse button, hold and drag to a toolbar.

PivotTable

This command serves to initiate a PivotTable from a data table. It also brings up the PivotTable and PiotChart Wizard when you are working on an active PivotTable.

Here’s how it’s done:

Select Data from the Categories pane. Scroll down to the bottom of the Commands pane and find PivotTable and PivotChart Report icon. Left-click, hold and drag to a toolbar.

More Stuff

Find your favorite command not shown on a Toolbar and load it up. Pronto!

The other day I read an article on how to prepare my MacBook Pro for Lion, the new Mac OS X coming out this month, and discovered that any application that requires PowerPC is incompatible with Lion and will not work.

So I followed the instructions to check all the installed programs on my MacBook Pro and was surprised to find that Microsoft Query will be incompatible. This will affect any Excel for Mac versions you might have on your computer — 2004, 2008, 2011.

I would venture a guess that 99.4% of all Excel users have never used Microsoft Query, but I’m one who does and thought this might be significant to a select few. (Notice that Open XML for Excel and Charts uses PowerPC too.)

Microsoft Query allows you to pull data into an Excel spreadsheet from “behind the wall,” so-to-speak, of a database or ERP system. I made a career out of doing this very thing. Getting data that others could not.

In Microsoft 2010 (Windows) the new PowerPivot can replace Microsoft Query but in Excel for Mac there’s no replacement.

Excel for Mac users should beware, Microsoft Query is an incompatible app in the upcoming Lion upgrade for Mac OS X.

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.

Here’s what a US user sees:

From: 6/6/2011 to 6/10/2011

Here is the formula:

=”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)

The Min and Max dates are in column A on a worksheet named ExtractData.

The problem is trying to automatically change the date format in the second argument of the TEXT Function – “m/d/yyyy” – which is a string argument. We can use VBA to accomplish this, but first a refresher on the TEXT function syntax.

The Solution

The Application.International Property solves this problem with the xlMDY argument, which is TRUE if the date order is month-day-year, and FALSE if the date order is day-month-year. This property is put into the Workbook_Open event and modifies a defined name constant that’s used for the second argument of the TEXT function.

Here’s how it’s done.

Create a Defined Name Constant for the Date Format

I created a Defined Name Constant to store a Short Date format, and gave it the name sd_format.

In Excel 2007 and 2010, choose Formulas > Define Name to bring up the New Name dialog box where you type in sd_format in the Name box, and type =”m/d/yyyy” in the Refers to box. Remember the equals sign.

In Excel 2003, 2008, and 2011 choose Insert > Name > Define to bring up the Define Name dialog box. Type sd_format in the Names in workbook box, then type =”m/d/yyyy” in the Refers to box. Be sure to use the equals sign.

Substitute the Named Constant in the TEXT Function

The sd_format defined name can now be substituted for “m/d/yyy” in the second argument of the TEXT function. Here’s the new formula:

=”From: ” & TEXT(MIN(ExtractData!A:A),sd_format) & ” to ” & TEXT(MAX(ExtractData!A:A),sd_format)

Since sd_format is already a text string, enclosed quotes are not needed.

Create a Workbook Open Routine

In the VBA Editor, I created a Workbook_Open subroutine, which looks at the computers international setting for the US, and if TRUE changes the sd_format value to “m/d/yyy”, and otherwise changes it to “d/m/yyyy” for the UK short date format.

How it All Works

Each time the workbook is opened the Workbook_Open routine executes the IF-THEN-ELSE statement, which simply looks to see if the computers region setting is month-day-year, then sets the defined name sd_format to “m/d/yyyy” which is a US format. If the computer’s region setting for the long date format is NOT month-day-year (and presumably day-month-year) then sd_format is set to “d/m/yyyy” for the UK.

Every TEXT function using sd_format for the second argument will then have the proper short date format for that computer.

Note: Obviously if the region settings are changed on the computer while the file is open the file will have to be closed and reopened, but this would most likely be a rare occurrence.

I have no earthy idea why it took me so long to figure out how to delete the contents of a cell or range in Excel for Mac. Ever since I bought my MacBook Pro I’ve known the Delete key on a Mac isn’t really a Delete key.

I mean, since my background is with Windows, I have ingrained knowledge on how the Delete Key works on a computer. Ingrained, I tell you.

But all of that knowledge was shattered upon getting a Mac.

After some consternation, I learned that you have to hold down the fn key and the Delete key at the same time when you want to delete something on a Mac. (Skip to video)

After a while, you get used to the idea that the Delete key on a Mac is really a backspace key and using fn+Delete gives you the real Delete key action.

Of course if you’re a long time Mac user you probably think I’m cuckoo. But hey, this is my blog, think what you like. I’m not the only one who’s decided to start using a Mac after a lifetime of Windows abuse use.

Excel for Mac

Anyway, when using Excel on a Mac — I’ve got versions 2008 and 2011 — you run into a learning curve with all the unusual shortcut keys, function keys (1, 2), and menu and ribbon things that are different from the Windows version of Excel. So there’s a tendency to forget about how the Delete key works on a Mac.

I mean, this is Excel we’re talking about here. Hitting the Delete key is supposed to delete the contents of the active cell, for cryin’ out loud.

In Excel for Mac it does that, but the cursor also gets stuck inside the cell in edit mode. You have to hit the enter key to finish deleting the contents, but this act also moves the active cell to the next cell down.

And if you’ve selected a range and hit the Delete key, the active cell contents are deleted and the cursor is stuck inside the cell in edit mode. You have to hit the Enter key, which does nothing but take you to the next cell. The range contents are still there, with the exception of the active cell.

Not the kind of behavior that occurs in Excel for Windows.

How to Delete Cell and Range Contents in Excel for Mac

The trick is to remember that fn+Delete is really a keyboard shortcut to the Delete key on a Mac. Then the world rights itself and the planets align. Frustration abates. You’ve finally found the magic. Your mojo is back!

Watch this 54 second video to see what I’ve been babbling about for the past 454 words.

YouTube link