Microsoft 065-04940 - Office Excel 2007 User manual

Microsoft Office Excel 2007
September 2006

www.microsoft.com/office/excel
i
Table of Contents
MICROSOFT OFFICE EXCEL 2007 .........................................................................I
OVERVIEW OF MICROSOFT OFFICE EXCEL 2007................................................. 1
Create Better Spreadsheets ..................................................................................1
Improve Spreadsheet Analysis ..............................................................................2
Share Information with Customers and Partners......................................................3
Manage Information More Effectively .....................................................................4
CREATE BETTER SPREADSHEETS ........................................................................ 5
Enhanced Interface with More Room to Work.............................................................5
Microsoft Office Fluent User Interface.....................................................................5
Increased Spreadsheet Row and Column Capacity ...................................................6
Faster Performance..............................................................................................6
Support for Tables..................................................................................................6
Enhanced Table Tools ..........................................................................................6
New Formulas and Range Management Tools..........................................................8
Professional Charting Tools......................................................................................9
Quick Formatting Tools for Faster Results ...............................................................9
Dramatic Visual Effects in Charts ...........................................................................9
Shared Charting Engine Across the Microsoft Office System ......................................9
Custom Data Output Tools ....................................................................................10
Faster Formatting with Cell Styles ....................................................................... 10
Editing Report Headers and Footers ..................................................................... 11
Improved Printing Experience ............................................................................. 11
Customization with Microsoft Office Art and Styles................................................. 11
IMPROVE SPREADSHEET ANALYSIS ................................................................. 13
Rich Data Visualization with Conditional Formatting .................................................. 13
Rules Manager ..................................................................................................14
Improved Sorting and Filtering...............................................................................15
Easy-to-Use PivotTable and PivotChart Views........................................................... 17
Accessible and Visually Enhanced Tools ................................................................ 17

www.microsoft.com/office/excel
ii
Quick Access to Current Business Data ................................................................... 19
SQL Server 2005 Analysis Services......................................................................19
Role of PivotTable and PivotChart Views in SQL Server 2005 Analysis Services.......... 20
Benefits of the Field List ..................................................................................... 20
SHARE INFORMATION WITH CUSTOMERS AND PARTNERS............................... 22
Accessible, Browser-Based Data............................................................................. 22
Dynamically Render a Spreadsheet in HTML.......................................................... 22
High Fidelity Between the Excel Client and a Spreadsheet....................................... 23
Dashboards Created from Spreadsheets ............................................................... 23
Excel Services API .............................................................................................24
Support for New File Formats................................................................................. 24
Office XML Formats............................................................................................ 24
Support for XPS and PDF....................................................................................24
MANAGE INFORMATION MORE EFFECTIVELY ................................................... 25
Server-Based Control............................................................................................25
Data Connection Library ..................................................................................... 25
Report Management Features ............................................................................. 26
Publish to Office SharePoint Server 2007.............................................................. 27
Create a Review or Approval Workflow .................................................................28
Content Management Options ................................................................................29
Finishing Documents.......................................................................................... 30
Information Rights Management..........................................................................30
Records Management......................................................................................... 30
Information Management Policies ........................................................................31
Enabling Content Policies.................................................................................... 31
SUMMARY......................................................................................................... 32
RESOURCES...................................................................................................... 33
SYSTEM REQUIREMENTS .................................................................................. 34

www.microsoft.com/office/excel
1
Overview of Microsoft Office Excel 2007
Welcome to Microsoft®Office Excel®2007 spreadsheet software, included in the 2007
release of the Microsoft Office system. The Microsoft Office system, an integrated system
of programs, servers, and services for information workers, provides an even more
accessible and comprehensive set of tools for people who rely on working with
information efficiently and productively.
Office Excel 2007 is a powerful tool to create and format spreadsheets and to analyze
and share information to make more informed decisions. With the Microsoft Office
Fluent™ user interface, it’s easier to create and use rich data visualization, PivotTable®
views, and professional-looking charts. When Office Excel 2007 is used with Microsoft
Office SharePoint®Server 2007, you can take advantage of Excel Services—a new,
server-based version of Excel that is optimized for managing, consuming, and sharing
spreadsheets.
Ecma Office Open XML Formats reside at the heart of the new Microsoft Office system. A
compact and robust file format, Office XML Formats allow for better data integration
between documents and back-end systems. Also, with Excel XML Formats, you can
reduce file sizes, improve data recovery, and increase the interoperability of your
spreadsheets with other data sources such as line-of-business systems.
Create Better Spreadsheets
The Office Fluent user interface and new and improved capabilities makes it easier and
faster to create effective spreadsheets that are ready to be shared or printed.
New! The Office Fluent user interface presents the right tools when you need
them most.
New! Increased spreadsheet row and column capacity of one million rows
by 16,000 columns enables you to import and work with massive amounts of data
and achieve faster calculation performance with support for dual or multi-
core processors.

www.microsoft.com/office/excel
2
Improved! Support for working with tables of data helps you create and
format a table with a rich table styles gallery. Column headings remain in view as
you scroll, and auto-fill populates and expands any table automatically.
New! Fast formatting with Cell Styles helps you assemble a great-looking
report faster, with fewer clicks.
Improved! Formulas authoring experience includes a resizable formula bar
and context-based Formula AutoComplete—so that you can write the proper
formula syntax the first time, every time. You can also refer to named ranges and
tables within formulas and functions.
New! Chart Formatting tools help you create and format professional-looking
charts with greater ease using fewer clicks.
New! Use dramatic visual effects in charts such as 3-D, soft shadowing, anti-
aliasing, and glow.
New! Shared diagramming and charting with Microsoft Office Word 2007 and
Microsoft Office PowerPoint®2007 presentation graphics program help ensure a
consistent look across your spreadsheets, documents, and presentations.
New! Page Layout View for a better printing experience helps ensure that
what you see on the screen is what you’ll get in printed output and that any
headers and footers you add or edit are displayed in plain view.
Improved! Customization with Microsoft Office art, predefined styles, and
integrated graphics help you apply a consistent, professional look to your
spreadsheets that can easily be placed in other Microsoft Office system
documents.
Improve Spreadsheet Analysis
New data analysis and visualization tools help you analyze information, spot trends, and
access your company information more easily.
New! Conditional formatting with rich data visualization schemes helps
you set conditions more easily to discover trends or patterns in your information.

www.microsoft.com/office/excel
3
Improved! Sorting and filtering helps you find the information you need,
quickly and easily. Office Excel 2007 supports up to 64 levels to sort data,
including the ability to sort by color.
Improved! Create a PivotTable or PivotChart®more easily by using data
fields to reorient data quickly and find the answers you need.
New! Full support for Microsoft SQL Server™2005 Analysis Services
enables you to query your most current business data by using the flexibility of
Office Excel 2007.
Share Information with Customers and Partners
With Excel Services, you can share sensitive business information more broadly and
securely with coworkers, customers, and business partners.
New! Dynamically render a spreadsheet in HTML and then permit others to
view only the parts of a spreadsheet you want them to see by using their Web
browser.
New! High fidelity between the Office Excel 2007 client and spreadsheets
that are rendered using Excel Services helps you navigate, sort, filter, and interact
with tables or PivotTable views from a Web browser.
New! Business dashboards can be easily created from spreadsheets to
track key performance indicators (KPIs), and then you can share these broadly
through a Web browser.
New! Support for PDF and XML Paper Specification (XPS) file formats helps
you convert your spreadsheets into a fixed file format for easier sharing.*
*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 Install and use a PDF or XPS add-in.

www.microsoft.com/office/excel
4
Manage Information More Effectively
Use Excel Services and XML file format to manage and control important business
information more effectively.
New! With the Data Connection Library you can import external data into a
spreadsheet by using preconfigured external sources of business information.
New! Report management features help protect confidential business
information while ensuring people can view the data they need.
New! Publish to Office SharePoint Server 2007 to help prevent the spread of
multiple copies of business-critical spreadsheets throughout your organization,
protect sensitive business information, and ensure people are working with the
most current data.
New! Excel Services application programming interface (API) enables
developers to use the Office Excel 2007 powerful calculation engine in other
applications, such as a Web-based mortgage calculator or an options pricing
model.
New! Office XML Formats reduce file sizes, improve data recovery, and make
greater data interoperability possible.
New! Permissions-based control helps you manage who can access, view, and
make changes to spreadsheets stored on the server.
Office Excel 2007 in Action
Sylvie Narp is the Director of Finance for Fabrikam, Inc., a manufacturing company. With
15 direct reports, each responsible for a different section of the business, she needs
comprehensive, real-time visibility into the company’s financial data. Her employees need
a robust yet flexible tool for analyzing data to make more informed decisions. Office
Excel 2007 offers a comprehensive solution.

www.microsoft.com/office/excel
5
Create Better Spreadsheets
Organizing and working with large amounts of data can be a challenging task. In
previous versions of Excel, row and column capacity limits made it difficult to analyze
large amounts of data. Some of the most advanced tools and capabilities were contained
in menus and sub-menus, forcing users to find these commands on their own. Creating
and working with tables is one of the most fundamental reasons people use Excel, yet
using tables could be challenging. Beyond simply creating or formatting a table, there
was no way to refer to the table data intelligently. Building and formatting charts was
“click-intensive,” and even the most common tasks, like changing a chart’s title, could be
difficult. When you were ready to print your spreadsheet, it was difficult to know what
the output would look like before doing so. Furthermore, it was hard to know where a
page break might occur, which led to multiple, frustrating printing attempts that wasted
resources.
Office Excel 2007 addresses these challenges by helping you format business information
clearly and easily. Professional-looking reports and charts are now simple to create, so
you do not have to depend on an external design firm to get the look you want. When
you are ready to print, the Office Excel 2007 Page Layout View helps to ensure better
printed results.
Enhanced Interface with More Room to Work
Office Excel 2007 takes advantage of the Office Fluent user interface to make powerful
productivity tools easily accessible. It also offers more room to work and delivers faster
performance.
Microsoft Office Fluent User Interface
With the Office Fluent user interface of the Microsoft Office system, Office Excel 2007
presents the appropriate tools at the right time so that you can produce your best work
more quickly. Important features are organized and presented in context. For example,
when you are writing a formula to calculate a value, Office Excel 2007 displays the
Formulae tab and toolset to help you in your selection. Or, when you are working with a

www.microsoft.com/office/excel
6
table and you need help choosing the right format, the Office Excel 2007 intuitive table
galleries show you how a table will look before you make your final selection.
Increased Spreadsheet Row and Column Capacity
Office Excel 2007 delivers greater spreadsheet capacity—1 million rows by 16,000
columns—to facilitate working with massive amounts of data. You no longer have to use
many different spreadsheets to support your data.
To support a bigger grid, Office Excel 2007 increases several other limits. For example,
the number of rows allowed in a PivotTable chart increased from 64,000 to over 1 million,
and the amount of memory that Excel can use increased from 1 GB to the maximum
allowed by Microsoft Windows®, while the limit on the number of rows of a column or
columns that can be referred to in an array formula was completely eliminated.
Faster Performance
To a large number of customers, the calculation speed of Excel is extremely important—
perhaps the most important “feature.” Calculations in large, formula-intensive
spreadsheets are now faster than before with the Office Excel 2007 support for dual
processors. Office Excel 2007 spots formulas that can be calculated concurrently, and
then runs those formulas on multiple processors simultaneously (this capability is called
“multithreaded calculation”). The net effect is that a given spreadsheet finishes
calculating in less time, improving overall calculation performance.
Support for Tables
Office Excel 2007 helps you quickly construct, format, and expand tables using the
Ribbon and the table gallery.
Enhanced Table Tools
It is now easier to create tables. Enhancements in Office Excel 2007 help you assemble
data into a table with just a few clicks. When you need to expand the table to
accommodate more data, Office Excel 2007 quickly expands the table using the proper
format.

www.microsoft.com/office/excel
7
Simply click in the range of cells you want to format as a table, click Format as Table in
the Ribbon (on the Home tab), and select your favorite table style. Office Excel 2007
applies the table style to your range of cells and automatically labels column headings,
creates AutoFilters, and displays other relevant tools. Column headers stay in view when
you scroll through large tables, making it easier for your team members, auditors, or
customers to follow your analysis more easily.
Figure 22: Creating a table
You can quickly modify the format and appearance of a table by using the Table Gallery
feature in Office Excel 2007. Some of the designer-quality formatting options include
banded rows and columns for identifying key data or highlighting the last column of a
table. You can see a live preview of your selection before making the final changes.

www.microsoft.com/office/excel
8
Figure 23: Table tools
Office Excel 2007 in Action
Susan is one of Sylvie’s analysts. She is preparing a sales performance report for senior
management. Even though she is working with a large amount of data, she can easily
assemble the data into a table in just a few clicks. She can change the look of the table
quickly by using the Table Gallery, previewing her selection before she chooses the final
look. When she needs to write formulae on data from the table, Office Excel 2007
provides an intelligent way to reference this information by column heading name—
instead of an arbitrary cell reference. This helps Susan to write her formula more easily
and permits others to follow the logic of her spreadsheet when she shares it.
New Formulas and Range Management Tools
Office Excel 2007 offers robust tools for working with formulas and ranges, including:
A resizable formula bar that prevents long, complex formulae from spilling over
onto your spreadsheet.

www.microsoft.com/office/excel
9
Formula AutoComplete, which helps you write the proper formula syntax the first
time—every time.
Structured referencing that helps you refer to tables and ranges while writing
formulas.
Name Manager, which helps you organize, update, and manage multiple name
ranges from a central location.
Professional Charting Tools
Whether you are compiling a report for auditors or charting internal business data, Office
Excel 2007 simplifies the process and helps you achieve more professional-looking
results.
Quick Formatting Tools for Faster Results
The Office Fluent user interface offers charting tools to help you:
Create professional-looking charts in just a few clicks.
Format your chart with the Chart Layout and Chart Styles galleries, or manually
format each component, such as axes, titles, and other chart labels.
Dramatic Visual Effects in Charts
Adding special effects to your charts can help you communicate your analysis more
clearly. You can use stunning effects such as 3-D, soft shadowing, and anti-aliasing to
help identify key data trends and create more compelling graphical summaries.
Shared Charting Engine Across the Microsoft Office System
The improved charting capabilities in Office Excel 2007 are shared across the Microsoft
Office system, which means that you can create and interact with charts the same way—
regardless of the program you are using. For example, if you want to add a chart to a
report in Office Word 2007, you can take advantage of the same tools you would use in
Office Excel 2007 to modify and format the chart.

www.microsoft.com/office/excel
10
Figure 24: Charting tools
Office Excel 2007 in Action
Office Excel 2007 provides the tools to conduct sophisticated data analysis and convey
that information in easy-to-read charts. When Carol Philips, Fabrikam Inc.’s senior
product marketing manager, needs some charts for an important investor presentation,
she works with Sylvie and Susan. Susan imports key financial data from the previous
fiscal year into a spreadsheet, highlights relevant sections, and displays the results in a
series of compelling, three-dimensional charts that contain shadowing and other rich
effects. All of the charting tools are clearly visible from the Office Fluent user interface,
so the entire process takes far less time. Susan does not need to navigate through
different toolbars or dialog boxes to label her chart title or axes properly.
Custom Data Output Tools
Office Excel 2007 offers new tools for creating custom effects and styles. This feature,
together with tools that you can use to view a spreadsheet exactly as it appears in print
format, helps workers of all skill levels to produce professional-looking spreadsheets.
Faster Formatting with Cell Styles
Cell Styles offer visual previews of how a cell might look with format enhancements, so
you can modify the look of a report with fewer clicks.

www.microsoft.com/office/excel
11
Editing Report Headers and Footers
By using the new Page Layout View that displays headers and footers in plain view, you
can quickly change the title of your report or add additional details at the bottom of each
page in the footer.
Improved Printing Experience
With the new Page Layout View, you can see exactly how a spreadsheet will print. By
changing the spreadsheet’s orientation from Portrait to Landscape, you might prevent
your spreadsheet from being truncated when you print.
Figure 25: Page Layout View
Customization with Microsoft Office Art and Styles
Customization with Microsoft Office art, predefined styles, and integrated graphics help
you quickly apply a consistent, professional look to your spreadsheets that can easily be
placed in other Microsoft Office system documents. You can apply these or other

www.microsoft.com/office/excel
12
predefined styles to any spreadsheet.

www.microsoft.com/office/excel
13
Improve Spreadsheet Analysis
When working with large amount of data, people usually struggle to discover patterns or
trends or to reorder the data to make more informed decisions. PivotTable views—a
powerful tool for reorienting large amounts of data to answer different questions—were
difficult to create, putting them out of reach for all but the most advanced Excel users.
The enhancements in Office Excel 2007 address these challenges. Office Excel 2007
offers powerful analytical tools to help you discern trends from complex data. By
analyzing information effectively, you can gain business insight and make more informed
decisions.
Rich Data Visualization with Conditional Formatting
You can now discover and illustrate important trends and highlight exceptions in your
data by using conditional formatting with rich visualization schemes. You can apply
conditional formatting more easily and identify trends by using colored gradients, data
bars, and icons.
Greater Than…and Less Than…:Enables you to create value-based rules like
Greater Than, Less Than, Between, Equal To, or Text That Contains.
Top/bottom rules: Enable you to format differently the top/bottom x (or x%)
items.
Data Bars: By default, when you apply data bars with one click via the Ribbon,
Excel uses the highest and lowest values in the range to draw the shortest and
longest bar. You can further tweak the color of the data bar or the way Excel
computes the values for the shortest and longest bar by selecting More Options.
Color Scales: Enables you to create a two-color or three-color scale rule using
default colors. When you apply color scales with one click, Excel uses the lowest,
highest, and midpoint values in the range to determine the color scales. You can
change the colors or the way Excel determines the values for the highest, lowest,
or midpoint values by selecting More Options.

www.microsoft.com/office/excel
14
Icon Sets:Enables you to create a conditional formatting rule with three-icon,
four-icon, or five-icon icon sets of your choice. The most commonly used icons are
exposed here; by default, Excel uses percentiles to calculate the break between
icon sets. You can change the icons or the way Excel determines the values for the
icon sets, as well as pick from further icon sets, by selecting More Options.
Figure 26: Conditional formatting icon sets
Rules Manager
Use the Rules Manager to work with conditional formatting when you need to:

www.microsoft.com/office/excel
15
View existing rules. You can view the rule condition, the range it is applied to, and
the format set on the rule. The Show Formatting Rules for drop-down menu at
the top of the dialog box enables you to change the scope of the rules you are
looking at. You can view rules for the selected cells, the current worksheet, the
current table, other worksheets in the book, other tables in the workbook, or the
entire workbook.
Add new rules via the New Rule button.
Change the order in which rules are evaluated. This is done using the UP ARROW
and DOWN ARROW keys.
Edit existing rules via the Edit Rule button. The Edit Rule UI is the same as the
New Rule UI.
Delete rules via the Delete Rule button.
Control whether more than one rule can evaluate to true.
Improved Sorting and Filtering
Sorting and filtering are two of the most important types of basic analysis that you can
do with data. New options for sorting and filtering make Office Excel 2007 the ideal tool
for working with large amounts of complex data. You can:
Select any number of items for filter conditions with multi-select in AutoFilters.
Sort or filter by the background color of cells.
Filter by applying “quick filters” for specific data types (for example, filter by
quarter, month, or year for a date column).
Reapply a set of filters with one button click.
Manage up to 64 sorting levels from a central location.

www.microsoft.com/office/excel
16
Figure 27: Enhanced filtering and sorting

www.microsoft.com/office/excel
17
Office Excel 2007 in Action
After organizing her data into a table, Susan is still having difficulty perceiving how the
numbers are trending. By using conditional formatting, she can illustrate these conditions
in a more intuitive way. For example, by applying a three-color gradient to Fabrikam
Inc.’s 2005 results, Susan creates a financial “heat map” that tells her the areas that
demand her attention, as the higher values are now depicted in green. She quickly
pinpoints that product sales were particularly strong in the Northeast Region. With the
ability of Office Excel 2007 to sort by color, Susan can better identify the states in which
the highest sales volume occurred, and then share this insight with Sylvie during their
next meeting.
Easy-to-Use PivotTable and PivotChart Views
Office Excel 2007 offers new tools for creating and working with PivotTable views and
PivotChart views that make it easy to ask multiple questions about a given data set. With
enhanced context menus, contextual tool tips, and new layout and design options, these
features can expose this advanced tool to even more people.
Accessible and Visually Enhanced Tools
Information workers of varying skill levels can now create a PivotTable or PivotChart in a
few simple steps. Whether you are working with local data or accessing back-end data
(such as a SQL Server 2005 Analysis Services cube), all you need to decide is which
fields to display as rows, which fields to display as columns, and which fields to display as
values and in what form (values can be displayed as sum, averages, or other
calculations).