What's New in Excel 2007?

This tutorial is for you if you've used a previous version of Excel. Here you'll find an overview of what's new and what's changed in Excel 2007.

New User Interface

Microsoft Excel continues to grow in power, sophistication, and capability, but one thing that has changed very little since the early '90s is its user interface. The once-simple toolbar has been packed with so many features over the years that few users know where to find them all. Microsoft has addressed this problem in Excel 2007 by radically redesigning the user interface. The time-honored menu-and-toolbar user interface has been scrapped and replaced with a new “tab-and-ribbon” interface that makes every feature easy to locate and use.

Other elements that comprise the new look include:

Six new modern-looking fonts:
The default workbook font is now 11-point Calibri, which is much more readable than the old 10-point Arial, especially in smaller sizes.

Quick Access Toolbar:
A personal toolbar, to which you can add commands that you use regularly. This toolbar is the only part of the Excel 2007 interface that the user can customize.

The Mini toolbar:
A new addition to the right-click menu. This toolbar contains commonly-used formatting icons, displayed near your mouse pointer for quick access.

New File Formats

Over the years, Excel’s XLS file format has become an industry standard. Excel 2007 still supports that format, but it now uses new default “open” file formats that are based on XML (Extensible Markup Language).

For compatibility, Excel 2007 still supports the old file formats so that you can continue to share your work with those who haven’t upgraded to Excel 2007.

New Worksheet Functions

IFERROR
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.

AVERAGEIF
Calculates a conditional average (similar to SUMIF and COUNTIF).

AVERAGEIFS
Calculates a conditional average using multiple criteria.

SUMIFS
Calculates a conditional sum using multiple criteria.

COUNTIFS
Calculates a conditional COUNT using multiple criteria.

In addition, 39 worksheet functions that used to require the Analysis Toolpak add-in are now built-in. Excel 2007 also includes seven new CUBE functions that retrieve data from SQL Server Analysis Services.

Worksheet Tables

Working with tables is easier than ever. A table is just a rectangular range of cells that usually contains column headers. The designers of Excel 2007 realized that such tables are widely used in Excel, and they’ve taken the concept to a new level.

Once you designate a particular range to be a table using the Insert > Tables > Table command, Excel provides you with some very efficient tools that work with the table:

• You can apply attractive formatting with a single click.
• You can easily insert summary formulas in the table’s total row.
• If each cell in a column contains the same formula, you can edit one of the formulas, and the others change automatically.
• You can easily toggle the display of the table’s the header row and totals row.
• Removing duplicate entries is easy.
• Autofiltering and sorting options have been expanded.
• If you create a chart from a table, the chart will always reflect the data in the table—even if you add new rows.
• If you scroll a table downwards so that the header row is no longer visible, the column headers now display where the worksheet column letters would be.

Larger Worksheets

Over the years, perhaps the most common complaint about Excel was the size of a worksheet. A worksheet now has 1,048,576 rows and 16,384 columns, which works out to more than 17 billion cells, an Excel 2007 worksheet has more than 1,000 times as many cells as an Excel 2003 worksheet.

Excel 2003 versus Excel 2007

	Excel 2003	Excel 2007
Number of rows	65,536	1,048,576
Number of columns	256	16,384
Amount of memory used	1 Gbytes	Maximum allowed by Windows
Number of colors	56	4.3 billion
Number of conditional formats per cell	3	Unlimited
Number of levels of sorting	3	64
Number of levels of undo	16	100
Number of items shown in the Auto-Filter dropdown	1,000	10,000
The total number of characters that can display in a cell	1,000	32,000
Number of unique styles in a workbook	4,000	64,000
Maximum number of characters in a formula	1,000	8,000
Number of levels of nesting in a formula	7	64
Maximum number of function arguments	30	255
Maximum number of function arguments	30	255

Styles and Themes

Mostly people aren’t very good with graphic design. Worksheets with gaudy colors and unreadable blue-on-green text are common.

Excel has always supported named styles, which can be applied to cells and ranges. Excel 2007 brings this feature to the forefront by providing a good assortment of predefined styles, easily accessible by choosing Home > Styles > Cell Styles.

With the introduction of document themes, Excel 2007 makes it easy to create good-looking worksheets. A theme consists of a color palette, font set, and effects. You now have one-click access to a gallery of professionally-designed themes that can dramatically change the look of your entire spreadsheet—almost always for the better. Access the theme gallery by choosing Page Layout > Themes > Themes.

Better Looking Charts

Excel 2007 offers no new chart types, and many of the long-time chart-related feature requests have been ignored by Microsoft.Excel charts now look better than ever. For the first time, you can honestly use the term “boardroom quality” to describe Excel charts.

Page Layout View

As an option, you can display your worksheet as a series of pages. This new Page Layout view ensures no surprises when it’s time to print your work. Even better, the Page Layout view includes “click and type” page headers and footers—which is much more intuitive than the old method. Unlike the standard print preview, Page Layout view is fully functional in terms of spreadsheet editing.

Excel’s new Page Layout view makes it easy to see how your printed work will appear.

Enhanced Conditional Formatting

Conditional formatting refers to the ability to format a cell based on its value. Conditional formatting makes it easy to highlight certain values so that they stand out visually. For example, you may set up conditional formatting so that if a formula returns a negative value, the cell background displays green.

In the past, a cell could have at most three conditions applied. With Excel 2007, a you can format a cell based on an unlimited number of conditions. But that’s the least of the improvements. Excel 2007 provides a number of new data visualizations: data bars, color scales, and icon sets.

Excel 2007 includes quite a few other improvements to conditional formatting. In general, conditional formatting is much more flexible, easier to set up, and relies less on creating custom formulas to define the formatting rules.

Consolidated Options

In the past, Excel provided far too many dialog boxes to set various options. In Excel 2007, most dialog boxes have been consolidated into a massive Excel Options dialog box. To display this dialog box, choose File > Excel Options.

The options are grouped into tabs, which you select on the left. Locating some of the options still isn’t easy, but the new implementation is much better than it used to be. The Excel Options dialog box is also resizable—just click and drag the lower right corner to change the size.

SmartArt

Excel 2007 still includes a wide assortment of Shapes that you can use to create visual diagrams, such as flow charts, org charts, or diagrams that depict relationships. But the new SmartArt feature is a much better tool for such tasks. You can quickly add shadows, reflection, glow, and other special effects.

Formula AutoComplete

Entering formulas in Excel 2007 can be a bit less cumbersome, thanks to the new Formula AutoComplete feature. When you begin typing a formula, Excel displays a continually updated drop-down list of matching items, including a description of each item. When you see the item you want, press Tab to enter it into your formula. The items in this list consist of functions, defined names, and table references.

Collaboration Features

Excel Services is new server technology that ships with Office 12. It’s part of the Microsoft Office Share Point Server product. Excel Services supports loading, calculating, and rendering Excel spreadsheets on servers.

If your company is set up with Excel Services, you can use Excel 2007 to collaborate with your coworkers efficiently and present data to those who don’t use Excel.

Compatibility Checker

Given all the new features in Excel 2007, you may be hesitant to share a workbook with others who use an earlier version of Excel. To find out how your workbook will function with previous versions, use the compatibility checker. Choose Office > Prepare > Run Compatibility Checker.

Improved Pivot Tables

Excel’s pivot table feature is probably one of its most underutilized features. A pivot table can turn a large range of raw data into a useful interactive summary table with only a few mouse clicks. Microsoft hopes to make this feature more accessible by improving just about every aspect of pivot tables in Excel 2007.

Charts created from pivot tables (pivot charts) now retain their formatting when they’re updated. This loss of formatting had been a frustration for hundreds of thousands of users, and Microsoft finally did something about it.

Other New Features

• Trust Center: Protecting yourself from malicious macros is a bit easier with Excel 2007. For example, you can disable all macros, except those in workbooks that are stored in trusted locations on your computer.
• PDF add-in: You can create an industry-standard Adobe PDF file directly from Excel using an add-in available from Microsoft.
• Improved zooming: Use the zoomer control on the right side of the status bar to quickly zoom in or zoom out on your worksheet.
• More control over the status bar: You can now control the type of information that appears in the status bar.
• Color Schemes: Change the appearance of Excel by applying one of three color schemes that ship with Excel (Blue, Silver, or Black).
• Resizable formula bar: When editing lengthy formulas, you can increase the height of the formula bar so that it doesn’t obscure your worksheet. Just click and drag on the bottom border of the formula bar.
• Lots of new templates: Why reinvent the wheel? Choose Office > New, and you can choose from a variety of templates. One of them may be exactly (or at least close to) what you need.

When you first launch Excel, it starts you off with a new blank worksheet.A worksheet is the grid of cells where you type your information and formulas.This grid is the most important part of the Excel window. It's where you'll perform all your work, such as entering data, writing formulas, and reviewing the results.

Each workbook is comprised of one or more worksheets, and each worksheet is made up of individual cells.Each cell contains a value, a formula, or text. A worksheet also has an invisible draw layer, which holdscharts, images, and diagrams. Each worksheet in a workbook is accessible by clicking the tab at the bottomof the workbook window. In addition, workbooks can store chart sheets. A chart sheet displays a single chartand is also accessible by clicking a tab.

The grid divides your worksheet into rows and columns. Columns are identified with letters (A, B, C…), while rows are identified with numbers (1, 2, 3…).The smallest unit in your worksheet is the cell. Cells are identified by column and row. For example, B4 is the address of a cell in column B (the third column), and row 4 (the sixth row). An Excel cell can hold up to 32,000 characters.

A worksheet can span an eye-popping 16,000 columns and 1 million rows. When you enter information, you enter it one cell at a time. However, you don't have to follow any set order. For example, you can start by typing information into cell B40 without worrying about filling any data in the cells that appear in the earlier rows.

Active cell indicator
This dark outline indicates the currently active cell.

Column letters
Letters range from A to IXFD. To select an entire column click a column heading.

Office button
This button have a lots of options for working with Excel.

Formula bar
Cell information or formulas appear in this line.

Name box
Displays the active cell address or the name of the selected cell, range, or object.

Page view buttons
Change the way the worksheet is displayed by clicking one of these buttons.

Quick Access Toolbar
A customizeable toolbar that holds commonly-used commands

Ribbon
The main location to find Excel's commands.

Row numbers
Numbers range from 1 to 1,048,576, to select an row click a row number.

Sheet tabs
Each of these tabs represents a different sheet in the workbook.

Sheet tab scroll buttons
These buttons let you scroll the sheet tabs to display tabs that aren't visible.

Status bar
This bar displays various messages like status of the Num Lock, Caps Lock, and ScrollLock keys on your keyboard.

Tab list
Similar to a menu, display a different ribbon.

Zoom control
Worksheet in and out zoom controller.

The current cell in above figure is B7. You can recognize the current (or active) cell based on its heavy black border. You'll also notice that the corresponding column letter (B) and row number (7) are highlighted at the edges of the worksheet. Just above the worksheet, on the left side of the window, the formula bar tells you the active cell address.