About spreadsheets

You can use a spreadsheet to enter and calculate data. A spreadsheet consists of columns and rows of cells. You can enter data directly into the cells of the spreadsheet and perform calculations that are based on the data by using formulas. You can make changes to the data and see the results of calculations automatically. You can also sort, filter, and find data in a spreadsheet. The following example shows a spreadsheet on a Web page designed to be used as a mortgage calculator. When you change the values in the bordered input cells in the browser, the spreadsheet calculates the mortgage payment.

Spreadsheet on a Web page

Show Browser and license requirements

To view and work with an interactive spreadsheet in the browser, you must have the Microsoft Office Web Components installed, a valid Microsoft Office 2003 license, and Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later. Without a proper license, you might be able to view the components but not interact with them. See your network or computer administrator to see if your computer meets the necessary requirements to interact with a spreadsheet.

Show Saving changes

Changes you make in the spreadsheet are valid only for the current browser session. If you want to save changes you've made to data on a spreadsheet, you can export the data to a Microsoft Excel worksheet and save it there.

Show Disabled features

Some items in your spreadsheet may have been protected by the designer of the Web page. For instance, the designer may have locked certain cells, disabled sorting and filtering, disabled the toolbar from being displayed, or disabled display of the Commands and Options dialog box. If you have problems accessing these features, and your computer meets browser and license requirements, contact the designer of the Web page.

Show About the Spreadsheet Web Part

In addition to using the Spreadsheet component on a standard Web Page, if you have access to a server running Microsoft Windows SharePoint Services, you can use the spreadsheet as the Spreadsheet Web Part on a Web Part page. Before a Windows SharePoint Services site can support Spreadsheet Web Parts, the site administrator must install the Office 2003 Web Parts and Components on that site. Office 2003 Web Parts and Components is available from Downloads on Microsoft Office Online. This installation needs to be performed only once to support any number of Spreadsheet Web Parts on that site.

Spreadsheet Web Part

The Spreadsheet Web Part supports all of the features of the spreadsheet component, and can also support additional features specific to the Web Part, such as the ability to create Web Part connections that allow the Web Part to send or receive data from the selected row to other Web Parts.

Note The Spreadsheet Web Part has the same license requirements as the spreadsheet component, and requires Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

For more information about the Spreadsheet Web Part, see the following help topics in Microsoft Office Excel 2003 Help: "About the Spreadsheet Web Part," "Create a data-bound Spreadsheet Web Part," and "Create a connectable Spreadsheet Web Part." In the Table of Contents, click Excel and the Web and then click Use the Spreadsheet Web Part.


About XML lists

When a user or the designer of a Spreadsheet component queries and imports data from a data source using a data retrieval service or a Data Retrieval Service Connections file (.uxdc), the data is displayed in a table structure that is called an XML list. An XML list behaves much like a datasheet in a database program: each row in the list is treated as a single record when adding, deleting, filtering, and sorting items in the list. An XML list is associated with a query that can be refreshed to display current information from the data source. You can also edit this query to provide different query parameter values, select different fields, filter data, and sort data so you can efficiently retrieve just the subset of data you want.

An XML List

An XML list has the following visual features that distinguish it from other ranges in a spreadsheet.

  • A frame clearly outlines the data in the XML list. Data inside the frame is returned from the data source; data outside the frame is not part of the data returned from the data source.
  • The XML list can display column headings that are not part of the data retrieved from the data source, so your headings never get sorted with the data or excluded when the data is filtered.
  • The XML list displays a new row at the bottom of the list called the insert row to make adding a new record simple: just click in the insert row and type.

If the designer has not locked the entire XML list or disabled other features of the XML list, working with data in an XML list behaves as described in the following sections.

Show Adding records

By default, when you click any of the cells within an XML list, a blank row with a blue asterisk character in the leftmost cell is displayed at the end of the list. This row is called the insert row. You use the insert row to add new rows to an XML list. The following rules and behaviors apply when adding records using the insert row.

  • You can type values in any of the unlocked columns in the insert row, which automatically displays a new insert row below the row in which you are currently typing.
  • When you right-click in any row other than the first row in an XML list, point to Insert, and then click Row, a new row is not inserted. Instead, the selection moves to the insert row. If you right-click in the first row, point to Insert, and then click Row, a new row will be inserted above the XML list.
  • If the page designer has applied formatting to a column in an XML list, the same formatting is applied to all values in that column when you add a new record to an XML list.
  • When a row is added to an XML list, any values in the spreadsheet below the list are shifted down by one row.

Show Pasting records

When pasting cells into the insert row, the following rules apply.

  • You can paste multiple rows of data.
  • If the number of columns pasted is greater than the number in the XML list, the extra columns are pasted to the right of XML list, but do not become part of the list.
  • If validation is defined for any of the columns, and validation fails for any of the pasted values, the entire paste operation will fail. An error message displays the first value that failed validation.

Show Deleting records

The following rules and behaviors apply when deleting rows in an XML list.

  • When you select one or more rows other than the insert row in an XML list, right-click, point to Delete, and then click Row, the selected rows are deleted, and all rows below it— as well as any values below the list— are shifted up.
  • You cannot delete the insert row.

Show Sorting records

Sorting XML lists uses the same commands and toolbar buttons as sorting other data in a spreadsheet, but the following rules apply.

  • It is not possible to sort a subset of an XML list, regardless of how cells are selected within the list. When sorting, entire rows (records) within the list are sorted, and all rows in the list are sorted.
  • The column names in the drop-down lists for the Sort Ascending and Sort Descending buttons on the toolbar come from the values in cells in the row immediately above the first row in an XML list. If the cells in the row immediately above the list do not have values, the labels Column 1 (column letter), Column 2 (column letter), and so on, are used.
  • If selection extends outside of the XML list, sorting is disabled.

Show Filtering records

Filtering values in an XML list by using the AutoFilter button on the toolbar works the same as filtering any other data in a spreadsheet, except that filtering in an XML list filters entire list rows and always applies to all rows in the list. The AutoFilter arrows are displayed in the row immediately above the XML list, unless the list begins on row 1, in which case, the filter arrows are displayed in the first row of the XML list data. For this reason, if the XML list does not have a header row, it is easier to filter data if you insert a row above the XML List and add column headings.

Show Inserting and deleting columns

You cannot insert or delete columns within an XML list. However, if your selection is in the first (leftmost) column of an XML list, you can insert a column to the left (outside) of the list.

Show Calculated columns

The designer of a Spreadsheet component containing an XML list can create calculated columns within the list that are not part of the data source bound to the XML list. For example, if the data source has two columns that contain an item's unit price and the number of items, the designer could add a calculated column to the XML list to calculate the extended price. The formula in a calculated column is applied to any new rows that are added using the insert row.

When using an XML list, you can add formulas to cells in the insert row, but your formula is not applied to each new row, and will not be saved when using the Save command, or after refreshing data from the external data source.

Show Working with the associated query

You can work with the query associated with an XML list by right-clicking the list, and then by using the commands on the External Data submenu. The following table describes these commands.

Command | Description
Refresh Data | Requeries data by using the query associated with the XML list to display current information from the data source.
Save Data | If the definition of the query and data source supports updating the data source, this command updates data in the data source with information from the XML list. If the query does not support updates, this command is disabled. Note The Data Connection Wizard does not support creating update queries.
Edit Query | Displays the Data Connection Wizard with the Select Fields dialog box displayed to let you select different fields to display. You can click Back to change different settings in the query definition such as selecting the data object to work with, and to specify query parameter values. You can click Next to sort data, specify filter criteria, and to limit the number of rows returned. The settings available to you in the Data Connection Wizard will vary depending on the data source you are working with. When you are finished editing the query, click Finish.
Delete Query | Deletes the XML list and its associated query from the spreadsheet. Important Using this command displays a message asking you to confirm that you want to delete the query. If you answer Yes, you cannot undo this command. If the layout and format of the spreadsheet contain the definition of the XML list, you can refresh the page to display the XML list again.


Browser support for spreadsheets

To work with interactive spreadsheets, you must have Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later, the Microsoft Office Web Components installed, and an appropriate Microsoft Office 2003 license. If you do not have an Office 2003 license, you can install the components and view them on a Web page, but you cannot interact with them.

Note The Spreadsheet Web Part has the same license requirements as the spreadsheet component. For more information about the Spreadsheet Web Part, see "About the Spreadsheet Web Part" in About spreadsheets.


About Microsoft Office Web Components

Microsoft Office Web Components is a set of ActiveX controls installed with Microsoft Office 2003. With Office Web Components installed on your computer, you can interactively use a spreadsheet, a PivotTable list, and a chart on a Web page in Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later.

If Office Web Components is not installed on your computer but you have an Office 2003 site license that permits intranet distribution, you can configure the components so that users will be prompted to download Office Web Components from your corporate intranet. This allows users at your site to use a data access page without having Office 2003 software installed on their computers.

For information about how to configure Office Web Components, see the Microsoft Office Resource Kit Web Site.


About View-only mode for Office Web Components

If you do not have a Microsoft Office 2003 software license installed on your computer or accessed via a Microsoft Office 2003 application, you can view a Microsoft Office Web Component— a Spreadsheet Component, Chart Component, or PivotTable Component— on a Web page, but you can't interact with it.

To be able to interact with and use full functionality of the component you are viewing, you must install Microsoft Office 2003 or have access to an Office 2003 license. If you are in a corporate or group environment, check with your network administrator for assistance.

When you are in View-only mode, you can:

  • View data.
  • Print.
  • Select and scroll.
  • Use hyperlinks.
  • Resize a component.
  • Select sheets in the Spreadsheet Component.
  • Expand or collapse members in a PivotTable list.
  • Refresh data in a PivotTable list.
  • Use the About, Help, and Refresh commands. Help topics that deal with interactivity (such as formatting cells, dragging fields, sorting, filtering, and so on) do not apply.

All commands except About, Help, and Refresh are unavailable in View-only mode.


About accessing a data source on a different domain

The spreadsheet attempted to access data on another domain, but failed due to one of the following reasons:

  • The site being accessed by the control is not included in the list of trusted sites.
  • In Internet Explorer, on the Security Settings dialog box, under Miscellaneous, Access data sources across domains is either set to Disable, or set to Prompt and when prompted, you clicked Cancel.

To enable access across domains, do the following:

Show Enable prompting

  1. In Internet Explorer, on the Tools menu, click Internet Options. On the Security tab, select the appropriate Web content zone, and click Custom Level. Under Miscellaneous, set Access data sources across domains to Prompt.
  2. When prompted, if you trust the data provider, click OK to allow the PivotTable list to access data from the specified domain.

Show Add the site to the list of trusted sites

If the site being accessed is always trustworthy, add the site to the list of trusted sites.

  1. In Internet Explorer, on the Tools menu, click Internet Options. On the Security tab, select Trusted sites as the Web content zone, and click Sites.
  2. In the Trusted sites dialog box, enter the site details, and click OK.


About getting design help for spreadsheets

Some of the content in this topic may not be applicable to some languages.

When you view and interact with a spreadsheet in your Web browser, you are working with a component that was created in another program called a design program. In a design program such as Microsoft FrontPage or data access pages in Microsoft Access, you can add the data that you want in the spreadsheet, add the spreadsheet to a Web page you're designing, and publish the Web page to a Web server. Help about designing a spreadsheet is available from within the design program.

To learn more about designing a spreadsheet, first choose a design program to work in. You can start in any of the supported design programs, and then later work on the same spreadsheet in a different design program if you need different design features.

Show Getting help in design programs

If you have a Microsoft Office 2003 license and the Microsoft Office Web Components installed, you can create and modify spreadsheets in several programs. You can create the spreadsheet in one program, and later use a different program to modify the spreadsheet or the Web page that it's on.

The choice of which design program you use depends on the amount of design or administrative work you want to do on the Web page itself, the type of data you want to use, and your own familiarity with different design programs.

Microsoft Excel If you want to try different formulas, adjust references, import data from other sources, or design an interactive chart to reflect the data in your spreadsheet, Excel is an excellent design environment to use to create spreadsheets.

Microsoft FrontPage If you want to create a Web page or modify spreadsheets published from Excel, FrontPage is the recommended design program. You can create spreadsheets on Web pages in FrontPage in addition to modifying spreadsheets that were designed in other programs. Use the extensive Web page editing and administrative features of FrontPage to add controls, graphics, themes, and more to your Web page, and then make the page part of your Web site. FrontPage is available in Office 2003.

Microsoft Access If you want to create Web pages that operate on and display data from databases, and your users have Internet Explorer 5.01 SP2 or later, use data access page Design view in Access to create spreadsheets. You can use Access to establish a connection from an Access database to the Web page, and then you can use formulas in the spreadsheet to return values from the database to the spreadsheet. Access is available in Office 2003.

Microsoft Visual Basic If you need a more comprehensive programming environment than the scripting capabilities provided by Excel, FrontPage, and Access, you can design spreadsheets in Microsoft Visual Basic. Use Visual Basic when you want to include a spreadsheet or calculation engine in a custom solution. Users can view and interact with spreadsheets on the Visual Basic forms in your programs. Instead of a browser, your form becomes the run-time environment, and your program can automate many spreadsheet or calculation features.

When you are working in a design program, you can get help in the following ways:

  • Design program Help For information about the commands and features available in the design program that you can use to work with spreadsheets, see Help in the design program. For example, in Microsoft FrontPage, click Microsoft FrontPage Help Button image.

    Microsoft Excel provides both general Help about saving Excel data for use on Web pages, and specific information about saving spreadsheets for use on Web pages. To access this Help in Excel, click Microsoft Excel Help Button image.

  • Spreadsheet design-time Help If you have access to the Commands and Options dialog box in another design program, you have access to design-time Help topics. In a spreadsheet that you have created or activated in a design program, click Help Button image in the Commands and Options dialog box or click Help on the spreadsheet toolbar.

Show Getting help with writing scripts and programs that use spreadsheets

You can use scripts and programs to automate spreadsheets, or you can include spreadsheets in run-time programs other than Web browsers— programs that you develop in a programming environment such as Microsoft Visual Basic.

Writing scripts Microsoft Office programs provide the Microsoft Script Editor to help you write scripts. For example, you can use the Script Editor in Microsoft FrontPage to customize a spreadsheet. For information about running the Script Editor, see Help in your Office design program. In the Script Editor, you can display additional Help about using its features and writing scripts.

Object model Help For help developing a program or script to work with a spreadsheet, you can display information about the object model, properties, and methods specific to spreadsheets. This object model Help is installed whenever you install the Microsoft Office Web Components, of which the Spreadsheet Component is one. Depending on the design program you're using, you can access this Help in either of two ways:

  • Help in Microsoft Visual Basic In Visual Basic or Microsoft Visual Basic for Applications (VBA), you can create a reference to the Office Web Components library. You will then have access to Help through the object browser or by selecting a keyword and pressing F1. For information about creating references and using object model Help, see Help in Visual Basic or VBA.
  • Help in other environments If you are using another development environment, locate and double-click the Help file owcvba11.chm.

Show Getting help for the Spreadsheet Web Part

The Spreadsheet Web Part combines the use of several technologies, including Microsoft Windows SharePoint Services, Microsoft Office 2003, Microsoft Office Excel 2003, and Microsoft Office Web components. The Spreadsheet Web Part is in essence the Spreadsheet component hosted within a Web Part. The Help information you might need to use this Web Part is available from several different locations.

Show Getting Spreadsheet Web Part Help

The Spreadsheet Web Part Help topics are context-sensitive Help topics that answer questions you might have specific to the Web Part, such as what is its purpose, how to use it, what are the common and custom properties, how to make connections to other Web Parts, and how to troubleshoot problems.

This Help information becomes available when you install the Microsoft Office Web Parts and Components product on a Windows SharePoint Services server.

To display Spreadsheet Web Part Help information, on the Web Part menu, click Help.

Show Getting Microsoft Office Spreadsheet Component Help

The Spreadsheet Component is self-contained and can be incorporated into other Microsoft Office products and user-designed applications. The Microsoft Office Web Components contain a separate Help system designed to explain how to use them.

This is the Help information you are reading now, which becomes available when you install the Microsoft Office Web Components on your computer.

To get Office Spreadsheet Component Help information, on the Spreadsheet Component toolbar, click Help.

Show Getting Microsoft Windows SharePoint Services Help

The Windows SharePoint Services Help system provides information you may need about features related to the Spreadsheet Web Part, especially creating, using, and managing Web Part Pages.

This Help information becomes available when an administrator installs Windows SharePoint Services on a server.

To display this Help information, click Help on the top link bar.

Show Getting Data Connection Wizard Help

The Data Connection Wizard is used in several Microsoft Office applications, including the Spreadsheet component and the Spreadsheet Web Part.

This Help information becomes available when one or more Microsoft Office products are installed on your computer.

To display this Help information, see the following Help topics in any Microsoft Office product: "More about Office data connection files" and "More about data sources".

Show Getting Custom Spreadsheet Web Part Help

The Help information for creating a custom Spreadsheet Web Part is available in Microsoft Office Excel 2003.

This Help information becomes available when Office Excel 2003 is installed on your computer.

To display this Help information, see the following Help topics in Office Excel 2003 Help: "About the Spreadsheet Web Part," "Create a data-bound Spreadsheet Web Part," and "Create a connectable Spreadsheet Web Part." In the Table of Contents, click Excel and the Web and then click Use the Spreadsheet Web Part.

Show Getting help from additional Microsoft Web sites

The following Microsoft Web sites are good sources of additional, up-to-date information about using and customizing the Spreadsheet Web Part:

Microsoft Developer Network
Microsoft Office Assistance Center
Microsoft SharePoint Products and Technologies Assistance Center


Keyboard shortcuts in spreadsheets

For information about the keys to activate and deactivate the Microsoft Office Spreadsheet Component on the page, see Help in your design program.

Show Keys for displaying shortcut menus

SHIFT+F10
Display shortcut menu

Show Keys for moving and scrolling in a spreadsheet

Arrow keys
Move one cell up, down, left, or right
CTRL+arrow key
Move to the edge of the current data region
HOME
Move to the beginning of the row
CTRL+HOME
Move to the beginning of the sheet
CTRL+END
Move to the last cell on the sheet, which is the cell at the intersection of the right-most used column and the bottom-most used row (in the lower-right corner), or the cell opposite the home cell, which is typically A1
PAGE DOWN
Move down one screen
PAGE UP
Move up one screen
ALT+PAGE DOWN
Move one screen to the right
ALT+PAGE UP
Move one screen to the left
CTRL+PAGE DOWN
Move to the next sheet
CTRL+PAGE UP
Move to the previous sheet
The TAB key
Move forward between unlocked cells on a protected sheet
SHIFT+TAB
Move backward between unlocked cells on a protected sheet

Show Keys for moving with SCROLL LOCK on

SCROLL LOCK
Turn SCROLL LOCK on or off
UP ARROW or DOWN ARROW
Scroll one row up or down
LEFT ARROW or RIGHT ARROW
Scroll one column left or right

Show Keys for working with sheets

SHIFT+F11
Insert a new sheet
CTRL+PAGE DOWN
Move to the next sheet
CTRL+PAGE UP
Move to the previous sheet
CTRL+E
Export all sheets to Microsoft Excel

Show Keys for entering data on a sheet

ENTER
Complete a cell entry and move down in the selection
CTRL+ENTER
Fill the selected cell range with the current entry
SHIFT+ENTER
Complete a cell entry and move up in the selection
The TAB key
Complete a cell entry and move to the right in the selection
CTRL+SHIFT+ENTER
Enter an array formula
SHIFT+TAB
Complete a cell entry and move to the left in the selection
ESC
Cancel a cell entry
Arrow keys
Move one character up, down, left, or right
HOME
Move to the beginning of the line
END
Move to the end of the line
ALT+0128 (on the numeric keypad, with Num Lock on)
Enter the euro currency character Euro currency symbol

Show Keys for working in cells

F2
Edit the active cell and position the insertion point at the end of the line
F9
Calculate all sheets in the active spreadsheet
= (equal sign)
Start a formula
ALT+= (equal sign)
Insert a SUM formula by using the AutoSum command
BACKSPACE
Delete the character to the left of the insertion point, or delete the selection
DELETE
Delete the character to the right of the insertion point, or delete the selection
CTRL+SHIFT+ENTER
Enter a formula as an array formula

Show Keys for formatting data

CTRL+B
Apply or remove bold formatting
CTRL+I
Apply or remove italic formatting
CTRL+U
Apply or remove an underline

Show Keys for editing data

F2
Edit the active cell and put the insertion point at the end of the line
F4
Toggle between absolute mode and relative mode for the currently selected reference
ESC
Cancel an entry in the cell
BACKSPACE
Clear the active cell and leave in edit mode, or delete the preceding character in the active cell as you edit the cell contents
ENTER
Complete a cell entry

Show Keys for inserting, deleting, and copying a selection

CTRL+C
Copy the selection
CTRL+X
Cut the selection
CTRL+V
Paste the selection
DELETE
Clear the contents of the selection
CTRL+Z
Undo the last action

Show Keys for moving within a selection

ENTER
Move from top to bottom within the selection (down), or move in the direction that is selected in the Commands and Options dialog box
SHIFT+ENTER
Move from bottom to top within the selection (up), or move opposite to the direction that is selected in the Commands and Options dialog box
The TAB key
Move from left to right within the selection, or move down one cell if only one column is selected
SHIFT+TAB
Move from right to left within the selection, or move up one cell if only one column is selected

Show Keys for selecting cells, columns, or rows

SHIFT+arrow key
Extend the selection by one cell
CTRL+/
Select the array containing the active cell.
CTRL+SHIFT+arrow key
Extend the selection to the last nonblank cell in the same column or row as the active cell
SHIFT+HOME
Extend the selection to the beginning of the row
CTRL+SHIFT+HOME
Extend the selection to the beginning of the sheet
CTRL+SHIFT+END
Extend the selection to the last used cell on the sheet (lower-right corner)
CTRL+SPACEBAR
Select the entire column
SHIFT+SPACEBAR
Select the entire row
CTRL+A
Select the entire sheet
SHIFT+PAGE UP
Extend the selection up one screen

Show Keys for working with AutoFilter drop-down lists

CTRL+T
Enable AutoFilter on the selected range
ALT+DOWN ARROW
Open the AutoFilter drop-down list for the selected cell
The TAB key
Alternately move to the most recently selected item, the OK button, and the Cancel button in an AutoFilter drop-down list
Arrow keys
Move to the next or previous item in an AutoFilter drop-down list
Letter or number keys
Move to the first item that starts with that letter or number in an AutoFilter drop-down list
SHIFT+arrow keys
Extend the selection in an AutoFilter drop-down list
SPACEBAR
Select or clear the check boxes for the selected items in an AutoFilter drop-down list
ENTER
Close an AutoFilter drop-down list and apply any changes you made
ESC
Close an AutoFilter drop-down list without applying your changes

Show Keys for working with Help

Show Keys for working in the Help window

F6
Switch between the navigation pane and topic pane
ALT+O
Display the Options menu to access any Help toolbar command
ALT+SPACEBAR
Display the program Control menu
ALT+F4
Close the Help window

Show Keys for working in the navigation pane

CTRL+TAB
Switch to the other tab
ENTER
Open or close a selected book, or open a selected Help topic
DOWN ARROW
Select the next book or Help topic
UP ARROW
Select the previous book or Help topic
SHIFT+F10
Display shortcut menu

Show Keys for working in the topic pane

ALT+RIGHT ARROW
Go forward to a Help topic you viewed previously
ALT+LEFT ARROW
Go back to a Help topic you viewed previously
The TAB key
Select the next hidden text or hyperlink, or Show All or Hide All at the top of a topic
SHIFT+TAB
Select the previous hidden text or hyperlink
ENTER
Perform the action for the selected Show All, Hide All, hidden text, or hyperlink
UP ARROW or DOWN ARROW
Scroll toward the beginning or end of a Help topic
PAGE UP or PAGE DOWN
Scroll toward the beginning or end of a Help topic in larger increments
HOME or END
Go to the beginning or end of a Help topic
CTRL+P
Print the current Help topic
CTRL+A
Select the entire Help topic
CTRL+C
Copy selection to the Clipboard
SHIFT+F10
Display shortcut menu


About spreadsheet tools, commands, and options

Show Toolbar

The toolbar at the top of the spreadsheet provides a set of commands for working with spreadsheet data. To see the command name of each button, rest the pointer on the button. If you do not see a toolbar, or cannot display the toolbar, the designer of the Web page may have disabled it. If you are viewing the spreadsheet in a Spreadsheet Web Part and the designer has not disabled the toolbar, you can click the Toolbar command on the toolstrip to display and hide the toolbar.

Show Commands and Options dialog box

The Commands and Options dialog box includes commands to find data in the spreadsheet, such as Find Next, and properties you can set, such as the background color of cells and the widths of columns. The dialog box also includes some commands that aren't present on the toolbar or shortcut menus. To display the dialog box, click Commands and Options Button image on the toolbar.

The commands and properties in the Commands and Options dialog box are distributed among several tabs such as Formula, where you can see the formula in a selected cell or define names that can be used in formulas, and Format, where you can set number formats, change the color of text, select border styles, and more.

If you cannot display the Commands and Options dialog box, the designer of the Web page may have disabled it.


Show or hide the spreadsheet toolbar

  1. Right-click a cell in the spreadsheet, click Commands and Options on the shortcut menu, and then click the Workbook tab.
  2. Under Show/Hide, select or clear the Toolbar check box.


Show or hide the Commands and Options dialog box

Do one of the following:

  • To show the Commands and Options dialog box, click Commands and Options Button image on the spreadsheet toolbar.
  • To hide the Commands and Options dialog box, click Close Button image in the upper right corner of the dialog box.


Add or remove sheets

  1. Click Commands and Options Button image on the spreadsheet toolbar, and then click the Workbook tab.
  2. Under Worksheets, do one of the following:
    • To add a sheet, click Insert. Repeat to add multiple sheets.

      You can change the name of an inserted sheet by selecting the name in the Sheet name list, typing a new name in the Sheet name box, and pressing ENTER.

    • To remove a sheet, click the name of the sheet in the Sheet name list, and then click Delete.

      The sheet and its data are permanently deleted.


Change the name of a sheet

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Workbook tab.
  2. Under Worksheets, in the Sheet name list, click the name of the sheet you want to change.
  3. Type the new name in the Sheet name box.
  4. Press ENTER.


Change the order of sheets

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Workbook tab.
  2. Under Worksheets, in the Sheet name list, click the name of a sheet you want to move.
  3. Next to Order, click Up Up arrow to move the sheet toward the beginning of the workbook, or Down Down arrow to move the sheet toward the end of the workbook.


Show or hide column headers or row headers

Column headers are the letters in gray cells (or in cells of another color, depending on your Microsoft Windows display settings) across the top of the spreadsheet, and row headers are the numbers in gray cells down the side of the spreadsheet.

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Sheet tab.
  2. Under Show/Hide, select or clear the Column headers or Row headers check boxes.


Show or hide gridlines in a spreadsheet

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Sheet tab.
  2. Under Show/Hide, select or clear the Gridlines check box.


Show or hide the sheet selector

The sheet selector consists of the Sheet Name tab and the Sheet list, the second of which appears when you click the arrow on the tab.

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Workbook tab.
  2. Under Show/Hide, select or clear the Sheet selector check box.


Save changes

Changes you make to a spreadsheet in the browser are available during the current browser session only. To save changes to data, do one of the following:

  • On the spreadsheet toolbar, click Export to Microsoft Excel Export to Microsoft Excel and save a copy of the spreadsheet as a Microsoft Excel worksheet.
  • Copy the spreadsheet data to Excel or another program on your computer, and then save the data in that program.


Undo changes

  • To undo the last change you made in the spreadsheet, click Undo Button image on the toolbar. You can undo multiple changes (up to 16) by clicking Undo Button image more than once.
  • Changes you make in the browser are not saved. To undo all changes made during the current session, close the page, and then re-open it.


Print a spreadsheet

To print a spreadsheet from the browser, you must print the entire Web page.

  • In Microsoft Internet Explorer, click Print on the File menu.

Note If the spreadsheet is wider or longer than the printed page, the areas that don't fit on the page are not printed. For more control over how the data is printed, including the ability to print a long list and set page breaks, you can export the spreadsheet to Microsoft Excel by clicking Export to Microsoft Excel Export to Microsoft Excel on the toolbar. For information about printing from Excel, see Excel Help.


About entering text, numbers, dates, and times in a spreadsheet

Some of the content in this topic may not be applicable to some languages.

Show Entering text

Characters treated as text In a spreadsheet, text is considered to be any combination of numbers, spaces, and nonnumeric characters— for example, a spreadsheet treats the following entries as text:

10AA109, 127AXY, 12-976, 208 4675

Entering numbers and dates as text To enter numbers and dates so that they are stored as text, type an apostrophe (') at the beginning of the text. For example, to enter the part number 02-01-23 as text instead of a date, type '02-01-23.

Aligning text By default, all text is left-aligned in a cell. You can change the alignment to be centered or right-aligned.

Text can't be wrapped in cells You cannot display multiple lines of text in a single cell.

Repeating text To enter the same text string in several cells, select the cells, type the text, and then press CTRL+ENTER.

Euro sign To enter the euro sign ( Euro ), make sure NUM LOCK is turned on, and then type ALT+0128 on the numeric keypad. The fonts that can display the euro sign are as follows:

Operating system | Fonts
Microsoft Windows XP | Most fonts support the euro sign
Microsoft Windows 2000 | Arial, Courier New, Tahoma (regular and bold), Times New Roman

Show Entering numbers

Characters that can be used as numbers In a spreadsheet, a number can contain only the following characters:

0 1 2 3 4 5 6 7 8 9 + – ( ) , / $ % . E e Euro

The spreadsheet ignores leading plus signs (+) and treats a single period (.) within a number string as a decimal. All other strings that combine numbers with nonnumeric characters are treated as text.

Entering fractions To avoid entering a fraction as a date, precede fractions with a 0 (zero) and a space; for example, type 0 1/2.

Entering negative numbers Precede negative numbers with a minus sign (–), or enclose the numbers in parentheses ( ).

Aligning numbers By default, all numbers are right-aligned in a cell. You can change the alignment to be centered or left-aligned.

How numbers appear The number format that's applied to a cell determines the way the number is displayed. If you type a number into a cell that has the General number format, the spreadsheet might apply a different number format. For example, if you type $14.73, the spreadsheet applies a currency format. You can change the number format.

The General number format In cells that have the default General number format, a spreadsheet displays numbers as integers (789), decimal fractions (7.89), or scientific notation (7.89E+08). Scientific notation is automatically used if the number is longer than the width of the cell. The General number format displays up to 11 digits, including a decimal point and characters such as "E" and "+." To use numbers that consist of more than 11 digits, you can apply the Scientific number format (exponential notation).

15-digit limit Regardless of the number of digits displayed, a spreadsheet stores numbers with up to 15 digits of precision. If a number contains more than 15 significant digits, the spreadsheet converts the extra digits to zeros (0).

Regional settings The characters recognized as numbers depend on the options you select in the regional settings of Microsoft Windows Control Panel. The options that are selected also determine the default format for numbers— for example, the period (.) is used as the decimal symbol on United States English–based systems.

Repeating data To enter the same data in several cells, select the cells, type the data, and then press CTRL+ENTER.

Show Entering dates and times

Dates and times are numbers A spreadsheet treats dates and times as numbers. The way that a time or date is displayed in a cell depends on the number format applied to the cell. When you type a date or time that the spreadsheet recognizes, the cell's format changes from the General number format to a date or time format. By default, dates and times are right-aligned in a cell. If the spreadsheet cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell.

Control Panel Options you select in the regional settings of Control Panel determine the default format for the current date and time, including the characters that are recognized as date and time separators— for example, the slash (/) and hyphen (–) for dates and the colon (:) for times on United States English–based systems.

Use a four-digit year When interpreting ambiguous dates, the spreadsheet makes certain assumptions. For example, when you enter a date that includes only the month and one or two digits, the spreadsheet assumes that 1 through 31 is the day and that the year is the current year. For instance, December 01 is assumed to be December 1 of the current year, not December of the year 2001. December 32 is assumed to be December 1, 1932. 12/1/30 is December 1, 1930, but 12/1/29 is December 1, 2029, unless your system administrator has changed the default cutoff dates.

To ensure that dates are interpreted by the spreadsheet the way that you intended, always type four digits for the year.

Entering dates and times together To enter a date and time in the same cell, type a space between the date and the time— for example, 7/10/1976 23:12.

Entering times as AM or PM To enter a time based on the 12-hour clock, type a space and then AM or PM after the time— for example, type 9:00 followed by AM or PM.

Viewing the serial number behind a date or time Regardless of the format that's used to display a date or time, a spreadsheet stores all dates as serial numbers and stores all times as decimal fractions. To display a date as a serial number or display a time as a fraction, select the cells that contain the date or time, and change the number format to the General number format. In the General number format, the date portion of the number is to the left of the decimal point, and the time portion to the right of the decimal point. In a spreadsheet, the date serial number 1 corresponds to the date Sunday, December 31, 1899.

Calculating with dates and times You can add and subtract dates and times and include them in other calculations. To use a date or time in a formula, enter the date or time as text and enclose the text in quotation marks. For example, the following formula would display a difference of 68:

="5/12/2004"-"3/5/2004"


Enter text, numbers, dates, and times in a spreadsheet

  1. Click the cell where you want to enter data.
  2. Type the data, and then press ENTER or the TAB key.

Notes

  • To separate the parts of a date, use a slash or a hyphen; for example, type 9/5/2002 or 5-Sep-2002.

  • To enter a time based on the 12-hour clock, type a space and then type AM or PM after the time; for example, type 9:00 PM. Otherwise, the spreadsheet enters the time as AM.

  • Precede formulas with an equal (=) sign.

  • To enter a hyperlink, type the URL address, beginning with a protocol such as http:// or ftp://, and then press ENTER.

  • To enter the same data in several cells, select the cells, type the data, and then press CTRL+ENTER.


How a spreadsheet handles dates in the year 2000 and beyond

Because the rules that govern the way any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations.

How a spreadsheet interprets ambiguous dates When interpreting ambiguous dates, the spreadsheet makes certain assumptions. For example, when you enter a date that includes only the month and one or two digits, the spreadsheet assumes that 1 through 31 is the day and that the year is the current year. For instance, the spreadsheet assumes that December 01 is December 1 of the current year, not December of the year 2001. The spreadsheet assumes that December 32 is December 1, 1932.

How a spreadsheet interprets two-digit years By default, when you enter a two-digit year value, the spreadsheet interprets the year as follows:

  • 00 through 29 The spreadsheet interprets the two-digit year values 00 through 29 as the years 2000 through 2029. For example, if you type the date 5/28/19, the spreadsheet assumes the date is May 28, 2019.
  • 30 through 99 The spreadsheet interprets the two-digit year values 30 through 99 as the years 1930 through 1999. For example, if you type the date 5/28/98, the spreadsheet assumes the date is May 28, 1998.

Use four-digit years To ensure that the spreadsheet interprets year values the way you intended, type year values as four digits (for example, 2001, rather than 01).

Change how years are interpreted Your system administrator can customize Microsoft Office so that different rules determine how dates are interpreted. For example, your administrator could change the earliest two-digit year value that's interpreted as a date in the twentieth century from 30 to another number. Also, administrators can change the default date format to display four-digit years instead of two-digit years.

To find out if your system uses customized date interpretation, see your administrator. For information about how to customize date interpretation, see the Office 2003 Resource Kit.


Change data in a cell

  1. Click the cell you want to change.
  2. Do one of the following:

    • To enter a new number or text in the cell, replacing the current contents of the cell, type the new value.

    • To change a portion of the contents of the cell, double-click the cell and make your changes.

  3. To accept your changes to the cell, press ENTER.

Note A cell can have only one font and text format.


About moving and copying data

When you copy a cell, you copy the entire cell, including formulas and their resulting values, and cell formats. Cells that you copy or move replace any data in the area where you paste the cells. There are some differences in how pasting cells affects an XML list. For information on these differences, see "Pasting records" in About XML lists.

Show Copying and moving affects formulas

When you move a formula, the cell references within the formula do not change. When you copy a formula, absolute cell references do not change; however, relative cell references will change in relation to the position of the pasted cell. For example, if you copy the formula:

=A1+B1

from cell C1 to cell D2, which is one column to the right and one row down, the references in the formula update to reflect the new position:

=B2+C2

Show Filtering affects copying and moving

If the area you select to copy includes cells hidden by filtering, you copy only the visible cells. If the area where you paste the data includes rows hidden by filtering, you might need to turn off filtering to see all of the copied cells. You cannot move (or cut) data from a range that includes filtered rows. To move the data you must first turn off filtering.

Show Copying to other programs

Cells that are copied from a spreadsheet are placed on the Office Clipboard in XML Spreadsheet, HTML, and plain text formats. The most appropriate format is chosen for the application to which you are pasting. For example, when you paste the cells into a Microsoft Office Excel 2003 worksheet, the formulas, values, and cell formats are reproduced on the worksheet by pasting in XML Spreadsheet format. When you paste the cells into a Microsoft Word document, the cells are pasted as a table using HTML format. The cell formatting and values are preserved, but not formulas. When you paste into Notepad, the cells are pasted as plain text, which does not include formatting or formulas.

Show Copying in the Spreadsheet Component differs from Microsoft Excel

Copying and moving data in a spreadsheet is different from copying and moving data in Excel in the following ways:

No copy indicator Cells that you've copied in a spreadsheet do not have the Excel moving dashed border to indicate what was copied.

No Paste Special command When you copy cells in a spreadsheet, you copy values, formatting, and formulas. You cannot paste these elements separately, transpose rows for columns when you paste, or apply an add, subtract, multiply, or divide operation, as you can in Excel.

Cells can't be inserted when you paste In a spreadsheet, cells that you paste always replace existing cells. In Excel, you can insert the copied cells. To add blank space for data that you copy or move in a spreadsheet, you can insert rows and columns before you paste the copied cells.

Copied data remains on the Clipboard When you copy data in a spreadsheet, it remains on the Clipboard until you copy other data or erase the Clipboard. After copying data, you can perform other operations in the spreadsheet, and then paste the data. In Excel, the contents are removed from the Clipboard if you perform other operations or press ESC before you paste the data.

Data is cut immediately When you move data in a spreadsheet, the data disappears as soon as you cut it, and you can paste the cut data only once. When you move data in Excel, the cut data isn't removed from its original location until you paste it, and you can paste the data multiple times.


Move and copy data

Show Move or copy whole cells

When you copy or cut a cell and then paste it, you copy or move the entire cell, including formulas and their resulting values, and cell formats.

If the area you select to copy includes cells hidden by filtering, you copy only the visible cells. You cannot move data from a range that includes filtered rows. To move the data you must first turn off filtering.

  1. Select the cells you want to move or copy.
  2. Do one of the following:

    • To move the cells, click Cut Button image.

    • To copy the cells, click Copy Button image.

    Note When you cut data, you can paste it only once, but if you copy data, you can paste it multiple times. If you want to paste the data in several locations, use the Copy button to copy the data.

  3. Click the upper-left cell of the area where you want to paste the cells.
  4. Click Paste Button image.

Notes

  • Cells that you copy or move replace any data in the area where you paste the cells.
  • When you move a formula, the cell references within the formula do not change. When you copy a formula, absolute cell references do not change; however, relative cell references will change.

Show Move or copy characters within a cell

  1. Double-click the cell with the characters you want to move or copy.
  2. In the cell, select the characters you want to move or copy.
  3. Do one of the following:

    • To move the characters, click Cut Button image.

    • To copy the characters, click Copy Button image.

  4. In the cell, click where you want to paste the characters.
  5. Click Paste Button image.
  6. Press ENTER.

Note When you move or copy characters from within one cell to another cell, the moved or copied characters take on the formatting of the destination cell.


Insert or delete rows or columns

Show Insert rows or columns

  1. Click a cell in the column to the right of where you want to insert a new column or in the row below where you want to insert a new row.

    If you want to insert multiple rows or columns, select multiple cells. For example, to insert three rows, select three cells in a column.

  2. Right-click the cell or selection, point to Insert on the shortcut menu, and then click Rows or Columns.

Show Delete rows or columns

  1. Click a cell in the column or row you want to delete.

    If you want to delete multiple rows or columns, select multiple cells. For example, to delete three rows, select three cells in a column.

  2. Right-click the cell or selection, point to Delete on the shortcut menu, and then click Rows or Columns.


Insert, change, or delete a hyperlink

Show Insert a hyperlink

  1. Click the cell where you want to insert the hyperlink.
  2. Type the address, beginning with a valid protocol such as http://, and then press ENTER to enter the address as a hyperlink. For example, type http://example.microsoft.com

Note The address appears as blue underlined text in the cell. If you want to display text for the hyperlink other than the address itself, use the HYPERLINK spreadsheet function.

Show Change a hyperlink

  1. Click a cell next to the hyperlink, and then use the arrow keys to move to the cell.
  2. Press F2 to begin editing in the cell.
  3. Type your changes to the hyperlink address or to the formula that uses the HYPERLINK spreadsheet function.

Note If you are modifying a hyperlink that was published from a Microsoft Excel worksheet and was originally created by using the Insert Hyperlink dialog box in Excel, you can change the text that's displayed for the hyperlink, but you cannot change the address that the hyperlink goes to.

Show Delete a hyperlink

  1. Click a cell next to the hyperlink, and then use the arrow keys to move to the cell.
  2. Press DELETE.


Clear cell contents

  1. Select the cells that you want to clear.
  2. Press DELETE.

Show Tip

Clearing cell contents deletes values and formulas, but does not delete formatting. To delete values, formulas, and formatting, select the cells and then click Cut Button image.


About formulas

Show What is a formula?

A formula is an equation that performs operations on spreadsheet data or other data. Formulas can perform mathematical operations, such as addition and multiplication, or they can join text or compare spreadsheet values. The following formula adds the value in cell A1 on a spreadsheet to the value in cell A2:

=A1+A2

Formulas can use constants (such as a number or text value), can refer to cells on the spreadsheet, or can use values from data on the same Web page.

Elements of a formula

A formula in a spreadsheet always begins with an equal sign (=). Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Calculation operators are symbols that specify the type of calculation— such as addition (+), multiplication (*), and subtraction (-)— to be performed on the elements of the formula.

Show Calculation

Formulas are calculated from left to right, beginning with the equal sign, and according to a specific order for each operator in the formula. For instance, multiplication is performed before addition.

=5+2*3*4

In the preceding example, the leftmost multiplication operation (2*3) is performed first for a result of 6; then the next multiplication operation in the left-to-right sequence (6*4) is performed for a result of 24. Finally, the addition operation (5+24) is performed for a result of 29.

You can control the order of calculation by changing the syntax of the formula. For example, if you use parentheses in the following example to change the syntax of the example above, 5 and 2 are added first (5+2), then the result (7) is multiplied by 3 for a result of 21, and then 21 is multiplied by 4 for a result of 84.

=(5+2)*3*4

Calculation is automatic by default, which means that when a cell that a formula refers to changes, the formula recalculates. However, you can set calculation to manual so that you can recalculate only when you choose.

Show Constants vs. cell references

A formula can refer to constant values and to other cells. The cell that contains the formula is known as a dependent cell when its value depends on the values in other cells. For example, cell B2 is a dependent cell if it contains the formula =C2.

Whenever a cell that the formula refers to changes, the dependent cell also changes, by default. For example, if a value in any of the following cells changes, the result of the formula =B2+C2+D2 also changes.

Data in a worksheet

If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.

Show Functions

The Microsoft Office Spreadsheet Component provides many predefined, or built-in, formulas, which are known as functions. Functions can be used to perform simple or complex calculations. For example, the following formula uses the SUM function to add a range of cells:

=SUM(A1:A5)


Examples of commonly used formulas

Show Create a sum based on one condition

Use the SUMIF spreadsheet function to create a sum for one range that's based on a value in another range. For example, for every cell in the range B5:B25 that contains the value "Northwind", the following formula calculates the sum for the corresponding cells in the range F5:F25.

=SUMIF(B5:B25,"Northwind",F5:F25)

Show Count the occurrences of a value or a condition

The COUNTIF spreadsheet function counts the occurrences of a value in a range of cells— for example, the number of cells in the range B5:B25 that contain the text "Northwind".

=COUNTIF(B5:B25,"Northwind")

Show Calculate a running balance

You can create a checkbook register in a spreadsheet to track your banking transactions. As part of the spreadsheet, you can build a formula to calculate your running balance. In this example, assume that cell F6 contains the previous balance, cell D7 contains the first transaction's deposit subtotal, and cell E7 contains any cash-received amount.

To calculate the current balance for the first transaction, enter the following formula in cell F7:

=SUM(F6,D7,–E7)

As you enter new transactions, copy this formula into the current balance cell for each new deposit.

Show Join first and last names

By using the ampersand (&) text operator, you can join, or concatenate, a list of first names in one column with a list of last names in another column by using a formula. In these examples, assume that cell D5 contains the first name and cell E5 contains the last name.

To display the full name in the format "first_name last_name" (for example, "John Smith"):

=D5&" "&E5

To display the full name in the format "last_name, first_name" (for example, "Smith, John"):

=E5&", "&D5

Show Join a date with text

To join, or concatenate, two values to produce one continuous text value, use the ampersand (&) text operator. To connect a text string with a number, date, or time value stored in a cell, use the TEXT spreadsheet function. For example, if cell G5 contains a billing date of 5-Jun-2000, you can display the text "Statement date: 5-Jun-2000" by using this formula:

="Statement date: "&TEXT(G5, "d-mmm-yyyy")

Show Increase a number by a percentage

You can increase a numeric value that's stored in a cell by a percentage. In this example, assume that cell G5 contains the value that you want to increase by 5 percent. Multiply the value in G5 by 1 to return the starting value, and then add the percent you want to increase it by so that the formula returns the increased value as a result.

=G5*(1+5%)

If the percentage amount is stored in cell G2, use this formula:

=G5*(1+$G$2)

The reference to cell G2 is an absolute cell reference, so the formula can be copied to other cells without changing the reference to G2.


Create a formula

  1. Click the cell in which you want to add a formula.
  2. Type an equal sign (=).
  3. Type the formula.
  4. Press ENTER.

Show Tip

To insert a sum for a range of cells automatically, select the first blank cell that follows the data you want to sum, either below the column of data or to the right of the data in a row, click AutoSum Button image on the spreadsheet toolbar, and then press ENTER.


Change a formula

  1. Double-click the cell that contains the formula you want to change so that the formula appears in the cell.

    If the cell contains a hyperlink, click a cell next to the cell you want to change, and then use an arrow key to select the cell with the formula you want to change. Then press F2.

  2. Make changes to the formula in the cell.
  3. Press ENTER.


View a formula and its result at the same time

  1. Click the cell that contains the formula you want to view.
  2. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Formula tab.
  3. Review the formula in the Formula in active cell (reference) box. The result of the formula is displayed in the Cell value box.


Insert a total for a range

You can insert a sum for a range of cells automatically by using AutoSum.

  1. Click the first blank cell that follows the data you want to sum, either below the column of data or to the right of the data in a row, or select a column range to sum.
  2. On the spreadsheet toolbar, click AutoSum Button image.

    A suggested formula is displayed in the cell you selected or in the cell below the column range you selected.

  3. Do one of the following:

    • To accept the suggested formula, press ENTER.

    • To change the suggested formula, select the range you want to sum and press ENTER.


Change when a spreadsheet calculates

When a spreadsheet contains many formulas, it might take a long time to calculate. To speed up your work, you can change the calculation mode from automatic to manual so that you can decide when to recalculate the spreadsheet.

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Workbook tab.
  2. Under Calculation, do one of the following:

    • To recalculate the spreadsheet whenever a value changes, select Automatic.

    • To recalculate the spreadsheet only when you press F9 or click Calculate, select Manual.


About using names in formulas

Show Overview

A defined name in a formula can make it easier to understand the purpose of the formula. For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30).

Names are available to any sheet. For example, if the name ProjectedSales refers to the range A20:A30 on the first sheet, you can use the name ProjectedSales on any other sheet to refer to range A20:A30 on the first sheet.

Names can also be used to represent formulas or values that do not change (constants). For example, you can use the name SalesTax to represent the sales tax amount (such as 6.2 percent) applied to sales transactions.

Note By default, names use absolute cell references.

Show Guidelines

What characters are allowed? The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.

Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators— for example, Sales_Tax or First.Quarter.

How many characters can be used? A name can contain up to 255 characters.

Are names case sensitive? Names can contain uppercase and lowercase letters. The Spreadsheet Component does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same spreadsheet, the second name will replace the first one.


Define names for cells, formulas, or constants

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Formula tab.
  2. Under Names, type the name you want to use in the Name box.

    If you want to define a name that is available to only one sheet (a local name), precede the name you want to use by the sheet name followed by an exclamation point. For example, if you want to name a cell Budget, and make it available only on Sheet2, type the following in the Name box:

    Sheet2!Budget

  3. In the Refers to box, type = (equal sign), followed by the formula, constant value, or reference to the cell or range of cells you want to name. For example, to name cells A1 through A5 on Sheet1, type the following:

    =Sheet1!$A$1:$A$5

  4. Click Define.


Change or delete a defined name

  1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Formula tab.
  2. Under Names, in the All names box, click the name you want to change or remove.
  3. Do one of the following:
    • To change the name, type a new name in the Name box and click Define. Then, click the original name in the All names box and click Remove.
    • To change the cell, formula, or constant represented by a name, change it in the Refers to box, and then click Define.
    • To delete the name, click Remove.


About cell and range references

Show Overview

A reference identifies a cell or a range of cells on a spreadsheet and tells the spreadsheet where to look for the values or data that you want to use in a formula. By using references, you can use data that's contained in different parts of a spreadsheet in one formula or use the value from one cell in several formulas.

Spreadsheets use the A1 reference style, which labels columns with letters (A through ZZZ, for a total of 18,278 columns) and labels rows with numbers (1 through 262,144).

To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50.

To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, type a colon (:), and then enter the reference to the cell in the lower-right corner of the range. The following are examples of references.

To refer to | Use
The cell in column A and row 10 | A10
The range of cells in column A and rows 10 through 20 | A10:A20
The range of cells in row 15 and columns B through E | B15:E15
All cells in row 5 | 5:5
All cells in rows 5 through 10 | 5:10
All cells in column H | H:H
All cells in columns H through J | H:J
The range of cells in columns A through E and rows 10 through 20 | A10:E20

Show Relative and absolute references

Relative references When you create a formula that contains references to cells or ranges, those references are usually based on their positions relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5. The value is found one cell above and one cell to the left of B6. This is known as a relative reference.

Formula with relative reference

When you copy and paste a formula that uses relative references, the references in the pasted formula automatically adjust to refer to different cells relative to the position of the formula. In the following example, the formula in cell B2, =A1, refers to one cell above and to the left of B2. When the formula is pasted in cell B3, it adjusts to =A2, which refers to the cell that is one cell above and to the left of cell B3.

Copied formula with relative reference

Absolute references If you don't want a reference to be adjusted when you copy a formula to a different cell, use an absolute reference. To create an absolute reference, type a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1 in the formula =A5*C1, for example, add dollar signs to the formula as follows:

=A5*$C$1

Mixed references You can also make only a row or only a column reference absolute. This is known as a mixed reference. For example, if you want to copy a formula in which a column reference is updated but the row reference is not, put the dollar sign in front of the row number only:

=C$1

Show Tip

To quickly change from relative to absolute references, select the reference in the formula and press F4. Each time you press F4, the reference cycles from absolute column and absolute row to relative column and absolute row to relative column and relative row, and back to absolute column and absolute row.


About calculation

Calculation is the process that a spreadsheet uses to compute formulas in its cells and then display the results as values. By default, formulas are automatically calculated when a change occurs in the cells that the formulas refer to. However, you can change how a spreadsheet calculates.

Stored vs. displayed values Values are sometimes displayed and stored differently. Dates and times, for example, are stored as serial numbers but are usually displayed and printed in one of several date or time formats. The spreadsheet uses the stored serial numbers in calculations, which makes it possible to calculate the number of days between two dates.

The format that stored values are displayed in sometimes depends on how you choose to format them. For example, you can format a cell that contains the number 123456789 to display as 1.23E+08 by using the Scientific format. Or you can format a cell that contains a date with the serial number 36699 to display as "6/22/2000" or as "22-Jun-2000". Changing the display of a value does not change the stored value.

The regional settings in Microsoft Windows Control Panel also determine how number, currency, date, and time formats are displayed. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.


About calculation operators

Operators specify the type of calculation that you want to perform on the elements of a formula. The Spreadsheet Component includes four types of calculation operators: arithmetic, comparison, text concatenation, and reference. Operators calculate in a specific order.

Show Types of operators

Show Arithmetic

Use the following arithmetic operators to perform basic mathematical operations, such as addition, subtraction, or multiplication; to combine numbers; and to produce numeric results.

Arithmetic operator | Meaning | Example
+ (plus sign) | Addition | 3+3
– (minus sign) | Subtraction Negation | 3–1 –1
* (asterisk) | Multiplication | 3*3
/ (slash mark) | Division | 3/3
% (percent sign) | Percent | 20%
^ (caret) | Exponentiation | 3^2 (the same as 3*3)

Show Comparison

To compare two values, use the following comparison operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Comparison operator | Meaning | Example
= (equal sign) | Equal to | A1=B1
> (greater than sign) | Greater than | A1>B1
< (less than sign) | Less than | A1<B1
>= (greater than or equal to sign) | Greater than or equal to | A1>=B1
<= (less than or equal to sign) | Less than or equal to | A1<=B1
<> (not equal to sign) | Not equal to | A1<>B1

Show Text concatenation

To join, or concatenate, one or more text strings to produce a single piece of text, use the ampersand (&) operator.

Text operator | Meaning | Example
& (ampersand) | Concatenates | "North" & "wind" produces "Northwind"

Show Reference

To combine ranges of cells for calculations, use the following reference operators.

Reference operator | Meaning | Example
: (colon) | Range operator, which produces one reference to all the cells between two references, including the two references | B5:B15
, (comma) | Union operator, which combines multiple references into one reference | SUM(B5:B15,D5:D15)
(single space) | Intersection operator, which produces one reference to cells that are common to two references | SUM(B5:B15 A7:D7) In this example, cell B7 is common to both ranges.

Show Order in which operators calculate

If you combine several operators in a single formula, the Spreadsheet Component performs the operations in the order shown in the following table. If a formula contains operators with the same precedence— for example, if a formula contains both a multiplication and division operator— the Spreadsheet Component evaluates the operators from left to right. To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first.

Operator | Description
: (colon) (single space) , (comma) | Reference operators
– | Negation (as in –1)
% | Percent
^ | Exponentiation
* and / | Multiplication and division
+ and – | Addition and subtraction
& | Concatenation (joins two strings of text)
= < > <= >= <> | Comparison


About array formulas and array constants

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. When you're editing an array formula, the formula appears in italic formatting.

Array constants can be used in place of references when you don't want to enter each constant value in a separate cell on the worksheet.

Some of the built-in functions are array formulas, and must be entered as arrays to get the correct results.

Show Using array formulas

To calculate a single result This type of array formula can simplify a spreadsheet model by replacing several different formulas with a single array formula.

For example, the following array formula calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.

Array formula that produces a single result

When you enter the formula =SUM(B2:C2*B3:C3) as an array formula, it multiples the Shares (in cells B2 and C2) and Price (in cells B3 and C3) for each stock, and then adds the results of those calculations together.

To calculate multiple results Some spreadsheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments.

For example, given a series of three sales figures (20234, 21003, and 10000 in column B) for a series of three months (1, 2, and 3 in column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of this formula, you can enter the formula into three cells in column C (C1:C3).

Array formula that produces multiple results

When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962, which would be shown in cells C1, C2, and C3), based on the three sales figures and the three months.

Show Using array constants

In an ordinary formula, you can enter a reference to a cell containing a value, or you can enter the value itself, which is also called a constant. Similarly, in an array formula, you can enter a reference to an array, or you can enter the array of values contained within the cells, which is also called an array constant. Array formulas accept constants in the same way that nonarray formulas do, but you must enter the array constants in a certain format.

Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such as #N/A. Different types of values can be in the same array constant— for example, {1,3,4;TRUE,FALSE,TRUE}. Numbers in array constants can be in integer, decimal, or scientific format. Text must be enclosed in double quotation marks— for example, "Tuesday".

Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the special characters $ (dollar sign), parentheses, or % (percent).

The format of array constants

Array constants are enclosed in braces ( { } ).

Separate values in different columns with commas (,). For example, to represent the values 10, 20, 30, and 40, enter {10,20,30,40}. This array constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column reference.

Separate values in different rows with semicolons (;). For example, to represent the values 10, 20, 30, and 40 in one row and 50, 60, 70, and 80 in the row immediately below, you would enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}.


Create an array formula

Show Calculate a single result

You can use an array formula to perform several calculations to generate a single result. This type of formula can simplify a spreadsheet model by replacing several different formulas with a single array formula.

  1. Click the cell in which you want to enter the array formula.
  2. Type the array formula.

    For example, the following calculates the total value of an array of stock prices and shares, without using a row of cells to calculate and display the individual values for each stock.

    Array formula that produces a single result

    When you enter the formula =SUM(B2:C2*B3:C3) as an array formula, it multiples the Shares (in cells B2 and C2) and Price (in cells B3 and C3) for each stock, and then adds the results of those calculations together.

  3. Press CTRL+SHIFT+ENTER.

Show Calculate multiple results

Some spreadsheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments have.

  1. Select the range of cells in which you want to enter the array formula.
  2. Type the array formula.

    For example, given a series of three sales figures (20234, 21003, and 10000 in column B) for a series of three months (1, 2, and 3 in column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of this formula, enter it into three cells in column C (C1:C3).

    Array formula that produces multiple results

    When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate results (22196, 17079, and 11962, which would be shown in cells C1, C2, and C3) based on the three sales figures and the three months.

  3. Press CTRL+SHIFT+ENTER.


Edit an array formula

If an array formula occupies multiple cells, you must edit the entire range as though it is a single cell. You cannot change just one element of an array formula.

  1. Select the cell that contains the array formula, or if the array formula occupies multiple cells, select the range that contains the array formula.

    Show How?

    1. Select a cell within the array.
    2. Press CTRL+/ (forward slash).
  2. Press F2.
  3. Make changes to the array formula.
  4. Press CTRL+SHIFT+ENTER.


Select an array formula range

  1. Select a cell within the array.
  2. Press CTRL+/ (forward slash).


Move or copy an array formula

  1. Select the entire array range.

    Show How?

    1. Select a cell within the array.
    2. Press CTRL+/ (forward slash).

    Click Cut Button image or Copy Button image on the spreadsheet toolbar.

  2. Select the entire destination range; otherwise, the array will be pasted into a single cell.
  3. Click Paste Button image .


About using functions

Spreadsheet functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. For example, the SUM function totals values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

Arguments Arguments can be numbers, text, logical values such as TRUE or FALSE, error values such as #N/A, cell references, constants, formulas, or other functions. Each argument you designate must produce a valid value for that argument.

Structure The structure of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name.

Nested functions

In certain cases, you might need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.

Valid returns When a nested function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument returns a TRUE or FALSE value, the nested function must return TRUE or FALSE. If it doesn't, the spreadsheet displays a #VALUE! error value.


Format cells in a spreadsheet

  1. Select the cells you want to format.
  2. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Format tab.
  3. Do one or more of the following:

    Show Change text font or size

    • To change the appearance of the text, click a button in the Text format group.

    • To change the font or size of the text, click a different font in the Font list or a different size in the list next to it.

    Show Change a number format

    When you change the format of a number in a cell, the displayed value for the number is changed, but not the stored value. For example, the stored value for the date 6/22/2000 is a serial number: 36699. This number remains the same, even when the cell's number format is changed to Thursday, June 22, 2000.
    • Under Text format, click the format you want in the Number Format list.

    Show Place borders around cells

    • Under Text format, next to Border, select the border color, style, width, and placement options you want.

    Show Change the background color of cells

    1. Under Text format, next to Cell, click the arrow next to Fill Color Button image.
    2. On the color palette, click the color you want.

    Show Align data in a cell

    • To align text horizontally, click Align Left Button image, Center Button image , or Align Right Button image under Text format.
    • To align text vertically, click Align Top Button image, Align Middle Button image , or Align Bottom Button image under Text format.

    Note By default, text is aligned to the left side of a cell, and numbers, dates, and times are aligned to the right side of a cell.


Number formats

Some of the content in this topic may not be applicable to some languages.

You can change the formats of numbers in a spreadsheet by using the Commands and Options dialog box, both in the design program and at run time in the browser. The settings in Microsoft Windows Control Panel also determine how number, currency, date, and time formats are displayed. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.

In Microsoft Visual Basic or a script, you can assign custom number formats to the data. You can also type custom formats available in the spreadsheet object model in the Number format box. To find out more, see how to get help for programming a spreadsheet.

The following number formats are available in the Number format list on the Format tab of the Commands and Options dialog box.

Format | Effects
General date | Displays a number as a date and optional time. For example, 8/31/99 05:54 AM. The fractional part of the number is the time. If a number has no fractional part, only a date appears. If a number has only a fractional part and no integer part, only a time appears.
Long date | Displays a date according to the Windows regional setting for the long date format. For example, Thursday, January 25, 2001.
Medium date | Displays a date according to the Windows regional setting for the month and date separator, and displays two digits for the year. For example, Jan/25/01.
Short date | Displays a date according to the Windows regional setting for the short date format. For example, 1/25/01.
Long time | Displays a time according to the Windows regional setting for the long time format. For example, 8:45:36 PM.
Medium time | Displays a time in 12-hour format with an AM or PM designator, omitting the seconds. For example, 8:45 PM.
Short time | Displays a time in 24-hour format, omitting the seconds. For example, 20:45.
General | Displays a number right-aligned, with no thousands separator, and preceded by a minus sign if negative. Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format. Displays text left-aligned.
Currency | Displays a number according to the Windows regional setting for currency, with a thousands separator if appropriate and two decimal places. For example, with the default English (United States) regional settings, $2,532.75.
Euro currency | Displays a number as euro currency, with a thousands separator and two decimal places. The euro sign is placed before or after the number according to the Windows regional setting for currency. For example, with the default English (United States) regional setting, Euro 12.35.
Fixed | Displays a number with two decimal places. For example, 68.30.
Standard | Displays a number with a thousands separator and two digits to the right of the decimal point. The Windows regional setting for numbers determines what's used for the thousands separator. For example, with a comma as the thousands separator, 1,800.00.
Percent | Displays a number multiplied by 100, with two decimal places and a percent sign. For example, the value 0.8914 would be displayed as 89.14%.
Scientific | Displays a number in exponential notation. For example, 1.25E+10.
Yes/no | Displays No if the number is 0; displays Yes for any other number.
True/false | Displays False if the number is 0; displays True for any other number.
On/off | Displays Off if the number is 0; displays On for any other number.


Merge or unmerge cells

  1. Select the cells that you want to merge. Keep in mind that when you merge cells, only data in the upper-left cell of the selected range is retained. Data in all other cells that you merge is deleted.
  2. On the toolbar, click Commands and Options Button image, and then click the Format tab.
  3. Under Cell format, click Merge Cells Button image or Unmerge Cells Button image.


Change the width of a column

  1. Make sure column headers are displayed.

    Show How?

    Column headers are the letters in gray cells (or in cells of another color, depending on your Microsoft Windows display settings) across the top of the spreadsheet, and row headers are the numbers in gray cells down the side of the spreadsheet.

    1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Sheet tab.
    2. Under Show/Hide, select or clear the Column headers or Row headers check boxes.
  2. Select the columns you want to change.
  3. Point to the right boundary of the column header of the column or selection until the pointer changes to Pointer with perpendicular double-headed arrow.

  4. Drag the boundary to the width you want, or, to make the column width fit the contents, double-click the right column boundary.


Change the height of a row

  1. Make sure row numbers are displayed on the spreadsheet.

    Show How?

    Column headers are the letters in gray cells (or in cells of another color, depending on your Microsoft Windows display settings) across the top of the spreadsheet, and row headers are the numbers in gray cells down the side of the spreadsheet.

    1. On the spreadsheet toolbar, click Commands and Options Button image, and then click the Sheet tab.
    2. Under Show/Hide, select or clear the Column headers or Row headers check boxes.
  2. Select the rows you want to change.
  3. Point to the bottom boundary of the row header of the row or selection until the pointer changes to Pointer with perpendicular double-headed arrow.

  4. Drag the boundary until the row or selection is the height you want, or, to make the row height fit the contents, double-click the bottom row boundary.


Display text or a spreadsheet so that it reads right to left or left to right

Use right-to-left text direction or display when your spreadsheet will be viewed in languages that read from right to left.

Show Display an entire spreadsheet right to left or left to right

  1. On the toolbar, click Commands and Options Button image, and then click the Sheet tab.

  2. Under Show/Hide, select or clear the Display right to left check box.

When you select right-to-left display, the elements of the spreadsheet are displayed as a mirror image of the default left-to-right display: The row numbers are displayed on the right side of the spreadsheet and the vertical scroll bars appear on the left side.

Show Display text right to left or left to right

Text direction defaults to Context, which means text direction is automatically displayed as left to right or right to left depending on the language used. You can set specific cells to display text direction as right to left or left to right so that if you are working with multiple languages in different cells, individual cells can have different text directions.

  1. Select the cells for which you want to set text direction.

  2. On the toolbar, click Commands and Options Button image, and then click the Format tab.

  3. Under Text Format, click the arrow next to the Text direction box, and select an option from the list.


About finding data

The Find what feature (Commands and Options dialog box, Sheet tab) can locate text, numbers, dates, and times that have been entered directly into cells or that are the results of formulas.

Keep the following guidelines in mind when using Find what options:

  • Match case By default, Find what is not case sensitive. To find only those cells with values that match the case of your entry, select the Match case check box.
  • Entire cell only By default, Find what searches for cells that include the value you enter in the Find what box. For example, an entry of "John" finds cells with strings such as "Johnson" or "Little John." To find cells that contain only the value you entered and nothing else, select the Entire cell only check box.
  • Formatted values The Find what feature searches for displayed values, not formats. For example, suppose a cell is formatted with the Yes/No number format. If the cell contains a zero (0), the cell displays the word No, and you can use Find what to locate the phrase "No". You can't use Find what to find all cells formatted with a particular color or font.
  • Search direction The Find what feature searches from left to right across the columns of the first row, and then from left to right across the columns of the second row, and so on.
  • Wildcard characters The Find what feature does not allow wildcard characters such as question mark (?) and asterisk (*).
  • Hidden data The Find what feature does not find data in hidden cells or cells hidden by filtering.


Find data

  1. To search the entire spreadsheet, click a single cell, or, to search within a specific range of cells, select the range.

  2. On the toolbar, click Commands and Options Button image, and then click the Sheet tab.
  3. In the Find what box, type the text or value you want to find.

    Notes

    • By default, Find what searches for cells that include the value you enter in the Find what box. For example, an entry of "John" finds cells with strings such as "Johnson" or "Little John." If you want to find cells that contain only the value you entered and nothing else, select the Entire cell only check box.
    • By default, Find what is not case sensitive. If you want to find only those cells with values that match the case of your entry, select the Match case check box.
  4. Click Find Next.


Sort data

Data that you want to sort should be set up as a list. A list has similar items in each column, has labels for the columns across the top row, and does not include any blank rows or columns.

Show Sort by a single column

  1. To sort an entire list, click a cell in the list. The column labels are not included in the sort.

    If you want to sort part of a list, select the range to sort. If you do not want to sort the column labels with the rest of the range, do not select the column labels as part of the range.

  2. Click the arrow next to Sort Ascending Button image or Sort Descending Button image on the spreadsheet toolbar, and then click the column you want to sort by.

Notes

  • Rows hidden by filtering or set to a height of zero aren't sorted.
  • You can sort rows only. You cannot sort the order of the columns.
  • Sorting is not case sensitive. For example, when you sort ascending, "ALL" and "all" are sorted before "BLUE" and "blue".

Show Sort by more than one column

To sort by more than one column, sort the list multiple times, each time by a different column. For example, the following list is organized by salesperson. The list was first sorted by the Product column (also called the inner column or lower-category column) so that the products would be in alphabetical order; the list was next sorted by the Salesperson column (also called the outer column or higher-category column).

Example of data sorted by two columns

  1. Determine the order of categorization you want first. For example, if you're sorting two columns, first determine which is the higher-category column and which is the lower-category column.

  2. Select a cell in the lower-category (inner) column.

  3. Click the arrow next to Sort Ascending Button image or Sort Descending Button image on the spreadsheet toolbar, and then click the column you want to sort by.

  4. Select a cell in the higher-category (outer) column, and repeat the step above.

  5. Repeat steps for each higher-category (outer) column until you're done.

Notes

  • Rows hidden by filtering or set to a height of zero aren't sorted.
  • You can sort rows only. You cannot sort the order of the columns.
  • Sorting is not case sensitive. For example, when you sort ascending, "ALL" and "all" are sorted before "BLUE" and "blue".

Note There are some differences in how sorting affects an XML list. For information on these differences, see "Sorting records" in About XML lists.


Apply or remove a filter

Show Apply a filter

  1. Click a cell within the list that you want to filter, or select the specific range of cells that you want to filter.
  2. On the toolbar, click AutoFilter Button image so that it is selected.
  3. Click the arrow in the column that contains the data you want to display.

  4. To display only the items you want to see, clear the Show All check box, and then select the check box for each item you want to display.

Show Remove a filter

  • On the toolbar, click AutoFilter Button image so that it is no longer selected.


Sum filtered data

  1. Filter the data to display the rows you want to sum.

    Show How?

    Show Apply a filter

    1. Click a cell within the list that you want to filter, or select the specific range of cells that you want to filter.
    2. On the toolbar, click AutoFilter Button image so that it is selected.
    3. Click the arrow in the column that contains the data you want to display.

    4. To display only the items you want to see, clear the Show All check box, and then select the check box for each item you want to display.

    Show Remove a filter

    • On the toolbar, click AutoFilter Button image so that it is no longer selected.

  2. Click the cell below the data you want to sum.
  3. Click AutoSum Button image on the spreadsheet toolbar, and then press ENTER.

Note The AutoSum button sums only the data in visible rows. To include all rows in the sum, show all rows and then sum the rows.


About spreadsheets published from Excel

When a spreadsheet has been published from Microsoft Excel, most Excel features are retained and work the same way as in Excel. Features in the following list will work differently or will not be retained when published or saved as a spreadsheet on a Web page.

Excel formulas, functions, and calculation features | What happens when published or saved as a Web page
Labels in formulas | Labels are converted to cell references.
References to data on other worksheets | As long as you publish the entire workbook, references to other worksheets publish correctly. If you publish only the referencing worksheet, references to other worksheets are converted to values.
Subtotals | Numbers and calculations appear correctly. Group and outline features are not retained.
1904 date system | The displayed dates remain the same, but the serial numbers for the dates are converted to the 1900 date system.
Precision as displayed | Cell value is published.
R1C1 reference style | R1C1 reference style is converted to A1 reference style.
Excel formatting, graphics, and display features | What happens when published or saved as a Web page
Rotated or vertical text | Rotated or vertical text is converted to horizontal text.
Distributed alignment | Not retained.
Thai alignment | Not retained.
Indented text | Not retained.
Wrapped text in cells | Not retained.
Graphics | Not retained.
Pattern fills | Not retained.
Dash dot borders | Dash dot borders are converted to solid borders.
Multiple fonts in a single cell | Not retained. The font of the first character in the cell is used.
Conditional formatting | Not retained.
Cell comments | Not retained.
Data validation restrictions and messages | All validation types except Custom and List are retained. Additionally, cell references in validation rules, custom input messages, and custom error messages are not supported.
Auditing tracer arrows | Not retained.
Outlining | Collapsed rows are published as hidden. Expanded rows are published normally.
Printing or page setup features | Not retained.
Excel file, sharing, and import features | What happens when published or saved as a Web page
Shared workbook information | Not retained.
Lotus compatibility | R1C1 reference style is converted to A1 reference style.
External data ranges | Values are published. The ability to refresh from the source data is not retained.
Web queries | Values are published. The ability to refresh from the source data is not retained.
Passwords | Not retained.
Excel list and XML list features | What happens when published or saved as a Web page
Excel lists | Not retained.
XML lists | The XML list is retained, but if the list has a totals row, the totals row is not retained.


Export a spreadsheet to an Excel workbook

  • On the spreadsheet toolbar, click Export to Microsoft Excel Export to Microsoft Excel to run or switch to Microsoft Excel and display the data in a workbook.

Notes

  • Because Excel supports 256 columns and 65,536 rows, data in columns IW through ZZZ and rows 65,537 through 262,144 are not exported from the spreadsheet. You can, however, copy this data to more than one Excel worksheet.
  • When you export data from a spreadsheet to Excel, only the values and formulas are exported. Links that connect to other data on the Web page or to other Web pages are not retained in Excel.
  • When you export some spreadsheet number formats, the data in Excel is displayed with a custom number format. For example, the Yes/no, True/false, and On/off spreadsheet formats are displayed with custom number formats in Excel. For information about custom number formats in Excel, see Excel Help.


Troubleshoot error values in a spreadsheet

If a formula cannot properly evaluate a result, the spreadsheet displays an error value. For example, error values can result when a formula expects a numeric value but finds text, when a cell that is referenced by a formula has been deleted, or when the cell is not wide enough to display the result.

Error values might not be caused by the formula itself. For example, if a formula shows #N/A or #VALUE!, a cell referenced by the formula might contain the error.

Following are possible causes and solutions for each error value.

Show #####

A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell. You can resize the column by dragging the boundary between the column headings.

Show #CIRC!

The #CIRC! error value occurs when a formula refers to itself, either directly or indirectly.

Possible cause | Suggested action
The formula in the cell refers to itself directly. For example, cell B10 contains the formula =SUM(B5:B10). | Check the references in the formula and make sure the references do not refer to the cell itself.
The formula in the cell refers to itself indirectly. For example, cell B10 contains the formula =SUM(B5:B9) and cell B5 contains the formula =B10*0.5. | Check all cells that are referenced by the formula, and make sure none of them contains a reference to the cell that contains the #CIRC! error. If any of the cells that are referenced by the formula contain formulas themselves, make sure the cells that are referenced by those formulas do not contain references to the cell that contains the #CIRC! error.

Show #DIV/0!

The #DIV/0! error value occurs when a formula divides by 0 (zero).

Possible cause | Suggested action
Using a divisor that refers to a blank cell or to a cell that contains 0. (If an operand refers to a cell that is blank, it is interpreted as 0.) | Change the cell reference, or enter a value other than 0 in the cell that's used as a divisor. You can enter the value #N/A in the cell that's referenced as the divisor, which changes the result of the formula to #N/A, denoting that the divisor value is not available. To prevent an error value from displaying, use the IF spreadsheet function. If the divisor refers to a cell that's blank or contains 0, the IF function can display nothing instead of an error value. For example, use =IF(B5=0,"",A5/B5). The two quotation marks represent an empty text string.
Using 0 for a divisor— for example, =5/0. | Change the divisor to a number other than 0.

Show #N/A

The #N/A error value occurs when a value is not available to a function or a formula.

If certain cells on your spreadsheet will contain data that is not yet available, you can enter #N/A in those cells so that formulas that refer to those cells will return #N/A instead of attempting to calculate a value.

If the necessary values are available, and you see the #N/A error value where you do not expect it, the function might have specific requirements for arguments that have not been met, or values might not be referenced correctly in a formula or in function arguments.

Possible cause | Suggested action
Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP spreadsheet function. | Make sure the lookup_value argument is the correct type of value— for example, a value or a cell reference, but not a range reference.
Using the MATCH spreadsheet function with an improper match_type argument. | The MATCH spreadsheet function contains a match_type argument that specifies how the function matches the lookup_value argument with values in the lookup_array argument. For example, the match_type argument can be set to look for a value that is a close match or an equal match in a lookup_array argument that is sorted in ascending or descending order, or in a lookup_array argument that is not sorted. If the function cannot find a match, make sure the lookup_array argument is sorted according to the match_type argument you are using, and/or try to change the match_type argument.
Using the HLOOKUP or VLOOKUP spreadsheet function with the range_lookup argument set to FALSE to locate a value that is not available. | The VLOOKUP and HLOOKUP spreadsheet functions contain a range_lookup argument that tells the function to find an exact match or an approximate match. If the argument is TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than the lookup_value argument will be returned. If the argument is FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. Make sure the value you are looking for is in the lookup_array argument.
Omitting one or more arguments in a spreadsheet function. | Include all required arguments in the function.

Show #NAME?

The #NAME? error value occurs when text in a formula is not recognized.

Possible cause | Suggested action
Misspelling the name of a function. | Correct the spelling.
Entering text in a formula without enclosing the text in double quotation marks. The entry is not interpreted properly as text. | Enclose text in the formula in double quotation marks. For example, the following formula joins a piece of text "The total amount is " with the value in cell B50: ="The total amount is "&B50
Omitting a colon (:) in a range reference. | Make sure all range references in the formula use a colon (:). For example, =SUM(A1:C10).

Show #NULL!

The #NULL! error value occurs when you specify an intersection of two areas that do not, in fact, intersect.

Possible cause | Suggested action
Using an incorrect range operator. | To refer to two areas that don't intersect, use the union operator, the comma (,). For example, the formula =SUM(A1:A10,C1:C10) sums two ranges that do not intersect, with the comma separating the two ranges. To refer to two areas that do intersect, use the intersection operator, the single space. For example, =SUM(B5:C15 A10:D10) calculates the sum of the cells that both ranges have in common— that is, where they intersect— B10 and C10.
Using an incorrect cell reference. | Check for typing errors in the reference to the ranges.

Show #NUM!

The #NUM! error value occurs when a problem occurs with a number in a formula or function.

Possible cause | Suggested action
Using an inappropriate argument in a function that requires a numeric argument. | Make sure the arguments that are used in the function are the correct type.
Using a spreadsheet function that iterates, such as IRR or RATE, when the function cannot find a result that's accurate within a certain percent after a certain number of tries. | Try different values for the guess argument.
A formula that produces a number that is too large or too small to be represented in the spreadsheet. | Values in the spreadsheet must be between Minus 1 times 10 to the 307th power and 1 times 10 to the 307th power .

Show #REF!

The #REF! error value occurs when a cell reference is not valid.

Possible cause | Suggested action
Deleting cells that are referred to by other formulas, or pasting moved cells over cells that are referred to by other formulas. | Correct the formulas, or restore the cells on the spreadsheet by clicking Undo Button image immediately after you delete or paste the cells.

Show #VALUE!

The #VALUE! error value occurs when the wrong type of argument or operand is used.

Possible cause | Suggested action
Entering text when the formula requires a number or a logical value, such as TRUE or FALSE. The text cannot be translated into the correct data type. | Make sure that the value for the operand or argument is of the required data type for the formula or function and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!. Use the SUM spreadsheet function in the formula as follows to add the two values (the SUM function ignores text): =SUM(A5:A6)
Supplying a range to an operator or a function that requires a single value. | Change the range to a single value.
Omitting a range when a function requires one. | Some functions, such as COUNTIF and SUMIF, require a range. Make sure you have included all required range arguments.
Supplying a value other than 1 for the A1 argument of the ADDRESS spreadsheet function. | The ADDRESS function can return only A1 style references. Change the argument to 1.
Using computed criteria— a formula as a condition— for the database functions (Dfunctions). | The database functions in the Spreadsheet Component do not support computed criteria (for example, "=Field1<Field2"). Change the criteria to simple conditions such as "Field1".
Referring to multiple ranges with the AREAS spreadsheet function. | The AREAS spreadsheet function does not support multiple ranges. Refer to only one range of cells.


Troubleshoot spreadsheets

Finding Help information

Show I can't find the help information I want

Depending on how you are using the Spreadsheet Component, you will find help information in different locations. For more information, see About getting design help for spreadsheets.

Entering and editing data and formulas

Show I can't change cells in the spreadsheet.

The cells might be locked and protected. See the designer of the Web page for more information.

Show I can't wrap text within cells in a spreadsheet.

Wrapped text within cells is not supported in the Microsoft Office Spreadsheet Component. To correctly display the text, you can shorten the text or use multiple cells for the text, or you can widen the column so that all of the text shows.

Show There is no formula bar in my spreadsheet.

Unlike Microsoft Excel, the Microsoft Office Spreadsheet Component does not provide a formula bar to edit cell contents. However, you can edit the contents of a cell by double-clicking the cell or by selecting the cell and pressing F2.

Show I can't change a hyperlink.

If you are modifying a hyperlink that was published from a Microsoft Excel worksheet and was originally created by using the Insert Hyperlink dialog box in Excel, you can change the text that's displayed for the hyperlink, but you cannot change the address that the hyperlink goes to. To change the address, delete the hyperlink and insert a new hyperlink.

Show I can't control the size of my spreadsheet.

Your spreadsheet might be set to resize automatically. See the designer of the Web page for more information.

Show I can't control whether scroll bars are displayed or hidden in my spreadsheet.

Your spreadsheet may have been set to resize automatically by the designer of the Web page. If so, scroll bars appear when the spreadsheet reaches the maximum height or width set, regardless of whether you set scroll bars to be displayed or hidden. See the designer of the Web page for more information.

Filtering, sorting, and finding data

Show Data does not sort correctly in a spreadsheet.

If sorting a spreadsheet does not produce the results you expect, check the following:

Check your selection before sorting When you select a range of cells to sort, the spreadsheet sorts everything within that range, and it sorts only that range. This means that if your header row is within the selection, the header row will be sorted. It also means that if you select one column of data within a list that must be sorted, only that column will be sorted and not the entire list. Try to select a single cell within the range you want to sort, but not within a header row. The spreadsheet will automatically detect the data to sort and will exclude the header row. If this does not work properly for your data, try to select the entire range you want to sort, without selecting the header row.

Check that numbers are in a numeric format If a value is sorted out of order, the cell might be formatted as text and not as a number. For example, negative numbers from some accounting systems become text when the data is imported into a spreadsheet. To apply the correct formatting, click the cell, and then change the format of the number.

Check that dates and times are formatted correctly Dates and times are treated as numbers. When you type a date or time into a cell, the cell's format changes from the General number format to a built-in date or time format. For dates and times to sort correctly, all dates and times in a column must have the same date or time format. To apply the correct formatting, click the cell, and then change the format of the number.

Remove any leading spaces Make sure that no leading spaces have been inserted before data. Remove the leading spaces before sorting the data.

Check the locale setting Sort orders vary by locale setting. Make sure that you have the proper locale setting in the Regional Settings or Regional Options in Control Panel. For information about changing the locale setting, see Windows Help.

Make sure you sort multiple columns in the correct order When you sort on more than one column, the order in which you sort determines how the data will be organized. Sort lower-level (inner) categories first, and then sort higher-level (outer) categories.

Show My spreadsheet list does not show all of my data.

The list might be filtered using the AutoFilter command to display specific rows. If an arrow appears to the right of the top cell in each column, do one of the following:

  • Click the arrow, and then click Show All.
  • Make sure the AutoFilter button Button image on the toolbar is not selected.

Show I have merged cells, and I can't filter my spreadsheet.

The Microsoft Office Spreadsheet Component does not support the ability to filter lists that contain merged cells. If your spreadsheet contains merged cells, unmerge the cells and then try to use the AutoFilter command again.

Show The Find Next command is not finding data in my spreadsheet.

If you have selected a range of data, the Find Next command searches only in that selection for the value you specify. To find data anywhere in the spreadsheet, select a single cell before clicking Find Next.

Show I published a spreadsheet from Excel, and the AutoFilter drop-down list is wrong.

In Microsoft Excel, you can create AutoFilter criteria that cannot be shown in the spreadsheet AutoFilter drop-down list. For example, there is no equivalent for "Top 10..." or "begins with" criteria in the spreadsheet.

When you publish an AutoFilter range in Excel that uses criteria that cannot be shown in the spreadsheet AutoFilter list, the filtered items are displayed correctly on the Web page, but the AutoFilter list does not reflect the chosen criteria.

You can reset the drop-down list by clicking AutoFilter Button image on the spreadsheet toolbar to turn the filter off, and then click AutoFilter Button image again to turn the filter on. This will also reset the displayed list.

Importing and exporting

Show I can't publish a password-protected spreadsheet from Excel.

From Microsoft Excel, you can publish a protected worksheet only by using a blank password. Open the original workbook in Excel, remove the password, and try to publish the worksheet again.

Show My spreadsheet filter is not exporting to Excel correctly.

In a spreadsheet, you can create AutoFilter criteria that can't be shown in the AutoFilter drop-down list or the Custom AutoFilter dialog box in Microsoft Excel. For example, there is no direct equivalent in Excel for a spreadsheet filter that has three unselected items. When you export the spreadsheet to Excel and the spreadsheet contains filtering criteria that Excel does not support, the entire AutoFilter range is shown in Excel.

To export your filtered data and have it display as it does in the spreadsheet, it is recommended that you not have more than two criteria set for each filter; that is, you must not have more than two check boxes with a different setting (selected or cleared) from any of the other check boxes.

Show I get a message that there are too many rows or columns when I try to export my spreadsheet to Excel.

Spreadsheets can contain data in columns A through ZZZ and rows 1 through 262,144. Microsoft Excel worksheets, on the other hand, can contain data only in columns A through IV and rows 1 through 65,536. To export your spreadsheet to Excel, you must delete or move data from columns beyond IV and rows beyond 65,536. If you want, you can copy the additional data to another Excel worksheet.

Working with the spreadsheet in the browser

Show I cannot interact with the spreadsheet.

If you can view but not interact with your spreadsheet, you might not have access to a Microsoft Office 2003 license. See your network administrator for more information.

Show When I open a Web page that contains a spreadsheet in the browser, I receive the error "Cannot open URL."

The source data Web page is unavailable If some of the data in the spreadsheet comes from another Web page, check to see if the Web page is available and that you have proper access to the Web page.

The Web page is not local If the spreadsheet contains a formula that links to data on another Web page, check whether you have proper access to the Web page and that the Web page resides on your local computer or intranet. See your network administrator for more information.

Show Clicking the Edit button in my Web browser opens my Web page in the wrong program.

Programs that are available from the Edit button in your Web browser depend on how the page was published.

If clicking the Edit button in your Web browser opens a program that you do not want to use, quit the program, start the program you want to use, and open the Web page in that program.

Error values

Show I see error values instead of results for formulas.

When part of a formula cannot be calculated, or if there is a problem with the formula, the formula returns an error value, such as #NAME? or #DIV/0! To determine the cause, read about troubleshooting error values.

Accessing a data source on another domain

Show The spreadsheet attempted to access data on another domain, but failed.

Configuration settings in Microsoft Internet Explorer control whether an ActiveX control is allowed to access a data source on a domain other than the server on which the Web Page is located. For information on how to configure Internet Explorer to allow the spreadsheet to access data on another domain, see About accessing a data source on a different domain.