Excel Semi-Pro Microblog: Archive

Keyboard Shortcut - Page Down and Up in Excel for...

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.

Dec 27th

Master Excel Shortcuts On a Mac. →

Sep 24th

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

More fuel to the fire.

Jun 2nd

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?)

May 24th

Two Ways to Use the INDEX Function to Return an...

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...

Apr 9th

Repeat that Last Command in Excel (Mac)

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.)

Mar 17th

Microsoft doesn’t want to stop OnLive’s service,... →

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

Mar 10th

The NETWORKDAYS Formula

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...

Feb 16th

1 tag

Feb 15th

Switching to a Mac [Completely]

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.

Nov 21st

Excel 2007 Pivot Chart Question

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,...

Nov 4th

Excel 2011 and Lion

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...

Jul 23rd

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.

Jul 9th

How to Delete a Cell or Range Normally in Excel... →

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.

Jun 16th

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...

Jun 13th

1 note

With So Many Alternatives, Why Does Microsoft... →

May 17th

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.

May 4th

Developer Macro Settings

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...

Mar 31st

1 note

Word to the Wise

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.

Mar 18th

Mar 12th

An Excel Dashboard Resource

For a number of years I created reports and charts for the printing industry. Since my work as a consultant was limited to a relatively short time period, the basic Key Performance Indicators were covered before we moved on to the next project. I just started reading Excel Dashboards and Reports (Mr. Spreadsheet’s Bookshelf) and found some seriously good stuff in the introduction chapter....

Mar 5th

1 note

F2 in Excel for Mac →

Seems a hot topic today.

Mar 3rd

Switch Between Workbooks Faster in Excel 2007 and... →

Mar 1st

Free Kindle via John Walkenback? →

This tale is making the rounds and the prediction is almost here.

Feb 27th

Zoom That Spreadsheet [Windows]

This post reminded me that you can zoom in and out with your mouse on an Excel spreadsheet: just hold the Control key down while you roll the center scroll wheel. Doesn’t work for Excel on a Mac because the everything zooms, not just Excel.

Feb 26th

Problems with Left to Right Sort in Excel →

A reader pointed out column widths are a problem when sorting from left to right in Excel. I checked it out and found even more stuff to worry about that I’ll share tomorrow. Beware the Left to Right Sort in Excel.

Feb 18th

Capture Screen Shot of Drop-Down List in Excel

Sometimes I need to capture a screen shot of Excel when viewing a drop-down list, which is hard because once you touch another key the drop-down list disappears. So I capture a picture of the entire screen when the drop-down is visible, then use that image to capture my rectangular picture. In Windows I use the PrtScn key to capture the entire screen to the Clipboard, then open Paint and use...

Feb 16th

TIME Limitation 32767

The TIME function has three arguments - Hour, Minute, Second. Each argument takes values from 0 to 32767. The Hours argument takes any number greater than 23 and divides by 24, then the remainder is treated as the hour value. The Minute argument takes any number greater than 59 and converts to hours and minutes. The Seconds argument takes any number greater than 59 and converts to hour, minutes,...

Feb 13th

125% Zoom

If a spreadsheet was created with Excel on a Windows machine, every time I open that spreadsheet with Excel on a Mac, the first thing I do is change the zoom to 125% so it looks normal.

Feb 10th

1 note

Toggle Absolute and Relative in Excel for Mac

In Excel for Mac, toggling between Absolute, Relative, and Mixed cell references is not done with the F4 key like you would in Windows. Toggle a cell reference by using the keyboard shortcut COMMAND+T. Remember you have to be editing a cell for this to work. Either click inside the formula bar or use the keyboard shortcut CONTROL+U to edit a cell. Then you can use COMMAND+T to start toggling.

Feb 9th

Unassigned Keyboard Shortcuts in Excel 2010

The CTRL combinations CTRL+E, CTRL+J, CTRL+M, AND CTRL+ Q are currently unassigned shortcuts in Excel 2010.

Feb 9th

Paste Special Shortcut in Excel

Live and learn. Using the CTRL+ALT+V keyboard shortcut brings up the Paste Special Dialog box in Excel. Available only after you have cut or copied an object, text, or cell contents on a worksheet or in another program. Won’t work if you’ve setup hotkeys in Evernote to paste clipboard with that exact keyboard combination (like me). In Excel for Mac CONTROL+SHIFT+V will paste as...

Feb 8th

Crashing the Mac with Excel 2011

Here’s what I think happened: I opened Excel 2011, used the Help feature for something then minimized the Help window to the Dock so I could do something in the spreadsheet. After doodling around with a couple other programs I closed Excel and the Mac started to freeze up. I used Cmd+Option+Esc to Force Quit Excel, which seemed to work, but then I noticed the Help window was still in the...

Feb 5th

Excel Time and Date Articles →

I consolidated my posts for Time and Date on a single page on my website for easy access. It seems a popular topic for Excel users.

Feb 4th

My Top 5 Quick Access Toolbar Commands

I was recently looking at Commands Not in the Ribbon (Excel 2010 and 2007) and found a couple of new commands that are handy when added to the Quick Access Toolbar. Here’s my top five favorite commands on the Quick Access Toolbar. Paste Values Repeat Freeze Sheet Panes Switch Windows Wrap Text

Feb 4th

Preferred File Location in Excel for Mac

Excel can save files in a directory of your choice every time. Open Excel for Mac (2011 or 2008) and choose Excel > Preferences, select the General tab, then click the Select… button for the Preferred file location, find the folder of your choice and click Choose, then click OK.

Jan 30th

Try Excel 2011 Free for 30 Days [Mac] →

Check out Office 2011 free on your Mac for 30 days by simply registering.

Jan 27th

Jan 22nd

Convert Volatile to Static Time in Excel

The Now() Function will provide a dynamic time that changes much like the RAND() function does. If you want a static time, use the keyboard shortcut Ctrl+; (semicolon) which will put the computers system date in the active cell.

Jan 20th

The Name Box in Excel for Mac

If you can’t find the Name box in Excel 2011 or 2008 the Formula bar will need to be in full view by choosing View > Formula Bar.

Jan 19th

Get Rid of That Hyperlink in Excel

When you want a hyperlink, without the hyperlink there’s a simple solution. Just type a hyperlink into a spreadsheet cell, then use the keyboard shortcut Ctrl+Z to remove the link. The text remains in the cell, but no more hyperlink. Sweet!

Jan 19th

SkyDrive Excel File Link Not Working with iPad

I’m having all kinds of trouble trying to access an Excel spreadsheet that’s located in a public folder on my Windows SkyDrive account with my iPad. Even though it’s public, I have to sign-in to see the file, but can’t interact. Then after signing out and following the link a second time the website kicked me into a mobile view, which is slightly horrendous and has no...

Jan 14th

Importing Data from a Web Page

I’ve been trying to do things equally across both Excel platforms - Windows and Mac - but importing data from a web page is something that the Windows version (Excel 2007, 2010) has going for it that’s lacking in the Mac version (Excel 2011). Some of this is frustrating as hell.

Jan 14th

Keyboard Shortcut for Number Format

The keyboard shortcut Ctrl+Shift+1 works in both Windows and Mac versions of Excel to apply the Number format with two decimal places, thousands separator, and minus sign for negative values.

Jan 13th

Zoom Excel 2011 for Windows Spreadsheets →

When I use Excel 2011 and open a spreadsheet that was created in a Windows version of Excel, I normally set the zoom to 125% because of the difference in column widths. Why can’t things just be standardized between the two?

Jan 12th

View 2 Worksheets Simutaneously in Excel

Sometimes you find yourself switching back and forth between two different worksheets and would like to see them together at the same time. Just choose View > New Window, then select Arrange All, click an Arrange option (like Horizontal) and click OK. Now you can see both worksheets in the same Excel Window.

Jan 7th

Redo and Repeat Commands

The Redo command uses the keyboard shortcut Ctrl+Y, but so does the Repeat command. In Excel for Windows the F4 key is also used as a shortcut key for Redo/Repeat, but not in Excel for Mac. (You can fix the F4 button on the Mac.) In Excel for Windows, the Repeat command icon can be added to the Customized Keyboard, which I find to be somewhat interesting.

Jan 6th

Familiarize yourself with the Excel 2011 Ribbon →

Direct link to Microsoft Office:Mac HowTo Excel topic. If you happen to turn off the Excel Ribbon using the instructions here, turning it back on is done through the Excel > Preferences > Ribbon dialog box.

Jan 3rd