Two Ways to Use the INDEX Function to Return an Array
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.
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.
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?
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.
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.
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.
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.
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.
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.
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 Ctrl+V to paste the picture into the Paint program. I then use the Snipping Tool to grab a rectangular image from the screen shot in Paint.
On a Mac I use CMD+Shift+3 to copy a screen shot to the desktop, select the Screen Shot image, press the space bar to view, the click the double arrows to show full screen. I then use CMD+Shift+4 to capture a rectangular image of my choice to the desktop. (show above)
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, and seconds.
So the max for each argument for the TIME function looks like this.
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.
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 values.
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 dock and the Mac was non-responsive.
Moral of the story, close any Help windows before closing Excel.
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.
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.
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.
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.
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 interactivity (and several errors on the page).
Guess the iPad is persona non grata on Windows Live SkyDrive.
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).
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?
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.
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.