About designing and publishing a spreadsheet

Overview

Before you begin

Creating and customizing a spreadsheet

Testing and publishing

Overview

You can use a spreadsheet to enter and calculate data. A spreadsheet consists of columns and rows of cells. You can enter data directly into the cells of the spreadsheet and perform calculations that are based on the data by using formulas. When you create an interactive spreadsheet by using one of the Microsoft Office 2000 design programs and put that spreadsheet on a Web page, you give Web users the opportunity to interact with the data and perform calculations directly in the browser. Users can make changes to the data and see the results of calculations automatically.

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 the user changes the values in the bordered input cells in the browser, the spreadsheet calculates the mortgage payment.

Design process The process of designing an interactive spreadsheet has several steps, each of which is covered in more detail in this topic. The following is an overview of that process:

  • Before you begin

  • Creating and customizing the spreadsheet in a design program.

  • Testing and publishing the spreadsheet on a Web page.

Return to top

Before you begin

Do your users have the required browser and license? To view and work with an interactive spreadsheet, users must have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office 2000 or, if their company has an Office 2000 site license, by downloading the Office Web Components from their intranet. Users must also use Microsoft Internet Explorer version 4.01 or later. Learn more about browsers that fully support spreadsheets for the Web.

Do you have the right design program? The design program is the program you use to create or modify an interactive spreadsheet. You can publish a spreadsheet from Microsoft Excel 2000, Microsoft Access 2000, or Microsoft FrontPage 2000. If you're a programmer, you can create spreadsheets in Microsoft Visual Basic.

Different design programs provide different capabilities. For example:

  • You can use Excel's extensive calculation and formula features to create a worksheet and then publish or save it as an interactive spreadsheet. You can also create an interactive chart that's based on the spreadsheet and publish it along with the spreadsheet.

  • You can use FrontPage 2000 to create a spreadsheet on a Web page, dress up the page with special formatting and designs, and then add the page to a Web site.

  • You can use Access to create a spreadsheet that is connected to and displays data from a database. Then, you can create formulas on the spreadsheet that use values from the database. When the database values change, the spreadsheet recalculates.

  • If you're a programmer, you can insert a spreadsheet on a Visual Basic form and take advantage of the Visual Basic programming environment to automate the spreadsheet.

Once you have created the spreadsheet on a Web page, you can work with the page in one or more different design programs to customize and enhance it. For example, you can use FrontPage 2000 to open and modify an interactive Web page that was created in data access page Design view in Microsoft Access. You can also export the spreadsheet to Excel to perform further analysis by using Excel's analysis features, make changes, and save the data in a workbook on your own computer.

Learn about capabilities of different design programs.

Return to top

Creating the spreadsheet and customizing it

To create a spreadsheet, follow the instructions in your design program to place a spreadsheet control on your Web page. Then, you can enter data and formulas on the spreadsheet, or import data to use on the spreadsheet. To create a spreadsheet in Microsoft Excel, see Help for saving or publishing worksheets in Excel.

Spreadsheet toolbar and property toolbox After you have created a spreadsheet, you can change formatting and display options, set the size of the spreadsheet, protect cells, and more by using the Spreadsheet Property Toolbox and the spreadsheet toolbar. Learn about the Spreadsheet Property Toolbox and toolbar.

Restrict access in the browser Even in a browser that fully supports spreadsheets, such as Internet Explorer 5, users do not have access to some of the commands and features that you can use in a design program. To prevent users from making changes to data or formulas in the spreadsheet, you can set further restrictions. Learn about protecting a spreadsheet.

Provide instructions You can add descriptive text, instructions, or other information about the spreadsheet by typing the text in a visible place on the spreadsheet or on the Web page. Learn about providing custom instructions for spreadsheets.

Include a chart You can also display a chart of the data that's in your spreadsheet on your Web page. When a user in the browser changes the data displayed in the spreadsheet, the chart is automatically updated. For example, if your spreadsheet contains an amortization table that uses formulas to calculate mortgage payments, you can include an interactive chart on the Web page to show how principal and interest payments compare over time. If the user changes the interest rate or down payment figures used in the formula, the spreadsheet formulas update the information, and the chart updates to reflect the latest figures. Learn about creating and using a chart.

Scripts Many of the features and capabilities of a spreadsheet can be accessed and controlled with scripts. If you are familiar with writing scripts for Web pages in a language such as Microsoft Visual Basic Scripting Edition (VBScript) or JavaScript, you can add scripts to the spreadsheet to do things such as getting values from a database and calculating information in the background.

Microsoft Office 2000 programs provide the Microsoft Script Editor for creating and modifying scripts. For information about writing scripts, see Help in your design program. For information about accessing features and capabilities of spreadsheets from a script, see the programming Help system for spreadsheets.

Return to top

Testing and publishing

Check the spreadsheet in the browser Before making the spreadsheet available to your users, you should test it in the browser. Microsoft Internet Explorer 5 is part of the Microsoft Office 2000 package, and it is recommended for accessing the full interactive capabilities of the spreadsheet. Users with Internet Explorer 4.01 or later can also view and interact with the spreadsheet. If your users have different browser versions, you might want to test the spreadsheet in each version to make sure it displays and updates the information properly. Learn about support for spreadsheets in different browsers.

Make changes If you're not satisfied with the spreadsheet and the Web page, you can go back into the design program and make changes. For example, if you published the spreadsheet from Microsoft Excel, you can make changes to the original Excel worksheet and republish the spreadsheet.

Publish the Web page When you are satisfied with the results, make your Web page available to your users. See Help for your design program for information about publishing Web pages, or consult your Webmaster.

Return to top


About design programs for a spreadsheet

You can create and modify spreadsheets in several programs. You can create the spreadsheet in one program, and later use a different program to modify the spreadsheet or the Web page that it's on. The choice of which design program you use depends on the amount of design or administrative work you want to do on the Web page itself, the type of data you want to use, and your own familiarity with different design programs. For instance, if your source data originates on a Microsoft Excel worksheet, you can create and publish a spreadsheet in Microsoft Excel 2000. If you have data in a database that you want to import or link to from a spreadsheet, you can use data access page Design view in Microsoft Access 2000. If you want to add special effects to your Web page or add the page to a Web site, you can use Microsoft FrontPage 2000. If you're a programmer, you can use Microsoft Visual Basic to create a spreadsheet on a form and then automate the spreadsheet. This topic provides information about:

Microsoft Excel 2000

Microsoft FrontPage 2000

Microsoft Access 2000

Microsoft Visual Basic

Getting more design information To find out more about design capabilities and procedures, see Help for the design program you want to use. How to get help using a design program.

Microsoft Excel 2000

If you want to try different formulas, adjust references, import data from other sources, or design an interactive chart to reflect the data in your spreadsheet, Microsoft Excel is an excellent design environment to use to create spreadsheets. When you want to modify a spreadsheet published from Excel, you must use a different design program. Learn about working with a spreadsheet that was published from Excel.

You can format the spreadsheet and make adjustments to its size and appearance in another design program. If any of your users have Microsoft Internet Explorer version 4.01, be sure to use Microsoft FrontPage 2000 to modify spreadsheets that were published from Excel. Excel publishes spreadsheets to Web pages with full support for Internet Explorer 4.01 and later, and FrontPage 2000 maintains this support. If you modify a Web page that was published from Excel by using data access page Design view in Microsoft Access 2000; however, users might need to install Internet Explorer 5 before they can use your Web page.

Return to top

Microsoft FrontPage 2000

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

Return to top

Microsoft Access 2000

Use data access page Design view in Access to create spreadsheets for users who have Microsoft Internet Explorer 5 or later. Access provides specialized features for creating Web pages that operate on and display data from databases. You can use Access to establish a connection from an Access database to the Web page, and then you can use formulas in the spreadsheet to return values from the database to the spreadsheet.

Microsoft Access 2000 is available in Microsoft Office 2000 Professional and Office 2000 Premium.

Return to top

Microsoft Visual Basic

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

Return to top


About working with a spreadsheet that was published from Microsoft Excel 2000

When you publish a spreadsheet from Microsoft Excel 2000, you can open the Web page and make design changes in Microsoft FrontPage 2000 or data access page Design view in Microsoft Access 2000. You can also open the original worksheet (.xls file) in Microsoft Excel (if you saved it), make changes, and republish the spreadsheet to the same Web page. If you didn't save the original copy, you can export the spreadsheet to Microsoft Excel, make changes, and either save a copy or publish the changed spreadsheet.

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

Excel formulas, functions, and calculation features | What happens when published or saved as a Web page
Array formulas | Converted to values.
Labels in formulas | Converted to cell references.
Named cells or named ranges | Converted to use references rather than named cells or ranges.
Named constant values | Converted to values.
References to data on other worksheets | References are converted to values.
Subtotals | Numbers and calculations appear correctly. Group and outline features are not retained.
1904 data system | The displayed dates remain the same, but the serial numbers for the dates are converted to the 1900 date system.
Precision as displayed | Not supported. Precision as displayed value is published.
R1C1 reference style | Converted to A1reference style.

Excel formatting, graphics, and display features | What happens when published or saved as a Web page
Rotated or vertical text | Converted to horizontal text.
Distributed alignment | Not retained.
Thai alignment | Not retained.
Indented text | Not retained.
Wrapped text in cells | Not retained.
Graphics | Not retained.
Pattern fills | Not retained.
Dotted or broken borders | Converted to solid borders.
Multiple fonts in a single cell | Not retained. The font of the first character in the cell is used.
Conditional formatting | Current cell formatting is retained. Formatting is no longer conditional.
Cell comments | Not retained.
Data validation restrictions and messages | Not retained.
Auditing tracer arrows | Not retained.
Outlining | Collapsed rows are published as hidden. Expanded rows are published normally.
Printing or page setup features | Not retained.

Excel file, sharing, and import features | What happens when published or saved as a Web page
Shared workbook information | Not retained.
Lotus compatibility | R1C1 reference style is converted to A1 reference style.
External data ranges | Values are published. Ability to refresh from the source data not retained.
Web queries | Values are published. Ability to refresh from the source data not retained.
Passwords | To publish a protected worksheet, you must use a blank (or no) password.


About the Spreadsheet Property Toolbox and the toolbar

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 while you are designing it. Users in the browser can also use the toolbar to work with 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 property toolbox includes some commands that aren't present on the toolbar or right-click menus. To display the toolbox, click Property Toolbox [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 sections 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 properties and commands are available in the property toolbox both when you are designing the spreadsheet and when it's displayed in the browser. Others are available only when you are working in a design program.

Restricting access to the toolbar and property toolbox If you don't want users in the browser at run time to have access to the commands and property settings in the Spreadsheet Property Toolbox, you can make the toolbox unavailable in the browser.

If you don't want users to have access to the toolbar in the browser, you can hide the toolbar. To ensure that users cannot redisplay the toolbar, you must also make the Spreadsheet Property Toolbox unavailable because the toolbox includes a command to redisplay the toolbar.


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 Type in the keyword to find box, 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 turn this highlighting on or off, click Options [image] on the toolbar in the Help window, and then click Search Highlight On or Search Highlight Off.

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

Available in both the browser and the design program

General section

Format section

Show/Hide section

Calculations section

Find section

Available in the design program only

Import Data section

Protection section

Title Bar section

Advanced 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 underlined, 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 | Sets the alignment of text within cells to General, Left, Center, or Right. General left aligns text and right aligns numbers and dates.
Vertical alignment list | Sets the alignment of text within cells to 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 | When the Manual calculation option is selected, recalculates the spreadsheet.
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 the entire spreadsheet, if a single cell is selected.
Find Next button | Searches for the text or value in the find box above.

Return to top

Import Data section

Option | Description
URL box | Allows you to enter the URL address of a Web page or the name of a text file that contains data you want to import into the spreadsheet.
Import Now button | Retrieves the data from the Web page or text file specified in the URL box.
Refresh from URL at run time check box | Retrieves the data from the specified Web page when a user opens the Web page that contains the spreadsheet in the browser.

Return to top

Protection section

Option | Description
Enable protection check box | Prevents changes to any cells that have been locked with the Lock Cells check box, both in the browser and in the design program. In the browser only, turns on protection for sorting and filtering if the Allow sorting or Allow filtering check boxes have been cleared, respectively.
Lock Cells check box | Prevents changes to the selected cells when Enable Protection is selected, both in the browser and in the design program.
Allow property toolbox at run time check box | Allows access to the Spreadsheet Property Toolbox in the browser.
Allow sorting check box | Allows to sort data in the spreadsheet users in the browser.
Allow filtering check box | Allows users to turn AutoFilter on or off in the browser.

Return to top

Title Bar section

Option | Description
Title box | Allows you to enter the text for the title bar.
Text format | Makes the title bar text bold, italic, or underlined, and sets the font color.
Font list | Sets the font for the title bar text from the list of fonts available on your system.
Size list | Sets the size of the title bar text from the font sizes available on your system.
Background | Sets the background color for the title bar.

Return to top

Advanced section

Option | Description
AutoFit spreadsheet list | Allows the spreadsheet to expand and shrink as needed to display the data (True), or displays the spreadsheet in an area of a fixed size (False).
Maximum height box | When AutoFit spreadsheet is set to True, sets the maximum height of the spreadsheet either in pixels or as a percentage of the height of the entire Web page.
Maximum width box | When AutoFit spreadsheet is set to True, sets the maximum width of the spreadsheet either in pixels or as a percentage of the width of the entire Web page.
Horizontal scroll bar list | When AutoFit spreadsheet is set to False, displays a scroll bar at the bottom of the spreadsheet (True) or hides this scroll bar (False).
Vertical scroll bar list | When AutoFit spreadsheet is set to False, displays a scroll bar at the side of the spreadsheet (True) or hides this scroll bar (False).
Viewable range box | Sets the range of cells displayed in the spreadsheet.
Move after return list | Moves the cell selection when you press ENTER (True), or keeps the same cell selected (False).
Move direction list | When Move after return is set to True, sets the direction in which the cell selection moves when you press ENTER.
Freeze panes list | Keeps visible the columns to the left of the selected cell (Left) or the rows above the selected cell (Top), or allows all rows and columns to scroll (Unfreeze).
Enable events list | Allows programs and scripts to monitor spreadsheet events (True), or prevents programs and scripts from monitoring spreadsheet events (False).
Display right to left list | Displays the spreadsheet for viewing in languages that read right to left (True), or left to right (False).

Return to top


Spreadsheet toolbar buttons and commands

Office logo cube Office logo cube Displays information about the Spreadsheet Component, including the version you have installed. If you import data from a Web page, the cube spins while data is being retrieved.

Undo Reverses the last command or deletes the last entry you typed. You can undo multiple actions.

Cut Removes the selected data from the spreadsheet and places it on the Clipboard.

Copy Copies the selected data from the spreadsheet to the Clipboard.

Paste Inserts the contents of the Clipboard at the insertion point, and replaces any selected data. This command is available only when cut or copied data is available on the Clipboard.

AutoSum Adds numbers automatically with the SUM function.

Sort Ascending Sorts the selected cells in A – Z 0 – 9 order.

Sort Descending Sorts the selected cells in Z – A 9 – 0 order.

AutoFilter Turns filtering on or off. Filtering lets you display only the rows that match values you specify.

Export to Excel Runs or switches to Microsoft Excel and copies the entire spreadsheet into a new Excel workbook.

Property Toolbox Displays the Spreadsheet Property Toolbox so that you can carry out commands and set properties.

Help Displays Help topics about how to use a spreadsheet.


How users can work with a spreadsheet at run time

This topic provides information about how users in the Microsoft Internet Explorer Web browser can interact with a spreadsheet that you create. Understanding the browser capabilities of spreadsheets should help you better design them to serve the needs of your users.

This topic provides information about:

Working in a browser vs. a design program

The browser interface for spreadsheets

What users can do in the browser

How the restrictions you set affect working in the browser

Working in a browser vs. a design program

The Spreadsheet Component lets you create a spreadsheet in a design program for run-time access in a Web browser or other run-time environment. Microsoft Excel 2000, Microsoft FrontPage 2000, and Microsoft Access 2000 are design programs that you can use to create and modify spreadsheets. Learn about choosing a design program.

The run-time features that are discussed in this topic are available to users who have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office 2000 or, if their company has an Office 2000 site license, by downloading the Office Web Components from their corporate intranet. Users should also have a Web browser that fully supports interactive spreadsheets. If you plan to create spreadsheets for use in a program other than a Web browser, such as a Microsoft Visual Basic form to be viewed as part of a custom solution, see Help for your design program to find out how users of your work can access and interact with the spreadsheet.

Return to top

The browser interface for spreadsheets

In the browser at run time, users can activate a spreadsheet and interact with it by using the same toolbar and property toolbox that you use while you are designing the spreadsheet. However, users in the browser don't have access to some features that are available to you in the design program, including the ability to import data, set or remove protection, show or format the title bar, and advanced features such as setting the viewable range of the spreadsheet.

Spreadsheet on a Web page with property toolbox

In the browser, the toolbar and the Spreadsheet Property Toolbox allow users to interact with a spreadsheet.

Return to top

What users can do in the browser

Users can enter and calculate data Subject to any restrictions you set when you design the spreadsheet, users can add, delete, or change data, create or modify formulas, and apply formatting options, just as you can in the design program.

How the browser version affects what users can do To view and fully interact with a spreadsheet on a Web page, your users must have Microsoft Internet Explorer version 4.01 or later. Learn about support for spreadsheets in different browsers.

Help for the user in the browser The user in the browser has access to two types of Help: the Help system in the browser itself, and the additional Help content provided from within a spreadsheet. The latter explains how to use the commands and features that are available in the spreadsheet. To view this run-time Help, activate a spreadsheet in the browser and click Help [image] on the spreadsheet toolbar. You can also provide your own custom instructions for a spreadsheet in the browser.

Return to top

How restrictions you set affect working in the browser

If you want to simplify the options available to users in the browser, you can set restrictions. For example, you can lock specific cells to prevent users from changing values or formulas. You can hide a whole area of the spreadsheet or prevent users from filtering or sorting data. You can also prevent users from displaying the Spreadsheet Property Toolbox. Learn about spreadsheet protection.

Return to top


Display the Help Table of Contents for your Design Program

When you display a Spreadsheet Help topic, the Contents tab in the Help window lists only Spreadsheet Help topics.

To display the topics in the Help for your design program:

  1. Click the Answer Wizard tab in the Help window, type a question about a subject other than a spreadsheet, and then click Search.

  2. Click a topic in the Help for your design program, and then click Display.

  3. Click the Contents tab to see the Help contents for the design program.


Get additional help for creating a spreadsheet

This topic provides information about:

Getting help in other design programs

Getting help writing scripts and programs that use spreadsheets

Getting help in other design programs

When 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 in the design program. For example, in Microsoft FrontPage 2000, click the Office Assistant, or click Microsoft FrontPage Help [image].

    Microsoft Excel provides both general Help about saving Excel data for use on Web pages and specific information about saving spreadsheets for use on Web pages. To access this Help in Excel, click the Office Assistant, or click Microsoft Excel Help [image].

  • Spreadsheet design-time Help If you have access to the Spreadsheet Property Toolbox in another design program, you have access to the Help topics you are viewing now. In a spreadsheet that you have created or activated in a design program, click Help [image] in the Spreadsheet Property Toolbox or click Help on the spreadsheet toolbar.

Return to top

Getting help with writing scripts and programs that use spreadsheets

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

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

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

  • Help in Microsoft Visual Basic In Visual Basic or 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

For the keys to activate and deactivate a spreadsheet, see Help in your design program.

Press | To
The TAB key | Selects 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 upper-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 key | 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 key | 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. Reverse the last command or delete 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.

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 (NUM LOCK must be turned on) | Enter the euro currency symbol [image] .

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


Create a Microsoft Office spreadsheet

The specific steps for creating a spreadsheet depend on the program you are using to design your Web page. For more information, see Help for that program. If your design program Help does not provide a procedure for creating controls such as spreadsheets, follow these steps:

  1. In your design program, open or create the Web page where you want the spreadsheet to appear.

  2. By using the design program's procedure for adding a control to a Web page, select the spreadsheet control and designate the location and initial size of the control on the Web page.

  3. Enter data and formulas in the spreadsheet, or import the data to use in the spreadsheet.

  4. Use the Spreadsheet Property Toolbox and toolbar to customize your spreadsheet.

  5. Using the instructions in your design program, publish the spreadsheet.


Create a chart for a spreadsheet

If you publish a spreadsheet from Microsoft Excel, you can take advantage of Microsoft Excel's extensive charting capabilities to create a chart that's based on spreadsheet data and publish the chart with the spreadsheet. This is one of the easiest ways to publish a spreadsheet and chart together.

In other design programs, you can create a spreadsheet and a chart by using two Microsoft Office Web Components — the Spreadsheet Component and the Chart Component.

Follow these steps to create a chart for your spreadsheet.

  1. Following the directions in your design program, use the Spreadsheet Component to create the spreadsheet on a Web page, and put in it the data you want to use in your chart.

  2. Select the range of cells in the spreadsheet that contain the data you want to use in the chart.

  3. Following the directions in your design program, use the Chart Component to create a chart on the same Web page where the spreadsheet exists.

  4. Follow the instructions in the Chart Wizard.

    For help in any step of the Chart Wizard, click Help [image].

Tip For Help customizing the chart and using the Chart Property Toolbox, right-click the chart and click Help on the shortcut menu.


Save changes to a spreadsheet

To save your changes, use the design program's command to save the Web page or file containing the spreadsheet. For information about the save command, see Help in your design program.

Notes

  • If you published the spreadsheet from a Microsoft Excel worksheet and then opened the Web page in another design program, saving the Web page does not save the changes back to the original Microsoft Excel worksheet.

  • If you want to save a copy of the spreadsheet as a Microsoft Excel worksheet on your system, you can export the spreadsheet to Excel. You can also copy spreadsheet data to Excel and other programs.

  • In the browser, users cannot save the Web page, but they can export and copy from the browser.


Export a spreadsheet to a Microsoft Excel workbook

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. 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 that connect to other data on the Web page or other Web pages are not retained in Excel. For example, if you imported the data for the spreadsheet from a Web page or text file, when you export the data to Excel, no link is maintained to the Web page or text file.

  • When you export some spreadsheet number formats, the data in Excel is displayed with a custom number format. For example, the Yes/no, True/false, and On/off spreadsheet formats are displayed with custom number formats in Excel. For information about custom number formats in Excel, see Excel Help. Learn about spreadsheet number formats.


Print a spreadsheet

To print a spreadsheet while you're in a design window, you must print the entire Web page. See Help in your design program for specific instructions on printing a Web page.

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 have made to the spreadsheet since the last time you saved the Web page, close the Web page without saving it in the design program.


Display the spreadsheet toolbar

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Click Toolbar [image].


Hide the spreadsheet toolbar

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Click Toolbar [image].


Display the Spreadsheet Property Toolbox

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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


Display or hide headers for each column in a spreadsheet

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

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Do one of the following:

    To display 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 numbers for each row in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Do one of the following:

    To display row numbers, make sure Row headers [image] is pressed in.

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


Display or hide the spreadsheet title bar

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Do one of the following:

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

Note You can customize the title bar, including changing the text and formatting.


Display only part of a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. In the Viewable range box, type the range to display.

    For example, to display all of rows 1 through 15, type 1:15. To display all of columns A through F, type A:F. To display cells A1 through B20, type A1:B20.

Notes

  • This procedure allow you to hide data and calculations. To prevent users from changing displayed sections of a spreadsheet in the browser, you can protect the spreadsheet.

  • If the area allocated for the spreadsheet is larger than the viewable range, the unused area is displayed with a gray fill. To eliminate the unused area, you can change how the space for the spreadsheet is allocated.

  • If the toolbar for the spreadsheet is displayed, the area used by the spreadsheet will be at least as wide as the toolbar.

  • This setting cannot be changed from the toolbox in the browser.


Display or hide gridlines in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Do one of the following:

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

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


Display or hide scroll bars in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. In the AutoFit spreadsheet list, click False.

  5. To display a scroll bar at the bottom of the spreadsheet, click True in the Horizontal scroll bar list.

    To hide the scroll bar at the bottom of the spreadsheet, click False in the Horizontal scroll bar list.

    To display a scroll bar at the side of the spreadsheet, click True in the Vertical scroll bar list.

    To hide the scroll bar at the side of the spreadsheet, click False in the Vertical scroll bar list.

Notes

  • If you hide the scroll bars and also set the AutoFit spreadsheet option to False, inserting rows or columns in the browser might make existing rows or columns disappear off the edges of the spreadsheet.

  • These settings cannot be changed from the toolbox in the browser.


Change which cell in a spreadsheet becomes active when you press ENTER

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. In the Move after return list, do one of the following:

    To make an adjacent cell the active cell after you enter data in a cell, click True, and in the Move direction list, click the direction you want to move: Down, Left, Right, or Up.

    To keep the same cell the active cell after you enter data, click False.

Notes

  • The move direction takes effect only when you set the Move after return option to True.

  • The cell selection does not move when the boundaries of the spreadsheet are reached. For example, when the cell selection is set to move down and you press ENTER in a cell in the bottom row, the selection does not move.

  • This setting cannot be changed from the toolbox in the browser.


Display a spreadsheet so that it reads right to left, or vice versa

Use right-to-left display when your spreadsheet will be viewed in languages that read from right to left. When you select right-to-left display, the elements of the spreadsheet are displayed as a mirror image of the default left-to-right display: The row numbers are displayed on the right side of the spreadsheet and the vertical scroll bars appear on the left side.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. In the Display right to left list, do one of the following:

    To display the spreadsheet right to left, click True.

    To display the spreadsheet left to right, click False.

Note This setting cannot be changed from the toolbox in the browser.


About features that are available for spreadsheets in different browsers

To view and work with interactive spreadsheets, users must have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office 2000 or, if their company has an Office 2000 site license, by downloading the Office Web Components from their intranet.

The full set of features that spreadsheets on the Web can provide is not available in all browsers.

Microsoft Internet Explorer 5 and later By using this browser, users have access to all spreadsheet features that are accessible from a browser. Internet Explorer 5 is required when data access page Design view in Microsoft Access 2000 is used to create a spreadsheet, or when you modify a spreadsheet and save the Web page by using Access.

Microsoft Internet Explorer versions 4.01, 4.1x, and 4.2x Users have access to all spreadsheet features that are accessible from a browser. In some cases, users must also have Internet Explorer 5 installed to view spreadsheets on Web pages that were created by using data access page Design view in Microsoft Access.

Microsoft Internet Explorer 4.0 Users have access to all spreadsheet features that are accessible from the toolbar in the spreadsheet and right-click menu, but they cannot access the Spreadsheet Property Toolbox. In some cases, users must also have Internet Explorer 5 installed to view spreadsheets on Web pages that were created by using data access page Design view in Microsoft Access.

Other browsers In earlier versions of Microsoft Internet Explorer and in other browsers, users cannot view interactive spreadsheets. A spreadsheet appears as an outlined box on the Web page.


About providing custom instructions for a spreadsheet

For users in the browser, Help is available that explains how to use the features and capabilities of spreadsheets. Depending on the skills of your users and the type of work they will want to do, you might want to supplement this run-time Help by providing custom instructions for your spreadsheet.

Help available from the browser For users who have Microsoft Internet Explorer version 4.01 or later and the Microsoft Office Web Components, Help specific to spreadsheets is automatically available. This run-time Help is separate from the Help available from the browser's Help menu.

The run-time spreadsheet Help topics provide general information for all users about using spreadsheets in the browser and are not the same as the spreadsheet design Help topics you are reading now. Other Microsoft Office Web Component controls, such as PivotTable lists, also have their own separate Help available in the browser.

To see what Help is provided in the browser, create a spreadsheet on a Web page, open the Web page in Internet Explorer, click the spreadsheet, and then click Help [image] on the toolbar in the spreadsheet. In the Help window, you can view a table of contents and search the topic text. The Office Assistant is not available in the browser to answer questions.

Users who do not have the Office Web Components do not have access to run-time Help. Learn about support for spreadsheets in different browsers.

Providing custom instructions If you want to provide specific instructions or information about the spreadsheet, you can provide your own instructions on the Web page. Although you cannot connect your instructions directly into the online Help content that's provided with the spreadsheet, you can include text directly on the Web page that describes the spreadsheet, or you can provide a hyperlink to a separate Web page of instructions. For information about how to do this on different types of Web pages, see Help for your Web page design program.


Keep specific rows or columns visible as you scroll a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Click a cell in the row below the rows you want to keep visible as you scroll, or click a cell in the column to the right of the columns you want to keep visible.

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

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

  5. In the Freeze Panes list, do one of the following:

    To keep the columns to the left of the selected cell visible, click Left.

    To keep the rows above the selected cell visible, click Top.

    To keep both the rows above the cell and the columns to the left of the cell visible, first click Left, and then click Top.

    To allow all rows and columns to scroll, click Unfreeze.

Note This setting cannot be changed from the toolbox in the browser.


Turn automatic sizing on or off for a spreadsheet

When you turn automatic sizing on, the spreadsheet expands and shrinks on the Web page as necessary when you insert and delete rows and columns or when you change row heights or column widths.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. In the AutoFit spreadsheet list, do one of the following:

    To allow the spreadsheet to expand and shrink as needed, click True. If you want to prevent the spreadsheet from expanding beyond a specified size, type a value either in pixels or as a percentage of the entire Web page in the Maximum height box or the Maximum width box, or in both boxes. For example, to allow the spreadsheet to expand up to half the width of the Web page, type 50% in the Maximum width box. To limit the width of the spreadsheet to 300 pixels, type 300 in the Maximum width box.

    To display the spreadsheet within the fixed space you allocated in the design program, click False.

Notes

  • If you set AutoFit spreadsheet to False and also hide the scroll bars, inserting rows or columns in the browser might make existing rows or columns disappear off the edges of the spreadsheet. How to display scroll bars.

  • If the toolbar for the spreadsheet is displayed, the area used by the spreadsheet will be at least as wide as the toolbar.

  • This setting applies only in the browser, not when you view the spreadsheet in the design program.

  • This setting cannot be changed from the toolbox in the browser.


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 considered to be any combination of numbers, spaces, and nonnumeric characters — for example, a spreadsheet treats the following entries as text:

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

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

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

Text can't be wrapped in cells You cannot display multiple lines of text in a single cell or enter "hard" carriage returns within a cell.

Euro sign To enter the euro sign [image], make sure NUM LOCK is turned on, and then 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 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 within a number string as a decimal. All other strings that combine numbers with nonnumeric characters are treated as text.

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

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

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

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

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

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

Regional settings The characters recognized as numbers depend on the options you select in Regional Settings of Control Panel. The options that are selected also determine the default format for numbers — for example, the period (.) is used as the decimal symbol on United States English–based systems. If users have different regional settings on their systems from those you use when you create the spreadsheet, numbers might be displayed differently in the browser.

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 you select in the Regional Settings of the Windows Control Panel determine the default format for the current date and time and the characters that are recognized as date and time separators — for example, the slash (/) and hyphen (–) for dates and the colon (:) for times on United States English–based systems. Users might have different regional settings from those you use when you design the spreadsheet, resulting in different separator characters being recognized in the browser.

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

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

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 enter a date and time in the same cell, type a space between the date and the time.

The 12-hour or 24-hour clock To enter a time that's 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 spreadsheet stores the time as 3:00 AM. If you type 3:00 p, as you would in Excel, the spreadsheet stores the characters 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 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 the Fixed format, the date portion of the number is to the left of the decimal point, and the time portion to the right of the decimal point.

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

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

The date system In a spreadsheet, days are numbered from the beginning of the twentieth century; 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. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. Type the data, and press ENTER or the TAB key.

    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.

  • How to enter formulas.

  • How to enter hyperlinks.


Change data in a spreadsheet cell

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Click the cell you want to change.

  3. Do one of the following:

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

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

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

Notes

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

  • If you don't want users to be able to change the data in the browser, you can lock the cells you don't want changed.

  • If you see a message indicating that the range is locked, you must turn off protection before you can modify the cell.


About moving and copying data in 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; however, relative cell references will change in relation to the position of the pasted cell. 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 a 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 the Excel moving dashed border to indicate what was copied.

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

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

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

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

More information

Move or copy whole cells

Move or copy characters within a cell


Move and copy data in a spreadsheet

What do you want to move or copy?

Whole cells

Characters within a cell

Not sure which choice you want?


Move or copy characters within a spreadsheet cell

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Double-click the cell with the characters you want to move or copy.

  3. In the cell, select the characters you want to move or copy.

  4. Do one of the following:

    To move the characters, click Cut [image].

    To copy the characters, click Copy [image].

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

  6. Click Paste [image].

  7. Press ENTER.


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. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Select the cells you want to move or copy.

  3. Do one of the following:

    To move the cells, click Cut [image].

    To copy the cells, click Copy [image].

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

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


Insert a row or a column in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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


Delete a row or a column from a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

    To delete multiple rows or columns, select multiple cells.

  3. 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 can't delete rows or columns from a range that includes filtered rows. To delete the rows or columns, 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.


Clear spreadsheet cell contents

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Select the cells that you want to clear.

  3. Press DELETE.

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


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. 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, and subtraction — 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 more 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), 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 you 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 provides 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.

Return to top


About cell and range references in a spreadsheet

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 that's contained in different parts of a spreadsheet in one formula or use the value from one cell in several formulas.

Spreadsheets use the A1 reference style, which labels columns with letters (A through 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 more about relative and absolute references.


About absolute and relative references

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

Formula with relative reference

When you copy 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 a reference to be adjusted when you copy a formula to a different cell, use an absolute reference. To create an absolute reference, type a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1 in the formula =A5*C1, for example, add dollar signs to the formula as follows:

=A5*$C$1

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

=C$1

More information

About cell and range references


About calculation in a spreadsheet

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

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 as 1.23E+08 by using the Scientific format. Or you can format a cell that contains a date with the serial number 36699 to display as "6/22/2000" or as "22-Jun-2000". Changing the display of a value does not change the stored value. Change the format of a number in a spreadsheet cell.

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.


Spreadsheet 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 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
/ (slash mark) | 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 spreadsheets

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
& | Concatenation (joins two strings of text)
= < > <= >= <> | Comparison


Create a formula in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. Type an equal sign (=).

  4. Type the formula.

  5. Press ENTER.

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

More information

Using formulas in a spreadsheet

Using functions in a spreadsheet

Troubleshooting error values in formulas


Change a formula in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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.


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

For more information on the functions, controls, and properties you can use on a spreadsheet, see the programming Help topics for spreadsheets


View a formula and its result at the same time

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

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

  5. 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 or formulas might be truncated in the Calculations section. To view the entire 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 in a spreadsheet

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

Using AutoSum to find the sum of a range of cells

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

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

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


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 sum for one range that's based on a value in another range. For example, for every cell in the range B5:B25 that contains the value "Northwind", the following formula calculates the sum for the corresponding cells in the range F5:F25.

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

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

Return to top

Join first and last names

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

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

=D5&" "&E5

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

=E5&", "&D5

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 text string with a number, date, or time value stored in a cell, 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-yyyy")

Return to top

Increase a number by a percentage

You can increase a numeric value that's stored in a cell by a percentage. In this example, assume that cell F5 contains the value that you want to increase by 5 percent.

=F5*(1+5%)

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

=F5*(1+$F$2)

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

Return to top


Change how a spreadsheet calculates

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

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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.


About using functions in a spreadsheet

Spreadsheet functions

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

Arguments Arguments can be numbers, text, logical values such as TRUE or FALSE, error values such as #N/A, or cell references. Each 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 TRUE or FALSE. If it doesn't, the spreadsheet displays a #VALUE! error value.

Learn about troubleshooting error values returned by functions.


Change the appearance of data in a spreadsheet

Change the format of data

Change the text font, size, or style

Change the format of a number

Learn about built-in number formats

Change the alignment of data

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

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


Change the format of a number in a spreadsheet cell

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. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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.


Number formats in a spreadsheet

You can change the formats of numbers in a spreadsheet by using the Spreadsheet Property Toolbox, both in the design program and at run-time in the browser. 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 an AM or PM designator, 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 in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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 in the Text format group.

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

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


Change the width of a column in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Make sure column headers are displayed.

  3. Point to the right boundary of the column header of the column you want to change 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 right 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.


Change the height of a row in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Make sure row numbers are displayed.

  3. Point to the bottom boundary of the row header of the row you want to change 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 bottom 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. When you use this method, it's not necessary to display row numbers first.


Place borders around data in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Select the cells that 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.

Note You can change the color or width of borders.


Change the color or width of borders in a spreadsheet

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

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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. 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 an existing border line to turn it off. Click the same border line again to turn on the border with the new color or width.

    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.


Align data to the left, center, or right in a spreadsheet cell

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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.

Note The General alignment attribute aligns text to the left side of a cell and aligns numbers, dates, and times to the right side of the cell.


Align data at the top, center, or bottom in a spreadsheet cell

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  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.


Change the background color of cells in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Select the 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.


Merge multiple cells into one cell in a spreadsheet

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. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Select the cells that 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].


Split a merged cell into separate cells in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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


Change the spreadsheet title bar

What do you want to do?

Change the title bar text

Change the font and text style

Change the background color


Change the contents of the spreadsheet title bar

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. If the Title Bar section is not expanded, click the Title Bar bar.

  4. In the Title box, change the text for the title.

Notes

  • If your new title is longer than will fit on the title bar, the title bar displays as much of the text as will fit.

  • In the browser, users can display or hide the title bar. However, the options in the Title Bar section are not available in the browser.


Change the font and text style for the spreadsheet title bar

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. If the Title Bar section is not expanded, click the Title Bar bar.

  4. Click the format options you want.

    To make the title text bold, click Bold [image].

    To make the title text italic, click Italic [image].

    To underscore the title text, click Underline [image].

    To change the color of the title text, click the arrow next to Font Color [image], and then click the color you want. If you click the same text color as the background color, you won't be able to see the text.

    To change the font, click a different font in the Font list. The fonts available in the list are determined by the fonts you have installed on your system.

    To change the font size, click a different size in the Size list. The font sizes available are determined by the fonts you have installed on your system.

Note In the browser, users can display or hide the title bar. However, the options in the Title Bar section are not available in the browser.


Change the background color of the spreadsheet title bar

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. If the Title Bar section is not expanded, click the Title Bar bar.

  4. Click the arrow next to Background Color [image], and then click the color you want.

    If you set the background color to the same color as the text color, you won't be able to see the text.

Note In the browser, users can display or hide the title bar. However, the options in the Title Bar section are not available in the browser.


Allow or prevent sorting in a spreadsheet in the browser

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Do one of the following:

    To allow sorting, select the Allow sorting check box.

    To prevent sorting, clear the Allow sorting check box and select the Enable protection check box.

Note The Enable protection setting affects all protection options, including Allow filtering and Lock cells. If you want users to be able to change cells in the spreadsheet, you should specify which cells can be changed after the spreadsheet is protected.


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. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. Do one of the following:

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

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

Notes

  • Rows hidden by filtering aren't sorted.

  • You can sort rows only. You cannot sort the order of the columns.

  • Sorting is not case sensitive. For example, when you sort ascending, "ALL" and "all" are sorted before "BLUE" and "blue".

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


About sorting data by more than one column in a spreadsheet

To sort data in a list by more than one column, you can sort one column, then sort the next column, and so on. 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 by salesperson. The Product column was sorted first so that the products would be in alphabetical order; the Salesperson column was sorted second.

When sorting multiple columns, determine the order of categorization you want first, and then work backward, 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 alphabetical 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.


Find data in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

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

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

  6. Click Find Next.

Notes

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

  • Wildcard characters The Find command does not allow wildcards such as ? and *.

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

  • Search direction 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 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. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Click a cell within the list that you want to filter.

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

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

  5. Do one of the following:

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

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

Notes

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

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

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

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

  • If you don't want users to be able to turn filtering on or off in the browser, you can make filtering unavailable at run time.


Sum filtered data in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. On the toolbar in the spreadsheet, click AutoSum [image], and then press ENTER.

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


Insert a hyperlink to another Web page in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Click the cell where you want to insert the hyperlink.

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

Notes

  • When you click a hyperlink in the design program, your browser is started or switched to and the Web page is opened.

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


Modify a hyperlink in a spreadsheet

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. Press F2 to begin editing in the cell.

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

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


Bring data from other sources into a spreadsheet

One easy way to bring data from other sources into a spreadsheet is to copy data from another source (such as a text file, a Microsoft Excel worksheet, or a Microsoft Word document) to the Clipboard and then paste the data into the spreadsheet by clicking a cell and then clicking Paste [image] on the spreadsheet toolbar. Other ways to get data from other sources are to import the data from a Web page or text file or to create formulas that reference data on the same Web page.

What do you want to do?

Import data from a Web page or text file

Create a formula that uses data on the same Web page


Import data from a Web page or text file into a spreadsheet

The data in the Web page or text file that you want to import must be in an HTML table and must be on the same Web server or computer as your spreadsheet. You cannot import data from a Web page or text file that has been saved to your computer from a different Web server or computer.

Caution Data is always imported beginning in cell A1 of the spreadsheet. Existing data will be overwritten without warning.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

  3. If the Import Data section is not expanded, click the Import Data bar.

  4. In the URL box, type the address or path of the Web page or text file containing the HTML table that you want to import.

    An example of an address of a Web page is http://example.microsoft.com/example.htm

    An example of a path to a file is file://c:\windows\example.txt

  5. Click Import Now.

  6. If you want the spreadsheet to be updated with the latest data from the Web page or text file each time the spreadsheet is opened in the browser, select the Refresh from URL at run time check box.

Notes

  • You cannot use this method to import images or controls. Only HTML tables are imported.

  • This command works only in the design program and cannot be run in the browser at run time.


About protecting a spreadsheet

You can protect a spreadsheet so that users in the browser have limited or no ability to make changes to cells, to sort, to filter, or to use the Spreadsheet Property Toolbox. Protection options are available only during design time. When you apply these options, they take effect during run time, and generally do not apply while you're designing the spreadsheet.

Prevent users from making changes to cells If you want to lock certain cells, such as those cells that contain formulas, you can protect a spreadsheet so that some or none of the cells can be changed by users in the browser. By default, all cells in a spreadsheet are locked. However, this setting has no effect until you enable protection. When you enable protection, users cannot change any of the cells in the spreadsheet unless you specify which cells can be changed after a spreadsheet is protected.

Prevent users from sorting or filtering By default, users in the browser can sort and filter even if you enable protection. You must select these options in the Protection section of the Spreadsheet Property Toolbox, and then enable protection before these settings take effect. How to prevent sorting and prevent filtering in a spreadsheet at run time.

Display only part of a spreadsheet You can display only part of a spreadsheet so that users have no access to parts of the spreadsheet that you want to hide.

Prevent display of the Spreadsheet Property Toolbox The Spreadsheet Property Toolbox is available at run time by default, regardless of whether you enable protection on a spreadsheet. You can prevent the display of the property toolbox at run time without enabling protection.

Notes

  • You cannot set passwords on spreadsheets.

  • If you don't want users to make any changes to your spreadsheet, consider publishing a noninteractive HTML version of the spreadsheet. See Microsoft Excel Help for more information.


Allow or prevent ability to turn filtering on or off in a spreadsheet at run time

When you prevent filtering in a spreadsheet in the browser, the AutoFilter command is disabled so that users cannot turn on or off the filter. However, users can change the criteria for a filtered list. For example, if you filter a list at design time to show sales for a specific month only, and then prevent filtering, users can then change the criteria for the filter to show, for instance, sales for a different month or all months. However, they cannot remove the filter. If you publish a spreadsheet without a filter, users will not be able to turn the filter on.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Do one of the following:

    To allow filtering, select the Allow filtering check box.

    To prevent filtering, clear the Allow filtering check box and select the Enable protection check box.

Note The Enable protection setting affects all protection options, including Allow sorting and Lock cells. If you want users to be able to change cells in the spreadsheet, you should specify which cells can be changed after the spreadsheet is protected.


Prevent or allow programs and scripts to monitor events in a spreadsheet

For information about automating spreadsheet operations from a program or script, see Help for programs and scripts.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. In the Enable events list, do one of the following:

    To allow programs and scripts to monitor events in the spreadsheet, click True.

    To prevent programs and scripts from monitoring events in the spreadsheet, click False.

Notes

  • For information about spreadsheet events, see Microsoft Visual Basic Help.

  • This setting cannot be changed from the toolbox in the browser.


Prevent the display of the Spreadsheet Property Toolbox in the browser

This procedure makes the Spreadsheet Property Toolbox unavailable in the browser at run time. This setting cannot be changed in the browser.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Clear the Allow Property Toolbox at run time check box.

Note In the design program, the Spreadsheet Property Toolbox is always available, and this setting has no effect.


Remove protection from a spreadsheet

This procedure can be performed at design time only. Users in the browser do not have access to this option.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

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

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

  4. Clear the Enable protection check box.


Specify which cells can be changed after a spreadsheet is protected

Cells in a spreadsheet are locked by default, but this setting applies only when protection is enabled. Before you enable protection, you can specify which cells can be changed after you protect the spreadsheet. Learn about protecting a spreadsheet.

  1. In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

  2. Select the cells that can be changed after the spreadsheet is protected.

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

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

  5. Clear the Lock cells check box.

  6. To specify other cells that can be changed after a spreadsheet is protected, repeat steps 2 through 5.

  7. To enable protection so that all cells other than those you specified are locked, select the Enable protection check box.

Note You cannot set a password for a spreadsheet. However, you might be able to set password protection for the Web page your spreadsheet is on by using the protection features of your Web server. See your Web administrator for more information.


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

If your users' systems are customized differently from your own, dates that users enter in the browser might be interpreted differently from dates you enter in the design program. To ensure that dates are interpreted correctly in the browser, enter all dates with four digits for the year.

Change how years are interpreted Your system administrator, or the system administrator for your users, 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.


Troubleshoot problems in a spreadsheet

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

Entering and editing data and formulas

I can't change cells in the spreadsheet.

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

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

There is no formula bar in my spreadsheet.

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

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

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

I can't set a password for a spreadsheet.

I can't change a hyperlink that I published from Microsoft Excel.

I can't control the size of my spreadsheet.

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

Filtering, sorting, and finding data

Data does not sort correctly in a spreadsheet.

My spreadsheet list does not show all of my data.

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

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

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

Importing and exporting

My spreadsheet filter is not exporting to Excel correctly.

I get the message "Data cannot be imported from URL..." when I try to import data in my spreadsheet.

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

Working with the spreadsheet in the browser

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

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

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


Troubleshoot error values in a spreadsheet

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

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

Which error value do you need help with?

#####

#CIRC!

#DIV/0!

#N/A

#NAME?

#NULL!

#NUM!

#REF!

#VALUE!


I can't change cells in the spreadsheet.

The cells might be locked and protected. At design time, you can unlock and/or unprotect the spreadsheet.

More information

About protecting a spreadsheet

Specify which cells can be changed after a spreadsheet is protected

Remove protection from a 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 wrap text within cells in a spreadsheet.

Wrapped text within cells is not supported in the Spreadsheet Component. If you published a spreadsheet from Microsoft Excel 2000 that had wrapped text in it, the text will appear on one line. To correctly display the text, you can shorten the text or use multiple cells for the text, or you can widen the column so that all of the text shows.


There is no formula bar in my spreadsheet.

Unlike Microsoft Excel, the 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.


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

The Spreadsheet Component does not support names or named ranges. If you publish data from Microsoft Excel 2000 that contains a name or named range, the names are converted to values or ranges.


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

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


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 range of columns in a spreadsheet is through ZZ. If you are viewing fewer than these (for example, 10 rows), and you are in a design environment, then you can change the viewable range of the spreadsheet.


I can't set a password for a spreadsheet.

You cannot set a password for a spreadsheet. Depending on where you publish your Web page, you might be able to set a password that would be required to open the page the spreadsheet is on. For example, you may be able to set a password for access permission on your Web server. For more information, see your Web site administrator.


I can't change a hyperlink that I published from Microsoft Excel.

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

Change the address in Excel and republish In the original Excel worksheet, you can modify both the displayed text and the hyperlink address. You can then republish the data to the Web. For the procedures, see Help in Excel.


I can't control the size of my spreadsheet.

Your spreadsheet might be set to size automatically. When the AutoFit command in the Spreadsheet Property Toolbox is enabled, the spreadsheet expands and shrinks on the Web page as necessary when you insert or delete rows and columns or change row heights and column widths. Spreadsheets that are published from Microsoft Excel have automatic sizing (AutoFit) on by default. You can change whether automatic sizing is on or off at design time by using the Spreadsheet Property Toolbox.


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

When your spreadsheet is set to size automatically (that is, the AutoFit command in the Spreadsheet Property Toolbox is enabled), scroll bars appear when the spreadsheet reaches the maximum height or width set in the Spreadsheet Property Toolbox, regardless of whether you set scroll bars to be displayed or hidden. If you want to control how scroll bars are displayed, turn automatic sizing off.


Data does not sort correctly in a spreadsheet.

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

Check your selection before sorting When you select a range of cells to sort, the spreadsheet sorts everything within that range, and it sorts only that range. This means that if your header row is within the selection, the header row will be sorted. It also means that if you select one column of data within a list that must be sorted, only that column will be sorted and not the entire list. Try to select a single cell within the range you want 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. For example, negative numbers from some accounting systems become text when the data is imported into a spreadsheet. To apply the correct formatting, click the cell, and then change the format of the number.

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

Remove any leading spaces Make sure that 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.


My spreadsheet list does not show all of my 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.

  • Make sure the AutoFilter button [image] on the toolbar is not pressed in.


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.


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

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

More information

Find data in a spreadsheet


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

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

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

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


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 unselected items. When you export the spreadsheet to Excel and the spreadsheet contains filtering criteria that Excel does not support, the entire AutoFilter range is shown in Excel.

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


I get the message "Data cannot be imported from URL..." when I try to import data in my spreadsheet.

Data that you want to import must be in an HTML table. If it is not, use an HTML editor, such as Microsoft FrontPage 2000, to open the file and arrange the data in a table. For information on arranging data in HTML tables, see Help in your HTML editor.


I get a message that there are too many rows or columns when I try to export my spreadsheet toI 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.


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

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

The source data Web page is unavailable If some of the data in the spreadsheet comes from another Web page, you must make sure that Web page is available and that you and your intended users have proper access to the Web page.

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


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

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


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

Programs that are available from the Edit button in your Web browser 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, quit the program, start the program you want to use, and open the Web page in the program.


What does the error ##### in a spreadsheet 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.


What does the error #CIRC! in a spreadsheet 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! in a spreadsheet 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 changes the result of the formula to #N/A, denoting that the divisor value is not available. To prevent an error value from displaying, use the IF spreadsheet function. If the divisor refers to a cell that's blank or contains 0, the IF function can display nothing instead of an error value. For example, use =IF(B5=0,"",A5/B5). The two quotation marks represent an empty text string.
Using 0 for a divisor — for example, =5/0. | Change the divisor to a number other than 0.
Running a macro that uses a function or a formula that uses 0 for a divisor. | Make sure the divisor in the function or formula is not 0 or blank.


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


What does the error #NAME! in a spreadsheet 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).
A formula links to another spreadsheet or data on the same Web page. | The formula cannot return a value during design time. In the browser, display the Web page that contains the spreadsheet and the data the formula refers to, and the formula will return the value.
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! in a spreadsheet 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 the union operator, the comma (,). 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 the intersection operator, the single space. For example, =SUM(B5:C15 A10:D10) calculates the sum of the cells that both ranges have in common (that is, where they intersect), B10 and C10.
Using an incorrect cell reference. | Check for typing errors in the reference to the ranges.


What does the error #NUM! in a spreadsheet mean?

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

Possible cause | Suggested action
Using an inappropriate argument in a function that requires a numeric argument. | Make sure the arguments that are used in the function are the correct type.
Using a spreadsheet function that iterates, such as IRR or RATE, when the function cannot find a result that's accurate within a certain percent after a certain number of tries. | Try different values for the guess argument.
A formula that produces a number that is too large or too small to be represented in the spreadsheet. | Values in 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! in a spreadsheet mean?

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

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


What does the error #VALUE! in a spreadsheet 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 that the value for the operand or argument is of the required data type for the formula or function and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!. Use the SUM spreadsheet function in the formula as follows to add the two values (the SUM function ignores text): =SUM(A5:A6)
Supplying a range to an operator or a function that requires a single value. | Change the range to a single value.
Omitting a range when a function requires one. | Some functions, such as COUNTIF and SUMIF, require a range. Make sure you have included all required range arguments.
Supplying a value other than 1 for the A1 argument of the ADDRESS spreadsheet function. | The ADDRESS function can 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.


Find the data you want to see in a spreadsheet

You can locate data within a spreadsheet in either of two ways: You can search for a specific value, or you can filter the data to display only rows that contain a specific value.

What do you want to do?

Find a specific value

Display rows that contain a specific value