Excel 2007

Visio Experts

bVisual Microsoft Visio & MapPoint Solution Providers

This page will list some of the new features in Excel 2007.

So what is new in Excel 2007?

You may be able to obtain a copy of the Office Beta 2 from here.

General

Microsoft has several resources for Excel 2007. User Guide

David Gainer has a set of must read blog entries on the new features.

For better performance, Excel 2007 has support for dual processors and multithreaded chipsets.

The new UI

Excel 2007 like Access 2007, Outlook 2007, PowerPoint 2007 and Word 2007, has the new User Interface (UI). A new, results-oriented interface presents tools when you need them, in a clear and organized fashion. Live visual previews, predefined style galleries, table formats, and other content help you get more out of Excel 2007 capabilities.

Spreadsheet Size

Microsoft has been listening, the last cell in Excel has gone from IV65536 to XFD1048576. That is going from 256 columns to 16,385 columns and from 65,536 rows to 1,048,576 rows. The new spreadsheet capacity is the same as an Excel 2003 workbook with 1,024 spreadsheets.

To get to the last cell (the bottom right corner) open an empty sheet press Ctrl Down Arrow and then Ctrl Right Arrow.

Thanks to David Gainer here is a list of other changes to limitations.

Item	Old Limit	New Limit
The total number of available columns in Excel	256 (2^8)	16k (2^14)
The total number of available rows in Excel	64k (2^16)	1M (2^20)
Total amount of PC memory that Excel can use	1GB	Maximum allowed by Windows
Number of unique colours allowed a single workbook	56 (indexed colour)	4.3 billion (32-bit colour)
Number of conditional format conditions on a cell	3 conditions	Limited by available memory
Number of levels of sorting on a range or table	3	64
Number of items shown in the Auto-Filter dropdown	1,000	10,000
The total number of characters that can display in a cell	1k (when the text is formatted)	32k or as many as will fit in the cell (regardless of formatting)
The number of characters per cell that Excel can print	1k	32k
The total number of unique cell styles in a workbook (combinations of all cell formatting)	4000	64k
The maximum length of formulas (in characters)	1k characters	8k characters
The number of levels of nesting that Excel allows in formulas	7	64
Maximum number of arguments to a function	30	255
The number of characters that can be stored and displayed in a cell formatted as Text	255	32k
Maximum number of items found by “Find All”	~64k (65472)	~2 Billion
Number of rows allowed in a Pivot Table	64k	1M
Number of columns allowed in a Pivot Table	255	16k
Maximum number of unique items within a single Pivot Field	32k	1M
Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table	255 characters	32k
The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations	255	32k
The number of fields (as seen in the field list) that a single PivotTable can have	255	16k
The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)	8k	Limited by available memory
The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)	64k	Limited by available memory
The number of array formulas in a worksheet that can refer to another (given) worksheet	65k	Limited by available memory
The number of categories that custom functions can be bucketed into	32	255
The number of characters that may be updated in a non-resident external workbook reference	255	32k
Number of rows of a column or columns that can be referred to in an array formula	65,335	Limitation removed (full-column references allowed)

Sorting

Excel 2007 has gone from a choice of three columns to sort by to a choice of sixty four columns and now has the ability to sort by colour. Use AutoFilter to simplify filtering. The AutoFilter's drop-down menu allows you to select multiple items by checking them off.

Views

It is now possible to work in Page Layout View or Page Break View. In Page Layout View you can edit the headers and footers.

A resizeable formula bar that prevents long formulae from spilling over onto the spreadsheet.

Status bar

The status bar includes statistical information about the selected cells. (average, count and sum)

Name Manager

The Name Manager helps organize, update and manage multiple name ranges from a central location.

Conditional Formatting

Conditional formatting has been expanded from three conditions to some larger number, and they are no longer mutually exclusive. Conditional formatting now includes rich data visualizations like the insertion of bars, colour gradients or icons within a cell. Once applied the formulas associated with the conditional formatting can be adjusted using the Conditional Formatting Manager. There is more details on David's blog.

Tables

Microsoft has enhanced how tables are handled. By right clicking within the table and choosing Create Table, Excel 2007 will automatically label columns, create AutoFilters and display other relevant tools.

If you hover over the different table formats in the Table Gallery, you will see a live preview of how your table will look. Some of the formats include alternating colours for rows (usually light and dark). If you delete a row, Excel 2007 will maintain the alternating pattern.

Excel 2007 has improved support for tables allowing you to create, format, expand, and refer to tables within formulae. When analyzing data contained in a large table, Excel 2007 keeps table headings in view while you scroll.

David Gainer on Tables.

Functions

Yes there are more functions. There are 343 functions with 51 new functions. New categories of Engineering and Cube have been added. The following are the new functions. The Engineering functions are now native to Excel, but most or all used to be part of the Analysis Toolpak.

The seven new CUBE functions are used to fetch data from OLAP cubes and place that data anywhere on an Excel spreadsheet.

In addition there are double byte versions of FIND, LEFT, LEN, MID, REPLACE, RIGHT and SEARCH (FINDB, LEFTB, LENB, MIDB, REPLACEB, RIGHTB and SEARCHB)

Cube	CUBEKPIMEMBER	Returns a key performance indicator (KPI) name, property, and measure, and displays the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used to monitor an organization's performance.
Cube	CUBEMEMBER	Returns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.
Cube	CUBEMEMBERPROPERTY	Returns the value of a member property in the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.
Cube	CUBERANKEDMEMBER	Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students.
Cube	CUBESET	Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.
Cube	CUBESETCOUNT	Returns the number of items in a set.
Cube	CUBEVALUE	Returns an aggregated value from a cube.
Engineering	BESSELI	Returns the modified Bessel function In(x)
Engineering	BESSELJ	Returns the Bessel function Jn(x)
Engineering	BESSELK	Returns the modified Bessel function Kn(x)
Engineering	BESSELY	Returns the Bessel function Yn(x)
Engineering	BIN2DEC	Converts a binary number to decimal
Engineering	BIN2HEX	Converts a binary number to hexadecimal
Engineering	BIN2OCT	Converts a binary number to octal
Engineering	COMPLEX	Converts real and imaginary coefficients into a complex number
Engineering	CONVERT	Converts a number from one measurement system to another
Engineering	DEC2BIN	Converts a decimal number to binary
Engineering	DEC2HEX	Converts a decimal number to hexadecimal
Engineering	DEC2OCT	Converts a decimal number to octal
Engineering	DELTA	Tests whether two values are equal
Engineering	ERF	Returns the error function
Engineering	ERFC	Returns the complementary error function
Engineering	GESTEP	Tests whether a number is greater than a threshold value
Engineering	HEX2BIN	Converts a hexadecimal number to binary
Engineering	HEX2DEC	Converts a hexadecimal number to decimal
Engineering	HEX2OCT	Converts a hexadecimal number to octal
Engineering	IMABS	Returns the absolute value (modulus) of a complex number
Engineering	IMAGINARY	Returns the imaginary coefficient of a complex number
Engineering	IMARGUMENT	Returns the argument theta, an angle expressed in radians
Engineering	IMCONJUGATE	Returns the complex conjugate of a complex number
Engineering	IMCOS	Returns the cosine of a complex number
Engineering	IMDIV	Returns the quotient of two complex numbers
Engineering	IMEXP	Returns the exponential of a complex number
Engineering	IMLN	Returns the natural logarithm of a complex number
Engineering	IMLOG10	Returns the base-10 logarithm of a complex number
Engineering	IMLOG2	Returns the base-2 logarithm of a complex number
Engineering	IMPOWER	Returns a complex number raised to an integer power
Engineering	IMPRODUCT	Returns the product of from 2 to 29 complex numbers
Engineering	IMREAL	Returns the real coefficient of a complex number
Engineering	IMSIN	Returns the sine of a complex number
Engineering	IMSQRT	Returns the square root of a complex number
Engineering	IMSUB	Returns the difference between two complex numbers
Engineering	IMSUM	Returns the sum of complex numbers
Engineering	OCT2BIN	Converts an octal number to binary
Engineering	OCT2DEC	Converts an octal number to decimal
Engineering	OCT2HEX	Converts an octal number to hexadecimal
External	EUROCONVERT	Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation)
External	SQL.REQUEST	Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming
Math and trigonometry	SUMIFS	Adds the cells in a range that meet multiple criteria
Statistical	AVERAGEIF	Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
Statistical	AVERAGEIFS	Returns the average (arithmetic mean) of all cells that meet multiple criteria.

GETPIVOTDATA was moved from the"Database and List Management" category to the "Lookup and Reference" category.

Charts

The charts have been improved. There are dramatic visual effects such as 3-D, soft shadowing, anti-aliasing and glow. The same chart engine is used in Word 2007 and PowerPoint 2007.

Pivot Tables and PivotCharts

PivotTable views allow you to quickly reorient your data to help you answer multiple questions. Find the answers you need faster because Office Excel 2007 will help you to create and use PivotTable views more easily. They use data fields to reorient data quickly.

PivotCharts allow for a more graphical representation of a PivotTable.

SmartArt Graphics

Smart Art allows you to add more complex graphics to the spreadsheet.

Business Dashboard

Business dashboards can be easily created from spreadsheets to track key performance indicators (KPIs) and then they can be shared through a Web browser.

Data Connection Library

The library allows you to import external data into a spreadsheet by using preconfigured external sources of informtion.

Compatibility

Excel 4 macros will still work.

Deprecated Features

File Formats

To address users concern over having their information in a proprietary format, Microsoft has created XPS (XML Paper Specification), an XML based file format that is easily readable. The downside of XML format is that it is not an efficient storage format. To overcome this issue Microsoft compresses the information using the Zip format. Additionally, because the file format is XML-based, with an open, royalty-free license, developers can more easily build solutions that utilize Office Excel 2007 document contents and metadata.

The Excel XML Format is compatible with Microsoft Office 2003, Office XP, and Office 2000 with the addition of a file format converter patch, available from Microsoft Office Online and Microsoft Update. Users of Office 2003, Office XP, and Office 2000 can open, edit, and save files using the new Excel XML Format.

It is a full-fidelity file format just like the Microsoft Office Open XML Formats. It is based on the same technologies as the Office Open XML Formats.

Acrobat files

Excel 2007 spreadsheets will also be able to export to PDF. A special PDF writer will no longer be required.

Events

To be researched.