What's new in Microsoft Office Excel 2007
Not sure which version to upgrade to? Excel 2010 has some key improvements to Excel 2007, including enhancements to functions and PivotTables and new features for visualizing data, such as slicers and sparklines.
Read an article or try Office 2010!
In a new results-oriented user interface, Microsoft Office Excel 2007 provides powerful tools and features that you can use to analyze, share, and manage your data with ease.
In this article
Results-oriented user interface
The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with drop-down galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.
No matter what activity you are performing in the new user interface—whether it's formatting or analyzing data—Excel presents the tools that are most useful to successfully complete that task.
More rows and columns, and other new limits
To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows and 6,300% more columns than you had available in Microsoft Office Excel 2003. For those of you who are curious, columns now end at XFD instead of IV.
Instead of 4 thousand types of formatting, you can now use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory.
To improve the performance of Excel, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.
You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports multiple processors and multithreaded chipsets.
Office Excel 2007 also supports up to 16 million colors.
Office themes and Excel styles
In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release programs, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.
Applying a theme A theme is a predefined set of colors, fonts, lines, and fill effects that can be applied to your entire workbook or to specific items, such as charts or tables. They can help you create great-looking documents. Your company may be providing a corporate theme that you can use, or you can choose from the predefined themes that are available in Excel. It's also easy to create your own theme for a uniform, professional look that can be applied to all of your Excel workbooks and other 2007 Office release documents. When you create a theme, the color, font, and fill effects can be changed individually so that you can make changes to any or all of these options.
Using styles A style is a predefined theme-based format that you can apply to change the look of Excel tables, charts, PivotTables, shapes, or diagrams. If built-in predefined styles don't meet your needs, you can customize a style. For charts, you can choose from many predefined styles, but you cannot create your own chart styles.
As in Excel 2003, cell styles are used to format selected cells, but you can now quickly apply a predefined cell style. Most cell styles are not based on the theme that is applied to your workbook, and you can easily create your own.
Rich conditional formatting
In 2007 Office release, you can use conditional formatting to visually annotate your data for both analytical and presentation purposes. To easily find exceptions and to spot important trends in your data, you can implement and manage multiple conditional formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply—in just a few clicks, you can see relationships in your data that you can use for your analysis purposes.
Easy formula writing
The following improvements make formula writing much easier in Office Excel 2007.
Resizable formula bar The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel.
Function AutoComplete With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.
Structured references In addition to cell references, such as A1 and R1C1, Office Excel 2007 provides structured references that reference named ranges and tables in a formula.
Easy access to named ranges By using the Office Excel 2007 name manager, you can organize, update, and manage multiple named ranges in a central location, which helps anyone who needs to work on your worksheet interpret its formulas and data.
New OLAP formulas and cube functions
When you work with multidimensional databases (such as SQL Server Analysis Services) in Office Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. New cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.
Improved sorting and filtering
In Office Excel 2007, you can quickly arrange your worksheet data to find the answers that you need by using enhanced filtering and sorting. For example, you can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter drop-down list, select multiple items to filter, and filter data in PivotTables.
Excel table enhancements
In Office Excel 2007, you can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that it's much easier to work with. New or improved functionality for tables includes the following features.
Table header rows Table header rows can be turned on or off. When table headers are displayed, they stay visible with the data in the table columns by replacing the worksheet headers when you move around in a long table.
Calculated columns A calculated column uses a single formula that adjusts for each row. It automatically expands to include additional rows so that the formula is immediately extended to those rows. All that you have to do is enter a formula once—you don't need to use the Fill or Copy commands.
Automatic AutoFiltering AutoFilter is turned on by default in a table to enable powerful sorting and filtering of table data.
Structured references This type of reference allows you to use table column header names in formulas instead of cell references, such as A1 or R1C1.
Total rows In a total row, you can now use custom formulas and text entries.
Table styles You can apply a table style to quickly add designer-quality, professional formatting to tables. If an alternate-row style is enabled on a table, Excel will maintain the alternating style rule through actions that would have traditionally disrupted this layout, such as filtering, hiding rows, or manual rearranging of rows and columns.
New look for charts
In Office Excel 2007, you can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.
The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart.
Visual chart element pickers Besides the quick layouts and quick formats, you can now use the new user interface to quickly change every element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements.
A modern look with OfficeArt Because charts in Office Excel 2007 are drawn with OfficeArt, almost anything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or bevel effect to make an element stand out or use transparency to make elements visible that are partially obscured in a chart layout. You can also use realistic 3-D effects.
Clear lines and fonts Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability.
More colors than ever You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog.
Chart templates Saving your favorite charts as a chart template is much easier in the new user interface.
Using Excel charts in other programs In 2007 Office release, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Because an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.
Copying charts to other programs Charts can be easily copied and pasted between documents or from one program to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.
Animating charts in PowerPoint In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and you have a lot more control. For example, you can make changes to individual animation steps, and use more animation effects.
In Office Excel 2007, PivotTables are much easier to use than in earlier versions of Excel. By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away—you no longer have to drag data to drop zones that aren't always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.
And after you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.
Using Undo in PivotTables You can now undo most actions that you take to create or rearrange a PivotTable.
Plus and minus drill-down indicators These indicators are used to indicate whether you can expand or collapse parts of the PivotTable to see more or less information.
Sorting and filtering Sorting is now as simple as selecting an item in the column that you want to sort and using sort buttons. You can filter data by using PivotTable filters, such as date filters, label filters, value filters, or manual filters.
Conditional formatting You can apply conditional formatting to an Office Excel 2007 Pivot Table by cell or by intersection of cells.
PivotTable style and layout Just like you can for Excel tables and charts, you can quickly apply a predefined or custom style to a PivotTable. And changing the layout of a PivotTable is also much easier to do in the new user interface.
PivotCharts Like PivotTables, PivotCharts are much easier to create in the new user interface. All of the filtering improvements are also available for PivotCharts. When you create a PivotChart, specific PivotChart tools and context menus are available so that you can analyze the data in the chart. You can also change the layout, style, and format of the chart or its elements the same way that you can for a regular chart. In Office Excel 2007, the chart formatting that you apply is preserved when you make changes to the PivotChart, which is an improvement over the way it worked in earlier versions of Excel.
Quick connections to external data
In Office Excel 2007, you no longer need to know the server or database names of corporate data sources. Instead, you can use Quicklaunch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and makes it easier to reuse a connection or to substitute a connection with another one.
New file formats
XML-based file format In 2007 Microsoft Office system, Microsoft is introducing new file formats for Word, Excel, and PowerPoint, known as the Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Office Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Office Excel 2007 XML-based and macro-enabled file format (.xlsm), the Office Excel 2007 file format for an Excel template (.xltx), and the Office Excel 2007 macro-enabled file format for an Excel template (.xltm).
Office Excel 2007 binary file format In addition to the new XML-based file formats, Office Excel 2007 also introduces a binary version of the segmented compressed file format for large or complex workbooks. This file format, the Office Excel 2007 Binary (or BIFF12) file format (.xls), can be used for optimal performance and backward compatibility.
Compatibility with earlier versions of Excel You can check an Office Excel 2007 workbook to see if it contains features or formatting that are not compatible with an earlier version of Excel so that you can make the necessary changes for better backward compatibility. In earlier versions of Excel, you can install updates and converters that help you open an Office Excel 2007 workbook so that you can edit it, save it, and open it again in Office Excel 2007 without losing any Office Excel 2007-specific functionality or features.
Better printing experience
Page Layout View In addition to the Normal view and Page Break Preview view, Office Excel 2007 provides a Page Layout View. You can use this view to create a worksheet while keeping an eye on how it will look in printed format. In this view, you can work with page headers, footers, and margin settings right in the worksheet, and place objects, such as charts or shapes, exactly where you want them. You also have easy access to all page setup options on the Page Layout tab in the new user interface so that you can quickly specify options, such as page orientation. It's easy to see what will be printed on every page, which will help you avoid multiple printing attempts and truncated data in printouts.
Saving to PDF and XPS format
You can save as a PDF or XPS file from a 2007 Microsoft Office system program only after you install an add-in. For more information, see Enable support for other file formats, such as PDF and XPS.
New ways to share your work
Using Excel Services to share your work If you have access to Excel Services, you can use it to share your Office Excel 2007 worksheet data with other people, such as executives and other stakeholders in your organization. In Office Excel 2007, you can save a workbook to Excel Services and specify the worksheet data that you want other people to see. In a browser, they can then use Microsoft Office Excel Web Access to view, analyze, print, and extract this worksheet data. They can also create a static snapshot of the data at regular intervals or on demand. Microsoft Office Excel Web Access makes it easy to perform activities, such as scrolling, filtering, sorting, viewing charts, and using drill-down in PivotTables. You can also connect the Excel Web Access Web Part to other Web Parts to display data in alternative ways. And with the right permissions, Excel Web Access users can open a workbook in Office Excel 2007 so that they can use the full power of Excel to analyze and work with the data on their own computers if they have Excel installed.
Using this method to share your work ensures that other people have access to one version of the data in one location, which you can keep current with the latest details. If you need other people, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way that you did in earlier versions of Excel to collect the information you need before you save it to Excel Services.
Using Document Management Server Excel Services can be integrated with Document Management Server to create a validation process around new Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.
Quick access to more templates
In Office Excel 2007, you can base a new workbook on a variety of templates that are installed with Excel, or you can quickly access and download templates from the Microsoft Office Online Web site.