Microsoft Office Development and Consultancy

Excel

2010 Changes

Multithreaded calculations when using multi-processor or dual-core chips.

This feature enables Excel to identify formulas that can be calculated concurrently, and then runs those formulas on multiple processors simultaneously. When Excel loads a workbook it asks the operating system how many processors are available.

2007 Changes

STRUCTURED REFERENCES

Easy referring to tables and named ranges (structured referencing) within formulas and functions

Formulas can now also include references to named ranges and table names

SS

The ability to incorporate structured references:

1) reference a table (or part of) by using the name

The maximum number of operands in a formula has been increased from 40 to 1,024.

The maximum length of a formula has been increased from 1,024 to 8,192 characters.

The number of cell references you can have in a particular cell has been increased from 8,000 to unlimited.

Formulas can now be nested up to 64 levels instead of a maximum of 7.

Can now contain structured references making it easier to work with tables or just portions of data.

MEMORY MANAGEMENT

This has been increased from 1GB in 2003 to 2 GB in 2007.

RESIZEABLE FORMULA BAR

The formula bar has its own dedicated space that never overlaps with the cells.

A resizable formula bar now prevents long, complex formulae from spilling over and obstructing some of your worksheet.

Resizable formula bar to accommodate long formulas.

You can quickly resize by dragging the resize bar at the bottom, or by clicking the auto expand/collapse button at the far right.

There is also a keyboard shortcut to toggle between between the collapsed (1 line) and expanded state.

SS

FORMULA AUTOCOMPLETE

Context based formula autocomplete

In addition to cell references Excel now provides structured references that reference named ranges and tables.

Excel functions – this includes all functions in the Excel library

User defined functions – Functions defined by the user either through VBA, automation add-ins, or .XLL’s show up as functions

Defined Names – All defined names are surfaced

Enumerated Arguments – Some enumerated arguments (like the function_num argument in the example) will have auto complete support

Table Structure References – More on this in post coming soon about improved table support.

SS from newsletter

MULTITHREADING

Multi-threaded support for dual processors

Multithreaded Calculations (MTC) makes it possible for Excel 2007 to automatically divide formula evaluation and compuational tasks into multiple calculation engines that are distributed among multiple processors

This arrangement reduces the time it is required to calculate workbook models because multiple calculations can be performed simultaneously.

MTC is turned on by default and it is set to create as many calculation engines as there are processors on the computer.

You can manually specify the number of calculation engines that Excel creates regardless of how many processors are on the computer.

CALCULATION

Calculations and formulas should be faster because Excel 2007 supports multi processors and multi-threaded chipsets.

Indicates the number of processes when updating

SS - status bar.

INCREASED PERFORMANCE

Excel 2007 now supports dual processors and multithreaded chipsets.

LENGTH OF FORMULAS

2003 limit - 1024 characters

2007 limit - 8000 characters

FORMULA NESTING

2003 limit - 7

2007 limit - 64

References to A:A ??? In formulas etc

The maximum number of nested functions increased from 7 to 64

The maximum length of a formula increased from 1,000 characters to 8,000 characters

The maximum number of characters that may be updated in a non-resident external workbook reference increased from 255 to 32,000

The number of cells that may depend on a single area before Excel must do a full calculation instead of a partial calculation (because it can no longer track the dependencies required to do a partial calculation increased from 8,000 to no limit

The number of different areas in a sheet that may have dependencies before Excel must do a full calculation instead of a partial calculation (because it can no longer track the dependencies required to do a partial calculation increased from 64,000 to no limit