Excel Semi-Pro Microblog

Keyboard Shortcut - Page Down and Up in Excel for Mac

Wed, 26 Dec 2012 17:53:47 -0800

Fiddling around in Excel on my Mac and realized that Page Down and Page Up are keyboard combinations Fn+Down Arrow and Fn+Up Arrow, respectively.

Master Excel Shortcuts On a Mac.

Sun, 23 Sep 2012 20:50:11 -0700

Master Excel Shortcuts On a Mac.

Office (Excel) on iPad in November [Report]

Fri, 01 Jun 2012 23:24:28 -0700

Office (Excel) on iPad in November [Report]:

More fuel to the fire.

Microsoft Office for iOS ??

Wed, 23 May 2012 15:16:21 -0700

Microsoft Office for iOS ??:

I hope they are taking their time and getting this right instead of being naturally slow to respond. (I can dream, right?)

Two Ways to Use the INDEX Function to Return an Array

Mon, 09 Apr 2012 10:00:40 -0700

Normally, to return column 2 of a named range, you would use the following formula =INDEX(myTable,0,2). The zero means: return all rows (of column 2).

If you want to return row 2 of the named range you would use =INDEX(myTable,2,0). Here the zero means: return all columns (of row 2).

This comes from the Help section of the INDEX function where in Excel 2011 it reads:

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press ⌘+RETURN.

Repeat that Last Command in Excel (Mac)

Sat, 17 Mar 2012 12:21:01 -0700

I’ve finally memorized the fact that on a Mac, the Excel keyboard shortcut to repeat the last command is: COMMAND+6 (and not the beloved F4 like on a Windows machine.)

Microsoft doesn’t want to stop OnLive’s service, just make them pay for it.

Fri, 09 Mar 2012 15:57:00 -0800

Microsoft doesn’t want to stop OnLive’s service, just make them pay for it. :

You would think that Microsoft would have created an Office Suite of apps for the iPad already.

The NETWORKDAYS Formula

Wed, 15 Feb 2012 19:46:01 -0800

To count the number of weekdays, minus holidays for your working days, you can use the NETWORKDAYS function, which has three arguments: Start Date, End Date, and Holidays.

The holidays are usually a range of Holiday dates, but this last argument is optional. If Holidays are not used the formula returns the number of weekdays between the two dates. If there is a Holiday list, then they are subtracted as well.

Here is an example:

The Holiday List is a named range that refers to cells A6:A8 in this example.

The answer in cell C2 is 21 days. Meaning July has 21 working days, which are all the weekdays in July minus the 4th of July, which is a Holiday in the USA.

There is also a NETWORKDAYS.INTL which has four arguments.

The Find and Replace dialog box in Excel 2011. Open with the...

Tue, 14 Feb 2012 19:44:45 -0800

The Find and Replace dialog box in Excel 2011. Open with the keyboard shortcut Command + F. Click the Replace button to replace text from the Find box. You can also choose Within: Sheet or Workbook.

Switching to a Mac [Completely]

Mon, 21 Nov 2011 10:15:05 -0800

There is a Parallels program that lets you switch seamlessly to Windows on a Mac. A another reason that makes it easier to run Excel for Windows on a Mac.

Excel 2007 Pivot Chart Question

Thu, 03 Nov 2011 20:18:28 -0700

I just used my first Excel 2007 Pivot Chart and noticed the Page Field Filters don’t show up on the chart, like they do in Excel 2003. How is someone to know if there is a filter applied to the chart data if they can’t see it on the chart?

Am I doing something wrong? And more importantly, how can I get the page filters to show up on the chart when printed?

Please, only qualified, intelligent responses.

Excel 2011 and Lion

Fri, 22 Jul 2011 18:48:57 -0700

In my article about Microsoft Query I’m getting a couple of comments about Lion not working with Excel for Mac. Everything I’ve seen online says that most people are using Excel 2011 with Lion and have no problems.

Excel 2008 if previously installed seems to work okay as well, however the install program on Office 2008 is purported to use PowerPC which does not work with Lion.

If you’re going to install Lion like I will probably do this weekend, back up your entire hard drive and update all your software before you do it.

Use Excel in a Web Browser

Fri, 08 Jul 2011 22:37:45 -0700

Use Excel in a Web Browser:

Some college students learn:

… how you can work on a spreadsheet in your web browser without having Excel installed on your computer.

How to Delete a Cell or Range Normally in Excel 2011

Thu, 16 Jun 2011 10:41:06 -0700

How to Delete a Cell or Range Normally in Excel 2011:

Using the Delete key in Excel 2011 for Mac will give you no end of trouble until you understand the trick. (Windows people don’t have this problem.)

Regain your sanity by watching this short video.

I’ve been busy with some activity related to a career change that will happen soon and...

Mon, 13 Jun 2011 05:54:50 -0700

I’ve been busy with some activity related to a career change that will happen soon and haven’t had time to post anything of substance. I’ve just returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings.

I’ll be posting some articles as soon as I get over the jet lag that comes with an eight-hour time change.

Cheers

With So Many Alternatives, Why Does Microsoft Office Still Reign?

Mon, 16 May 2011 19:20:58 -0700

With So Many Alternatives, Why Does Microsoft Office Still Reign?

I use the Paste Special functionality in Excel so frequently that for years I have kept a custom...

Wed, 04 May 2011 13:38:43 -0700

I use the Paste Special functionality in Excel so frequently that for years I have kept a custom button on my toolbar for easy access. Today I stumbled upon the keyboard shortcut for Paste Special and was stunned that I hadn’t known about its existence.

Ctrl+Alt+V is now burned into my memory.

Developer Macro Settings

Wed, 30 Mar 2011 16:00:06 -0700

I wrote a VBA subroutine in Excel 2007 to report on the contents of the VBA Project on a worksheet so I could see the number of modules, lines of code, etc.

Then I tried to run this macro with Excel 2010 and it wouldn’t execute. It finally dawned on me that you have to allow access to the VBA project in Settings.

So I had to choose Developer > Macro Security and check the box to Trust access to the VBA project object model.

And then my code ran like a hot knife through butter.

Word to the Wise

Fri, 18 Mar 2011 04:45:19 -0700

Don’t think about VBA programming code when you’re taking your contact lenses out at night. You just might find out in the morning the left contact is in the right eye, and the right contact is in the left eye.

Google Cloud Connect for Microsoft Office This looks too good to...

Fri, 11 Mar 2011 20:19:29 -0800

Google Cloud Connect for Microsoft Office

This looks too good to be true. I’m going to check it out when I get some time.

Google Cloud Connect for Microsoft Office brings collaborative multi-person editing to the familiar Microsoft® Office experience. You can share, backup, and simultaneously edit Microsoft Word, PowerPoint®, and Excel® documents with coworkers.

Watch the video to learn how Google Cloud Connect teaches your old docs new tricks.