About spreadsheets on Web pages

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 and perform calculations that are based on that data by using formulas. When a spreadsheet is published on a Web page, Web users can interact with the data and perform calculations directly in their browsers. Users can change the data and immediately see the results of the calculations.

Spreadsheet on a Web page

An example of a spreadsheet on a Web page that was designed to be used as a mortgage calculator. When you change the values in the bordered input cells, the spreadsheet calculates the mortgage payment.

More information

Learn about calculation in spreadsheets

Learn about formulas in spreadsheets

Learn about spreadsheet tools


About the Spreadsheet Toolbar and Property Toolbox

Toolbar The toolbar in a spreadsheet appears at the top of the spreadsheet and provides a set of commands that you can use to work with the data in the spreadsheet. To see the command name of each button, rest the pointer on the button.

Spreadsheet Property Toolbox The Spreadsheet Property Toolbox includes commands, such as Find Next, to find data in the spreadsheet, and properties you can set, such as the background color of cells and the widths of columns. The toolbox includes some commands that aren't present on the toolbar or right-click menus. To display the toolbox, click the Property Toolbox button [image] on the toolbar in the spreadsheet.

The commands and properties in the Spreadsheet Property Toolbox are distributed among several sections, each identified by a bar that displays the name of the section. When you first display the toolbox, the General and Format commands and properties are displayed. You can display (or expand) and hide the contents of the sections by clicking the bars. How to display and use the Spreadsheet Property Toolbox.

Some features and commands, such as the toolbar or the toolbox, can be disabled when the spreadsheet is designed. If a feature is not available, contact the person who created the spreadsheet.


Spreadsheet toolbar buttons and commands

Office logo cube Displays information about the Spreadsheet Component, including the version you have installed.

Undo Reverses the last command or actions, or deletes the last entry you typed. Learn about Undo.

Cut Removes the selection from the spreadsheet and places it on the Clipboard.

Copy Copies the selection to the Clipboard.

Paste Inserts the contents of the Clipboard at the insertion point, and replaces any selection. This command is available only if you have cut or copied something to the Clipboard.

AutoSum Adds numbers automatically with the SUM function. A range of cells to be added is suggested to you. If the suggested range of cells is incorrect, drag through the range you want and then press ENTER.

Sort Ascending Sorts the selected items in order from the beginning of the alphabet, the lowest number, or the earliest date.

Sort Descending Sorts the selected items in order from the end of the alphabet, the highest number, or the latest date.

AutoFilter A quick way to select specific items you want to display in a list.

Export to Excel Opens a copy of the spreadsheet in Microsoft Excel 2000 so that you can perform additional analysis of the data or save the data as a separate Excel workbook.

Property Toolbox Displays a toolbox you can use to change the formatting of the spreadsheet, view the formula contained within a cell, or find data within the spreadsheet.

Help Displays Help for the spreadsheet.


Spreadsheet Property Toolbox commands and options

Tip for finding a command or property in this topic quickly To find information about a specific command or property, click the Search tab in the Help window, type the name of the command or property in the box at the top of the tab, and then click List Topics. Click the name of this topic, and then click Display. When you scroll through this topic, all instances of the name you typed will be highlighted.

To find a command or property by using the following list Click the name of the section for which you want information:

General section

Format section

Show/Hide section

Calculations section

Find section

General section

Option | Description
Undo | Reverses the last command or deletes the last entry you typed. You can undo multiple actions.
Help | Displays Help topics

Return to top

Format section

Option | Description
Text format | Displays the text in the selected cells bold, italic, or underscored, and sets the font color.
Font list | Sets the font for the text in the selected cells from the list of fonts available on your system.
Size list | Sets the size of the text in the selected cells from the font sizes available on your system.
Cell Format commands
Fill color | Sets the background color for the selected cells.
Merge cells | Combines the selected cells into one cell.
Unmerge cells | Separates a merged cell into individual cells.
Borders | Applies borders of the color and thickness you specify to the selected cells.
Column width box | Sets the width, in pixels, of the selected columns.
Row height box | Sets the height, in pixels, of the selected rows.
Horizontal alignment list | Aligns text within cells General, Left, Center, or Right. General left aligns text and right aligns numbers and dates.
Vertical alignment box | Aligns text with cells Top, Center, or Bottom.
Number format list | Sets the display format for numbers. See a list of the available number formats.

Return to top

Show/Hide section

Option | Description
Toolbar | Displays the toolbar for the spreadsheet.
Gridlines | Displays gridlines between the spreadsheet cells.
Title bar | Displays the title bar at the top of the spreadsheet.
Column headers | Displays letters for each column at the top of the spreadsheet.
Row headers | Displays row numbers at the side of the spreadsheet.

Return to top

Calculations section

Option | Description
Formula in active cell (active cell reference) box | Displays the formula in the active cell. The result of the formula is displayed next to the box. You can make changes to the formula only in the cell, not in the toolbox.
Calculate Now button | Recalculates the spreadsheet when the Manual calculation option is selected .
Automatic calculation option | Recalculates the spreadsheet automatically whenever the data changes.
Manual calculation option | Recalculates the spreadsheet only when you click Calculate Now or press F9.

Return to top

Find section

Option | Description
Find box | Allows you to enter text or a value to search for in the selected range or, if a single cell is selected, the entire spreadsheet.
Find Next button | Searches for the text or value in the box above.

Return to top


How to get additional assistance with designing your spreadsheet

When you view and interact with a spreadsheet in your Web browser, you are working with a control that was created in another program called a design program. In a design program, 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.

This topic provides information about how to start designing a spreadsheet and then get additional Help:

Designing your own spreadsheet

Getting Help designing spreadsheets

Getting Help writing scripts and programs for spreadsheets

Designing your own spreadsheet

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.

Microsoft FrontPage 2000

Use FrontPage 2000 when you want to:

  • Create a new spreadsheet for users who have Microsoft Internet Explorer version 4.01 or later.

  • Add a spreadsheet to a new or existing Web page.

  • Modify a Web page that was published from Microsoft Excel 2000, particularly to change settings for the spreadsheet, such as hiding the toolbar.

Note To interact with spreadsheets that you create in FrontPage, users must have Internet Explorer 4.01 or later and the Microsoft Office Web Components. FrontPage 2000 is available in Microsoft Office 2000 Premium. The Office Web Components are available in Office 2000 Standard, Professional, and Premium.

Microsoft Access 2000

Use data access page Design view in Access 2000 when you want to:

  • Create and modify data access pages for users who have Internet Explorer 5 or later (or who use Internet Explorer 4.x but also have Internet Explorer 5 installed), especially pages that might include a spreadsheet.

  • Modify an existing Web page, particularly for adding data from a database to the page.

Note To interact with spreadsheets that you create in Access, users must have Internet Explorer 5 or later and the Microsoft Office Web Components. Access 2000 is available in Microsoft Office 2000 Professional and Premium. The Office Web Components are available in Office 2000 Standard, Professional, and Premium.

Microsoft Excel 2000

Use Excel 2000 when you want to:

  • Enter data, create formulas, apply different formats, or use other Excel features you would like published in the spreadsheet.

  • Publish data in a spreadsheet you want to use on a Web page.

  • Append a spreadsheet to the end of an existing Web page.

  • Update the data in a spreadsheet by republishing the spreadsheet.

Note To interact with spreadsheets that you publish from Excel, users must have Internet Explorer 4.01 or later and the Microsoft Office Web Components. Excel 2000 and the Office Web Components are available in Microsoft Office 2000 Standard, Professional, and Premium.

Microsoft Visual Basic or a scripting language

Use Visual Basic or a scripting language when you want to:

  • Use a programming environment such as Microsoft Visual Basic when you want to design a spreadsheet for use in a custom solution instead of in a Web browser.

  • Use a scripting language or a programming environment such as Microsoft Visual Basic when you want to create programs or scripts in both FrontPage 2000 and data access page Design view in Access to create a spreadsheet.

Return to top

Getting Help designing spreadsheets

Working with a spreadsheet in a design program When you create or activate a spreadsheet in a design program such as FrontPage 2000 or Access 2000 data access page Design view, the Spreadsheet Property Toolbox provides additional properties and commands that you can use to customize how the spreadsheet can be used in the browser. The design program might also provide its own commands and features for preparing source data and attaching it to a spreadsheet.

Getting Help in the design program While you are working in a design program, you can get Help designing a spreadsheet in either of two ways:

  • Design program Help For information about the commands and features available in the design program to work with spreadsheets, see Help for the design program. For example, in Microsoft Access, click the Office Assistant, or click Microsoft Access Help [image].

    Microsoft Excel provides both general Help about saving and publishing Excel data for use on Web pages and specific information about preparing data for use in a worksheet and then publishing the worksheet as a spreadsheet on a Web page. To access this Help in Excel, click the Office Assistant, or click Microsoft Excel Help [image].

  • Spreadsheet design Help In a spreadsheet that you have created or activated in a design program, click Help [image] on the spreadsheet's toolbar. This method displays design Help topics about the properties and commands you can use within the spreadsheet in the design program. This design program Help is not the same as the Help for the browser (that is, the Help topics you are currently viewing).

Return to top

Writing scripts and programs for spreadsheets

You can use scripts and programs to automate spreadsheets, or you can use 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 2000 programs provide the Microsoft Script Editor to help you write scripts. For example, you can use the Script Editor from Microsoft Access data access page Design view to customize a spreadsheet. For information about running the Script Editor, see Help for 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 use the Microsoft Office Web Components Visual Basic Reference to display information about the object model, properties, and methods specific to spreadsheets. This object model Help is installed whenever you install the Office Web Components, of which spreadsheets are one component. Depending on the design program you're using, you can access this Help in either of two ways:

  • Help in Visual Basic In Visual Basic or Visual Basic for Applications, you can create a reference to the Microsoft 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 Visual Basic for Applications.

  • Help in other environments If you are using another development environment, locate and double-click the Help file Msowcvba.chm.

Return to top


Keyboard shortcuts in spreadsheets

This topic lists:

Keys for activating, moving around, and selecting

Keys for carrying out commands

Keys for entering, editing, and copying data

Keys for working with AutoFilter drop-down lists

Keys for activating, moving around, and selecting

Press | To
The TAB key | Select cells in the spreadsheet.
SHIFT+TAB | Select the previous cell in the spreadsheet.
Arrow keys | Move the selection in the direction of the arrow key.
SHIFT+arrow key | Extend the selection in the direction of the arrow key.
CTRL+SPACEBAR | Select the current column.
SHIFT+SPACEBAR | Select the current row.
SHIFT+PAGE DOWN | Extend a selection down one screen. A screen is all of the data currently displayed in the spreadsheet.
SHIFT+PAGE UP | Extend a selection up one screen. A screen is all of the data currently displayed in the spreadsheet.
HOME | Move the selection to the first cell in the current row.
CTRL+HOME | Move the selection to cell A1 or the top left cell of the visible range.
CTRL+END | Move the selection to the last cell in the spreadsheet that contains data and is not hidden by filtering.
CTRL+arrow keys | Move the selection to the last cell in that direction of the current area containing data (the area of data bounded by blank rows and columns).
SCROLL LOCK+arrow keys | Scroll in the direction of the arrow key.
PAGE DOWN | Scroll down one screen. A screen is all of the data currently displayed in the spreadsheet.
PAGE UP | Scroll up one screen. A screen is all of the data currently displayed in the spreadsheet.
CTRL+A | Select all cells in the spreadsheet.

Return to top

Keys for carrying out commands

Press | To
F1 | Display Help topics. For information about keys to use in the Help window, see Help for your Web browser.
SHIFT+F10 | Display the shortcut menu for the spreadsheet.
underlined letter | Carry out a command on the shortcut menu when the menu is displayed. See ALT+ENTER for information about the Spreadsheet Property Toolbox.
ESC | Close the shortcut menu without carrying out a command.
ALT+ENTER | Display the Spreadsheet Property Toolbox. No keyboard shortcuts are available to carry out commands or set properties from the Spreadsheet Property Toolbox.
ALT+F4 | Close the Spreadsheet Property Toolbox.
CTRL+7 | Display or hide the toolbar for the spreadsheet.
CTRL+Z | Undo. Reverses the last command or deletes the last entry you typed.
ALT+= | Add numbers automatically with the SUM function.
CTRL+T | Turn AutoFilter on or off.
CTRL+E | Export the spreadsheet to Microsoft Excel.
F9 | Recalculate the spreadsheet.
CTRL+P | Print the spreadsheet.
F5 | Refresh data that was imported from other Web pages if the person who created the spreadsheet selected the Refresh from URL at run time check box on the Import Data section in the Property Toolbox and updates formulas that link to other controls.

Return to top

Keys for entering, editing, and copying data

Press | To
F2 | Edit the contents of the selected cell.
ENTER | Enter data in the current cell.
CTRL+ENTER | Enter data in multiple selected cells.
ESC | Cancel entering or changing data in a cell.
BACKSPACE | Clear the contents of the current cell.
DELETE | Clear the contents of the selected cells.
CTRL+X | Remove the selected data from the spreadsheet and place the data on the Clipboard.
CTRL+C | Copy the selected data from the spreadsheet to the Clipboard.
CTRL+V | Insert the contents of the Clipboard at the insertion point, and replace any selected data.
CTRL+B | Make text in the selected cells bold.
CTRL+U | Make text in the selected cells underlined.
CTRL+I | Make text in the selected cells italic.
ALT+0128 on the numeric keypad | Enter the euro currency character Euro currency symbol .

Return to top

Keys for working with AutoFilter drop-down lists

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

Return to top


Display the spreadsheet toolbar

  1. Right-click a cell on the spreadsheet, and then click Property Toolbox.

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. To display the spreadsheet toolbar, make sure Toolbar [image] is pressed in.

Notes If the Property Toolbox command is not available on the shortcut menu, the person who created the spreadsheet might have turned off access to the toolbox. Contact the spreadsheet creator for assistance.


Hide the spreadsheet toolbar

  1. On the toolbar in the spreadsheet, click Property Toolbox [image].

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. To hide the spreadsheet toolbar, make sure Toolbar [image] is not pressed in.

Note If the Property Toolbox [image] button is not available on the toolbar, the person who created the spreadsheet might have turned off access to the toolbox. Contact the spreadsheet creator for assistance.


Display the Spreadsheet Property Toolbox

  1. On the spreadsheet toolbar, click Property Toolbox [image].

  2. To expand or hide the contents of a section, click the bar with the name of that section.


Display or hide the title bar for the spreadsheet

  1. On the toolbar in the spreadsheet, click Property Toolbox [image].

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. Do one of the following:

    To show the title bar for the spreadsheet, make sure Title bar [image] is pressed in.

    To hide the title of the spreadsheet, make sure Title bar [image] is not pressed in.


Display or hide headers for each column

Column headers are the letters in gray cells (or other color, depending on your Windows display settings) across the top of the spreadsheet.

  1. On the toolbar in the spreadsheet, click Property Toolbox [image].

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. Do one of the following:

    To show the headers for each column, make sure Column headers [image] is pressed in.

    To hide the headers for each column, make sure Column headers [image] is not pressed in.


Display or hide row numbers

  1. On the toolbar in the spreadsheet, click Property Toolbox [image].

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. Do one of the following:

    To show the headers for each row, make sure Row headers [image] is pressed in.

    To hide the headers for each row, make sure Row headers [image] is not pressed in.


Display or hide gridlines

  1. On the toolbar in the spreadsheet, click Property Toolbox [image].

  2. If the Show/Hide section is not expanded, click the Show/Hide bar.

  3. Do one of the following:

    To show gridlines, make sure Gridlines [image] is pressed in.

    To hide gridlines, make sure Gridlines [image] is not pressed in.


Saving changes to a spreadsheet

If you're using Microsoft Internet Explorer 4.01 or later, changes that you make to a spreadsheet are retained while you are working in the browser, even when you return to the spreadsheet after viewing other Web pages. You cannot save your changes in the browser to a file, however.

Note If you close the browser, or if you click the Address bar and press ENTER, changes you made to the spreadsheet will be lost.

To make changes to a spreadsheet and save your changes in a file, you need to open the spreadsheet in a design program, such as Microsoft FrontPage 2000 or data access page Design view in Microsoft Access. You can also export the spreadsheet to Microsoft Excel and save the spreadsheet as an Excel workbook.


Export a spreadsheet to a Microsoft Excel workbook

If you want to perform additional analysis on the data in the spreadsheet, save changes you've made to the spreadsheet, or take advantage of Excel 2000's printing features, you can export the spreadsheet to an Excel 2000 workbook.

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

Notes

  • Because Excel supports only 256 columns, data in columns IW through ZZ is not exported from the spreadsheet. You can copy this data to an Excel worksheet.

  • When you export data from a spreadsheet to Excel, only the values and formulas are exported. Hyperlinks continue to work, but links to other data are changed to their last resulting value, and no links to the source data are maintained.

  • 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, or On/off spreadsheet formats are displayed with custom number formats in Excel. For information about custom number formats in Excel, see Excel Help. Learn about spreadsheet number formats.


Print your spreadsheet

To print a spreadsheet from the browser, click Print on the File menu in Microsoft Internet Explorer.

Notes

  • If the spreadsheet is wider than the printed page, the part on the right side that does not fit is not printed.

  • For more control over how the data is printed, including the ability to print all of a long list and set page breaks, you can export the spreadsheet to Microsoft Excel. For information about printing from Excel, see Excel Help.


Undo changes to a spreadsheet

  • To undo the last change you made in the spreadsheet, click Undo [image]. You can undo multiple changes.

  • To undo all changes you made to the spreadsheet, close the program you are using to view the spreadsheet, restart it, and then reopen the page.


Find data in a spreadsheet

  1. Do one of the following:

    To search through the entire spreadsheet, click a single cell.

    To search within a range of cells, select the range.

  2. On the toolbar in the spreadsheet, click the Property Toolbox [image].

  3. If the Find section is not expanded, click the Find bar.

  4. In the box, type the text or value you want to find.

  5. Click Find Next.

Notes

  • What you can find The Find command searches for text, numbers, dates, and times. It locates any of these values that have been entered directly into cells, or that are the results of formulas. For example,
This entry | Finds
John | John Johnson Little John
son | sonny Johnson
5201 | 5201 (as a number in a cell) 5201 First Street (as text in a cell)

  • Wildcards The Find command does not allow wildcards, such as ? and *.

  • Formatted values The Find command searches for values, not formats. For example, a cell may be formatted with the Yes/No number format. If the cell contains a zero (0), the cell displays the word No. However, if you use the Find command to locate the phrase No, this cell will not be found.

  • Which direction does it search The Find command searches from left to right across the columns of the first row, and then down to the next row.

  • Hidden data The Find command does not find data in hidden cells or cells that are hidden by filtering.


Filter a spreadsheet to display only the data you want to see

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

  1. Select a cell within the list.

  2. On the toolbar in the spreadsheet, click AutoFilter [image].

  3. Click the arrow in the column that contains the data you want to display.

  4. Do one of the following:

    To display items, select the check box for each item you want to display.

    To hide items, clear the check box for each item you want to hide.

    To display all items, select the Show All check box.

    To hide all items in the list, clear the Show All check box.

Notes

  • To display all items in a filtered column again, click the arrow in the column and select the Show All check box.

  • To quickly move to an item in the AutoFilter drop-down list, type the first letter or number of the item.

  • To turn off filtering and display all data in the spreadsheet again, click AutoFilter [image] again.

  • To filter a range of data that is not set up as a list, select the range and then click AutoFilter [image].

  • You cannot filter a range that includes merged cells. How to split up merged cells.

  • You cannot move or delete data from a range that includes filtered rows. To move or delete the data, you must first turn off filtering.


Sum filtered data in a spreadsheet

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

  2. Click the cell below the data you want to sum.

  3. On the toolbar in the spreadsheet, click AutoSum [image].

Notes

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

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Sort data in a spreadsheet

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

  1. Do one of the following:

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

    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. Do one of the following:

    To sort the column in A – Z 0 – 9 order, click Sort Ascending [image] on the toolbar in the spreadsheet, and then select the column you want to sort by.

    To sort the field in Z – A 9 – 0 order, click Sort Descending [image], and then select the column you want to sort by.

Notes

  • To undo the sort and return the items of data in the field to their previous order, click Undo [image].

  • Columns hidden by filtering aren't sorted.

  • You can only sort rows. 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".

  • You can sort by more than one column by sorting the columns one at a time.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


About sorting data by more than one column

To sort by more than one column, you can sort the data for one column and then the other column. The order in which you sort the columns determines how the data will be organized.

Example of data sorted by two columns

In this example, the list is organized primarily by salesperson and secondarily by product. The Product column was sorted first, so that the products is in alphabetic order; the Salesperson column was sorted second.

When sorting multiple columns, determine the order of categorization you want first, and then work backwards, sorting the column of highest categorization last. For example, if you're sorting on two columns, first determine which is the higher-category column and which is the lower-category column. Next, sort the lower-category column, and finally, sort the higher-category column. To list the products in alphabetic order for each salesperson, you would sort the Product column (the lower category) first and then the Salesperson column (the higher category).

How to sort data in a spreadsheet


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

This topic provides tips about entering:

Text

Numbers

Dates and times

Dates in the year 2000 and beyond

Entering text

Characters treated as text In a spreadsheet, text is any combination of numbers, spaces, and nonnumeric characters. For example, a spreadsheet treats the following entries as text:

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

Aligning text 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 or enter "hard" carriage returns within a cell.

Euro sign To enter the euro sign Euro currency symbol, type ALT+0128 on the numeric keypad. If you have Microsoft Windows 95 or Microsoft Windows NT 4.0 with Service Pack 3 or earlier, you might also need to install the euro product update from the Microsoft Web site. For information about connecting to the Microsoft Web site, see Help in your design program. The fonts that can display the euro sign are as follows.

Operating system | Fonts
Microsoft Windows 95 with the euro product update | Arial Black, Arial, Comic Sans (regular and bold), Courier New, Impact, MS Sans Serif, Tahoma (regular and bold), Times New Roman, Verdana
Microsoft Windows 98 | Arial, Courier New, Tahoma (regular and bold), Times New Roman
Microsoft Windows NT 4.0 with Service Pack 3 or earlier and the euro product update | Arial, Courier New, Times New Roman
Microsoft Windows NT 4.0 with Service Pack 4 | Arial, Courier New, Lucida Console, Lucida Sans Unicode, Times New Roman

Return to top

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 as a decimal. All other combinations of numbers and 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 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 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 may 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) if the number is longer than the width of the cell. The General format displays up to 11 digits, including a decimal point and characters such as "E" and "+." To use numbers with 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 that are recognized as numbers depend on the options that are selected in the Regional Settings in Control Panel. These options also determine the default format for numbers — for example, the period (.) that's used as the decimal symbol on United States-based systems.

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

Return to top

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 that are selected in the Regional Settings in Control Panel determine the default format for the current date and time and the characters recognized as date and time separators — for example, the slash (/) and dash (–) for dates, and the colon (:) for times on United States-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 include the four-digit year.

Note If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can use Regional Settings (Windows 98) or Regional Options (Windows 2000) for dates in Control Panel to change the century interpretation and change the default date format to display four-digit years without the assistance of your system administrator. For more information, see Help in Windows.

Dates in the year 2000 and beyond Learn about how a spreadsheet handles dates in the year 2000 and beyond.

Entering dates and times together To type a date and time in the same cell, separate the date and time with a space.

The 12-hour or 24-hour clock To type a time based on the 12-hour clock, type a space followed by AM or PM after the time. Otherwise, the spreadsheet bases the time on the 24-hour clock. For example, if you type 3:00 instead of 3:00 PM, the time is stored as 3:00 AM. If you type 3:00 p, as you would in Microsoft Excel, the characters are stored as text.

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 to display a time as a fraction, select the cells that contain the date or time, and change the number format to the Fixed format. In Fixed format, the date portion of the number is to the left of the decimal point, and the time portion is to the right of the decimal point.

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

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

The date system In a spreadsheet, days are numbered from the beginning of the twentieth century; for example, the date serial number 1 corresponds to the date January 1, 1900.

Return to top


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

  1. Click the cell where you want to enter data.

  2. Type the data and press ENTER or TAB.

    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 that's based on the 12-hour clock, type a space and then type AM or PM after the time; for example, 9:00 PM. Otherwise, the spreadsheet interprets the time as AM. If you type only a or p, such as 9:00p, the spreadsheet stores the characters as text instead of as a time.

Tips

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

  • See tips for entering numbers, text, dates, and times.

  • Learn about how a spreadsheet handles dates in the year 2000 and beyond.

  • Learn about how to enter formulas.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Change data in a cell

  1. Select the cell you want to change.

  2. Do one of the following:

    To enter a new number or text in the cell and replace 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 Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


About moving or copying data within a spreadsheet

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.

How 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; relative cell references will change appropriately. Learn about absolute and relative references.

How 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 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 Clipboard in HTML format. When you paste the cells into a Microsoft Excel worksheet, the formulas, values, and cell formats are reproduced on the worksheet. When you paste the cells into a Microsoft Word document, the cells are pasted as a table, including the cell formatting, and values but not formulas are copied. You can also copy cells to other programs that can interpret or accept the HTML format.

Copying in the spreadsheet vs. 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 a moving dashed border to indicate what was copied.

  • No Paste Special command When you copy data in a spreadsheet, you copy values, formatting, and formulas. You cannot copy these elements separately, transpose rows for columns when you copy, 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. To add blank space for data that you copy or move, you can insert rows and columns before you copy or move the data.

  • 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 Excel, the cut data isn't removed from its original location until you paste it. When you move data in a spreadsheet, the data disappears as soon as you cut it, and you can paste the data only once (you can paste copied data multiple times).

More information

Move or copy whole cells

Move or copy characters within a cell


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 [image].

    To copy the characters, click Copy [image].

  4. In the cell, click where you want to paste the characters.

  5. Click Paste [image].

  6. Press ENTER.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Move or copy whole cells in a spreadsheet

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. 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 pasted 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 selected cells, click Cut [image].

    To copy the selected cells, click Copy [image].

  3. Select the upper-left cell of the target destination area.

  4. Click Paste [image].

Notes

  • Cells that you copy or move replace any data in the area where you paste the cells.

  • When you move a formula, the cell references within the formula do not change. When you copy a formula, absolute cell references do not change; however, relative cell references will change. Learn about absolute and relative references.

  • 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. After you finish pasting the data, you can select and delete the original data.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Insert a row or column

  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.

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

  2. Do one of the following:

    To insert a row, right-click the cell or selection, point to Insert on the shortcut menu, and then click Rows.

    To insert a column, right-click the cell or selection, point to Insert on the shortcut menu, and then click Columns.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Delete a row or a column

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

    To delete multiple rows or columns, select multiple cells.

  2. Do one of the following:

    To delete a row, right-click the cell or selection, point to Delete on the shortcut menu, and then click Rows.

    To delete a column, right-click the cell or selection, point to Delete on the shortcut menu, and then click Columns.

Notes

  • You cannot delete rows from a range that includes filtered rows. To delete the rows, you must first turn off filtering by clicking AutoFilter [image].

  • If you delete data that's used in a formula elsewhere on the spreadsheet, you'll see an error value, such as #REF.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Clear spreadsheet cell contents

  1. Select the cells that you want to clear.

  2. Press DELETE.

Tips

  • 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 [image].

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


About using formulas in a spreadsheet

This topic contains information about:

What is a formula?

Elements of a formula

Calculation

Constants vs. dependent cells

Functions

What is a formula?

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

=A1+A2

Learn about commonly used formulas in a spreadsheet.

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.

Return to top

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, subtraction, etc. — to be performed on the elements of the formula. Learn more about calculation operators and expressions.

Return to top

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. In the following example:

=5+2*3*4

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.

Learn about the order in which operators are calculated

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), and 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. Learn more about calculation in a spreadsheet.

Note If the person who created the spreadsheet has enabled the Display right to left option, formulas begin with an equal sign on the right and are calculated from right to left.

Return to top

Constants vs. dependent cells

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

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

Data in a spreadsheet

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.

Return to top

Functions

The Spreadsheet Component contains many predefined, or built-in, formulas, which are known as functions. Functions can be used to perform simple or complex calculations. The most frequently used function is the SUM function, which is used to add the numbers in a range of cells. Learn about using functions in a spreadsheet.

Note When a spreadsheet is published to a Web page from Microsoft Excel, most functions are published as expected. However, some Excel worksheet functions are not available in the Spreadsheet Component. When these functions are published from Excel, they are replaced with the resulting values of the functions. Learn about Excel worksheet functions not available in the Spreadsheet Component.

Return to top


About cell and range references

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 you want to use in a formula. By using references, you can use data 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 ZZ, for a total of 702 columns) and labels rows with numbers (1 through 65536). 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 that's in the upper-left corner of the range, type a colon (:), and then enter the reference to the cell that's in the lower-right corner of the range. The following are examples of references.

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

Note Depending on the task you want to perform in the spreadsheet, you can use either relative cell references, which are references to cells relative to the position of the formula, or absolute references, which are cell references that always refer to cells in a specific location. Learn about absolute and relative references.


About absolute and relative references

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

Formula with relative reference

When you copy 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 B6, =A5, which is one cell above and to the left of B6, has been copied to cell B7. The formula in cell B7 adjusts to =A6, which refers to the cell that is one cell above and to the left of cell B7.

Copied formula with relative reference

Absolute references If you don't want references adjusted when you copy a formula to a different cell, use an absolute reference. You can create an absolute reference to a cell by placing 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

More information

About cell and range references


About calculation in a spreadsheet

Calculation is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. 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 recalculates data.

Learn about using formulas in a spreadsheet.

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, and this is how it's 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 the number 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 the number as 6/22/2000 or as 22-Jun-2000. Changing the display of a value does not change the stored value. Change the format of a number in a spreadsheet cell.

The settings in the Regional Settings Properties dialog box in the 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. For information about these formats, see Windows Help.


Calculation operators and expressions in formulas

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

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

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

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

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

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

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

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

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


Order of operators in formulas

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 the part of the formula to be calculated first in parentheses. Learn more about calculation operators.

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


Create a formula

  1. Click the cell in which you want to add a formula.

  2. Type = (equal sign).

  3. Type the formula.

  4. Press ENTER.

Notes

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

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.

More information

Using formulas in a spreadsheet

Using functions in a spreadsheet


Change a formula in a spreadsheet

  1. Click the spreadsheet.

  2. 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 click the cell once.

  3. Make changes to the formula in the cell.

  4. Press ENTER.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


View the formula in a cell

  1. Click the cell that contains the formula you want to view.

  2. On the toolbar in the spreadsheet, click Property Toolbox [image].

  3. If the Calculations section is not expanded, click the Calculations bar.

  4. Review the formula in the Formula in active cell (reference) box. The result of the formula is displayed next to the formula box.

Notes

  • To view the formula in another cell, click the cell while the Calculations section is expanded.

  • Long values and formulas can be truncated in the Calculations section. To view the whole formula, click the cell and press F2. When you are done viewing the formula, press ESC to return to the spreadsheet.


Insert a sum 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.

  2. On the spreadsheet toolbar, click AutoSum [image] .

    A suggested formula is displayed in the cell you selected (for a row) or in the cell below the range you selected (for a column).

  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.

Notes

  • If you're summing a column of data, you can select the range of cells you want to sum first, and then click Autosum [image] .

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Examples of commonly used formulas

This topic provides the following examples of commonly used formulas for spreadsheets. Learn more about using formulas in spreadsheets.

Create a sum that's based on one condition

Count the occurrences of a value or a condition

Calculate a running balance

Join first and last names

Join a date with text

Increase a number by a percentage

Create a sum that's based on one condition

Use the SUMIF spreadsheet function to create a total value for one range 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 total for the corresponding cells in the range F5:F25.

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

Return to top

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

Return to top

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

Return to top

Join first and last names

With 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

Return to top

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 number, date, or time value stored in a cell with a text string, use the TEXT spreadsheet function. For example, if cell F5 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(F5, "d-mmm-yy")

Return to top

Increase a number by a percentage

You can increase a numeric value stored in a cell by a percentage, such as 5 percent. In this example, assume that cell F5 contains the original value.

=F5*(1+5%)

If the percentage amount is stored in a cell (for example, cell F2):

=F5*(1+$F$2)

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

Return to top


Change how a spreadsheet recalculates

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. Click the spreadsheet.

  2. On the toolbar in the spreadsheet, click Property Toolbox [image].

  3. If the Calculations section is not expanded, click the Calculations bar.

  4. Do one of the following:

    To recalculate the spreadsheet whenever a value changes, click Automatic calculation.

    To recalculate the spreadsheet only when you press F9 or click Calculate Now, click Manual calculation.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


About creating a formula that uses data on the same Web page

You can return a value in a cell from data that's on the same Web page as your spreadsheet by using the HTML function Document. For instance, if you have a text box control on your page that's named Text1, the following formula will retrieve the value from the text box and display it in the cell that contains the formula:

=document.text1.value

You can also create formulas that perform calculations on values returned from the Web page. For instance, if the text box you're returning a value from contains a sales amount, and cell C1 on the spreadsheet contains a commission rate, you could multiply the value returned from the Web page with the value in the commission rate cell:

=$C$1*document.text1.value

You can also reference a value in another spreadsheet on the same Web page. For instance, if you have spreadsheet1 and spreadsheet2 on a Web page, the following formula in spreadsheet1 returns the value from cell A1 in spreadsheet2:

=document.spreadsheet2.range("a1").value

Notes

  • For more information on the functions, controls, and properties you can use on a spreadsheet, see the programming help for spreadsheets.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


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 adds 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, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

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, then the nested function must return a TRUE or FALSE. If it doesn't, the spreadsheet displays a #VALUE! error value.

Learn about the functions available for spreadsheets.


Insert a hyperlink to another Web page in a spreadsheet

Before you enter a hyperlink, make sure you know the URL address of the Web page you want to link to.

  1. Click the cell where you want the hyperlink.

  2. Type the URL address beginning with http://, and press ENTER to enter the address as a hyperlink. For example, type http://sample.microsoft.com.

Notes

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

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Modify a hyperlink in a spreadsheet

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

  3. Type your changes to the hyperlink address or the formula that uses the HYPERLINK function.

Notes

  • 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, you can change the text displayed for the hyperlink, but you cannot change the address that the hyperlink goes to.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Excel. Learn about saving changes to a spreadsheet.


Change the format of a number

When you change the format of a number in a cell, the stored value for the number is not changed. For instance, the stored value for the date 6/22/2000 is 36699, even when the format is changed to Thursday, June 22, 2000.

  1. Click the spreadsheet.

  2. Select the cells that you want to format.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. In the Number format list, click the format you want.

Learn about the available number formats.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Number formats

You can change the formats of numbers in a spreadsheet by using the Spreadsheet Property Toolbox. The settings in the Regional Settings Properties dialog box in 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. Learn about getting help for programming a spreadsheet.

The following number formats are available in the Number format list in the Format section of the Spreadsheet Property Toolbox. How to change the format of a number in a spreadsheet cell.

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


Change the text font or size

  1. Click the spreadsheet.

  2. Select the cells you want to format.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. Do one of the following:

    To change the appearance of the text, click a button next to Text format.

    To change the font of the text, select a different font in the Font list.

    To change the size of the font, select a different font size in the Size list.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Change the width of a column

  1. Click the spreadsheet.

  2. Make sure column headers are displayed.

  3. Point to the right boundary of the column header until the pointer changes to Pointer with perpendicular double-headed arrow.

  4. Do one of the following:

    Drag the boundary until the column is the width you want.

    To make the column width fit the contents, double-click the column boundary.

Notes

  • To change the width of multiple columns at one time, select the columns before you drag or double-click the boundary.

  • You can change the column width according to a specific number of pixels by using the Column width command in the Format section of the Spreadsheet Property Toolbox. When you use this method, it's not necessary to display column headers first.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Change the height of a row

  1. Click the spreadsheet.

  2. Make sure the row numbers are displayed.

  3. Point to the bottom boundary of the row header until the pointer changes to Pointer with perpendicular double-headed arrow.

  4. Do one of the following:

    Drag the boundary until the row is the height you want.

    To make the row height fit the contents, double-click the row boundary.

Notes

  • To change the height of multiple rows at one time, select the rows before you drag or double-click the boundary.

  • You can change the column width according to a specific number of pixels by using the Row height command in the Format section of the Spreadsheet Property Toolbox. With this method, it's not necessary to display row numbers first.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Place borders around data

  1. Click the spreadsheet.

  2. Select the cells you want to add borders to.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. In the Cell format group, click Borders [image].

  6. In the border diagram, click a line to turn a border on or off. Click an intersection point to turn on or off all borders connected to that intersection.

    Apply border diagram

    The border diagram map varies depending on your selection. For example, the border diagram for a single cell will contain four border lines, one for each side. If more than one cell is selected, there will be additional lines in the border diagram to represent the borders between cells.

Notes

  • You can change the color or width of borders.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Change the color or width of borders

This procedure is for borders that you have placed around cells. You cannot change the color or width of gridlines in a spreadsheet.

  1. Click the spreadsheet.

  2. Select the cells with the borders that you want to change.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. In the Cell format group, click Borders [image].

  6. Do one of the following:

    To change the color, click Border color [image] and click a different color.

    To change the width, click a size in the list.

  7. In the border diagram, click the border line you want to change.

    When you click a border in the border diagram, the border in the diagram will not change. However, the corresponding border in the selection on the spreadsheet will change.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Align data to the left, center, or right of a cell

  1. Click the spreadsheet.

  2. Select the cells that contain the data you want to align.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. Click the alignment you want in the Horizontal alignment list.

Notes

  • The General alignment aligns text to the left side of a cell, and numbers, dates, and time to the right side of the cell.

  • Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Align data at the top, center, or bottom of a cell

  1. Click the spreadsheet.

  2. Select the cells that contain the data you want to align.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. Click the alignment you want in the Vertical alignment list.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Change the background color of cells in a spreadsheet

  1. Click the spreadsheet.

  2. Select the range of cells that you want to change.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. In the Cell format group, click the arrow next to the Fill color button [image].

  6. On the color palette, click the color you want.

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Merge multiple cells into one cell

Caution When you merge cells into one cell, only data in the upper-left cell is retained. Data in all other cells that you merge is deleted.

  1. Click the spreadsheet.

  2. Select the cells you want to merge.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. In the Cell format group, click Merge Cells [image].

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


Split a merged cell into separate cells

  1. Click the spreadsheet.

  2. Click the merged cell that you want to separate.

  3. On the toolbar in the spreadsheet, click Property Toolbox [image].

  4. If the Format section is not expanded, click the Format bar.

  5. In the Cell format group, click Unmerge cells [image].

Note Changes you make are retained during the browser session only. To save your changes in a file, you can export the spreadsheet to Microsoft Excel. Learn about saving changes to a spreadsheet.


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. For the latest information about how Microsoft products are affected by the arrival of the year 2000, see the Microsoft Year 2000 Resource Center site on the Web.

How the 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, you can type year values as four digits (for example, 2001, rather than 01).

Change how years are interpreted Your system administrator can customize 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 Microsoft Office 2000 Resource Kit.

Note If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can use Regional Settings (Windows 98) or Regional Options (Windows 2000) for dates in Control Panel to change the century interpretation and change the default date format to display four-digit years without the assistance of your system administrator. For more information, see Help in Windows.


I can't add a blank row or column to the end of the spreadsheet

The maximum number of rows in a spreadsheet is 65,536. The maximum number of columns in a spreadsheet is 702 (column A through column ZZ). If you are viewing fewer than these (for example, 10 rows), then the person who created the spreadsheet set the viewable range of the spreadsheet to a fewer number of rows and columns.

To add a blank row or column to the end of the spreadsheet that contains fewer than the maximum rows and columns, insert a row or column above the last row or to the left of the last column. Then, move the data in the last row or column to the new row or column by cutting and pasting.


I can't change the formula in or the contents of a cell.

The person who created the spreadsheet might have protected the cell. To change the contents, contact the creator of the spreadsheet.


I can't edit a formula by using the Spreadsheet Property Toolbox.

Formulas cannot be edited in the Spreadsheet Property Toolbox. Instead, edit the formula in the cell.


I can't use a name to represent a range or value.

The Spreadsheet Component does not support names or named ranges. If the spreadsheet you're using was published from a Microsoft Excel worksheet that contains a name or a named range, the names were converted to values or ranges.


I can't wrap text within cells.

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


The formula bar is missing.

Unlike Microsoft Excel, the 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. You can also view a formula by using the Spreadsheet Property Toolbox.


When I press ENTER, cell selection moves to the wrong cell.

The person who created the spreadsheet might have specified the direction that cell selection moves when the ENTER key is pressed. To change the direction, contact the creator of the spreadsheet.


When I press ENTER, the selection does not move to another cell.

The person who created the spreadsheet might have disabled the option to move the selection after ENTER is pressed. To have this option enabled, contact the creator of the spreadsheet.


Data does not sort correctly.

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, it 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 sorted but not within a header row. The spreadsheet will automatically detect the data to sort and exclude the header row. If this does not work properly for your data, try to select the entire range you want sorted 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. 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 there are no leading spaces inserted before data. Remove the leading spaces before sorting the data.

Check the country setting Sort orders vary by country setting. Make sure that you have the proper country setting in the Regional Settings dialog box in Windows Control Panel. For information about changing the country setting, see your Windows documentation.

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. Learn about sorting data by more than one column.


I can't sort the data in the spreadsheet.

The person who created the spreadsheet might have disallowed sorting. To sort the data, you can export the spreadsheet to Microsoft Excel 2000. If you want to sort the data in your browser, contact the creator of the spreadsheet.


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

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


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 display 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 [image] on the spreadsheet toolbar to turn the filter off; and then clicking AutoFilter [image] again to turn the filter on. This will also reset the displayed list.


My list shows only some data.

AutoFilter is on The list might be filtered 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.

  • Click the AutoFilter button [image] on the toolbar to turn AutoFilter off.

Only a specific range can be viewed The person who creates the spreadsheet might have displayed only a specific range of cells. Specifying the viewable range allows confidential information to be hidden. If you need to see data outside the viewable range, export the spreadsheet to a Microsoft Excel workbook, or contact the creator of the spreadsheet.


The filtering commands don't work.

The person who created the spreadsheet might have disallowed filtering. To filter data, you can export the spreadsheet to Microsoft Excel 2000. If you want to filter data in your browser, contact the creator of the spreadsheet.


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 Custom AutoFilter dialog box in Microsoft Excel. For example, there is no direct equivalent in Microsoft Excel for a spreadsheet filter that has three items unchecked. 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. Excel.

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 ZZ. Microsoft Excel worksheets, on the other hand, can contain data only in columns A through IV. To export your spreadsheet to Microsoft Excel, you must delete or move data from columns beyond IV.


One or both of my scroll bars are missing.

The person who created the spreadsheet might have hidden the scroll bars, or might have set the spreadsheet to resize automatically so that scroll bars appear and disappear as necessary. To display either scroll bar, contact the creator of the spreadsheet.


When I scroll, some of the data does not move.

The person who created the spreadsheet might have turned on the Freeze panes option. This option can be used to keep specific rows or columns "frozen" on screen while other rows and columns scroll. To change the status of the frozen panes, contact the creator of the spreadsheet.


The spreadsheet changes size while I'm working with it.

As you insert or delete columns or rows or change row heights or column widths, some spreadsheets automatically adjust by expanding or shrinking. The person who created the spreadsheet might have set an option to allow automatic resizing.


When I open a Web page that contains a spreadsheet, I get the message "Cannot open URL."

The Web page is not local If the spreadsheet contains a formula that links to data on another page, that other page must reside on your local computer or intranet, and you must have proper access to it.


When I click the Edit button in my Web browser, my Web page opens in the wrong program.

Which programs are available from the Edit button in the Web browser depends 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, close the program, start the program you want to use, and open the Web page from there.


What does the error ##### mean?

Increase the width of the column 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. How to change the width of a column.


What does the error #CIRC! mean?

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

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


What does the error #DIV/0! mean?

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

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


What does the error #N/A mean?

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

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

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

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


What does the error #NAME? mean?

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

Possible cause | Suggested action
Misspelling the name of a function. | Correct the spelling.
Entering text in a formula without enclosing the text in double quotation marks. The entry is not interpreted properly as text. | Enclose text in the formula in double quotation marks. For example, the following formula joins a piece of text "The total amount is " with the value in cell B50: ="The total amount is "&B50
Omitting a colon (:) in a range reference. | Make sure all range references in the formula use a colon (:), for example, SUM(A1:C10).
The spreadsheet function dynamic link library file, Msowcf.dll, is missing. | Search for Msowcf.dll on your computer, and if it's missing, either reinstall Microsoft Office 2000 or contact your system administrator.


What does the error #NULL! mean?

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

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


What does the error #NUM! mean?

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

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


What does the error #REF! mean?

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

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


What does the error #VALUE! mean?

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

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


Some functions display the error #NAME? instead of a value.

Some of the spreadsheet functions are provided through a separate dynamic link library file, Msowcf.dll. If this file is missing, the functions display the #NAME? error value when you open the spreadsheet in your browser. Search for Msowcf.dll on your computer, and if it's missing, either reinstall Microsoft Office 2000 or contact your system administrator.