New 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 dropdown 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 its formatting or analyzing data, Excel presents the tools that are most useful to successfully complete that task.
Introducing the new interface
There is a new look for Office Excel 2007, a new user interface (UI) that replaces menus, toolbars, and most of the task panes from previous versions of Excel with a single mechanism that is simple and apparent. The new user interface is designed to help you be more productive in Excel, more easily find the right features for various tasks, discover new functionality, and be more efficient.
The New Interface commands hierarchy:
1. Menu Tabs.
3. Groups within each tab break a task into subtasks.
4. Command buttons (icons) in each group carry out a command or display a menu of commands.
Ribbon user interface: The primary replacement for menus and toolbars in Office Excel 2007 is the Ribbon. Designed for easy browsing, the Ribbon consists of tabs that are organized around specific scenarios or objects. The controls on each tab are further organized into several groups. The Ribbon can host richer content than menus and toolbars can, including buttons, galleries, and dialog box content.
Tabs that appear only when you need them: In addition to the standard set of tabs that you see on the Ribbon whenever you start Office Excel 2007, there are two other kinds of tabs, which appear in the interface and are useful for the type of task you are currently performing.
Contextual tools: Contextual tools enable you to work with an object that you select on the page, such as a table, a picture, or a drawing. When you click the object, the pertinent set of contextual tabs appears in an accent color next to the standard tabs.
Program tabs: Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, including Print Preview.
File Button: This button is located in the upper-left corner of the Excel window and opens the menu.
Quick Access Toolbar: The Quick Access Toolbar is located by default at the top of the Excel window and provides quick access to tools that you use frequently. You can customize the Quick Access Toolbar by adding commands to it.
Adding Commands to Quick Access Toolbar: In the Customize Quick Access Toolbar box, select either For all documents (as a default) or a specific document.
Click the command that you want to add, and then click Add.
Dialog Box Launchers: Dialog Box Launchers are small icons that appear in some groups. Clicking a Dialog Box Launcher opens a related dialog box or a task pane, providing more options related to that group.
Use the Keyboard to Access any Commands in the Ribbon
To use keyboard shortcut: To open a menu tab, press the Alt tab, now press a letter(s) or a number or a combination of a letter & a number , see below:
Step 1: press the Alt key or F10.
Press H, and then a letter(s) or a number or a combination of both (a letter & a number).
Use the Tab key to move between command buttons in the Ribbon.
Memory management, Workbook, Worksheet & Cells
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 dual-processors and multithreaded chipsets.
Numbers of Rows, Columns & Cells in a Worksheet
Excel 2007 sheet contains 1,048,576 rows by 16,384 columns, total of 17,180,033,024 cells compare to previous Excel versions which hold 65,536 rows by 256 columns, total of 16,777,216 cells.
New file formats
XML-based file format: In 2007 Microsoft Office system, Microsoft is introducing new files formats for Word, Excel, and PowerPoint, known as the Microsoft 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 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 Excel 2007 XML-based and macro-enabled file format (.xlsm), the Excel 2007 file format for an Excel template (.xltx), and the Excel 2007 macro-enabled file format for an Excel template (.xltm).
Themes, Colors & Formatting
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 applications, 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.
Number of Colors
Excel 2007 supports up to 16 million colors.
Rich conditional formatting
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.
Formulas & Functions
Easy formula writing
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.
Easy access to Named ranges: By using Name manager, you can organize, update, and manage multiple Named ranges in a central location, which helps all users who need to work on your worksheet interpret its formulas and data.
Very important and useful functions are added to Excel 2007. The functions are IFERROR, AVERAGEIF, AVERAGEIFS, SUMIFS and COUNTIFS. Read more and see example in Chapter 9, page 155.
New OLAP formulas and cube functions
When you work with multidimensional databases (such as SQL Server Analysis Services) in 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.
A New look of charts
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: Beside the quick layouts and quick formats, you can now use the new user interface to quickly change any 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: Since charts in Excel 2007 are drawn with OfficeArt, almost everything 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 a bevel effect to make an element to stand out or use transparency to make elements visible that are partially hidden 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 box.
Chart templates: Saving your favorite charts as a chart template is much easier in the new user interface.
Using Excel charts in other applications: In Excel 2007, 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. Since 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 applications: Charts can be easily copied and pasted between documents or from one application 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 give you much more control. For example, you can make changes to individual animation steps, and use more animation effects.
Sorting, Filtering & Tables
Improved sorting and filtering
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 dropdown list, select multiple items to filter, and filter data in PivotTables.
Excel table enhancements
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 its much easier to work with.
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 arent always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.
After you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.
Sharing & Connections
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 users, such as executives and other stakeholders in your organization. In 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 (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), 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. 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 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 users have access to one version of the data in one location, which you can keep current with the latest details. If you need other users, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way.
Quick connections to external data
You no longer need to know the server or database names of corporate data sources. Instead, you can use Quick Launch 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 make it easier to reuse a connection or to substitute a connection with another user.
Better printing experience
Page Layout View: In addition to the Normal view and Page Break Preview view, 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 to be. 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. Its 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: Like other 2007 Office release applications, Excel 2007 supports saving a workbook to a high-fidelity fixed file format, such as Portable Document Format (PDF) or XML Paper Specification (XPS) format, that encapsulates how it will look when it is printed. This allows you to share the content of your workbook in a format that is easy for other people to view online or print, without including the underlying formulas, external data queries, or comments.
Actually, this is what 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.