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.
Master Excel Shortcuts On a Mac. →
Office (Excel) on iPad in November [Report] →
More fuel to the fire.
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...
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...
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.)
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.
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...
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.
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,...
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...
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... →
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 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...
With So Many Alternatives, Why Does Microsoft... →
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
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...
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.
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....
F2 in Excel for Mac →
Seems a hot topic today.
Switch Between Workbooks Faster in Excel 2007 and... →
Free Kindle via John Walkenback? →
This tale is making the rounds and the prediction is almost here.
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.
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.
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...
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,...
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.
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.
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.
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...
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...
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.
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
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.
Try Excel 2011 Free for 30 Days [Mac] →
Check out Office 2011 free on your Mac for 30 days by simply registering.
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.
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.
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!
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...
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.
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.
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?
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.
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.
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.
Top 10 Excel Articles for 2010 →
A link to the most popular blog articles since starting Excel Semi-Pro in June of this year.
Vote for Excel Functions You Want Explained
Vote here for the Functions you want to see for: 30 Excel Functions in 30 Days coming January 2nd on the Contextures website. Debra Dalgleish is putting together a 2011 Challenge in January to improve your skills with Excel functions. My wish is for her to explain the INDIRECT Function. She has a list of Text, Information and Lookup and Reference functions you can vote on now to help her decide...