An improvement in Microsoft Excel is necessarily an enhancement to its formula capabilities. At the same time; there are many Excel 2007 Formulas features you may desire/dream:

• Highlighting of nested formulas
• Better tracer tools for debugging
• Commenting on formula arguments
• WYSYWYG algebraic debugger
• And more

Put them on your wish list for Excel 2010 or later versions; they might bring those features.

Now it is time to review Excel 2007 Formulas capabilities…

1) Write Formulas Faster and more Comfortably without Invoking the Function Wizard

• Functions (graphic 1; a)
• Names and constants (Excel Named Ranges). See graphic 1; a
• Functions constants (graphic 1; b, c)
• Description for each function, constant, named range (graphic 1; a, c)

You may be accustomed to write formulas directly on cells; so you know functions syntax to a tie but clues along the way are useful. VBA users have benefited long time with this feature.

The new Formula Autocomplete Tool pops up the following items as you write:

Graphic 1

2) Write/Read Longer Formulas than Ever

• Automatic wrap content of the Formula Bar (graphic 2)
• More arguments: from 30 to 255
• More nesting levels: from 7 to 64
• Greater number of characters in a formula: from 1024 to 8192
• Resizable Formula Bar

Writing long formulas is not a good habit (hard to write/read/debug) but Excel 2007 Formulas can be longer. Now you dispose of:

Important notice

These enhancements may encourage you to produce longer Formulas but you must be aware that they are a thread for your spreadsheet.

Graphic 2

3) Simplify Some of Your Workarounds You Used to Perform a COUNTIF Multiple, SUMIF multiple (SUMPRODUCT and Array Formulas)

• AVERAGEIF
• AVERAGEIFS
• SUMIFS (SUMIFmultiple)
• COUNTIFS (COUNTIFmultiple)

You may be one of those Excel users who have claimed for a SUMIF multiple, now you have it (SUMIFS). Just to remember, to make a multiple SUMIF in Excel 2003 you need to use some workarounds, for example:

=SUMPRODUCT(($A$2:$A$8<DATEVALUE(“01/05/2009”))*
($D$2:$D$8=B$14)*$B$2:$B$8)

Or this other one (array formula):

{=SUM(($A$2:$A$8<DATEVALUE(“01/05/2009”))*
($D$2:$D$8=B$14)*$B$2:$B$8)}

Now you can achieve the same outcome with SUMIFS. The syntax is this:SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

To accomplish the same result, your formula will be this one:

=SUMIFS(B2:B8,D2:D8,B15,A2:A8,”<“&DATEVALUE(“05/01/2009”))

That’s not all, Excel 2007 offers you a set of new conditional functions:

4) Simplify your Trapping-Error Formulas (IFERROR instead of IF/ISERROR Combination)

Stop nesting ISERROR in an IF formula; for example, you wrote before:=IF(ISERROR(VLOOKUP(C11,A1:B6,2,0)),”There is a problem”,VLOOKUP(C11,A1:B6,2,0))

Now the same formula could be written this way:

=IFERROR(VLOOKUP(C11,A1:B6,2,0),”There is a problem”)

It is simpler and cleaner.

5) Keep Table Formulas Consistent

Write a formula on a row basis and Excel 2007 Table considers the entire field. For example:

The formula =F8/D8 was typed in G8 but the New Excel Table uses structured reference to create a unique formula for the entire field, see below:

=Table6[[#This Row],[Order Amount]]/Table6[[#This Row],[Number of items]]

That’s not all, once you hit Enter the formula will be copied automatically up and down (wherever you type it). In other words the formula of any field will be always the same.

Graphic 3

6) Create and Maintain Excel Names more Efficiently with the Excel 2007 Name Manager

• Delete multiple names at once
• Filter names by criteria: by scope, by errors
• Add comments that are later displayed on formula Autocomplete Tool
• Set the scope of names: Workbook or Sheet
• Visualize all the names and their related info (name, value, Refers to, etc) in one pane

Now you can use names in Excel 2007 Formulas more efficiently (see graphic 4):

Graphic 4

7) Access Quickly to Excel Formulas Options/Tools/Commands

• Dedicated Ribbon called Formulas with Excel Functions organized and relevant tools at hand (see graphic 5).
• New Formulas tab at Options dialog (see graphic 6)

The options and tools to administer Formulas were repurposed, now you have:

Graphic 5

Graphic 6

8) Calculate Spreadsheets Faster

You will welcome this option is you work with intensive formula spreadsheets where calculation speed counts.

Multi thread calculation now is possible in Excel 2007. Use all the processors of your computers by proceeding this way:

Go to office Button>Excel Options>Advanced>Formulas>Enable multi-threaded calculation

In other words, Excel 2003 sub utilizes your Intel core duo PC.