allPM.com :: Excel 2007 Feature By Bob Umlas, Microsoft® Excel MVP

Excel 2007 Feature By Bob Umlas, Microsoft® Excel MVP

Published on Thursday, May 24, 2007 - 08:33 PM

Excel 2007. So much has already been written on it – about the Ribbon replacing the current menus; about there being over a million rows and over 16,000 columns; about the new charting, tables, pivot tables. But I haven’t seen very much written up on some of the new functions and a few other items, so I thought I’d give a brief introduction here.

There are 5 new builtin functions: IFERROR, SUMIFS, COUNTIFS, AVERAGEIF, and AVERAGEIFS. In previous versions of Excel, some functions would return an error, like if a VLOOKUP didn’t find a value. A sample would be: =IFERROR(VLOOKUP(…),0). If there’s an error, it returns 0, otherwise it returns the result of the lookup.
SUMIFS is a function which expands on Excel’s SUMIF function. The SUMIF function’s syntax is:

-- If the items in the range match the criteria, then add the values in the sum_range. For example, =SUMIF(A1:A100,“Bob”,B1:B100) would add up all the items in B1:B100 where the corresponding values in A1:A100 is equal to “Bob.” The limitation to this function is that you can’t calculate all the items where one range is “Bob” and another range is the year 2007. Enter SUMIFS. The syntax is:

-- an example would be =SUMIFS(B1:B100,A1:A100,”Bob”,C1:C100,2007) which would add the values in B where A is Bob and C is 2007.
The other new functions mentioned above work similarly.
Here’s a screenshot from Excel 2003:

Notice the long text obliterates the work area – Rows 1 through 4 are “chopped off” after column B (for viewing only). Here’s the same text in Excel 2007:

Where’s the rest of it? First, nothing is hidden in the work area. The formula bar is adjustable. You can use ctrl/shift/U to see this:

The formula bar “opens up” and moves the work area down. It can also be dragged up/down with the mouse instead of the keyboard shortcut.
Another new feature is a new “view”, called Page Layout view:

You might think that the Page Layout view would be on the Page Layout tab (3rd from the left, above), but it’s on the View tab, as shown (and it’s also always available at the bottom of the screen:

In this view, it’s easy to add items to the header and footer. There are still 3 sections:
Also notice that when you are in a header, you have a “contextual tab,” called “Header & Footer Tools”, which is only present when needed:

I’ll continue with more new features in future articles.

© 2007 allPM.com

About the Author
Bob Umlas has been a Microsoft Excel MVP since 1995. He has been a beta tester for new versions of Microsoft Excel since version 1.5 (on the Macintosh)! He has led several sessions at Microsoft's Tech-Ed: Maximizing Excel development using Array Formulas, and Excel Tips and Tricks. He is also the author of “This isn’t Excel, it’s Magic!”

What area of organizational business objectives is most impacted by projects?