Where is Control+Home for Excel on a Mac

by Gregory on April 28, 2013

I wrote a post stating that I could not find the Windows Ctrl+Home keyboard shortcut equivalent on a Mac. Well I’m here to tell you that I found the keyboard shortcut combination that does the same thing on a Mac. The Excel Gods are with me. Hallelujah!

Finding My Way Home

The key to finding this elusive keyboard shortcut is in the Keyboard Viewer. On your Mac select the Apple icon () and click System Preferences… Select Keyboard, and then make sure to click the Keyboard tab. Check the box for: Show Keyboard & Character Viewers in menu bar.

Keyboard Preferences Mac

Click the Keyboard Viewer icon Keyboard Viewer Iconin your Mac menu bar and a nice replica of your Mac keyboard will appear.

Keyboard Viewer Mac

You will notice that this viewer reflects the keys you tap on your keyboard. The screen shot above shows the Command and Shift keys are depressed. The Keyboard Viewer will also show different symbols when you press various keys, like fn, Control, Option, Command, etc.

This is where I noticed something interesting. While depressing the fn key, the left arrow button changes its angle to point up about 30 degrees. Knowing that allowed me to do a little testing in Microsoft Excel 2011 for the Mac.

Control+Home for Excel Mac

What I found is that the Windows Control+Home keyboard combination can be replicated on a Mac by either of the following keyboard shortcut combinations.

    fn+Command+Left Arrow

    fn+Control+Left Arrow

Another mystery solved.

Keyboard Shortcuts

Another aid in finding keyboard shortcuts comes in the form of an overlay for your Mac keyboard. The kind folks over at Excel Skin™ gave me an overlay that slips over the Mac keyboard and shows, via color coding, a wide array of shortcuts that work in Excel for Mac. Here is why you might want an excel skin.

{ Comments on this entry are closed }

Sometimes you need to extract the integer portion of a number. Sometimes the fractional part. Sometimes both. Excel makes it easy to get the integer and somewhat harder to get the fraction. If you just want the answer, skip to the technical details.

The Integer Part

What could be easier than the INT function? I mean INT almost screams INTEGER. So the name is intuitive. You almost “know” what it’s going to do, even if you haven’t used it before.

With only one argument, it’s execution is even simpler. Just feed it a number and out pops the integer portion.

Below you can see I have the number 14.125 in cell D1 and the formula =INT(D1) returns the integer 14 in cell E1.
INT Function

Date/Time Tip: A practical application for the INT function is to extract the date value from a date/time number. An example is a date/time value like 2/14/2013 9:04 AM in cell A2 and you only want the date. Using the formula =INT(A2) will strip out the time and leave the date 2/14/2013.

The Fractional Part

Here is where I would like some simplification. I mean, if we have the INT function for extracting an integer, you would think that there would be a FRAC function, or a simple name like that, to extract the fractional part.

If there was a FRAC function, you could also imagine that it would have only one argument. Just feed it a number and out pops the fraction. Simple. Elegant. Intuitive. No thought required. But, I digress.

To extract the fractional part of a number we can use the MOD function, which has not one, but two arguments. The reason it has two arguments is because it does more than extracting the fractional part. Forget about that.

Here is what you need to know about the MOD function and how to extract the fractional part of a number. The second argument of the MOD function is 1. Remember that. To reiterate, 1 is the second argument.

The first argument of the MOD function is the number. Shown below, you can see the number 14.125 in cell D1. The formula =MOD(D1,1) returns the fractional part 0.125 in cell E1.
MOD Function

Date/Time Tip: A handy way to extract a time value from a date/time number is to use the MOD function. Per our previous example, the date/time number 2/14/2013 9:04 AM in cell A2, the formula =MOD(A2,1) will return 9:04 AM.

Technical Details

Assume you have a number in cell A1.

To extract the integer value, use the formula:
=INT(A1)

To extract the fractional value, use the formula:
=MOD(A1,1)

{ Comments on this entry are closed }

Excel without a Mac

by Gregory on February 8, 2013

I sold my 15″ MacBook Pro yesterday and am waiting on a 21.5″ iMac to arrive in 2-3 weeks. So no more adventures with Excel 2011 for a bit.

Time to dust off the Dell desktop and reacquaint myself with the “real” Excel. That of the Windows variety. I’m putting Excel 2003 in my rear view mirror (finally) and will focus on Excel 2007 and 2010.

I would love to get Excel 2013 and test it out, but the Dell will need to be replaced this year so I’ll wait until I have a new PC. I would like it to be a PC / Tablet combo machine with Windows 8, but am going to wait until the dust settles on the new operating system.

When upgrading to a new version of Windows I usually make it a practice to wait until after “service pack 1″ is released before making the switch.

So Excel without a Mac is a survivable condition when you have a Windows PC for a backup. The converse is not true. Having a Mac without Excel would be the worst form of torture. Living with Numbers is not, in my opinion, a survivable condition. Excel on a Mac is a challenge, but nirvana when compared to Numbers.

Which leads me to wonder when the next version of Excel for the Mac will come out. I have high expectations: Power Pivot, Name Manager, Evaluate Formula dialog box, Status Bar Functions that aren’t circa Excel 2003, and elimination of the Menu bar.

However, my expectations might just be the result of some wishful thinking rooted in a dream state.

20130208-185246.jpg

{ Comments on this entry are closed }

Extract Time with the MOD Function in Excel

by Gregory on November 1, 2012

I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really.

The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) divided by two (2) equals two (2), with one (1) left over.

All numbers are evenly divisible by one (1) so the MOD function returns any fractional part when the second argument is one (1).

In the screen shot below, cell C2 has the Date-Time number: 10/8/12 6:28 PM. It has an underlying serial number: 41990.7698, which you can see in cell C3 with General formatting.

MOD Function Time Extract

Using the formula =MOD(C2,1) you can see the result in cells D2 and D3, with different cell formatting. Extracting the Time value from a Date-Time value is simple with the MOD function.

{ Comments on this entry are closed }

Extract Time from a Date-Time Number in Excel

October 9, 2012 Intermediate

I have a worksheet that tracks start and stop times for different events throughout the day, all during the week. Sometimes I have to pull out the Time of Day, irrespective of the Date, with the TIME function. The TIME function has three arguments: Hour, Minute, Second. I could use =TIME(11,30,0) in a cell to […]

Get the full story …

Extract a Date from a Date-Time Number in Excel

September 15, 2012 Intermediate

I recently saw a spreadsheet with the following function =LEFT(A2,8) where cell A2 was equal to 08/06/12 12:23 PM. The user was trying to extract the Date from the Date-Time value. The problem for me was that the LEFT function returns a “text” value. Excel is good at recognizing text values that look like dates, […]

Get the full story …

Add Macro Button to the Toolbar in Excel 2011

July 7, 2012 Intermediate

You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps. […]

Get the full story …

Control + Home in Excel for Mac

June 30, 2012 Features

One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years. I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go […]

Get the full story …

Blog Comments and Inspiration

June 24, 2012 General Update

I’d like to thank everyone who has left a comment on this blog within the past year. It’s been a privilege to interact with some of you. When I started this blog a couple of years ago, I was partially employed. Consequently I had lots of time to spend learning how to write blog posts […]

Get the full story …

Break Even Calculation with an Unlocked iPhone and International Rates

April 23, 2012 Formulas

I just upgraded my wife to a new iPhone 4S and since she’s finished with her contract, AT&T will now unlock her old iPhone 4. Having an unlocked phone is advantageous when traveling overseas because you can pick up a Sim card with a phone plan and save some money. The question I want to […]

Related Posts Plugin for WordPress, Blogger...
Get the full story …