Show All

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.

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.

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.

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.

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.

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.

Show All

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Show All

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:

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.

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.

Show All

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.

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 .

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 .

• 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 in the Commands and Options dialog box or click Help on the spreadsheet toolbar.

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.

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.

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.

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.

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.

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".

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.

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

Show All

About spreadsheet tools, commands, and options

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.

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 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 on the spreadsheet toolbar.
• To hide the Commands and Options dialog box, click Close in the upper right corner of the dialog box.

Add or remove sheets

1. Click Commands and Options 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 , 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 , 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 to move the sheet toward the beginning of the workbook, or Down 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 , 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 , 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 , 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 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 on the toolbar. You can undo multiple changes (up to 16) by clicking Undo 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 on the toolbar. For information about printing from Excel, see Excel Help.

Show All

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

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

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 ( ), 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:

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

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.

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.

Show All

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.

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

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.

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.

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.

Show All

Move and copy data

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 .
   • To copy the cells, click Copy .

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 .

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.

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 .
   • To copy the characters, click Copy .
4. In the cell, click where you want to paste the characters.
5. Click Paste .
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.

Show All

Insert or delete rows or columns

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.

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.

Show All

Insert, change, or delete a hyperlink

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.

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.

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.

Show All

Clear cell contents

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

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 .

Show All

About formulas

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.

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.

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.

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.

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)

Show All

Examples of commonly used formulas

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)

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")

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.

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

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")

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.

Show All

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.

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 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 , 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 .

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 , 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.

Show All

About using names in formulas

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.

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 , 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 , 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.

Show All

About cell and range references

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.

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.

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.

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

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.

Show All

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.

Types of operators

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.

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.

Text concatenation

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

Reference

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

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.

Show All

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.

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.

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).

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.

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}.

Show All

Create an array formula

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.

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.

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).

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.

Show All

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.

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).

Show All

Move or copy an array formula

1. Select the entire array range.

How?

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

Click Cut or Copy on the spreadsheet toolbar.

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

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.

Show All

Format cells in a spreadsheet

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

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.

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.

Place borders around cells

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

Change the background color of cells

• Under Text format, next to Cell, click the arrow next to Fill Color .
• On the color palette, click the color you want.

Align data in a cell

• To align text horizontally, click Align Left , Center , or Align Right under Text format.
• To align text vertically, click Align Top , Align Middle , or Align Bottom 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.

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 , and then click the Format tab.
3. Under Cell format, click Merge Cells or Unmerge Cells .

Show All

Change the width of a column

1. Make sure column headers are displayed.

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 , 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 .
4. Drag the boundary to the width you want, or, to make the column width fit the contents, double-click the right column boundary.

Show All

Change the height of a row

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

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 , 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 .
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.

Show All

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.

Display an entire spreadsheet right to left or left to right

1. On the toolbar, click Commands and Options , 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.

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 , 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 , 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.

Show All

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.

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 or Sort Descending 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".

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).

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 or Sort Descending 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.

Show All

Apply or remove a filter

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 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.

Remove a filter

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

Show All

Sum filtered data

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

How?

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 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.

Remove a filter

5. On the toolbar, click AutoFilter so that it is no longer selected.
2. Click the cell below the data you want to sum.
3. Click AutoSum 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.

Export a spreadsheet to an Excel workbook

• On the spreadsheet toolbar, click 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.

Show All

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.

#####

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.

#CIRC!

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

#DIV/0!

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

#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.

#NAME?

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

#NULL!

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

#NUM!

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

#REF!

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

#VALUE!

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

Show All

Troubleshoot spreadsheets

Finding Help information

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

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.

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.

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.

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.

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.

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

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.

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 on the toolbar is not selected.

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.

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.

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 on the spreadsheet toolbar to turn the filter off, and then click AutoFilter again to turn the filter on. This will also reset the displayed list.

Importing and exporting

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.

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.

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

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.

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.

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

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

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.