Excel for iPad – Paste as Values

by Gregory on June 16, 2014

Being a long standing Excel user and an avid Apple products consumer, I downloaded the new Microsoft Excel iPad app in April 2014 and quickly found out that you can’t do anything of substance until you pay a subscription fee. After looking at the available options I decided to purchase the Office 365 Personal yearly subscription, which is $69 + tax USD. It comes with full access to all Office iPad apps, OneDrive document storage in the cloud, and allows me to install the latest Office applications on a PC or Mac, which I haven’t done as of yet.

Here is Microsoft’s official blurb on what you get:

Office 365 Personal enables you to install the latest full desktop version of Office applications, including Word, Excel, PowerPoint, OneNote and Outlook, on 1 PC or Mac, in addition to 1 Windows tablet or iPad — so you can use all the features offline as well as online. You can also install Office on multiple smartphones.

I’m tempted to use my PC because I get to install Office 2013, which I don’t have, as compared to using Office 2011 that I already have on my iMac. But Microsoft is bringing out Office 2014 this year and I’ve decided to wait and see how they incorporate this version into Office 365 Personal. But I digress.

Excel on the iPad

The Excel app is more intuitive and much better than I imagined it could be. I’ve tried to use Numbers, but frankly it always feels like an alien designed it. I’m glad Microsoft held off until they “got it right” for the iPad.

I created a spreadsheet to document my vacation expenses and used it for a week to make entries. The file is saved automatically to OneDrive in the cloud were I can access it with the Safari browser on my iMac or my wife’s MacBook Air – or any browser for that matter. I can even view or modify the spreadsheet using the Office Mobile app on my iPhone.

The Ribbon menus are straightforward, albeit a little sparse in functionality. Yet the popup and shortcut menus help to increase that functionality, as you shall see in the following example.

I wanted to enter the current date into the aforementioned spreadsheet, but didn’t have a calendar handy so I entered the TODAY() formula. I needed to Copy and Paste as Values, but couldn’t find the Paste as Values functionality on the Ribbon. As it turns out, it was relatively easy to find.

When you enter an equal sign into a cell a list of formulas appears in a popup menu, which is similar to the desktop version of Excel. I tapped on TODAY() and hit the return button on my keyboard to enter the formula into the cell. Easy-peasy.

To copy the formula just tap the cell, a popup menu appears, and then you tap Copy. A moving rectangle on the cell border lets you know there is a copy value in memory.

Pasting the formula as values is not readily apparent, but easily discovered. Just tap the cell, then tap Paste from the popup menu. Notice the paste format icon at the bottom right corner of the cell – very similar to the desktop version on Excel.

Tap the paste format icon to bring up a menu where you tap Paste as Values and BOOM, you’re done. The screen shot sequence below shows what I’m talking about.

Paste as Values Excel iPad App Steps 1-3

Paste as Values Excel iPad App Steps 4-5

If you use Microsoft Excel, especially on a PC, this is the app for you. The look, feel, and functionality will be very familiar. The only problem is deciding if you need full functionality of the app and will purchase a yearly subscription to get it.

Note: I type on a Logitech Ultrathin Keyboard Folio for iPad Air (affiliate link) when I’m using the Excel app on my iPad, which increases the screen real estate immensely by not having an onscreen keyboard. The added benefit is that keyboard shortcuts work similar to the desktop version of Excel 2011 for Mac.

{ 5 comments }

The 24-Hour Rule

by Gregory on June 1, 2014

A long time ago I was doing some reading on how you write and publish blog posts. One suggestion that helps me is the 24-hour rule. And that is to finish your post and then wait 24 hours before reading it one-last-time. After all, a blog post is going to be public, out there on the internet with your name on it, for a very long time.

The problem with this 24-hour rule is that I usually find one or more things that need to be changed. And then I have to wait another 24-hours.

When you are on a deadline, like, for instance, having a self-imposed quota of 2 or 3 blog posts a week, the 24-hour rule is inconvenient. It drives you to compromise on the level of quality.

By that I mean that there is a hierarchy of quality levels starting with spelling and grammar and ending with saying what you intended in the most concise, articulate manner possible. A deadline drives you to ensure the spelling and grammar are correct and then you compromise on just how well you get your point across.

Tech Man

For me the deadline and quality levels force a certain tension, all of which drove me to abandon my blogging of all things Excel. Yet since a deadline and quality levels are self-imposed, they are also controllable.

I’ve decided to stick with the 24-hour rule and forget about the deadline, or any deadline for that matter. Consequently my blogging will continue, at my own pace.

Right now I’m working on a post about the Excel iPad app.

So the 24-hour rule stays and the journey continues.

{ 3 comments }

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

February 8, 2013 General Update

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 […]

Get the full story …

Extract Time with the MOD Function in Excel

November 1, 2012 Formulas

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) […]

Get the full story …

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 […]

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