About Access Web Datasheet

Access Web Datasheet provides an Excel-like environment for viewing and editing data. It displays the contents of a list or a document library in a grid of rows and columns. Items and columns in the list appear as rows and columns in Access Web Datasheet. You can add and edit rows and columns, apply filters and sort orders, display calculated values and totals, and more.

The following illustration shows the Employee list in Access Web Datasheet:

Employee list in Datasheet view

Callout 1 A row corresponds to a list item.

Callout 2 A column corresponds to a list field.

Callout 3 The drop-down arrow in the column header is used to filter and sort data.

Callout 4 The Total row is used to display aggregate values for each column.

Top of Page

View a list in Access Web Datasheet

Most list types can be viewed in Access Web Datasheet. You can do this in two ways:

  • By opening a list in a Standard view, and then switching to Datasheet view by clicking Edit in Datasheet in the List tab of the ribbon.

    Note If a Standard view does not support editing in Datasheet view, the Edit in Datasheet command will not be available. For example, the Explorer view of a document library does not support editing in Datasheet view.

  • By creating a Datasheet view. Note that you can set a Datasheet view as the default view for a list.

A Datasheet view is independent of all other views of a list. If you add or remove a column from a Standard view, the change does not affect the Datasheet view.

Some list types that do not support Datasheet view are as follows.

  • Discussion board
  • Survey
  • Image gallery
  • Web template library
  • Web Part catalog
  • Data sources
  • Inbox

Top of Page

Parts of a list in Access Web Datasheet

Rows An Access Web Datasheet contains one or more rows. Each row stores information about an item in the list. For example, a row in the Employee list stores information about an employee.

You can add a row by scrolling to the bottom of the list, and entering data in the star (*) row. You can also edit or delete a row. Your changes are sent to the server after you leave the row and are committed asynchronously. You can continue making changes to different rows, and the view commits your changes in the background. If your changes result in a conflict or error, the view will be updated with information necessary to resolve the conflict or error.

You can change the row height, sort rows based on one or more columns, and hide rows by applying filters.

Columns A view contains one or more columns. Each column stores a particular type of information for all items in the view. For example, the Name column in the Employee list stores the names of the employees.

You can add a column to a list in Datasheet view or Standard view. A column must have a name and must be of a specific data type. You can mark a column as required if you do not want the user to skip the column when entering data. You can also specify additional column settings, such as default value and maximum length. For more information on how to add a column to a list, see Microsoft SharePoint Foundation 2010 Help. To display Microsoft SharePoint Foundation 2010 Help, click Help ( Help icon).

You can modify an existing column or delete a column. You can customize the appearance of a column by showing or hiding columns, rearranging them, adjusting the width, and filtering and sorting rows based on column values. You can also add calculated columns to a list.

Datasheet group The Datasheet group in the ribbon, under the List tab displays commands for performing common tasks in Datasheet view, such as displaying the task pane or adding a new row.

Status bar The status bar at the bottom of the view displays useful information, such as:

  • error or warning messages
  • information specific to the current selection, such as "Read-only"
  • server communication status

Task pane The task pane allows easy, context-sensitive access to certain actions, without requiring you to move away from the Datasheet view.

The following illustration shows the task pane for a sample list:

Callout 1 At the top of the pane is the toolbar. It displays buttons for common commands, such as Cut, Copy, Paste, Sort, and Help.

Callout 2 Clicking the Net Shade bar hides or displays the task pane. You can drag the bar to resize the pane.

Callout 3 Hover over the scroll bar to scroll the pane vertically.

Callout 4 The interior of the pane displays various commands.

Top of Page

Software requirements for working with Access Web Datasheet

Your computer must meet a set of software requirements before you can view a list in Access Web Datasheet.

Top of Page


Software requirements for Access Web Datasheet

To open a list in Access Web Datasheet, you must meet the following requirements:

  • Install a 32 bit version of Microsoft Office 2010 on your computer.
  • Install the Microsoft Office Access Web Datasheet Component that is included with Office 2010 on your computer.

    Note This component is installed by default when you install Office 2010.

  • Use Microsoft Internet Explorer 6.0 or later to open the list.
  • Make sure security settings in the browser support Microsoft ActiveX controls.

If one or more of the above requirements are not met, you will see a message indicating that the list will be displayed in Standard view. The settings you specified for the Datasheet view, such as column order, sorting and filtering, and Total row settings, will be applied to the Standard view before the list is displayed. However, settings such as row height, column width and hidden columns, will be ignored.

Note that even if you do not meet all of the above requirements, you will be able to make changes to the Datasheet view. The changes will be saved, and you will be able to see the effect of your changes the next time you use a computer that meets all the software requirements listed above.


Display product and computer information

You can display the version of the Microsoft Office Access Web Datasheet Component that is installed on your computer, the product ID number, system information, and other related information by opening the About dialog box.

  • Right-click the top-left corner of the list, and click About.


Get help in Access Web Datasheet

  • To get help on Access Web Datasheet, right-click any cell inside the Datasheet view, and click Help.

    Important The Access Web Datasheet Help topics only cover functionality that is available in Datasheet view. For general assistance with SharePoint lists, click Help ( Help icon).

  • To get help on the SharePoint page containing the Datasheet view, click Help ( Help icon).
  • To get help when a List View Web Part is displayed in Datasheet view, click the Web Part menu, and then click Help. The Help topics cover Web Part Pages, Web Part properties, and Web Part connections.

Notes

  • Several dialog boxes and status bar messages include links to context-sensitive help information. You can also click Help ( Help icon) on the task pane toolbar.
  • If the Datasheet view is in focus, pressing F1 will display help on Datasheet view. Otherwise, it will display Internet Explorer Help.


Troubleshoot Access Web Datasheet

I cannot open a list in Access Web Datasheet, or the list opens in Standard view

I don't see any data, or I only some of the data in the view

I get the message "List/view does not exist"

I get the message "You do not have permissions"

I get the message "Attachments cannot be displayed or added"

I get the message "Unable to refresh the list"

I get the message "Required fields are missing from the view"

The view is read-only, or parts of the view are read-only

I cannot open a list in Access Web Datasheet, or the list opens in Standard view

This could be due to one of the following reasons:

  • Your computer does not meet all the software requirements for displaying Access Web Datasheet.
    • A datasheet component compatible with SharePoint Foundation 2010 is not installed.
    • Your browser does not support ActiveX controls.
    • Datasheet view is not supported on 64 bit installations of Microsoft Office.
    • Support for ActiveX controls is disabled.
  • If the list or or the current view does not support Datasheet view, the Datasheet View option in the Ribbon is dimmed. For example, viewing a slide library in Datasheet View is not supported.
  • An error occurred while accessing information about the list structure or the view definition. Try refreshing the view.

Top of Page

I don't see any data, or I only some of the data in the view

If you do not see any data in the view, it could be because either the server is busy or not found, or some other error occurred. Close the list and try viewing the list at a later time.

You might not see some rows or columns in the view due one or more of the following reasons:

  • You applied a filter, or a filter is specified as part of the view definition.

    To check if any filters are applied in the view, make sure the drop-down arrow in the column headers appear normal. If a column is filtered, the drop-down arrow appears a different color. The color difference may be difficult to see on some monitors.

    To check if a filter is specified as part of the view definition, in the Ribbon, under the List tab, click Modify this View. On the Edit view page, in the Filter section, check to see if a filter has been applied.

  • The column is hidden, or the column is not included in the view definition.

    To show all hidden columns, right-click the top left cell in the view and click Unhide.

    To check if a column is included in the view, in the Ribbon, under the List tab, click Modify this View. On the Edit view page, in the Columns section, check to see if the Display check box is not selected. To include the column in the view, select it.

  • The list requires content approval, and some or all of your items are either still pending or have been rejected.

    To check the approval status of your items, in the View drop-down list, click My Submissions. Look in the Approval Status and Comment columns. If an item has been rejected, edit the item. The item will be set to pending and will be sent for approval again.

Top of Page

I get the message "List/view does not exist"

The list or the Datasheet view does not exist. It is possible that it has been deleted by another user. Verify that the list and the view exist, and then try again.

Top of Page

I get the message "You do not have permissions"

This could be due to one of the following reasons:

  • You do not have the necessary permissions to make the change.
  • You have been blocked from adding content to the site.
  • You have exceeded your storage limits for the site.
To avoid these errors, contact your system administrator.

Top of Page

I get the message "Attachments cannot be displayed or added"

This could be due to an error that occurred on the server. Try adding or viewing your attachments later.

Top of Page

I get the message "Unable to refresh the list"

Due to a server error, the view cannot be refreshed. Click Yes to retry. If the error occurs again, click No, and try again later.

Top of Page

I get the message "Required fields are missing from the view"

The view does not include one or more columns that are marked required. A row must have non-blank values in its required columns before it can be submitted to the server.

Because the view does not include one or more required columns, you will not be able to do the following:

  • Add new rows.
  • Edit a row if it has a blank value in one of its required columns.

    Note A column that is marked required will not accept blank values. However, a column that was not marked required but was subsequently modified to require non-blank values might contain blank values. So when you change the setting of a column from Not required to Required, edit the list in Datasheet view to ensure that the column has a non-blank value for each row.

To avoid this message, either add the required fields to the view, or contact the list author.

Top of Page

The view is read-only, or parts of the view are read-only

This could be due one of several reasons. For more information, click the Read-only link on the status bar.

Top of Page


Make Datasheet view the default view

When you first create a list, a new "standard" view is defined for it by default. To change to Datasheet view, in the Ribbon, under List Tools, click List, and then click Datasheet View. However, if you want to make Datasheet view the default view for a list, you must first create a new Datasheet view of the list, then make that view the default view.

To create a new Datasheet view of a list and make it the default view:
  1. In the ribbon, click the List tab, then click Create View.
  2. On the Create View page, under Choose a view format, click Datasheet View.
  3. On the Create Datasheet View page, type a name for the view. You may want to include the word "Datasheet" in the view name to help you remember that it is a Datasheet view.
  4. Select the Make this the default view check box.
  5. Check that the Create a Public View option is selected so other visitors can see this view.
  6. At the bottom of the page, click OK.
To make an existing Datasheet view the default view:
  1. In the ribbon, click the List tab, and then click List Settings.
  2. On the List Settings page, under Views, click the name of the view that you want to make the default.
  3. On the Edit View page, in the Name section, select the Make this the default view check box.

    Note If the view is already set as the default view, or if it is set as a personal view, you will not see the check box.


Show, hide, or resize the task pane

  • To show or hide the task pane, on the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  • To resize the task pane, position the cursor over the Net Shade bar to the left of the pane. When the cursor changes to a resize cursor, click and drag to the left or right.


Keyboard shortcuts

You can use shortcut keys for quick access to frequently used commands or operations. The topics below list the shortcut keys available in Access Web Datasheet. You can also use access keys to move focus to different parts of the view without using the mouse.

Select parts of the view

Carry out commands

Edit data

Format a cell that supports rich HTML text

Sort and filter data

Select parts of the view

To do this | Press
Move selection from left to right, then down. If multiple cells are selected, move focus to the next cell within the selection. | TAB
Move selection from top to bottom, then right. If multiple cells are selected, move focus to the next cell within the selection. | ENTER
Move selection from right to left. If the current cell is the leftmost cell, SHIFT+TAB selects the last cell in the previous row. | SHIFT+TAB
Select the cell above the current cell. If the current cell is the topmost cell, SHIFT+ENTER selects the last cell in the previous column. | SHIFT+ENTER
Move the selection in the direction of the arrow key. | Arrow keys
Extend or reduce the selection in the direction of the arrow key. | SHIFT+Arrow key
Move the selection to the last cell in the direction of the arrow key. If there are blank cells in that direction, move to the last cell preceding the first blank cell. | CTRL+Arrow key
Extend selection to the last cell in the direction of the arrow key. If there are blank cells in that direction, extend selection to the last cell preceding the first blank cell. | CTRL+SHIFT+Arrow key
Select the first cell in the current row. | Home
Select the first cell in the first row. | CTRL+Home
Select the last cell in the last row. | CTRL+End
Extend selection to the beginning of the current row. | SHIFT+Home
Extend selection to the end of the current row. | SHIFT+End
Extend selection to the beginning of the list. | CTRL+SHIFT+Home
Extend selection to the end of the list. | CTRL+SHIFT+End
Extend selection to the entire column. If text in a cell is selected, and the column supports rich HTML formatting, clear formatting. | CTRL+SPACEBAR
Extend selection to the entire row. | SHIFT+SPACEBAR
Select the entire list. | CTRL+A
Scroll to the next list page. | PAGE DOWN
Display the previous list page. | PAGE UP
Extend a selection down one page. | SHIFT+PAGE DOWN
Extend a selection up one page. | SHIFT+PAGE UP
Scroll to display the current cell. | CTRL+BackSpace

Top of Page

Carry out commands

To do this | Press
Display the shortcut menu for the current selection. | SHIFT+F10
Carry out the shortcut menu command containing the underlined letter. | Underlined letter on a shortcut menu
Undo changes to rows that have not yet been committed. If a shortcut menu is visible, close the shortcut menu without carrying out a command. In edit mode, cancel edit mode, and discard your changes. | ESC
If the Datasheet view is in focus and not in edit mode, display Datasheet view Help. Else display Internet Explorer Help. | F1
Enter edit mode. | F2
Clear current cell. | Delete
Show the task pane. | F8
Hide the task pane. | SHIFT+F8
Copy selection to the Clipboard. | CTRL+C
Paste contents of the Clipboard. | CTRL+V
Move selection to the Clipboard. | CTRL+X
Show the Resolve Conflicts and Errors dialog box. | F7
Close the browser window. | ALT+F4

Top of Page

Edit data

To do this | Press
Undo changes made to the current cell. | CTRL+Z
Insert non-breaking hyphen. | CTRL+SHIFT+-
In a Total row, display the list of aggregate functions available for the current cell. In columns of type single line of text, display the list of existing values in the column. | ALT+Down

Top of Page

Format a cell that supports rich HTML text

To do this | Press
Move focus to the Font list. | CTRL+SHIFT+F
Move focus to the Font size list. | CTRL+SHIFT+P
Apply Bold style to selection. | CTRL+B
Underline selection. | CTRL+U
Italicize selection. | CTRL+I
Justify selection to the left. | CTRL+L
Justify selection to the center. | CTRL+E
Justify selection to the right. | CTRL+R
Add bullets to selection. | CTRL+SHIFT+L
Increase indent. | CTRL+M
Decrease indent. | CTRL+SHIFT+M
Clear rich HTML text formatting in the current cell. | CTRL+SPACEBAR

Top of Page

Sort and filter data

To do this | Press
Display the filter drop-down list for the selected column. | ALT+SHIFT+DOWN
Close the filter drop-down list. | ALT+SHIFT+UP
Move to the previous item in the filter drop-down list, when the list is displayed. | UP
Move to the next item in the filter drop-down list, when the list is displayed. | DOWN
Move to the first item in the filter drop-down list, when the list is displayed. | HOME
Move to the last item in the filter drop-down list, when the list is displayed. | END
Move one page up or down in the filter drop-down list, when the list is displayed. | Page Up, Page Down
Execute the selected item in the filter drop-down list, when the list is displayed. | ENTER

Top of Page


How does Access Web Datasheet retrieve and update data?

Editing data in Access Web Datasheet is much faster than editing data in Standard view.

In Standard view, you can edit only one item at a time. After editing an item, it must be committed to the server before you can edit the next item.

In Access Web Datasheet, you can make changes to a row, and then move on to the next row and start editing the cells in that row. The view takes care of sending your changes to the server and communicating the status back to you, without disrupting your work.

Asynchronous updates

Asynchronous data retrieval

Asynchronous updates

When you add or edit a row, Access Web Datasheet sends the change to the server, marks the row as pending, and displays the Pending ( Pending icon ) icon in the row header. When a row has pending changes, you cannot edit the row until the changes are committed. But you can continue editing other rows while the last set of changes are being sent to the server.

When the view gets a response from the server, it updates the row accordingly. If the edit resulted in a conflict or error, the row header shows the Conflict ( Conflict icon ) icon or the Error ( Error icon ) icon. If the edit was successfully committed, the Pending icon is removed, and the row is made available for editing.

Changes to the view definition, such as changing the order of columns or hiding the Total row, are also sent to the server asynchronously. However, you cannot make structural changes or edit a calculated column when there are pending changes. You must wait for all changes to commit and resolve any resulting conflicts or errors before changing the structure or editing a calculated column.

If you attempt to refresh or move away from the view when there are pending changes, you will be prompted to wait until all of your changes are committed. Based on the server response, you will then be able to resolve any conflicts or errors.

Top of Page

Asynchronous data retrieval

When you open a list in Access Web Datasheet, it retrieves the first 100 rows, and retrieves the rest asynchronously. The status bar displays Retrieving data when the view is still fetching data from the server. If you attempt to filter or sort data, you will have to wait until the list completes retrieving all the necessary data from the server to see the result of the filtering or sorting.

If the server is busy, not found, or if any other error occurs that prevents the view from retrieving the necessary rows, the view will display an error.

Top of Page


Select a cell, row, column, or list

Select a single cell

  • Click the cell, or press the arrow keys to move to the cell.

    Note If you click a cell that contains a hyperlink, you will navigate to the corresponding Web address. To select a cell containing a hyperlink, use one of the keyboard shortcuts.

Select the contents of a cell

  • Select the cell, click inside the cell, and then select the text in the cell.

Select a range of cells

  • Click the first cell of the range, and then drag to the last cell. Or, click the first cell in the range, and then hold down SHIFT and click the last cell in the range.

Note You can scroll to make the last cell visible.

Select a row or column

  • Click the row or column header.

Select multiple rows or columns

  • Click the first row or column header and then drag to the last row or column. Or, select the first row or column; then hold down SHIFT and select the last row or column.


Add a multi-valued choice column

You can use a multi-valued choice column to select more than one item from the list of choices at a time. For example, in a task-tracking list, you can assign more than one person to each task. You enter the possible choices when you add the column to your list.

Add a multi-valued choice column

  1. Right-click any column heading or any cell in the list, and then click Add Column.
  2. Under Name and Type, enter a name for the column, and then click Choice.
  3. Under Additional Column Settings, type the choices that you want to appear in the column, and then click Checkboxes (allow multiple selections).
  4. Set any other options you want, and then click OK.

To use a multi-valued choice column while viewing the datasheet, click the drop-down arrow in the cell, and then select or clear the check box next to each value that you want to add or remove from the cell.

Edit the list of choices in a multi-valued choice column

To add, edit, or remove items from a multi-valued choice column, follow this procedure:

  1. Right-click the column heading or any cell in the column that you want to edit, and then click Edit/Delete Column.
  2. Under Additional Column Settings, edit the list of choices.
  3. Set any other options you want, and then click OK.

Use a multi-valued choice column

To use a multi-valued choice column, follow this procedure:

  1. Click the drop-down arrow in the cell that you want to edit.
  2. Select or clear the check box next to each item that you want to add or remove from the cell.
  3. Click OK.

The selected choices appear in the cell, separated by semicolons (;).


Add a multi-valued lookup column

You can use a multi-valued lookup column to select more than one item from the list of choices at a time. For example, in a task-tracking list, you can assign more than one person to each task. The values in the lookup column come from an existing column in the current list, or from an existing column in another list.

Add a multi-valued lookup column

  1. Right-click any column heading or any cell in the list, and then click Add Column.
  2. Under Name and Type, enter a name for the column, and then click Lookup (information already on this site).
  3. Under Additional Column Settings, select the list that contains the values you want in your lookup column from the Get information from drop-down list.
  4. Select the column that contains the values from the In this column drop-down list.
  5. Select Allow multiple values.
  6. Set any other options you want, and then click OK.

To use a multi-valued lookup column while viewing the datasheet, click the drop-down arrow in the cell, and then select or clear the check box next to each value that you want to add or remove from the cell.

Edit the list of choices in a multi-valued lookup column

Because the items in a multi-valued lookup column come from another column in the current list or from a column in another list, you must edit the list on which the lookup column is based. For example, if the lookup column is based on the Title column of the Tasks list, you must edit the Tasks list to change the values in the lookup column.

Use a multi-valued lookup column

To use a multi-valued lookup column, follow this procedure:

  1. Click the drop-down arrow in the cell you want to edit.
  2. Select or clear the check box next to each item that you want to add or remove from the cell.
  3. Click OK.

The selected choices appear in the cell, separated by semicolons (;).


Add a row or column

Add a row

By default, the star (*) row — a blank row marked by an asterisk (*) in the row header — is displayed after the last row in a list. You can use this row to add a new item to the list.

Note You cannot insert a blank row between two existing rows. If the view is read-only, the star row is not displayed.

To add a row, perform either of the following steps:

  • If the blank row is not visible, right-click any cell and then click Add Row, or,
  • In the ribbon, click the List tab, and then in the Datasheet group, click New Row.

Add a column

  1. Right-click a cell in the list.
  2. Click Add Column.
  3. In the Create Column page, specify the column details, and click OK.

    The new column is added to the right of the existing columns. To move the column to a new location, drag its column header to the location you want.


Change column width

  1. Right-click the column header, and click Column Width.
  2. In the Column Width dialog box, type a number between 1 and 255.

Notes

  • If you specify 0 as the width, the column will appear hidden.
  • You can also drag the boundary on the right side of the column header until the column is the width you want. To size a column so that the contents of each cell fit in a single line, move the cursor to the right boundary of the column header. When the resizing cursor appears, double-click the boundary line.


Change row height

  1. Right-click a row header, and click Row Height.
  2. In the Row Height dialog box, type a number between 1 and 409.

Notes

  • Changing the height of a row changes the height of all rows in the list.
  • You can also drag the top or bottom boundary of the row header until the row is the height you want. To size a row so that the contents are fully visible, move the cursor to the bottom boundary of the row header. When the resizing cursor appears, double-click the boundary line.


Delete a row or column

Delete a row

  1. Click the row header of the row that you want to delete.

    Note To delete multiple rows, click the first row header and then drag to the last row. Or, select the first row, then hold down SHIFT and select the last row.

  2. Right-click the selection and click Delete Rows.

Deleted rows are placed in the site Recycle Bin, and can be restored to the list if needed. For more information on how to work with the Recycle Bin, open Help in SharePoint Foundation 2010 by clicking Help ( Help icon ).

Delete a column

  1. Right-click the column header of the column that you want to delete, and then click Edit/Delete Column.
  2. On the Change Column page, click Delete.

Notes

  • Deleted columns are not placed in the site Recycle Bin. They are permanently deleted.
  • You can delete only one column at a time. If cells in multiple columns are selected, the column containing the active cell will be taken as the selected column.
  • If a column is read-only, you cannot delete it. Also, you cannot delete some columns in lists of a certain type, because these columns are required for the list to function properly.


Modify a column

You can change a column’s name, description, and data type, or settings related to its data type.

  1. Right-click a cell in the column that you want to modify, and then click Edit/Delete Column.
  2. On the Change Column page, make the changes, and click OK.

Notes

  • You can modify only one column at a time. If cells in multiple columns are selected, the column containing the active cell will be taken as the selected column.
  • If a column is read-only, you cannot modify it.


Rearrange columns

Datasheet view offers a quick and easy way to change the order of columns.

  1. To move a column, place the mouse pointer on the header of the column that you want to move until you see the move ( Move pointer ) pointer.

    To move a range of columns, click the header of the first column, press the SHIFT key, then while keeping the SHIFT key pressed, click the last column in the range. Place the pointer on one of the sides of the rectangle that covers the selected columns to display the move pointer.

  2. Click and drag the selected columns to the new location.

When you move a column, the view definition gets automatically updated.

Note You cannot move rows. You can rearrange rows by sorting them.


Show or hide a column

Show a hidden column

A thicker vertical line to the right of a column header indicates that the column next to it is hidden.

  • To unhide a column, select the visible columns on either side of the hidden column, right-click the selection, and click Unhide.
  • To unhide all hidden columns in the list, right-click the top-left corner of the list, and click Unhide.

Hide a column

  • To hide a column, right-click the header of the column that you want to hide, and click Hide.


View previous entries in a Multiple lines of text column

You can configure a Multiple lines of text column so that when you edit the data in the column, the prior entries are preserved rather than overwritten. By using this feature, you can maintain a record of previous entries in the column, when the changes were made, and by whom.

Create a new append-only column

  1. Right-click any cell in the list and click Add Column.
  2. Type a name for the column in the Column name box, and then click Multiple lines of text.
  3. Under Additional Column Settings, under Append Changes to Existing Text, click Yes.
  4. Make any other changes that you want, and then click OK.

    The new column is added to the right of the existing columns. To move the column to a new location, drag its column header to the location you want.

Modify an existing column to be append-only

  1. Right-click the column header of the column you want to modify, and then click Edit/Delete Column.
  2. If the column is not already of the data type Multiple lines of text, click Multiple lines of text. If this option is not available, the data type of this column cannot be modified and you must create a new column in order to use the Append Changes option.
  3. Under Additional Column Settings, under Append Changes to Existing Text, click Yes.
  4. Make any other changes that you want, and then click OK.

View previous entries in an append-only column

Only the most recent entry in an append-only column is displayed in the Access Web Datasheet cell. To view the previous entries, do the following:

  • Right-click the cell for which you want to view previous entries, and then click View Entries.

    SharePoint Foundation 2010 displays all of the data for the item, including the previous entries in the append-only column.


About data types

For help on data types, see SharePoint Foundation 2010 Help.

To display SharePoint Foundation 2010 Help, on the SharePoint page, click Help ( Help icon).


Specify a default value for a column

You can specify a default value for columns of type single-line, choice, number, currency, or date and time. The default value will be shown automatically in the column for all new rows. You can leave the default value in the cell, or edit it any time.

  1. Right-click a column, and click Edit/Delete Column.
  2. Under Default Value, select one of the following:
    • Text If the column type is Single line of text, specify a string as the default value.
    • Choice If the column type is Choice, specify one of the choices from the list of choices you specified above.
    • Number If the column type is Number, specify a numeric value.
    • Currency If the column type is Currency, specify a currency value.
    • Today's Date If the column type is Date and time, and you want the default value to be the current date, select this option.
    • Any date If the column type is Date and time, and you want to specify a specific date, select the last option button, and enter a date in MM/DD/YYYY format.
    • Yes in the Default value box If the column type is Yes/No (check box), and you want the check box to appear selected by default, select Yes.
    • Calculated If you want to use a calculated value as the default value, enter a formula. Note that the formula cannot reference other columns in the list.


Troubleshoot data types

For help with troubleshooting problems related to data types, see SharePoint Foundation 2010 Help.

To display Help in SharePoint Foundation 2010, click Help ( Help icon).


Add, edit, or delete cell contents

Make changes one cell at a time

You can add, edit, or delete cell contents in edit mode.

  1. If the cell that you want to edit is not selected, click the cell.

    Note If the cell contains a hyperlink, you will navigate to the corresponding Web address.

  2. Click the cell again to enter edit mode, then make changes to the cell.

    Note You can also press F2 to enter edit mode.

  3. Press TAB or ENTER when done.

Note Press CTRL+Z to undo the last change you made to the cell. Press ESC to discard the changes you made to the cell and exit edit mode.

Bulk edit cells

You can bulk edit cells, rows, or columns in one of the following ways:

  • By copying and pasting data.
  • By dragging the fill handle.


Edit a hyperlink

This command is available only if the selected cell has a hyperlink.

  1. Right-click the cell that has a hyperlink, point to Hyperlink, then click Edit Hyperlink.
  2. In the Edit Hyperlink dialog box, type a valid URL in the Type the Web address text box. A valid URL must begin with a protocol, such as http:// or ftp://, and must not exceed 255 characters.
  3. Type the display text in the Type the description text box.


Enter data in a cell by selecting from a list of values

When entering data in a column of single line of text cells, you can select a value for the cell in two ways.

  • Click the cell in which you want to enter data. If a down arrow appears to the side, click the arrow and then click the value you want to select for that cell.

    Select from a list

  • Begin typing the data in the cell. Data that has been entered elsewhere in the column will be used to match what you have already typed and the closest result will appear in the cell. When the correct value appears, press Enter to select it.

    Type from a list


Get notified when contents of a list are modified

Show Receive notification when one of the following is changed: row, document, or cell

  1. Right-click the row header or cell for which you want to enable notification, and click Alert me.
  2. On the New Alert page, specify how and when you want to be notified.

Show Receive notification if any changes are made to the list or document library

  1. Right-click the top-left corner of the list or library, and click Alert me.
  2. On the New Alert page, specify how and when you want to be notified.


Undo changes

You can undo changes that have not yet been saved to the list. When in edit mode, you can undo changes made to the current cell.

  • Click Undo on the task pane toolbar, or press CTRL+Z.

Notes

  • For cells that support rich HTML formatting, each formatting command is considered an edit.
  • The Undo command is different from canceling edit mode by pressing Esc.
  • You can't undo changes to the list after you add or remove an attachment from a record. You can't undo changes to a row after the row has been saved.


Validate data

For help on validating data, see SharePoint Foundation 2010 Help.

To display Help in Windows SharePoint Services "version 4", click Help ( Help icon).


Format data

You can format the contents of a column if it supports multiple lines of text and rich text.

To enable support for rich text for a column:

  1. Right-click in the column you want to enable for rich text and click Edit/Delete Column.
  2. Under Name and Type, select Multiple lines of text if it's not already selected.
  3. Under Additional Column Settings, select Rich text (Bold, italics, text alignment).
  4. Click OK.

Once those settings are made, you can format the text in one or more cells, or just a portion of the text in a cell:

  1. Select the cells or the text that you want to format.
  2. If the task pane is not open, in the ribbon, click the List tab, and then click Show Task Pane.
  3. Apply the format you want. You can change font settings, text and background color, and indentation and alignment. You can also organize text into a list of numbered or bulleted items.

Notes

  • You can clear rich text formatting by selecting text, then pressing CTRL+Space. However, CTRL+Space does not remove the alignment setting.
  • You cannot format read-only cells or the Total row.
  • Formatting does not apply to rows hidden as a result of a filter.


Turn on or off text wrapping for a column

If text wrapping is on for a column, the cell contents in the column will be displayed in more than one line. If text wrapping is off, each cell will display only what will fit in a single line. By default, text wrapping is on for all columns.

Text wrapping applies to the following data types: Single line of text, Multiple lines of text, Choice, Person or Group, Hyperlink or Picture, and Calculated.

  • Right-click the column header for which you want to change the setting, and click Turn Wrap Text Off or Turn Wrap Text On.


Troubleshoot formatting data

I do not see the formatting buttons in the task pane.

The formatting does not apply to all rows in the column.

I copied text into a multiple line of text column, but the formatting does not work.

I get a message about a cell exceeding its maximum size.

I do not see the formatting buttons in the task pane.

To enable formatting of text, the column must support multiple lines of text as well as rich text.

To enable support for rich text for a column:

  1. Right-click in the column you want to enable for rich text and click Edit/Delete Column.
  2. On the Change Columns page, in the Name and Type section, select Multiple lines of text if it's not already designated as the column type.
  3. In the Additional Column Settings section, select Rich text (Bold, italics, text alignment).
  4. Click OK.

Once those settings are changed, you can format the text in one or more cells, or just a portion of the text in a cell:

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. Select the cells or the text that you want to format.
  3. In the Task Pane, use the Rich Text tools to apply the format you want. You can change font settings, text and background color, and indentation and alignment. You can also organize text into a list of numbered or bulleted items.

Top of Page

The formatting does not apply to all rows in the column.

When you select an entire column and apply formatting, the Total row and rows that are hidden due to filtering do not get formatted.

Top of Page

I copied text into a multiple line of text column, but the formatting does not work.

This could be because the formatting of the source text is not fully recognized by the list. Make sure the source text does not include formats that are not supported by the list.

To see what formats are supported, select a cell in a column that supports rich text formatting, and browse the buttons below the toolbar in the task pane.

Top of Page

I get a message about a cell exceeding its maximum size.

A formatted cell contains text and information about the formatting. If the data and the formatting information exceed the maximum size of the cell, you will see this message. Try reducing the text or the number of formats applied to the text.

Top of Page


About filtering data

Filtering is the process of extracting and displaying only the list items that meet certain conditions. By applying multiple filters to a list, you can find specific list entries quickly.

For example, the Managers view of the Employee list is created by filtering for employees whose Manager column is set to Yes. You can further filter this list to view managers whose Country/region column is set to USA and State column is set to California. The following illustration shows managers who work in California.

Managers view of the Employee list

Note the appearance of the drop-down arrow in the header of the State and Country/region columns.

When you switch views or close the Managers view, the Country/region and State filters will not be saved. When you open the list in Managers view next time, the list will show all managers.

You can apply a filter to a list in two ways:

  • A filter can be applied to a list in Datasheet View. The filter is reset when you navigate to a different page and must be reapplied the next time you want to view the same results.
  • When you specify a filter as part of the view definition, filter criteria are saved and are applied each time you open the list in the specified view.

Columns of type multiple lines of text and hyperlink or picture do not support filtering. For columns of type attachment, you can filter for Yes and No values. For columns of type multi-choice, cells that contain the selected value will be included.


Apply a filter

  1. Click the drop-down arrow in the column header.

    Note If a column does not support filtering, the filtering commands will be disabled for that column.

  2. In the list, do one of the following:
    • Click a cell value to filter for records that have a matching value. For multi-choice columns, records that contain the selected value will be selected.
    • Click (Blanks) to filter for records that don't have a value.
    • Click (Non-Blanks) to filter for records that have a value.
    • Click (Custom Filter...) to define custom filter criteria. In the Custom Filter dialog box, specify the comparison operators and values. For example, to search for names beginning with "A" or "B", select begins with in the top left list and type A in the top right list. Select begins with in the middle left list and B in the middle right list. Select the top Or operator to link the two criteria, and click OK.
  3. Click (Show All) to remove filters from that column.

Note You can filter and sort multiple columns in a view.


Remove a filter or sort order

To see if a column is filtered, click the drop-down arrow in the column header. If a column has a filter, an entry other than Show All will be selected in the drop-down list.

Show To remove a filter from a column

  1. Click the drop-down arrow in the column header.
  2. Click Show All in the list.

Show To remove all filters and sorting orders

  • On the task pane toolbar, click Remove Filter/Sort.


Sort rows

Sorting is used to arrange items in a list in the desired order. This can be useful when trying to find specific entries or when identifying specific groupings of entries.

A list can be sorted in two ways

  • A list can be sorted while in Datasheet View. The sorting is reset when you navigate to a different page and must be reapplied the next time you want to view the same results.
  • When you specify sorting as part of the view definition, sorting criteria are saved and are applied each time you open the list in the specified view.

To sort on a single column

  1. Click the drop-down arrow in the column header.
  2. Click Sort Ascending or Sort Descending.
  3. You may repeat this process with other columns to organize the list more precisely.

To sort on multiple columns at once

When sorting on multiple columns, the order in which you sort the columns determines how the data will be organized. Usually, you must sort the most important information first. For example, to list the employees in alphabetical order for each department, it is most important that everyone is grouped by department. Therefore, you would first sort the Department column, then sort the Employees column.

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Sort.
  3. In the Custom Sort dialog box, under Sort by, select the outermost sort column, and click Ascending or Descending.
  4. Repeat the above step for two more columns, ending with the least important sorting criteria.
  5. Click OK.

Notes

  • You can apply one or more filters on a sorted view.
  • The columns of type Multiple lines of text, Multi-valued choice, or Multi-valued lookup do not support sorting. If a column does not support sorting, the sorting commands are disabled for that column.
  • The order of rows after sorting depends on the type of data in the columns that are used for sorting.

    Default sort orders for different types of data

    In an ascending sort, the Datasheet view uses the following order. In a descending sort, this sort order is reversed except for blank cells, which are always placed last.

    Type of data | Sort order
    Numbers | Sorted from the smallest negative number to the largest positive number
    Alphanumeric Sorted left to right, character by character. For example, if a cell contains the text "A100," it is placed after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

    Text and text that includes numbers are sorted in the following order: 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

    Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.
    Logical values | No is placed before Yes.
    Error values | All error values are treated as equals for sorting purposes.


Troubleshoot sorting and filtering

I cannot save the sort order or filter criteria in the view

The sort and filter commands are not available

Some of the operators are missing in the Custom Filter dialog box

I cannot see the column on which I want to sort rows or filter data

The sort order does not match the settings on my computer

I applied a filter or sort but the view does not appear filtered or sorted

I cannot save the sort order or filter criteria in the view

Any sorting or filtering that you apply to a list after opening it in Datasheet view is not saved. When you close the view or switch to another view, the settings are lost. To save a sort order or filter criteria, specify them as part of the view definition.

Top of Page

The sort and filter commands are not available

If a column does not support sorting or filtering, the commands are not displayed when you click the column header.

Top of Page

Some of the operators are missing in the Custom Filter dialog box

The list of operators displayed in the Custom Filter dialog box varies with the data type of the selected column. For example, Begins with and Contains are not available for numeric columns.

Top of Page

I cannot see the column on which I want to sort rows or filter data

Make sure the column is not hidden in Datasheet view. Right-click the cell in the top left corner of the list, and click Unhide. If the Unhide command is disabled, then check the view definition to make sure the column is included in the view.

Top of Page

The sort order does not match the settings on my computer

This could be due to the regional settings on your SharePoint server. For more details, see SharePoint Foundation 2010 Help. To display Help in SharePoint Foundation 2010, click Help ( [image]).

Top of Page

I applied a filter or sort but the view does not appear filtered or sorted

When you add or edit data after applying a filter or sort, the view does not automatically refresh the data. Try refreshing the view, or reapply the filter or sort order.

Top of Page


About moving, copying, and pasting cell contents

You can move or copy portions of a list to another location in the same list, or to another document. When you move a selection, the selection is removed from the list and is moved to the Clipboard. When you copy a selection, the selection remains in the list, and a copy is placed in the Clipboard. Pasting the contents of the Clipboard transfers it to the destination.

Moving or copying parts of a list

Pasting contents of the Clipboard into a list

Moving or copying parts of a list

If the selected range includes cells that either cannot be left blank or are read-only, the move operation will fail. Columns with the following data types cannot be left blank, so they are not cleared as a result of a move operation: Checkbox and Calculated.

The following table lists the contents of the Clipboard following a move or copy operation.

Move or copy... | Contents of the clipboard
Data or one or more cells (selection does not include entire rows or a column) | Cell contents are cleared or copied and placed in the Clipboard.
One or more rows | Selected rows will be deleted or copied and placed in the Clipboard.
A column (can only be copied, cannot be moved) | Selection will be placed in the Clipboard.
Total row | If moving, expression for the cells in the Total row is set to None. The Total row is not affected by a copy operation. Aggregate values are placed in the Clipboard.
Check box cell | Not cleared during a move operation. Yes or No is placed in the Clipboard.
Calculated column | Not cleared during a move operation. Calculated values are placed in the Clipboard.

Top of Page

Pasting contents of the Clipboard into a list

When you paste the contents of the Clipboard, new rows will be added to the bottom of the Datasheet view if necessary. However, if the view does not have sufficient columns, the paste operation will fail. A paste operation will also fail in the following situations:

  • Some or all of the destination cells are read-only
  • Pasting the contents into the selected cells will make them fail the validation rules
  • Attempting to paste data that does not match the data type of the destination cells
  • Attempting to paste data into a calculated column

When you attempt to paste data of one type into a column of a different type, the list will try to convert the source data to match the type of the destination column. For example, if the source data is numeric, and the destination cells support text, source data will be converted to text and then placed in the destination cells. If the source data cannot be converted to match the destination cell type, the paste operation will fail.

The following table lists the result of different paste operations:

Contents of the Clipboard | Attempting to paste contents to... | Result of the paste operation
A single cell | A range of cells | Contents copied to every cell in the selected range.
A range of cells | A single cell | Destination range expands to fit the entire contents of the Clipboard, where the selected cell becomes the top left cell of the destination range.
One or more rows or columns | First cell of a row or column | Destination range expands to fit the entire contents of the Clipboard, where the selected cell becomes the top left cell of the destination range.
One or more rows | A cell other than the first cell of a row | The paste operation fails.
One or more columns | A cell other than the first cell of a column | The paste operation succeeds, unless the destination range goes beyond the last row in the view, and the view includes required columns. Since the required columns in the new rows will be blank, new rows are not added, and you see a message indicating some rows were skipped.
A range of cells | A range of cells | If the dimensions match, the paste operation will succeed, otherwise, it will fail.
Cells in the Total row | Total row | Expression contained in the source cells will be placed in the destination cells.
Cells in the Total row | Anywhere other than the Total row | Aggregate values will be placed in the destination cells.

Top of Page


Automatically fill cells with data

You can fill a range of cells with a specific value, a range of values, or with linearly increasing values.

Copy contents of a cell to other cells within the column

Copy a range of values to other cells within the column

Linearly increasing values for a range of cells within the column

Copy contents of a cell to other cells within the column

For example, the Year column in row 1 contains the value 1980, and you want to copy it to rows 2, 3, 4, and 5.

  1. Select the cell whose contents you want to copy.
  2. Position the mouse pointer on the drag fill handle at the bottom right corner of the cell.

    Mouse pointer on drag fill handle

  3. When you see the drag cursor (+), drag until all the destination cells are included. You can drag above or below the selected cell.

    Destination cells selected with drag fill handle

  4. When you release the mouse button, the contents of the first cell will be copied into the destination cells.

    Destination cells filled with data from source cell

Top of Page

Copy a range of values to other cells within the column

For example, you want to fill the cells in the Quarter column with the values 1, 2, 3, 4, 1, 2, 3, 4, and so on.

  1. Type 1 in the first row, 2 in the second row, 3 in the third row, and 4 in the fourth row.
  2. Select all four cells.
  3. Position the mouse pointer on the drag fill handle at the bottom right corner of the selection.
  4. When you see the drag cursor (+), hold down CTRL and drag until all the destination cells are included. You can drag above or below the selected cell.

Top of Page

Linearly increasing values for a range of cells within the column

In certain text and numeric columns, you can fill cells with a series of linearly increasing values that increment upwards from the source cell or cells. Column types that do not support this operation are listed in the notes below.

For example, you want to fill the cells in the Year column with the values 1910, 1920, 1930, and so on.

  1. Type 1910 in the first row, and 1920 in the second row.
  2. Select the Year cell of the first and second rows.
  3. Position the mouse pointer on the drag fill handle at the bottom right corner of the selection.
  4. When you see the drag cursor (+), drag until all the destination cells are included. You can drag above or below the selected cell.

Note If the initial series contains a single value, you must hold down CTRL while dragging the fill handle to generate a series.

Examples of linearly increasing values

The following table lists a few series that can be generated by dragging the fill handle.

Initial values | Resulting series
1, 2, 3 | 4, 5, 6...
Mon | Tue, Wed, Thu...
Monday | Tuesday, Wednesday, Thursday...
Jan | Feb, Mar, Apr...
Jan, Apr | Jul, Oct, Jan...
1/1/2005 | 1/2/2005, 1/3/2005...
1/15/2005, 3/15/2005 | 5/15/2005, 7/15/2005...
1999, 2000 | 2001, 2002, 2003...
Qtr3 | Qtr4, Qtr1, Qtr2...
Q3 | Q4, Q1, Q2...
text1, textA | text2, textA, text3, textA...
1st Period | 2nd Period, 3rd Period...
Product 1 | Product 2, Product 3...

Notes

  • You can automatically fill more than one column in a single operation. The selected cells in each column will be used to fill the destination cells.
  • Columns with the following data types do not support filling with linearly increasing values:
    • Multiple lines of text
    • Choice
    • Lookup
    • Yes/No (check box)
    • Person or Group
    • Hyperlink or Picture
    • Calculated
  • If you try to fill a cell that is read-only or attempt to fill a cell with an invalid value, the fill operation will fail for that cell.

Top of Page


Troubleshoot automatically filling cells with data

I get the message "Cells in the destination region are read-only"

You are dragging the fill handle to include cells that are read-only.

I get the message "Rows were skipped because new rows contained invalid values in required fields"

You are dragging the fill handle to include rows that do not have any values in one or more columns that are marked required.

I get the message "You cannot perform this operation on a calculated column"

You cannot fill values in a calculated column by dragging the fill handle. A calculated column gets its values by evaluating a formula. To view or edit the formula of a calculated column, click a cell in the column, then click again.

I get the message "Attempting to fill cells or rows with invalid values"

You are attempting to fill cells with values that would fail the cell or row validation rules.

I get the message "Some data was truncated"

You are attempting to fill cells with values that are longer than the maximum length allowed in the destination cells.


Troubleshoot moving, copying, and pasting cell contents

I cannot paste data in a column, or I get the message "N cells could not be pasted"

I get the message "Some data was truncated"

I get the message "Cells in the region are required and cannot be cleared"

I get the message "Cells/Rows in the region are read-only"

I get the message "Cannot be pasted because the paste area is not completely on the list"

I get the message "Cannot be pasted because the cut or copy area is not the same size as the paste area"

I get the message "Some rows were skipped"

I cannot paste data in a column, or I get the message "N cells could not be pasted"

Do one or more of the following:

  • Make sure the column is not a calculated column.
  • Make sure the type of data you are attempting to paste matches the type of the destination cells.
  • Make sure the validation rules set for a cell or a row will not be violated by the paste operation.

Top of Page

I get the message "Some data was truncated"

You are attempting to paste more characters than are allowed in one or more of the destination cells. For example, if you try to paste more than 255 characters in a cell of type single-line of text, the extra characters will be truncated.

Top of Page

I get the message "Cells in the region are required and cannot be cleared"

You are attempting to move one or more cells in a column that is marked required. A required column cannot be left blank; instead of trying to move data, try copying the data to the destination, then delete unwanted rows.

Top of Page

I get the message "Cells/Rows in the region are read-only"

You are attempting to move data from one or more cells or rows that are read-only, or paste data in cells or rows that are read-only. You cannot change the contents of read-only cells and rows.

Top of Page

I get the message "Cannot be pasted because the paste area is not completely on the list"

This could be due to one of the following reasons:

  • You are attempting to paste one or more rows into a cell other than the first cell of a row.
  • You are attempting to paste a range of cells into a cell, but the view does not have the required number of rows and columns to the right and bottom of the destination cell.

Top of Page

I get the message "Cannot be pasted because the cut or copy area is not the same size as the paste area"

You are attempting to paste a range of cells into a destination range, but the dimensions do not match. Make sure you select the same number of rows and columns in the destination range as in the source range.

Top of Page

I get the message "Some rows were skipped"

This could be due to one of the following reasons:

  • You attempted to copy one or more columns into a cell other than the first cell in the destination column.
  • You attempted to paste data into the star (*) row, but the operation does not add data to all the required columns in the view.

Any operation that requires new rows to be added but leaves the required columns empty, will fail. The paste operation only succeeds on existing rows, while the remaining rows are skipped.

Top of Page


Approve or reject pending items

If a list is set up to require content approval, users with the appropriate list management rights can review the items that other users have added to the list and either approve or reject them before the information is displayed to the public. Only approved items are displayed to the site users.

  1. In Datasheet View, check the Approval Status column to see if any items are waiting for approval.
  2. Change to Approve/Reject Items view:
    1. In the ribbon, click the List tab, and then in the Current View group, click Approve/reject Items from the Current View drop-down menu.
    2. Make sure you are still in Datasheet View. Click Datasheet View, if necessary.
  3. Optionally, you can filter the list to display only pending list items. Click the drop-down arrow in the column header of the Approval Status column and click Pending.
  4. To approve or reject a single row, click the current status of the item in the Approval Status column, click the down arrow, and then click either Approved or Rejected in the drop-down menu.

    To update adjacent rows with the same status or comment, enter the value in the first row, and drag the fill handle to include the rows you want. You can also copy and paste values in the columns.

  5. Type your comments in the Approver Comments column.

Note You can also change the status of an approved or rejected item back to pending. This will un-publish the item so that users visiting the site can no longer see it.


Submitting data changes to a list manager for approval

If you are adding or editing data in a list that requires content approval, your changes will not be visible to other users until it has been approved by someone with the appropriate content approval rights.

Note If you have content approval rights to the list, any changes you make to the list are automatically approved.

Create a new list item and submit it for review

  1. In the Ribbon, click the Item tab, and then in the New group, click New Item.
  2. On the New Item page, enter the information about the new item and then click OK.

    In Datasheet View, the new item appears in the list with an approval status of Pending. A user with content approval rights must review and approve the item before it will be visible to people visiting the site.

Edit an item that has already been approved

  1. Change to the My Submissions view.
    1. In the Ribbon, click the List tab, and then in the Custom Views group, click My submissions from the Current View dropdown menu.
    2. Make sure you are still in Datasheet View. Click Datasheet View if necessary.

    This view displays all the items you have added to the list, including rejected items. The view displays the Approval Status and Comment columns, in addition to all the other columns in the list. However, the Approval Status, Comment, Modified, and Modified By columns are read-only. Once an item has been approved, it can only be edited by the person who originated it.

  2. In the row for the item you want to edit, make the desired changes.

    In Datasheet View, the approval status of item you edited automatically changes to Pending. When a row is edited, it must go through the approval process as if it were a new entry.


About working with document libraries

Datasheet view offers the same level of support for document libraries as it does for lists. You can open a document library in Datasheet view to view the contents of a library, add and delete folders and documents, and edit documents and change their properties.

Working with documents in Datasheet view

Managing documents and folders in Datasheet view

Working with documents in Datasheet view

A library in Datasheet view

You can work with a document library in Datasheet view in two ways.

  • In the Ribbon, under the Library tab, click Datasheet View.
  • Create one or more custom views for the library based on the Datasheet View. Here's how:
    1. In the Ribbon, under the Library tab, click Create View.
    2. On the Create View page, in the Choose a view format section, click Datasheet View.
    3. Type a name for the view in the View Name box. You may want to include the word "Datasheet" in the view name so you can tell what kind of view it is later.
    4. Set any other properties you want for your view.
    5. Click OK.
    6. The new view will be available in the Current View drop-down list.

      Note If the view’s folder setting is set to Show items inside folders, only the top-level documents and folders are displayed when you open the library. If the folder setting of the view is set to Show all items without folders, then all documents, including those that are located under different folders, are displayed as rows when you open the library.

To change the settings of an existing Datasheet view:

  1. In the Ribbon, under the Library tab, click Modify this View.
  2. On the Edit View page, select or change the properties for this view.
  3. Click OK.

Top of Page

Managing documents and folders in Datasheet view

  • You can add and upload documents to the library the same way you add and upload documents in All Documents view or in any other Standard view.
  • If the view’s folder setting is set to Show documents inside folders, you can add new folders, and navigate to different folders in the library.
  • You can open, edit, and check in or check out a document.
  • You can delete documents and folders in Datasheet view. When you delete a folder, the contents of the folder, including subfolders, are deleted from the library.
  • You can add columns, or delete and modify a column that you or another user added to the view. You can also enter and edit data stored in such columns in rows that contain documents. Rows corresponding to folders cannot be edited. Note that columns such as Type and Modified By are read-only. You cannot modify or delete them, nor can you enter or edit the contents of such columns.
  • You can show or hide the Total row.
  • You can sort and filter the library based on different columns, including the Type column.
  • Datasheet view does not display the star (*) row for a document library.
  • You cannot enter a value in the Name column either by dragging the fill handle, or by selecting a value from the cell’s drop-down list.

Top of Page


Add, view, or remove documents attached to a row

You can attach one or more documents to a row in certain types of lists. Attachments appear as a paperclip icon in the Attachments column of a row. If none of the items in a list have attachments, the Attachments column does not appear.

Add an attachment to a new item

  1. In the ribbon, click the Item tab, and then in the New group, click New Item.
  2. On the New Item page, type a name for the item and specify the other item properties.
  3. Click Attach File.
  4. Choose the file or files that you want to attach to the new item, and then click Open.
  5. Click OK.
  6. Click OK.

Add an attachment to an existing item

  1. In Datasheet View, right-click the Attachments column of a row, and click Edit Attachment.

    Note You cannot add attachments to a row that has not yet been committed.

  2. In the Attachments to List Item dialog box:
    • To add an attachement, click Add. Choose the file or files that you want to attach to the row, and then click Open.

      To preview a file before attaching it, double-click it in the Attachments to List Item dialog box.

    • To remove an attachment, select it and click Remove
  3. When you have all the attachments you want in the Attachments to List Item dialog box, click OK to attach them to the row. A paperclip icon will appear in the Attachments column to indicate that the row contains one or more attached files.


Check in or out a document

To check in a document that is checked out to you, but not currently open

  1. Right-click the row header or any cell in the row corresponding to the document that you want to check in, point to Document, then click Check in Document.
  2. On the Check In page, select whether you want to keep the document checked out to you after checking it in to the server or release it so that others can work on it.
  3. Optionally, in the Comments box, you can describe the changes you made to the document.
  4. Click OK.

To check out a document

  • Right-click the row header or any cell in the row corresponding to the document that you want to check out, point to Document, then click Check Out Document.

Note You cannot check out a document that is currently checked out by another user.


Delete a folder or document

Deleting a folder will delete all of its contents, including subfolders.

  1. Right-click the row header or any cell in the row corresponding to the folder or document that you want to delete, then click Delete Folder or Delete Document.
  2. To confirm, click Yes.

Note You can only delete a single folder at a time.


Edit a document

When you open a document for editing, SharePoint Foundation 2010 can perform several required tasks for you automatically, such as checking out the file, downloading it to your computer, and opening the relevant program.

  • Right-click the row header or any cell in the row corresponding to the document that you want to edit, point to Document, and then click Edit Document.

    If check out of the document is required before editing, SharePoint Foundation 2010 notifies you that you are about to check out and edit the document. Click OK to continue.

The document is opened using the appropriate Office application. You can edit the document, and save the document back to the library or to a different location. When you close a checked out document, you are given the option of either checking the document back in or updating the file on the server while keeping it checked out.

Note You cannot edit a document that has been checked out by another user. You also cannot edit any of the columns in the row corresponding to the document until it is checked back in.


Establish item-level permissions

You can can restrict users from viewing or editing items in a list by establishing item-level permissions. For example, you can set permissions for the list such that users can view only their own items, or you can set permissions such that users can view all items but edit only their own. When you create a new list, permissions are set so that all users can view and edit all items, regardless of who created them.

Restrict users from reading other users' items

Set read access permissions by using the following procedure:

  1. In the ribbon, click the List tab, and then in the Settings group, click List Settings.
  2. On the List Settings page, click Advanced Settings.
  3. On the Advanced Settings page, under Item-level Permissions, click one of the following Read access options:
    • Click Read all items to allow all users to read all items in the list.
    • Click Read items that were created by the user to allow users to read only the items they have created.
  4. Click OK.

Restrict users from editing items

Set edit access permissions by using the following procedure:

  1. In the ribbon, click the List tab, and then in the Settings group, click List Settings.
  2. On the List Settings page, click Advanced Settings.
  3. On the Advanced Settings page, under Item-level Permissions, click one of the following Edit access options:
    • Click Create and edit all items to allow all users to edit all items in the list.
    • Click Create items and edit items that were created by the user to allow users to edit only the items they have created.
    • Click None to prevent all users from editing all items.
  4. Click OK.


Make a document available for discussions

  • Right-click the row header or any cell in the row corresponding to the document that you want to make available for discussions, and click Discuss.

The document will open in Internet Explorer. The browser window will display the Discussions toolbar.


Open a folder or document

  • To navigate to a folder, or to open a document, click the icon in the Type column for the folder or file.

    If the document was created using Microsoft Office, the document will be opened using the appropriate Office application. All other documents will be opened in Internet Explorer.


Track and view version information for list items

You can store information about changes that are made to individual items in a list. You can then see what changes have been made to each item, in addition to when the changes were made and by whom.

Enable version tracking for items in a list

  1. In the ribbon, click the List tab, and then in the Settings group, click List Settings.
  2. On the List Settings page, click Versioning Settings.
  3. On the Versioning Settings page, in theItem Version History section, click Yes to create a version each time you edit an item.
  4. To limit the number of versions that are retained, select the Keep the following number of versions check box, and enter a maximum number of versions to retain.
  5. Click OK.

View versions for an item in a list

  1. Right-click the row header or any cell in the row corresponding to the item for which you want to view version information.
  2. Point to Item, and then click Versions.

    The version information is displayed.


View versions of a document

If the document versions setting of a document library is set to store versions of documents, then you can view information on the different versions of each document in the library.

View document versions

  • Right-click the row header or any cell in the row corresponding to the document whose versions you want to view, point to Document, and then click Document Versions.

Change document version settings

  1. In the ribbon, click the Library tag, and then in the Settings group, click Library Settings.
  2. On the List Settings page, under General Settings, click Versioning settings.
  3. On the Document Library Versioning Settings page, under Document Version History, select the versioning scheme you want, and then click OK.


About resolving conflicting changes

A conflict arises when two users make a change to the same portion of a list's data or structure. The user who submits a change first will succeed in committing his or her changes, but the second user will be notified of a conflict.

For example, user A changes the City column of the third row from Dallas to Seattle, and at the same time, you change the cell from Dallas to Houston. User A's changes are submitted to the server first, followed by your changes. The server informs you of a conflict and lets you resolve the conflict.

Note A conflict will occur even when users edit different cells in the same row.

The following illustration shows the list with the Conflict icon in the header of the third row.

A row with a conflict

You won't be able to make any changes to a row that has a conflict until you resolve the conflict.

Clicking the Conflict icon displays the Resolve Conflicts dialog box.

Note If you attempt to delete a row that has been updated by another user, a conflict will occur, but you won't see the Conflict icon because the row has already been updated. In that case, click Resolve in the status bar to open the Resolve Conflicts dialog box.

Resolve Conflicts dialog box

The details grid displays all the columns in your current view. For the affected row, it shows the change you made as well as the change made by the other user. The changed columns appear highlighted. Note that you cannot edit the values displayed in the details grid. At the top of the dialog box you can see the name of the user who made the change, and the date and time the change was made.

When two or more users make a series of changes, it is possible to get multiple conflicts. A new conflict could occur when you are trying to resolve an existing conflict. If there is more than one conflict, the Resolve Conflicts dialog box will enable you to view details of each conflict by clicking the Previous and Next buttons on the top right of the dialog box. The conflicts are sorted on row numbers. In other words, the conflict in the third row appears before the conflict in the fourth row. As you scroll through the conflicts, the affected row gets the focus in the datasheet. You can also view the details of a specific conflict by clicking the Conflict icon in the affected row. The dialog box will automatically scroll to the selected conflict, but you can view details of other conflicts by clicking Previous or Next.

Note If there are one or more errors in the view in addition to conflicts, the Resolve Conflicts dialog box will include details of the errors, and enable you to resolve them.

Based on the information in the details grid, you could either ignore the changes you made, or retry your changes. If the changes were made to different columns of a row, retrying your changes will merge your change with user A's change. If the changes were made to the same cell, your changes will overwrite user A's change. When there are multiple conflicts, you can take individual action for each conflict, or take a single action for all conflicts. To ignore or retry all of your changes, click Discard All My Changes or Retry All My Changes.

You can close the dialog box without taking any action, but you will not be able to leave or refresh the list, or make changes to a calculated column, until you resolve the conflict.

Show Special scenarios in the Resolve Conflicts dialog box

Here are the special cases where the Resolve Conflicts dialog box might present information differently.
  • You update a row that has been deleted by another user. In this case, the dialog box will not display the name of user or the date and time when the change was made. In the details grid, the row that corresponds to the other user simply displays Deleted row. The only option available to you is to discard your changes. You will not be able to retry your changes.
  • User A updates a column that is not included in your current view. In this case, the details grid will not include the column that was changed by user A. Retrying your changes will not overwrite the changes user A made to the columns not included in your view.
  • User A edits a column, and edits it again so that the cell gets the original value back. In this case, the dialog box will only show the latest change.
  • If the current filter hides a row with a conflict, the dialog box will temporarily remove the filter to show the row in the datasheet. After the dialog box is closed, the filter will be automatically reapplied.
  • If user A adds or deletes an attachment using the browser and you update a row, you will receive a conflict. However, the dialog box will highlight the column in the Other user's changes row only when the user adds the first attachment or removes the last attachment. The column in the details grid will show Yes when there are one or more atttachments, and No when there are none.
  • If the affected cell is a hyperlink, the details grid will display the URL if the display name is not available. For a picture, the grid will display the path. If the affected column includes rich text formatting, the grid will display the text without the formatting. However, the column will appear highlighted if the formatting was changed.

Top of Page

Resolving structural conflicts

It is also possible to get a structural conflict when two or more users are editing different parts of a list. A structural conflict occurs when it is not possible to resolve changes. For example, user A deletes a column, and user B attempts to update it. Or, user A changes the column's data type, and user B enters data that is not compatible with the new data type. In such situations, user B will be asked to refresh the list. Refreshing the list will retrieve the latest version of the list, but user B's changes will be discarded.

Top of Page

Resolving a conflict could lead to a new conflict or error

Resolving a conflict can result in one or more new conflicts or errors. For example, a third user, user C, makes a series of conflicting changes while you are resolving conflicts that arose due to user A's changes. If trying to reapply your changes results in a new conflict, it will be added to the list of existing conflicts, and the dialog box will be updated.

Top of Page


About resolving errors

You might encounter one or more errors while working with the Datasheet view. Some errors can be resolved by simply repeating the action or by refreshing the view, while others require a little more effort. Depending on the error, the view will provide you adequate information to help you resolve the error.

When errors can occur

Types of errors

Resolving errors

When errors can occur

Errors can occur when you:
  • Open a Datasheet view. For example, the view might not load or might not display any data.
  • View or edit data in the view. For example, committing your data changes might fail if the server could not be found.
  • View or edit the list structure. For example, deleting a column might result in an error due to lack of permissions.
  • View or edit the view definition. For example, reordering columns might fail due to a server timeout.

Top of Page

Types of errors

There are two types of errors - those that you can resolve by retrying your changes and those that you can resolve by discarding your changes.

Errors that you can resolve by retrying your changes:

  • No response from the server. This could be because of a timeout.
  • Server is busy or cannot be found.
  • Server response is not legible.
  • Any other unexpected error.

Errors that you can resolve by discarding your changes:

  • You do not have the necessary permissions to make the change.
  • You have been blocked from adding content to the site.
  • You have exceeded your storage limits for the site.
To avoid these errors, contact your system administrator.

Top of Page

Resolving errors

When an error occurs, the view displays the Error ( Error icon ) icon in the corresponding row, and also in the status bar. You will also see the text Click to resolve errors in the status bar. If there are conflicts and errors, you will see the text Click to resolve data conflicts and errors.

The following illustration shows the Resolve Errors dialog box.

Resolve Errors dialog box

The details grid displays all the columns in your current view. For the affected row, it shows the change you made. Note that you cannot edit the values displayed in the details grid. At the top of the dialog box, you can see the description of the error.

If there are multiple errors, you can view details of each error by clicking the Previous and Next buttons on the top right of the dialog box.

Based on the error information in the details grid, you could either ignore the changes you made, or retry your changes. When there are multiple errors, you can take individual actions for each error, or take a single action for all errors. To ignore or retry all of your errors, click Discard All My Changes or Retry All My Changes.

Note that any action you take to resolve an error might result in a new error. In that case, the dialog box will be updated to include details of the new error.

You can close the dialog box without taking any action, but you will not be able to leave or refresh the list, or make changes to a calculated column, until you resolve the error.

Top of Page


Resolve conflicting changes and errors

  1. Click Resolve on the status bar to display the Resolve Conflicts and Errors dialog box.

  2. Do one of the following:

    • To resolve the currently displayed conflict or error by ignoring the changes you made to the row, click Discard My Changes. Your changes will be lost.
    • To resolve all pending conflicts and errors by ignoring all of your changes to the list, click Discard All My Changes.
    • To resolve the currently displayed conflict or error by reapplying your changes, click Retry My Changes. In case of a conflict, if you and the other user change the same column, your changes will overwrite the other user's changes. If you edit different columns, your changes will be merged with the other user's changes.
    • To resolve all pending conflicts and errors by reapplying your changes, click Retry All My Changes.
    • To view the details of the next conflict or error, click Next on the top right corner of the dialog box.
    • To view the details of the previous conflict or error, click Previous on the top right corner of the dialog box.
    • To resolve conflicts and errors at a later time, click Close on the dialog box title bar.

Notes

  • The details grid shows all the columns in your current view. If the columns are not visible, use the horizontal scroll bar to scroll, or drag the right edge of the dialog box to increase the width of the details grid.

    To zoom the contents of a column, click the column in the details grid. Your changes and the other user's changes will be displayed in the Field Details dialog box. The dialog box is also useful for viewing rich text formatting changes.

  • The details grid displays Deleted row, instead of a row of data, if either you or the other user deletes a row. If the other user deletes the row, the dialog box does not display the name of the user or the date and time of the deletion. Also, you will not be able to retry your changes.


When do conflicts occur?

A conflict arises when two users make a change to the same portion of a list's data or structure. The user who submits a change first will succeed in committing his or her changes, but the second user will be notified of a conflict.

Types of changes that you can make to a list

Scenarios that will result in a conflict

Scenarios that will not result in a conflict

Scenarios that will result in a structural conflict

Scenarios that will not result in an error message or conflict

Types of changes that you can make to a list

You can make three types of changes to a list. The following table lists each type along with some examples:

Type of change | Examples
Change a list's data | Add, modify, or delete a row Paste Clipboard contents Add data to cells by dragging the fill handle Add or delete attachments Format the contents of one or more cells
Modify the structure of a list | Add or delete a column Change the settings of a column Make changes to a calculated column Change list settings
Change the view definition | Apply a filter or a sort order Change column width or row height Change column order Show or hide the Total row

Top of Page

Scenarios that will result in a conflict

The following table summarizes the scenarios in which a conflict will arise:

User A | User B
Deletes a row | Updates the same row
Updates a row | Updates or deletes the same row Note In a document library, if user A updates a row or a document, and user B deletes the same row or document, a conflict will not arise.
Adds or deletes an attachment using the browser | Updates or deletes the same row
Deletes a document | Updates the same row
Edits, renames, or saves a document | Updates the same row
Updates a row without editing document | Updates the same row
Changes rich text formatting | Updates or deletes the same row

In a document library, if user A opens a document and user B edits the document or changes its settings, user B will receive an error indicating that the document is locked.

Top of Page

Scenarios that will not result in a conflict

The following table summarizes the scenarios in which a conflict will not arise:

User A | User B | Example
Changes data | Changes view definition | User A adds a row and user B applies a sort order to the view
Changes structure | Changes view definition | User A adds a column, and user B applies a sort order to the view
Changes view definition | Changes data | User A applies a sort order, and user B adds a row
Changes view definition | Changes structure | User A applies a sort order, and user B adds a column
Changes view definition | Changes view definition | User A applies a sort order, followed by user B applying a different sort order Note Because the last change persists, user B's change will overwrite user A's change

Note In a document library, if users A and B open a document in read-only mode, and subsequently decide to edit and save the document by replacing the existing version, the user who saves second will overwrite the changes made by the other user.

Top of Page

Scenarios that will result in a structural conflict

The following table summarizes the scenarios in which an error message will be displayed. User B will be prompted to refresh the list, and their changes will be discarded.

User A | User B
Deletes a column | Updates data in the same column
Changes the data type of a column | Adds or updates data with type mismatch
Adds, renames, or deletes a column, changes a column's data type or other properties | Edits a calculated column
Makes list-level changes, such as renames the list, changes its description or security settings, rearranges the columns, enables or disables attachments, or adds the list to the Quick Launch bar | Edits a calculated column
Edits a calculated column | Adds, renames, or deletes a column, changes a column's data type or other properties. Note User B must be on the Add Column or Change Column page before user A starts editing the calculated column
Edits a calculated column | Makes list-level changes, such as renames the list, changes its description or security settings, rearranges the columns, enables or disables attachments, or adds the list to the Quick Launch bar Note User B must be on the List Settings page before user A starts editing the calculated column.

Top of Page

Scenarios that will not result in an error message or conflict

The following table summarizes the scenarios in which neither an error nor a conflict will occur:

User A | User B
Deletes a column | Deletes the same column
Deletes an attachment | Deletes the same attachment
Deletes a document | Deletes the same document or row
Deletes a row | Deletes the same row

Top of Page


About exporting a Datasheet view

You can export from SharePoint Foundation 2010 to Microsoft Access 2010 or Microsoft Excel 2010. For Access 2010, you can export to either a static or a linked table in a new or existing database. For Excel 2010, you can create a query of the Datasheet view in a new or existing worksheet, or a new workbook.

Exporting to Access

When you export from SharePoint Foundation 2010 to Access 2010, you are creating a static database table with data from Datasheet view. This static database table is not linked to the Datasheet view and does not update when changes are made to the data in SharePoint Foundation 2010.

Tracking a list in Access

When you choose Track this List in Access in the Task Pane, you create a table in Access that links dynamically to SharePoint Foundation 2010. If you change the data in Datasheet view, the changes are automatically updated in the Access table. Similarly, if you change the data in the Access table, the changes are automatically reflected in Datasheet view.

Note In some types of lists, there are columns that can be modified in the Datasheet view in SharePoint Foundation 2010, but not in linked Access tables.

Querying a list with Excel

When you query a Datasheet view with Excel, you are creating a query in Excel that retrieves data from Datasheet view. Once a Datasheet view has been exported to Excel 2010, any changes made to the data in the Datasheet view are updated automatically in the Excel worksheet. However, changes to the Excel worksheet are not transmitted back to SharePoint Foundation 2010.


Query a list with Excel

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Query List with Excel.

Microsoft Excel exports the Datasheet view as a Web query file.

If Excel is running when you click Chart with Excel, you are given the option to import the data in the Web query file into an existing worksheet, a new worksheet or a new workbook. Otherwise, Excel opens and imports the data to a new workbook. The imported data is displayed as a list in a worksheet and links the data with the information in SharePoint Foundation 2010.

Update worksheet with changes made in Datasheet view

After you make changes to data in SharePoint Foundation 2010, you can update the worksheet in Excel by using the refresh external data feature. For more information about queries, see Excel Help.


Export contact or event information

Export contact information

In the Standard view and the Datasheet view of a contact list, you can export an entire contact list or an individual contact from a contact list to Microsoft Outlook.

Export a contact list

  1. In the ribbon, click the List tab, and then in the Connect & Export group, click Connect to Outlook.
  2. Outlook prompts you to confirm connecting the contact list to Outlook. Click Yes.

Once you have exported a contact list to Outlook, any changes that are made to the contact list on Microsoft SharePoint Foundation 2010 will be automatically forwarded and updated in the contact list in Outlook. These changes are displayed the next time you reload the contact list in Outlook.

Export an individual contact

  1. Do one of the following:
    • In Standard view, click the drop-down arrow in the Last Name cell for the contact you want to export, and then click Export Contact.
    • In Datasheet view, right-click the contact you would like to export. Click Export Contact.
  2. You will be prompted to confirm downloading a virtual business card file (with file name extension vcf). Click Open.

This exports the contact to the default Outlook contacts list Contacts. Subsequently, you can decide whether to save the contact in Contacts, or in another contact list.

Top of Page

Export event information

You can export an entire event list or an individual event from an event list to Outlook. To view a calendar as an event list, in the Ribbon, under List Tools, click List, and then click All Events in the Current View drop-down list.

Export an event list

  1. In the ribbon, click the List tab, and then in the Connect & Export group, click Connect to Outlook.
  2. Outlook prompts you to confirm connecting the calendar in Microsoft SharePoint Foundation 2010 to Outlook. Click Yes.

After you have exported an event list to Outlook, any changes that are made to the event list on Microsoft SharePoint Foundation 2010 will be automatically forwarded and updated in the Outlook calendar. These changes are displayed the next time you reload the calendar in Outlook.

Export an individual event

  1. Do one of the following:
    • In Datasheet view, right-click anywhere on the row for the event you would like to export, and then click Export Event.
    • In Standard view, click the event you would like to export, and then, in the Custom Commands tab, click Export Event.
  2. You will be prompted to confirm downloading an Internet calendar file (with file name extension ics). Click Open.

This exports the event to the default Outlook calendar Calendar. Subsequently, you can decide whether to save the event in Calendar, or in another calendar.

Top of Page


Export or link a Datasheet view to Access

Export a Datasheet view to Access

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Export to Access.
  3. Choose to export the Datasheet to an existing database or a new database and click OK.

Microsoft Access prompts you to specify the location of the database and opens a table with the exported data.

Track a list in Access

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Track this List in Access.
  3. Choose to export the Datasheet to an existing database or a new database, and then click OK.

Access prompts you to specify the location of the database and creates a linked table that links to the data in SharePoint Foundation 2010.

Update Datasheet view with changes in a linked table

  1. Edit the linked table in Access.
  2. In the Datasheet View, in the ribbon, click the List tab, and then in the Datasheet group, click Refresh Data.

Update a linked table with changes in SharePoint

  • To see in your linked table any changes made to the data in SharePoint Foundation 2010, in Access, use the Refresh command.


Create an Access report

When you create a report in Microsoft Access with a Datasheet view, you create a linked table with the data in the Datasheet view and generate a report based on the data.

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. Click Report with Access.
  3. Choose to export the Datasheet to an existing database or a new database and click OK.

Access prompts you to specify the location of the database and creates a linked table that links to the data in SharePoint Foundation 2010 data. Then, it generates a simple report that displays all the fields and records in the linked table. For more information on reports in Access, search for "Create a report" in Access help.


Create an Excel chart

When you create a Microsoft Excel chart with a Datasheet view, Excel creates a query of the data in the Datasheet view, and then creates a chart based on the queried data.

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Chart with Excel.

Excel exports the Datasheet view as a Web query file.

If Excel is running when you click Chart with Excel, you are given the option to import the data in the Web query file into an existing worksheet, a new worksheet or a new workbook. Otherwise, Excel opens and imports the data into a new workbook. The imported data is displayed as a list in a worksheet and links the data with the information in SharePoint Foundation 2010. You are then prompted to create a chart using the Chart Wizard.


Create an Excel PivotTable

When you create a Microsoft Excel PivotTable with a Datasheet view, Excel creates a query the data in the Datasheet view, and then create a PivotTable based on the queried data.

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Create Excel PivotTable Report.

Excel exports the Datasheet view as a Web query file.

If Excel is running when you click Create Excel PivotTable Report, you are given the option to import the data in the Web query file into an existing worksheet, a new worksheet or a new workbook. Otherwise, Excel opens and imports the Datasheet into a new workbook. The imported data is displayed as a list in a worksheet and links the data with the information in SharePoint Foundation 2010. In Excel, the PivotTable field list and the PivotTable toolbar are displayed and you are prompted to create a PivotTable.


Print a Datasheet view with Excel

When you print a Datasheet view with Microsoft Excel, Excel creates a query of the data in the Datasheet view, and then prints the data returned by the query.

  1. In the ribbon, click the List tab, and then in the Datasheet group, click Show Task Pane.
  2. In the Task Pane, click Print with Excel.

Excel exports the Datasheet view as a Web query file.

If Excel is running when you click Print with Excel, you are given the option to import the data in the Web query file into an existing worksheet, a new worksheet or a new workbook. Otherwise, Excel opens and imports the Datasheet into a new workbook. The imported data is displayed as a list in a worksheet and links the data with the information in SharePoint Foundation 2010, and you are prompted to print the Excel list.


About calculation operators

Operators specify the type of calculation that you want to perform on the elements of a formula. Lists support three different types of calculation operators: arithmetic, comparison, and text.

Types of operators

The order in which a list performs operations in a formula

Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.

Types of operators

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

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

Comparison operators You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value of Yes or No.

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

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

Text operator | Meaning (Example)
& (ampersand) | Connects, or concatenates, two values to produce one continuous text value ("North"&"wind")

Top of Page

The order in which a list performs operations in a formula

Formulas calculate values in a specific order. A list formula might begin with an equal sign (=). Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Lists calculate the formula from left to right, according to a specific order for each operator in the formula.

Operator precedence

If you combine several operators in a single formula, lists perform 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 — lists evaluate the operators from left to right.

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

Use of parentheses

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because a list calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, the list adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

In the example below, the parentheses around the first part of the formula force the list to calculate [Cost]+25 first and then divide the result by the sum of the values in columns EC1 and EC2.

=([Cost]+25)/SUM([EC1]+[EC2])

Top of Page


About column references in a formula

A reference identifies a cell in the current row in a datasheet and tells a list where to look for the values or data you want to use in a formula. For example, [Cost] references the value in the Cost column in the current row. If the Cost column has the value of 100 for the current row, then =[Cost]*3 will return 300.

Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.

With references, you can use data contained in different columns of a list in one or more formulas. Columns of the following data types can be referenced in a formula: Single line of text, number, currency, date and time, choice, yes/no, and calculated.

You use the display name of the column to reference it in a formula. If the name includes a space or a special character, you must enclose the name in square brackets ([ ]). References are not case-sensitive. For example, you could reference the Unit Price column in a formula as [Unit Price] or [unit price].

Note You cannot reference a value in a row other than the current row. You also cannot include column references in a formula that is specified as the default value of a column.


About constants in formulas

A constant is a value that is not calculated. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. Constants can be of the following data types:

  • String (Example: =[Last Name] = "Smith")

    String constants are enclosed in double quotes and can be up to 255 characters.

  • Number (Example: =[Cost] >= 29.99)

    Numeric constants can include decimal places, and can be positive or negative.

  • Date (Example: =[Date] <> 7/7/2002)

    Date constants do not require delimiters.

  • Boolean (Example: =IF([Cost]>[Revenue], "Loss", "No Loss")

    Yes and No are Boolean constants. You can use them in conditional expressions. In the above example, if Cost is greater than Revenue, the IF function returns Yes, and the formula returns the string "Loss". If Cost is equal to or less than Revenue, the function returns No, and formula returns the string "No Loss".


About displaying totals and calculated values

You can perform different types of calculations in a list in Access Web Datasheet. You can display aggregate values, such as sum and average, for a column in the list. You can add calculated columns that use a formula to calculate values for each row in the list. You can also use formulas when specifying a default value for a column.

The following illustration shows the Employee-Payroll list in Access Web Datasheet. The list displays the aggregate values in the Total row at the bottom of the list, and a calculated column titled Net Pay.

Datasheet with a calculation and totals

The Total row displays a count of the rows in the Person column and the sum of values in the Net Pay column. The Net Pay column uses the formula that is displayed in the ScreenTip to calculate values for each row.

About displaying aggregate values

About displaying calculated values in a column

About using a formula to calculate default values for a column

About displaying aggregate values

In Datasheet View, you can show and hide the Total row by clicking Show Totals in the List tab of the ribbon. Initially, the Total row displays the sum for each numeric column in the list. If the list does not have any numeric columns, it displays a count of the items in the last column of the list. You cannot edit the values displayed in the Total row.

After enabling the Total row, you can show or hide the aggregate values for a specific column. You can also change the aggregate function that is used by a column by simply selecting a different function from the drop-down list in the Total row. The ScreenTip that appears when you hover your mouse pointer over an aggregate value specifies the aggregate function that is used by the column.

You cannot filter data in a list based on aggregate values. However, if a list is filtered, the aggregate values are calculated based only on the items that are included in the return set. The Total row does not get sorted along with the other rows in the list.

The following table lists the aggregate functions that are supported in a list, and the data types that support each function.

Function name | Description | Data types that support function
Average | Returns the average of the values in the column being aggregated. | Number, Currency, DateTime
Count | Tallies the number of values. For Boolean, Attachment, and DocIcon columns, only Yes values are included. | Number, Currency, DateTime, Boolean, Attachment, DocIcon, ShortText, MultiLineText, SingleChoice, MultiChoice, Lookup, Hyperlink
Maximum | Returns the largest value in the column being aggregated. | Number, Currency, DateTime
Minimum | Returns the smallest value in the column being aggregated. | Number, Currency, DateTime
Sum | Calculates the sum of the values in a column. | Number, Currency
Standard Deviation | Returns the standard deviation of the values in the column being summarized, assuming the values are a sample of a larger population. | Number, Currency
Variance | Returns the variance of the values in the column being summarized, assuming the values are a sample of a larger population. | Number, Currency

When you click the drop-down arrow in a cell in the Total row, you will see None displayed in the list, along with other aggregate functions. Select None for a column that you do not want to aggregate.

You can aggregate values in a calculated column. The list of functions that will be available for the column depends the data type of the value resulting from the calculation.

Top of Page

About displaying calculated values in a column

You can add a calculated column to a list to calculate values by using a formula. A formula can only refer to values in other columns within the same row. You cannot refer to a specific cell or a range of cells in a formula.

The calculated values are stored in the column, but you cannot edit the values. When the values referenced by the formula change, the list automatically recalculates the value for the calculated column.

The ScreenTip that appears when you rest your mouse pointer on a calculated value specifies the formula that is being used by the column. You can edit a formula by selecting a cell that uses the formula and entering edit mode. After you finish editing the formula, and exit edit mode, the new values are automatically calculated and displayed in the column.

You can sort and filter values in a calculated column.

Top of Page

About using a formula to calculate default values for a column

When you add a column to a list of type single line, choice, number, currency, or date and time, you can specify a formula instead of a constant as the default value. When you add a new row, the list will evaluate the formula and show the result as the default value in the column.

When you specify a formula as a default value, you can call any of the supported functions, but the formula cannot reference other columns. Since the formula will be evaluated before you enter data in other columns, column references are not allowed.

Top of Page


About formulas

Formulas are equations that perform calculations on values in a list. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

You can use a formula in a calculated column and to calculate default values for a column.

Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.

A formula can also contain any or all of the following: functions, column references, operators, and constants.

Parts of a formula
=PI()*[Result]^2

Functions The PI() function returns the value of pi: 3.142...

References (or column names) [Result] represents the value in the Result column for the current row.

Constants Numbers or text values entered directly into a formula, such as 2.

Operators The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies.

Examples

A formula might use one or more of the above elements. Here are some examples of formulas (in order of complexity).

  • Simple formulas (such as =128+345)

    The following formulas contain literal values and operators.

    Example | Description
    =128+345 | Adds 128 and 345
    =5^2 | Squares 5
  • Formulas that contain column references (such as =[Revenue] – [Cost])

    The following formulas refer to other columns in the same list.

    Example | Description
    =[Revenue] | Use the value in the Revenue column
    =[Revenue]*10/100 | 10% of the value in the Revenue column
    =[Revenue] > [Cost] | Returns Yes if the value in the Revenue column is greater than the value in the Cost column
  • Formulas that call functions (such as =AVG(1, 2, 3, 4, 5))

    The following formulas call built-in functions.

    Example | Description
    =MAX(Q1, Q2, Q3, Q4) | Returns the largest value in a set of values
    =IF(Cost>Revenue, "Not OK", "OK") | Returns Not OK if cost is greater than revenue. Else, returns OK.
    =DAY("15-Apr-2008") | Returns a day part of a date. This formula returns the number 15.
  • Formulas with nested functions (such as =SUM(ROUND([Cost],2),[Profit])

    The following formulas specify one or more functions as function arguments.

    Example Description
    =SUM(IF(A>B, A-B, 10), C) The IF function returns the difference between the values in columns A and B, or 10.

    The SUM function adds the return value of the IF function and the value in column C.

    =DEGREES(PI()) The PI function returns the number 3.14159265358979.

    The DEGREES function converts a value specified in radians to degrees. This formula returns the value 180.
    =ISNUMBER(FIND("BD",Column1)) The FIND function searches for the string BD in Column1 and returns the starting position of the string. It returns an error value if the string is not found.

    The ISNUMBER function returns Yes if the FIND function returned a numeric value. Else, it returns No.


Edit a formula

  1. Click any cell in the calculated column.
  2. Click the cell again to display the current formula.
  3. Edit the formula and press Enter.

    Note After you type the equal sign (=) in a calculated column, you can paste a column reference simply by clicking the cell in that column that belongs to the current row. However, if the column is of a data type that does not support calculations, if you are attempting to select a cell in a different row, or select more than one cell, the list will not paste the column reference in the formula.

  4. Click Continue to confirm your changes.
  5. If there are any conflicts, errors, or pending changes in the list, you will be prompted to commit or discard your changes. Changing a formula results in a change to the structure of the list, so the new formula can take affect only after all changes have been submitted to the server or discarded, and all conflicts and errors have been eliminated from the list.

    Click OK to commit changes and resolve conflicts and errors.

  6. The list will send the pending changes to the server. If there are conflicts and errors, you will see the Resolve Conflicts and Errors dialog box. Accept or discard your changes.

Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.


Examples of common formulas

You can use the following examples in calculated columns. Examples that do not include column references can be used to specify the default value of a column.

Important Formulas can only be used in columns with the data type Calculated. You cannot change the data type of a column to Calculated.

Conditional formulas

Show Check if a number is greater than or less than another number

Use the IF function to do this task.

Column1 | Column2 | Formula | Description
15000 | 9000 | =Column1>Column2 | Is Column1 greater than Column2? (Yes)
15000 | 9000 | =IF(Column1<=Column2, "OK", "Not OK") | Is Column1 less than or equal to Column2? (Not OK)

Show Return a logical value after comparing column contents

For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.
Column1 | Column2 | Column3 | Formula | Description
15 | 9 | 8 | =AND(Column1>Column2, Column1<Column3) | Is 15 greater than 9 and less than 8? (No)
15 | 9 | 8 | =OR(Column1>Column2, Column1<Column3) | Is 15 greater than 9 or less than 8? (Yes)
15 | 9 | 8 | =NOT(Column1+Column2=24) | Is 15 plus 9 not equal to 24? (No)

For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.

Column1 | Column2 | Column3 | Formula | Description
15 | 9 | 8 | =IF(Column1=15, "OK", "Not OK") | If the value in Column1 equals 15, then return "OK". (OK)
15 | 9 | 8 | =IF(AND(Column1>Column2, Column1<Column3), "OK", "Not OK") | If 15 is greater than 9 and less than 8, then return "OK". (Not OK)
15 | 9 | 8 | =IF(OR(Column1>Column2, Column1<Column3), "OK", "Not OK") | If 15 is greater than 9 or less than 8, then return "OK". (OK)

Show Display zeroes as blanks or dashes

Use the IF function to do this task.
Column1 | Column2 | Formula | Description
10 | 10 | =Column1-Column2 | Second number subtracted from the first (0)
10 | 10 | =IF(Column1-Column2,"",Column1-Column2) | Returns null when the value is zero (blank column)
15 | 9 | =IF(Column1-Column2,"-",Column1-Column2) | Returns a dash when the value is zero (-)

Date and time formulas

Show Add dates

To add a number of days to a date, use the addition (+) operator. Note that when manipulating dates, the return type of the calculated column must be set to Date and Time.
Column1 | Column2 | Formula | Description
6/9/2007 | 3 | =Column1+Column2 | Add 3 days to 6/9/2007 (6/12/2007)
12/10/2008 | 54 | =Column1+Column2 | Add 54 days to 12/10/2008 (2/2/2009)

To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1 | Column2 | Formula | Description
6/9/2007 | 3 | =DATE(YEAR(Column1),MONTH(Column1)+Column2,DAY(Column1)) | Add 3 months to 6/9/2007 (9/9/2007)
12/10/2008 | 25 | =DATE(YEAR(Column1),MONTH(Column1)+Column2,DAY(Column1)) | Add 25 months to 12/10/2008 (1/10/2011)

To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1 | Column2 | Formula | Description
6/9/2007 | 3 | =DATE(YEAR(Column1)+Column2,MONTH(Column1),DAY(Column1)) | Add 3 years to 6/9/2007 (6/9/2010)
12/10/2008 | 25 | =DATE(YEAR(Column1)+Column2,MONTH(Column1),DAY(Column1)) | Add 25 years to 12/10/2008 (12/10/2033)

To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1 | Formula | Description
6/9/2007 | =DATE(YEAR(Column1)+3,MONTH(Column1)+1,DAY(Column1)+5) | Add 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010)
12/10/2008 | =DATE(YEAR(Column1)+1,MONTH(Column1)+7,DAY(Column1)+5) | Add 1 year, 7 months, and 5 days to 6/9/2007 (1/14/2009)

Show Calculate the difference between two dates

Use the DATEDIF function to do this task.
Column1 | Column2 | Formula | Description
01-Jan-1995 | 15-Jun-1999 | =DATEDIF(Column1, Column2,"d") | Return the number of days between the two dates (1626)
01-Jan-1995 | 15-Jun-1999 | =DATEDIF(Column1, Column2,"ym") | Return the number of months between the dates, ignoring the year part (5)
01-Jan-1995 | 15-Jun-1999 | =DATEDIF(Column1, Column2,"yd") | Return the number of days between the dates, ignoring the year part (165)

Show Calculate the difference between two times

For presenting the result in the standard time format (hours:minutes:seconds), use the subtraction operator (-) and the TEXT function.

For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.

Column1 | Column2 | Formula | Description
06/09/2007 10:35 AM | 06/09/2007 3:30 PM | =TEXT(Column2-Column1,"h") | Hours between two times (4)
06/09/2007 10:35 AM | 06/09/2007 3:30 PM | =TEXT(Column2-Column1,"h:mm") | Hours and minutes between two times (4:55)
06/09/2007 10:35 AM | 06/09/2007 3:30 PM | =TEXT(Column2-Column1,"h:mm:ss") | Hours,minutes, and seconds between two times (4:55:00)

For presenting the result in a total based on one time unit, use the INT function, or HOUR, MINUTE, and SECOND functions.

Column1 | Column2 | Formula | Description
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =INT((Column2-Column1)*24) | Total hours between two times (28)
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =INT((Column2-Column1)*1440) | Total minutes between two times (1735)
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =INT((Column2-Column1)*86400) | Total seconds between two times (104100)
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =HOUR(Column2-Column1) | Hours between two times, when the difference does not exceed 24. (4)
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =MINUTE(Column2-Column1) | Minutes between two times, when the difference does not exceed 60. (55)
06/09/2007 10:35 AM | 06/10/2007 3:30 PM | =SECOND(Column2-Column1) | Seconds between two times, when the difference does not exceed 60. (0)

Show Convert times

To convert hours from standard time format to a decimal number, use the INT function.
Column1 | Formula | Description
10:35 AM | =(Column1-INT(Column1))*24 | Number of hours since 12:00 AM (10.583333)
12:15 PM | =(Column1-INT(Column1))*24 | Number of hours since 12:00 AM (12.25)

To convert hours from decimal number to the standard time format (hours:minutes:seconds), use the divisor operator and the TEXT function.

Column1 | Formula | Description
10:5833 | =TEXT(Column1/24, "h:mm") | Hours since 12:00 AM (10:35)
12:25 | =TEXT(Column1/24, "h:mm") | Hours since 12:00 AM (12:15)

Show Insert Julian dates

The phrase "Julian date" is sometimes used to refer to a date format that is a combination of the current year, and the number of days since the beginning of the year. For example, January 1, 2007 is represented as 2007001 and December 31, 2007 is represented as 2003356.

There is also a Julian date commonly used in astronomy, which is a serial date system starting on January 1, 4713 B.C.E.

Note This format is not based on the Julian calendar.

To convert a date to a Julian date, use the TEXT and DATEVALUE functions.

Column1 | Formula | Description
6/23/2007 | =TEXT(Column1,"yy")&TEXT((Column1-DATEVALUE("1/1/"& TEXT(Column1,"yy"))+1),"000") | Date in "Julian" format, with a two-digit year (07174)
6/23/2007 | =TEXT(Column1,"yyyy")&TEXT((Column1-DATEVALUE("1/1/"&TEXT(Column1,"yy"))+1),"000") | Date in "Julian" format, with a four-digit year (2007174)

To convert a date to a Julian date used in astronomy, use the constant 2415018.50.

This formula only works for dates after 3/1/1901, and if you are using the 1900 date system.

Column1 | Formula | Description
6/23/2007 | =Column1+2415018.50 | Date in "Julian" format, used in astronomy (2454274.50)

Show Show dates as the day of the week

To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions.
Column1 | Formula | Description
19-Feb-2007 | =TEXT(WEEKDAY(Column1), "dddd") | Calculates the day of the week for the date and returns the full name of the day (Monday)
3-Jan-2008 | =TEXT(WEEKDAY(Column1), "ddd") | Calculates the day of the week for the date and returns the abbreviated name of the day (Thu)

Math formulas

Show Add numbers

To add numbers in two or more columns in a row, use the addition operator or the SUM function.

Column1 | Column2 | Column3 | Formula | Description
6 | 5 | 4 | =Column1+Column2+Column3 | Add the values in the first three columns (15)
6 | 5 | 4 | =SUM(Column1,Column2,Column3) | Add the values in the first three columns (15)
6 | 5 | 4 | =SUM(IF(Column1>Column2, Column1-Column2, 10), Column3) | If Column1 is greater than Column2, add the difference and Column3. Else add 10 and Column3. (5)

Show Subtract numbers

Use the subtraction (-) operator to do this task.
Column1 | Column2 | Column3 | Formula | Description
15000 | 9000 | -8000 | =Column1-Column2 | Subtract 9000 from 15000 (6000)
15000 | 9000 | -8000 | =SUM(Column1, Column2, Column3) | Add numbers in the first three columns, including negative values (16000)

Show Calculate the difference between two numbers as a percentage

Use the subtraction (-) and division (/) operators, and the ABS function.
Column1 | Column2 | Formula | Description
2342 | 2500 | =(Column2-Column1)/ABS(Column1) | Percentage change (6.75% or 0.06746)

Show Multiply numbers

Use the multipliation (*) operator or the PRODUCT function to do this task.
Column1 | Column2 | Formula | Description
5 | 2 | =Column1*Column2 | Multiplies the numbers in the first two columns (10)
5 | 2 | =PRODUCT(Column1, Column2) | Multiplies the numbers in the first two columns (10)
5 | 2 | =PRODUCT(Column1,Column2,2) | Multiplies the numbers in the first two columns and the number 2 (20)

Show Divide numbers

Use the division operator (/) to do this task.

Column1 | Column2 | Formula | Description
15000 | 12 | =Column1/Column2 | Divides 15000 by 12 (1250)
15000 | 12 | =(Column1+10000)/Column2 | Adds 15000 and 9000, and then divides the total by 12 (2000)

Show Calculate the average of numbers

The average is also called the mean. To calculate the average of numbers in two or more columns in a row, use the AVERAGE function.

Column1 | Column2 | Column3 | Formula | Description
6 | 5 | 4 | =AVERAGE(Column1, Column2,Column3) | Average of the numbers in the first three columns (5)
6 | 5 | 4 | =AVERAGE(IF(Column1>Column2, Column1-Column2, 10), Column3) | If Column1 is greater than Column, calculate the average of the difference and Column3. Else calculate the average of the value 10 and Column3. (2.5)

Show Calculate the median of numbers

The median is the value at the center of an ordered range of numbers. Use the MEDIAN function to calculate the median of a group of numbers.
A | B | C | D | E | F | Formula | Description
10 | 7 | 9 | 27 | 0 | 4 | =MEDIAN(A, B, C, D, E, F) | Median of numbers in the first 6 columns (8)

Show Calculate the smallest or largest number in a range

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.
Column1 | Column2 | Column3 | Formula | Description
10 | 7 | 9 | =MIN(Column1, Column2, Column3) | Smallest number (7)
10 | 7 | 9 | =MAX(Column1, Column2, Column3) | Largest number (10)

Show Count values

To count numeric values, use the COUNT function.
Column1 | Column2 | Column3 | Formula | Description
Apple |  | 12/12/2007 | =COUNT(Column1, Column2, Column3) | Counts the number of columns that contain numeric values, including date and time values. Exludes text and null values. (1)
$12 | #DIV/0! | 1.01 | =COUNT(Column1, Column2, Column3) | Counts the number of columns that contain numeric values, but excludes error and logical values (2)

Show Increase or decrease a number by a percentage

Use the percentage (%) operator to do this task.
Column1 | Column2 | Formula | Description
23 | 3% | =Column1*(1+5%) | Increases number in Column1 by 5% (24.15)
23 | 3% | =Column1*(1+Column2) | Increase number in Column1 by the percent value in Column2: 3% (23.69)
23 | 3% | =Column1*(1-Column2) | Decrease number in Column1 by the percent value in Column2: 3% (22.31)

Show Raise a number to a power

Use the exponent (^) operator or the POWER function to do this task.
Column1 | Column2 | Formula | Description
5 | 2 | =Column1^Column2 | Calculates five squared (25)
5 | 3 | =POWER(Column1, Column2) | Calculates five cubed (125)

Show Round a number

To round up a number, use the ROUNDUP, ODD, and EVEN functions.

Column1 | Formula | Description
20.3 | =ROUNDUP(Column1,0) | Rounds 20.3 up to the nearest whole number (21)
-5.9 | =ROUNDUP(Column1,0) | Rounds -5.9 up (-6)
12.5493 | =ROUNDUP(Column1,2) | Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)
20.3 | =EVEN(Column1) | Rounds 20.3 up to the nearest even number (22)
20.3 | =ODD(Column1) | Rounds 20.3 up to the nearest odd number (21)

To round down a number, use the ROUNDDOWN function.

Column1 | Formula | Description
20.3 | =ROUNDDOWN(Column1,0) | Rounds 20.3 down to the nearest whole number (20)
-5.9 | =ROUNDDOWN(Column1,0) | Rounds -5.9 down (-5)
12.5493 | =ROUNDDOWN(Column1,2) | Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54)

To round a number to the nearest number or fraction, use the ROUND function.

Column1 | Formula | Description
20.3 | =ROUND(Column1,0) | Rounds 20.3 down, because the fraction part is less than .5 (20)
5.9 | =ROUND(Column1,0) | Rounds 5.9 up, because the fraction part is greater than .5 (6)
-5.9 | =ROUND(Column1,0) | Rounds -5.9 down, because the fraction part is less than -.5 (-6)
1.25 | =ROUND(Column1, 1) | Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)
30.452 | =ROUND(Column1, 2) | Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.

Column1 | Formula | Description
5492820 | =ROUND(Column1,3-LEN(INT(Column1))) | Rounds the number to 3 significant digits (5490000)
22230 | =ROUNDDOWN(Column1,3-LEN(INT(Column1))) | Rounds the bottom number down to 3 significant digits (22200)
5492820 | =ROUNDUP(Column1, 5-LEN(INT(Column1))) | Rounds the top number up to 5 significant digits (5492900)

Text formulas

Show Change the case of text

Use the UPPER, LOWER, or PROPER functions to do this task.
Column1 | Formula | Description
nancy Davolio | =UPPER(Column1) | Changes text to uppercase (NANCY DAVOLIO)
nancy Davolio | =LOWER(Column1) | Changes text to lowercase (nancy davolio)
nancy Davolio | =PROPER(Column1) | Changes text to title case (Nancy Davolio)

Show Combine first and last names

Use the ampersand (&) operator or the CONCATENATE function to do this task.
Column1 | Column2 | Formula | Description
Nancy | Fuller | =Column1&Column2 | Combines the two strings (NancyFuller)
Nancy | Fuller | =Column1&" "&Column2 | Combines the two strings, separated by a space (Nancy Fuller)
Nancy | Fuller | =Column2&","&Column1 | Combines the two strings, separated by a comma (Fuller, Nancy)
Nancy | Fuller | =CONCATENATE(Column2, ",", Column1) | Combines the two strings, separated by a comma (Fuller,Nancy)

Show Combine text and numbers from different columns

Use the CONCATENATE and TEXT functions, and the ampersand (&) operator to do this task.
Column1 | Column2 | Formula | Description
Buchanan | 28 | =Column1&" sold "&Column2&" units." | Combines contents above into a phrase (Buchanan sold 28 units)
Dodsworth | 40% | =Column1&" sold "&TEXT(Column2,"0%")&" of the total sales." | Combines contents above into a phrase (Dodsworth sold 40% of the total sales). Note The TEXT function appends the formatted value of Column2 instead of the underlying value, which is .4.
Buchanan | 28 | =CONCATENATE(Column1," sold ",Column2," units.") | Combines contents above into a phrase (Buchanan sold 28 units)

Show Combine text with a date or time

Use the TEXT function and the ampersand (&) operator to do this task.
Column1 | Column2 | Formula | Description
Billing Date | 5-Jun-2007 | ="Statement date: "&TEXT(Column2, "d-mmm-yyyy") | Combine text with a date (Statement date: 5-Jun-2007)
Billing Date | 5-Jun-2007 | =Column1&" "&TEXT(Column2, "mmm-dd-yyyy") | Combine text and date from difference columns into one column (Billing Date Jun-05-2007)

Show Compare column contents

To compare one column to another column or a list of values, use the EXACT and OR functions.
Column1 | Column2 | Formula | Description
BD122 | BD123 | =EXACT(Column1,Column2) | Compare contents of first two columns (No)
BD122 | BD123 | =EXACT(Column1, "BD122") | Compare contents of Column1 and the string "BD122" (Yes)
BD122 | BD123 | =OR(EXACT("BD121", Column1, Column2) | Compare the string "BD121" with the contents of the first two columns (No)

Show Check if a column value or a part of it matches specific text

To check if a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBer functions.

Column1 | Formula | Description
Davolio | =IF(Column1="Davolio", "OK", "Not OK") | Checks to see if Column1 is Davolio (OK)
Davolio | =IF(ISNUMBER(FIND("v",Column1)), "OK", "Not OK") | Checks to see if Column1 contains the letter v (OK)
BD123 | =ISNUMBER(FIND("BD",Column1)) | Checks to see if Column1 contains BD (Yes)

Show Count nonblank columns

Use the COUNTA function to do this task.
Column1 | Column2 | Column3 | Formula | Description
Sales | 19 |  | =COUNTA(Column1, Column2) | Counts the number of nonblank columns (2)
Sales | 19 |  | =COUNTA(Column1, Column2, Column3) | Counts the number of nonblank columns (2)

Show Remove characters from text

Use the LEN, LEFT, and RIGHT functions to do this task.
Column1 | Formula | Description
Vitamin A | =LEFT(Column1,LEN(Column1)-2) | Return 7 (9-2) characters, starting from left (Vitamin)
Vitamin B1 | =RIGHT(Column1, LEN(Column1)-8) | Return 2 (10-8) characters, starting from right (B1)

Show Remove spaces from the beginning and end of a column

Use the TRIM function to do this task.
Column1 | Formula | Description
Hello there! | =TRIM(Column1) | Remove the spaces from the beginning and end (Hello there!)

Show Repeat a characater in a column

Use the REPT function to do this task.
Formula | Description
=REPT(".",3) | Repeats a period 3 times (...)
=REPT("-",10) | Repeats a dash 10 times (----------)

Other formulas

Show Hide error values in columns

To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.
Column1 | Column2 | Formula | Description
10 | 0 | =Column1/Column2 | Results in an error (#DIV/0)
10 | 0 | =IF(ISERROR(Column1/Column2),"NA",Column1/Column2) | Returns NA when the value is an error
10 | 0 | =IF(ISERROR(Column1/Column2),"-",Column1/Column2) | Returns a dash when the value is an error


Show or hide aggregate values

  • To show or hide aggregate values for all columns in the list, in the ribbon, click the List tab, and then in the Datasheet group, click Show Totals.

    The Total row appears at the bottom of the list. It displays the sum of values for numeric columns, and if there are no numeric columns in the list, a count of non-empty rows in the last column in the list. If the row was visible when you clicked Show Totals , it will be hidden.

  • To show or hide aggregate values for one or more columns in the list, do the following:
    1. Click a cell in the Total row.
    2. Click the down arrow that appears in the cell.
    3. In the drop-down list of aggregate functions, select None to hide the total value. Select a function other than None to display a total value.


Troubleshoot totals and calculated values

I get a message saying the results of the formula will be sent to the server

I get a syntax error

The cell displays the formula instead of the result

I get a message about pending changes when I edit a calculated column

I get a timeout or unanticipated error

I get an error indicating that the list structure has changed

I get an error indicating that I do not have permission to change calculated columns in the list

I cannot edit a formula because one or more other columns require values

I get an error indicating that one or more column references are not allowed

I get an error indicating that a column does not exist

When editing a calculated column, I cannot select the column I want

Cells in a calculated column display #NAME?

Cells in a calculated column display #CIRC!

Cells in a calculated column display #VALUE!

Cells in a calculated column display #NUM!

Cells in a calculated column display #DIV/0!

Cells in a calculated column display #NULL!

Cells in a calculated column display #N/A

I get a message saying the results of the formula will be sent to the server

You edited the formula, and the list is attempting to submit the new results to the server. Do one of the following:

  • Click Continue to complete the edit operation. If there are pending changes, conflicts, or errors, the list will prompt you to take appropriate actions.
  • Click Cancel to return to the cell containing the formula.

Top of Page

I get a syntax error

This could be because of one or more of the following reasons:

  • The parentheses used in the formula do not match. Make sure each opening parenthesis has a matching closing parenthesis.
  • The formula includes a special character other than the equal sign (=) and calculation operators.
  • The formula calls one or more functions, but you have specified an incorrect number of arguments for one or more of these functions.
  • The formula uses a function that is not supported by a list. Specifically, lists do not support the Rand and Now functions. You cannot use the Today and Me functions in a calculated column, but you can use them to specify the default value for a column.

Top of Page

The cell displays the formula instead of the result

Make sure the cell that contains the formula is part of a calculated column. If you enter a formula in a column other than a calculated column, such as a column of type single line of text, the list will treat the formula as a text string and display it as it is.

Top of Page

I get a message about pending changes when I edit a calculated column

Any change to a calculated column results in a change to the list structure. You can make a structural change to a list only if there are no pending changes, conflicts, and errors. If the list encounters one of these in the list, you will prompted to wait until all changes are submitted to the server, and resolve any conflicts and errors, before you can add or edit a column.

Top of Page

I get a timeout or unanticipated error.

Refresh the list, and repeat the change you made to the calculated column.

Top of Page

I get an error indicating that the list structure has changed

Editing a calculated column results in a change to the list structure. If the list encounters a conflict between the change you made, and a structural change made by another user, you will prompted to refresh the list. Refreshing the list updates your view, but discards the changes you made to the calculated column. Try repeating your change.

Top of Page

I get an error indicating that I do not have permission to change calculated columns in the list

Editing a calculated column results in a change to the list structure. If you do not have permissions to make changes to a list structure, you will not be able to edit calculated columns in the list.

Top of Page

I cannot edit a formula because one or more other columns require values

The list must commit all of your changes before you can edit a formula. A row cannot be committed if one of its columns is set as a required column, but is left blank. Enter valid values in all the required columns and then trying editing a formula.

Top of Page

I get an error indicating that one or more column references are not allowed

The formula includes a reference to a column whose data type does not support calculations. Edit the formula to remove references to unsupported columns.

Top of Page

I get an error indicating that a column does not exist

You have specified a string as an operand, but the list could not find a column with a name that matches the string. Do one or more of the following:

  • Make sure the columns referenced in the formula exist.
  • Make sure the column names are enclosed in square brackets ([]) if they include spaces or special characters.
  • Verify the spelling of the column references.

Top of Page

When editing a calculated column, I cannot select the column I want

After you type the equal sign (=) in a calculated column, you can paste a column reference simply by clicking the cell in that column that belongs to the current row. However, if the column is of a data type that does not support calculations, or you are attempting to select a cell in a different row or select more than one cell, the list will not paste the column reference in the formula.

Top of Page

Cells in a calculated column display #NAME?

A column referenced in a formula does not exist anymore. Either add the column to the list, or edit the formula to remove the reference.

Top of Page

Cells in a calculated column display #CIRC!

The error is caused by one or more circular references. A circular reference occurs in the following situations:

  • The formula used in the column includes a reference to itself. For example, the formula used to calculate values for the Taxable Income column references the Taxable Income column.
  • Two or more calculated columns reference each other. For example, the Calc1 column uses the formula =[Calc2]+2 and the Calc2 column uses the formula =[Calc1]-1.
To remove a circular reference, do one or more of the following:
  • Make sure the formula does not reference the column that contains the formula.
  • If the formula references another column, make sure the referenced column's formula does not reference the first column.
  • Verify the spelling of the column references.

Top of Page

Cells in a calculated column display #VALUE!

This error can occur in one of the following two situations:

  • A wrong type of operand is used in the formula.
  • A wrong type of argument is specified for a function that is used in the formula.
Verify the data type of the operands and arguments.

Top of Page

Cells in a calculated column display #NUM!

This error occurs when the formula includes invalid numeric values, such as:

  • The value returned by the formula is too large to be represented in a list.
  • The formula encountered a date value that is earlier than 1/1/1900.
  • A function used in the formula expects a numeric argument, but you have specified a non-numeric argument. A wrong type of argument is specified for a function that is used in the formula.

Top of Page

Cells in a calculated column display #DIV/0!

This error occurs when a number is divided by zero (0), such as:

  • Explicitly specifying a division by zero (0), such as =133/0.
  • The formula includes column references, and at the time of evaluation, the values in the referenced columns result in a division by zero (0). For example, the [ECount1] and [ECount2] columns have the same values in some rows, so the formula =[Qty]/[ECount1]-[ECount2] results in an error for those rows.

Top of Page

Cells in a calculated column display #NULL!

This error occurs when an argument or operand specified in a formula has a null value. If one element of a formula has a null value, the entire formula results in a null value. To avoid this error, make sure null values are replaced with zero (0). You might also want to change the setting of the referenced columns to Required, so that all rows have a non-null value.

Top of Page

Cells in a calculated column display #N/A

This error occurs when a function is missing a value. Make sure you have specified all the required arguments to a function.

Top of Page


About functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the ROUND function rounds off a number in the Cost column.

=ROUND(Cost, 2)

Here are the primary issues to keep in mind when using a function:

  • Structure The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
  • Function name Name of a function that is supported by lists. Each function takes a specific number of arguments, processes them, and returns a value.
  • Arguments Arguments can be numbers, text, logical values such as True or False, or column references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
  • Argument tooltip A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.

Note Lists do not support the RAND and NOW functions. The TODAY and ME functions are not supported in calculated columns, but are supported in the default value setting of a column.

Top of Page

Nested functions

In certain cases, you may 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 sum of two column values.

=AVERAGE([Cost1], SUM([Cost2]+[Discount]))

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

Nesting level limits A formula can contain up to eight levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the SUM function is a second-level function because it is an argument of the AVERAGE function. A function nested within the SUM function would be a third-level function, and so on.

Top of Page


Add or edit a calculated column

You can add a column that calculates values for each row based on other values in the same row.

Add a calculated column

  1. In the ribbon, click the List tab, and then in the Settings group, click Create Column.
  2. On the Create Column page, specify a name for the column.
  3. Select Calculated as the type of information that will be stored in the column.
  4. In the Formula text area, type a new formula. To insert column references, select a column in the Insert Column list, and click Add to formula.
  5. Specify the format of the value that will be returned by the formula, and other related settings.

Edit a calculated column

  1. Right-click the column, and click Edit/Delete Column.
  2. In the Formula text area, edit the existing formula. To insert column references, select a column in the Insert Column list, and click Add to formula.
  3. Specify the format of the value that will be returned by the formula, and other related settings.

Notes

  • Adding or editing a column results in a change to the list structure. You can make a structural change to a list, only if there are no pending changes, conflicts, and errors. If the list encounters one of these in the list, you will prompted to wait until all changes are submitted to the server, and resolve any conflicts and errors, before you can add or edit a column.
  • You cannot change the data type of a column to Calculated. To use a formula in a non-calculated column, you must delete the column and then add a calculated column.


Change the type of total value displayed in a column

  1. Click the Total row of the column for which you want to change the total type.
  2. Click the drop-down arrow, and select the function you want from the list.

Note Selecting None from the list for a column turns off the total value for that column.


DATE

Returns the sequential serial number that represents a particular date.

Syntax

DATE(year,month,day)

Year The year argument can be one to four digits.

  • If year is between 0 (zero) and 1899 (inclusive), the value is added to 1900 to calculate the year. For example,

    DATE(108,1,2)
    returns January 2, 2008 (1900+108).

  • If year is between 1900 and 9999 (inclusive), the value is used as the year. For example,

    DATE(2008,1,2)
    returns January 2, 2008.

Month is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example,

DATE(2008,14,2)
returns the serial number representing February 2, 2009.

Day is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example,

DATE(2008,1,35)
returns the serial number representing February 4, 2008.

Remarks

  • Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
  • The DATE function is most useful in formulas where year, month, and day are formulas, not constants.

Example

Year | Month | Day | Formula | Description (Result)
2008 | 1 | 1 | =DATE([Year],[Month],[Day]) | Serial date for the date (1/1/2008 or 39448)


DATEDIF

Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3.

Syntax

DATEDIF(start_date,end_date,unit)

Start_date A date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).

End_date A date that represents the last, or ending, date of the period.

Unit The type of information you want returned:

Unit | Returns
"Y" | The number of complete years in the period.
"M" | The number of complete months in the period.
"D" | The number of days in the period.
"MD" | The difference between the days in start_date and end_date. The months and years of the dates are ignored.
"YM" | The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD" | The difference between the days of start_date and end_date. The years of the dates are ignored.

Remarks

  • Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
  • The DATEDIF function is useful in formulas where you need to calculate an age.

Example

Start_date | End_date | Formula | Description (Result)
1/1/2001 | 1/1/2003 | DATEDIF(Start_date,End_date,"Y") | Two complete years in the period (2)
6/1/2001 | 8/15/2002 | DATEDIF(Start_date,End_date,"D") | 440 days between June 1, 2001, and August 15, 2002 (440)
6/1/2001 | 8/15/2002 | DATEDIF(Start_date,End_date,"YD") | 75 days between June 1 and August 15, ignoring the years of the dates (75)
6/1/2001 | 8/15/2002 | DATEDIF(Start_date,End_date,"MD") | The difference between 1 and 15 — the day of start_date and the day of end_date — ignoring the months and the years of the dates (14)


DATETIME

The DATETIME function is a combination of DATE and TIME functions.

DATE returns the sequential serial number that represents a particular date.

TIME returns the decimal representation of the time with a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax

DATETIME(year,month,day,hour,minute,second)

Year The year argument can be one to four digits. You cannot use a year before 1900. If year argument is between 0 (zero) and 1899, the value is added to 1900 to calculate the year. For example, DATETIME(108,1,2,n,n,n) returns the date as 39.449; which is the serial number representing January 2, 2008 (1900+108). If year argument is between 1900 and 9999, the value is used as the year. For example, DATETIME(2008,1,2,n,n,n) also returns the date as 39.449.

Month is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATETIME(2008,14,2,n,n,n) returns the serial number representing February 2, 2009.

Day is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATETIME(2008,1,35,n,n,n) returns the serial number representing February 4, 2008.

Hour is a number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, DATETIME(n,n,n ,27,0,0) returns the time as.125 or 3:00 AM.

Minute is a number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, DATETIME(n,n,n ,0,750,0) returns the time as .520833 or 12:30 PM.

Second is a number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, DATETIME(n,n,n ,0,0,2000) returns the time as .023148 or 12:33:20 AM.

Remarks

Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Example

Year | Month | Day | Hour | Minute | Second | Formula | Description
2006 | 12 | 1 | 12 | 45 | 25 | =DATETIME([Year],[Month],[Day],[Hour],[Minute],[Second]) | Serial number for the date and the decimal representation of the time of the day (39,052.5315393519)

Top of Page


DATEVALUE

Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.

Syntax

DATEVALUE(date_text)

Date_text is text that represents a date in a date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates.

If the year portion of date_text is omitted, DATEVALUE uses the current year from your server's built-in clock. Time information in date_text is ignored.

Remarks

  • Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
  • Most functions automatically convert date values to serial numbers.

Example

Formula | Description (Result)
=DATEVALUE("8/22/2008") | Serial number of the text date (39682)
=DATEVALUE("22-AUG-2008") | Serial number of the text date (39682)
=DATEVALUE("2008/02/23") | Serial number of the text date (39501)
=DATEVALUE("5-JUL") | Serial number of the text date, assuming the computer's built-in clock is set to 2000 (37807)


DAY

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax

DAY(serial_number)

Serial_number is the date of the day you are trying to find.

Remarks

  • Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
  • Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

Formula | Description (Result)
=DAY("15-Apr-2008") | Day of the date (15)


DAYS360

Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

Syntax

DAYS360(start_date,end_date,method)

Start_date and end_date are the two dates between which you want to know the number of days. If start_date occurs after end_date, DAYS360 returns a negative number.

Method is a logical value that specifies whether to use the U.S. or European method in the calculation.

Method | Defined
FALSE or omitted | U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.
TRUE | European method. Starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month.

Remark

Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Example

Col1 | Col2 | Formula | Description (Result)
1/30/2008 | 2/1/2008 | =DAYS360([Col1],[Col2]) | Number of days between the two dates, based on a 360-day year (1)


HOUR

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Syntax

HOUR(serial_number)

Serial_number is the time that contains the hour you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Remark

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Formula | Description (Result)
=HOUR("3:30:30 AM") | Hour of time (3)
=HOUR("3:30:30 PM") | Hour of time (15)
=HOUR("15:30") | Hour of time (15)


MINUTE

Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

Syntax

MINUTE(serial_number)

Serial_number is the time that contains the minute you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Remarks

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5, since it is half of a day).

Example

Formula | Description (Result)
=MINUTE("4:48:00 PM") | Minutes of the time (48)


MONTH

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Syntax

MONTH(serial_number)

Serial_number is the date of the month you are trying to find.

Remarks

Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

Formula | Description (Result)
=MONTH("15-Apr-2008") | Month of the date (4)


NOW.TIME

Returns the decimal representation of the current time. The decimal number returned by NOW.TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Note NOW.TIME returns the current time only. To use a function for a particular time, use the TIME function. For more information, see the topic TIME function.

Syntax

NOW.TIME()

Remark

The NOW.TIME function syntax has no arguments. You must include the empty parentheses with the function name. Otherwise, SharePoint Foundation 2010 will not recognize it as a function.


SECOND

Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59.

Syntax

SECOND(serial_number)

Serial_number is the time that contains the seconds you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Remark

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Formula | Description
=SECOND("4:48:18 PM") | Seconds in the time (18)
=SECOND("4:48 PM") | Seconds in the time (0)


TIME

Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax

TIME(hour,minute,second)

Hour is a number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.

Minute is a number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.

Second is a number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM

Remark

Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Hour | Minute | Second | Formula | Description (Result)
12 | 0 | 0 | =TIME([Hour],[Minute],[Second]) | Decimal representation of the time of day, for the time (0.5)
16 | 48 | 10 | =TIME([Hour],[Minute],[Second]) | Decimal representation of the time of day, for the time (0.700115741)


TIMEVALUE

Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

Syntax

TIMEVALUE(time_text)

Time_text is a text string that represents a time in any one of the spreadsheet time formats; for example, "6:45 PM" and "18:45" text strings within quotation marks that represent time.

Remarks

  • Date information in time_text is ignored.

Example

Formula | Description (Result)
=TIMEVALUE("2:24 AM") | Decimal part of a day, for the time (0.1)
=TIMEVALUE("22-Aug-2008 6:35 AM") | Decimal part of a day, for the time (0.274305556)


TODAY

Returns the serial number of the current date. The serial number is the date-time code used for date and time calculations.

Note You can only use the TODAY function as a default value; you cannot use it in a calculated column.

Syntax

TODAY

Remark

Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.


WEEKDAY

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax

WEEKDAY(serial_number,return_type)

Serial_number is a sequential number that represents the date of the day you are trying to find.

Return_type is a number that determines the type of return value.

Return_type | Number returned
1 or omitted | Numbers 1 (Sunday) through 7 (Saturday).
2 | Numbers 1 (Monday) through 7 (Sunday).
3 | Numbers 0 (Monday) through 6 (Sunday).

Remark

Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Example

Formula | Description (Result)
=WEEKDAY("2/14/2008") | Day of the week, with numbers 1 (Sunday) through 7 (Saturday) (5)
=WEEKDAY("2/14/2008",2) | Day of the week, with numbers 1 (Monday) through 7 (Sunday) (4)
=WEEKDAY("2/14/2008",3) | Day of the week, with numbers 0 (Monday) through 6 (Sunday) (3)

Note 2/14/2008 is a Thursday.


YEAR

Returns the year corresponding to a date.

Syntax

YEAR(serial_number)

Serial_number is the date of the year you want to find.

Remarks

Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.

Example

Formula | Description (Result)
=YEAR("7/5/2008") | Year of the first date (2008)
=YEAR("7/5/10") | Year of the second date (2010)


DDB

Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.

Syntax

DDB(cost,salvage,life,period,factor)

Cost is the initial cost of the asset.

Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).

Life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset).

Period is the period for which you want to calculate the depreciation. Period must use the same units as life.

Factor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method).

All five arguments must be positive numbers.

Remarks

  • The double-declining balance method computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods. DDB uses the following formula to calculate depreciation for a period:

    ((cost-salvage) - total depreciation from prior periods) * (factor/life)

  • Change factor if you do not want to use the double-declining balance method.

Example

Cost | Salvage | Life | Formula | Description (Result)
2400 | 300 | 10 | =DDB([Cost],[Salvage],[Life]*365,1) | First day's depreciation. Factor is automatically assumed to be 2. (1.32)
2400 | 300 | 10 | =DDB([Cost],[Salvage],[Life]*12,1,2) | First month's depreciation (40.00)
2400 | 300 | 10 | =DDB([Cost],[Salvage],[Life],1,2) | First year's depreciation (480.00)
2400 | 300 | 10 | =DDB([Cost],[Salvage],[Life],2,1.5) | Second year's depreciation using a factor of 1.5 instead of the double-declining balance method (306.00)
2400 | 300 | 10 | =DDB([Cost],[Salvage],[Life],10) | Tenth year's depreciation. Factor is automatically assumed to be 2 (22.12)

Note The results are rounded to two decimal places.


FV

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax

FV(rate,nper,pmt,pv,type)

For a more complete description of the arguments in FV and for more information on annuity functions, see PV.

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Set type equal to | If payments are due
0 | At the end of the period
1 | At the beginning of the period

Remarks
  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
  • For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

Example 1

Rate | Nper | Pmt | PV | Type | Formula | Description (Result)
6% | 10 | -200 | -500 | 1 | =FV(Rate/12, Nper, Pmt, PV, Type) | Future value of an investment with the specified arguments (2581.40)

Note The annual interest rate is divided by 12 because it is compounded monthly.

Example 2

Rate | Nper | Pmt | Formula | Description (Result)
12% | 12 | -1000 | =FV([Rate]/12, [Nper], [Pmt]) | Future value of an investment with the specified arguments (12,682.50)

Note The annual interest rate is divided by 12 because it is compounded monthly.


IPMT

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions, see the PV function.

Syntax

IPMT(rate,per,nper,pv,fv,type)

Rate is the interest rate per period.

Per is the period for which you want to find the interest and must be in the range 1 to nper.

Nper is the total number of payment periods in an annuity.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Set type equal to | If payments are due
0 | At the end of the period
1 | At the beginning of the period

Remarks
  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
  • For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

Example

Rate | Period | Nper | PV | Formula | Description (Result)
10% | 1 | 3 | 8000 | =IPMT([Rate]/12, [Period]*3, [Nper], [PV]) | Interest due in the first month for a loan with the specified arguments (-22.41)
10% | 1 | 3 | 8000 | =IPMT([Rate], 3, Nper, [PV]) | Interest due in the last year for a loan with the specified arguments, where payments are made yearly (-292.45)

Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.


ISPMT

Calculates the interest paid during a specific period of an investment.

Syntax

ISPMT(rate,per,nper,pv)

Rate is the interest rate for the investment.

Per is the period for which you want to find the interest, and must be between 1 and nper.

Nper is the total number of payment periods for the investment.

Pv is the present value of the investment. For a loan, pv is the loan amount.

Remarks

  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
  • For all the arguments, the cash you pay out, such as deposits to savings or other withdrawals, is represented by negative numbers; the cash you receive, such as dividend checks and other deposits, is represented by positive numbers.
  • For additional information about financial functions, see the PV function.

Example

Rate | Per | Nper | PV | Formula | Description (Result)
10% | 1 | 3 | 8000000 | =ISPMT([Rate]/12,[Per],[Nper]*12,[PV]) | Interest paid for the first monthly payment of a loan with the specified arguments (-64814.8)
10% | 1 | 3 | 8000000 | =ISPMT([Rate],1,[Nper],[PV]) | Interest paid in the first year of a loan with the specified arguments (-533333)

Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.


NPER

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Syntax

NPER(rate, pmt, pv, fv, type)

For a more complete description of the arguments in NPER and for more information about annuity functions, see PV.

Rate is the interest rate per period.

Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due.

Set type equal to | If payments are due
0 or omitted | At the end of the period
1 | At the beginning of the period

Example
Rate | Pmt | PV | FV | Type | Formula | Description (Result)
12% | -100 | -1000 | 10000 | 1 | =NPER([Rate]/12, [Pmt], [PV], [FV], 1) | Periods for the investment with the specified arguments (60)
12% | -100 | -1000 | 10000 | 1 | =NPER([Rate]/12, [Pmt], [PV], [FV]) | Periods for the investment with the specified arguments, except payments are made at the beginning of the period (60)
12% | -100 | -1000 | 10000 | 1 | =NPER([Rate]/12, [Pmt], [PV]) | Periods for the investment with the specified arguments, except with a future value of 0 (-9.578)


NPV

Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

Syntax

NPV(rate,value1,value2, ...)

Rate is the rate of discount over the length of one period.

Value1, value2, ... are 1 to 29 arguments representing the payments and income.

  • Value1, value2, ... must be equally spaced in time and occur at the end of each period.
  • NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
  • Arguments that are numbers, empty, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.

Remarks

  • The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below.
  • If n is the number of cash flows in the list of values, the formula for NPV is:

  • NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
  • NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(...), ...) = 0.

Example 1

In the following example:

  • Rate is the annual discount rate.
  • Value1 is the initial cost of investment one year from today.
  • Value2 is the return from first year.
  • Value3 is the return from second year.
  • Value4 is the return from third year.
Rate | Value1 | Value2 | Value3 | Value4 | Formula | Description (Result)
10% | -10000 | 3000 | 4200 | 6800 | =NPV([Rate], [Value1], [Value2], [Value3], [Value4]) | Net present value of this investment (1,188.44)

In the preceding example, you include the initial $10,000 cost as one of the values, because the payment occurs at the end of the first period.

Example 2

In the following example:

  • Rate is the annual discount rate. This might represent the rate of inflation or the interest rate of a competing investment.
  • Value1 is the initial cost of investment one year from today.
  • Value2 is the return from first year.
  • Value3 is the return from second year.
  • Value4 is the return from third year.
  • Value5 is the return from fourth year.
  • Value6 is the return from fifth year.
Rate | Value1 | Value2 | Value3 | Value4 | Value5 | Value6 | Formula | Description (Result)
8% | 40000 | 8000 | 9200 | 10000 | 12000 | 14500 | =NPV(Rate, [Value2], [Value3], [Value4], [Value5], [Value6])+[Value1] | Net present value of this investment (1,922.06)
8% | 40000 | 8000 | 9200 | 10000 | 12000 | 14500 | =NPV(Rate, [Value2], [Value3], [Value4], [Value5], [Value6], -9000)+[Value1] | Net present value of this investment, with a loss in the sixth year of 9000 (-3,749.47)

In the preceding example, you don't include the initial $40,000 cost as one of the values, because the payment occurs at the beginning of the first period.


PMT

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

PMT(rate,nper,pv,fv,type)

For a more complete description of the arguments in PMT, see the PV function.

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to | If payments are due
0 or omitted | At the end of the period
1 | At the beginning of the period

Remarks
  • The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

Show Tip

To find the total amount paid over the duration of the loan, multiply the returned PMT value by nper.

Example 1

In the following example:

  • Rate is the annual interest rate
  • Nper is the number of months of payments
  • PV is the amount of loan
Rate | Nper | PV | Formula | Description (Result)
8% | 10 | 10000 | =PMT([Rate]/12, [Nper], [PV]) | Monthly payment for a loan with the specified arguments (-1,037.03)
8% | 10 | 10000 | =PMT([Rate]/12, [Nper], [PV], 0, 1) | Monthly payment for a loan with the specified arguments, except payments are due at the beginning of the period (-1,030.16)

Example 2

You can use PMT to determine payments to annuities other than loans.

In the following example:

  • Rate is the annual interest rate.
  • Nper is the years you plan on saving.
  • PV is the amount you want to have save in 18 years.
Rate | Nper | PV | Formula | Description (Result)
6% | 18 | 50000 | =PMT([Rate]/12, [Nper]*12, 0, [PV]) | Amount to save each month to have 50,000 at the end of 18 years (-129.08)

Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.


PPMT

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax

PPMT(rate,per,nper,pv,fv,type)

For a more complete description of the arguments in PPMT, see PV.

Rate is the interest rate per period.

Per specifies the period and must be in the range 1 to nper.

Nper is the total number of payment periods in an annuity.

Pv is the present value — the total amount that a series of future payments is worth now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 or 1 and indicates when payments are due.

Set type equal to | If payments are due
0 or omitted | At the end of the period
1 | At the beginning of the period

Remark

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.

Example 1

Rate | Nper | PV | Formula | Description (Result)
10% | 2 | 2000 | =PPMT([Rate]/12, 1, [Nper]*12, [PV]) | Payment on principle for the first month of loan (-75.62)

Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.

Example 2

Rate | Per | PV | Formula | Description (Result)
8% | 10 | 200,000 | =PPMT([Rate], [Per], 10, [PV]) | Principal payment for the last year of the loan with the specified arguments (-27,598.05)


PV

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

Syntax

PV(rate,nper,pmt,fv,type)

Rate is the interest rate per period. For example, if you obtain an automobile loan at a 10 percent annual interest rate and make monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as the rate.

Nper is the total number of payment periods in an annuity. For example, if you get a four-year car loan and make monthly payments, your loan has 4*12 (or 48) periods. You would enter 48 into the formula for nper.

Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. For example, the monthly payments on a $10,000, four-year car loan at 12 percent are $263.33. You would enter -263.33 into the formula as the pmt. If pmt is omitted, you must include the fv argument.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). For example, if you want to save $50,000 to pay for a special project in 18 years, then $50,000 is the future value. You could then make a conservative guess at an interest rate and determine how much you must save each month. If fv is omitted, you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due.

Set type equal to | If payments are due
0 or omitted | At the end of the period
1 | At the beginning of the period

Remarks
  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
  • The following functions apply to annuities:
    • FV
    • IPMT
    • PMT
    • PPMT
    • PV
    • RATE
    An annuity is a series of constant cash payments made over a continuous period. For example, a car loan or a mortgage is an annuity. For more information, see the description for each annuity function.
  • In annuity functions, cash you pay out, such as a deposit to savings, is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number. For example, a $1,000 deposit to the bank would be represented by the argument -1000 if you are the depositor and by the argument 1000 if you are the bank.
  • One financial argument is solved in terms of the others. If rate is not 0, then:

    If rate is 0, then:

    (pmt * nper) + pv + fv = 0

Example

In the following example:

  • Pmt is the money paid out of an insurance annuity at the end of every month.
  • Rate is the interest rate earned on the money paid out.
  • Nper is the years the money will be paid out.
Pmt | Rate | Nper | Formula | Description (Result)
500 | 8% | 20 | =PV([Rate]/12, 12*[Nper], [Pmt], , 0) | Present value of an annuity with the specified arguments (-59,777.15).

The result is negative because it represents money that you would pay, an outgoing cash flow. If you are asked to pay (60,000) for the annuity, you would determine this would not be a good investment because the present value of the annuity (59,777.15) is less than what you are asked to pay.

Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.


RATE

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

Syntax

RATE(nper,pmt,pv,fv,type,guess)

For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

Pv is the present value — the total amount that a series of future payments is worth now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due.

Set type equal to | If payments are due
0 or omitted | At the end of the period
1 | At the beginning of the period

Guess is your guess for what the rate will be.

  • If you omit guess, it is assumed to be 10 percent.
  • If RATE does not converge, try different values for guess. RATE usually converges if guess is between 0 and 1.

Remark

Make sure that you are consistent about the units you use for specifying guess and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for guess and 4*12 for nper. If you make annual payments on the same loan, use 12% for guess and 4 for nper.

Example

Nper | Pmt | PV | Formula | Description (Result)
4 | -200 | 8000 | =RATE([Nper]*12, [Pmt], [PV]) | Monthly rate of the loan with the specified arguments (1%)
4 | -200 | 8000 | =RATE([Nper]*12, [Pmt], [PV])*12 | Annual rate of the loan with the specified arguments (0.09241767 or 9.24%)

Note The years of the loan is multiplied by 12 to get the number of months.


SLN

Returns the straight-line depreciation of an asset for one period.

Syntax

SLN(cost,salvage,life)

Cost is the initial cost of the asset.

Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).

Life is the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

Example

Cost | Salvage | Life | Formula | Description (Result)
30,000 | 7,500 | 10 | =SLN([Cost], [Salvage], [Life]) | The depreciation allowance for each year (2,250)


SYD

Returns the sum-of-years' digits depreciation of an asset for a specified period.

Syntax

SYD(cost,salvage,life,per)

Cost is the initial cost of the asset.

Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).

Life is the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

Per is the period and must use the same units as life.

Remark

  • SYD is calculated as follows:

Example

Cost | Salvage | Life | Formula | Description (Result)
30000 | 7500 | 10 | =SYD([Cost],[Salvage],[Life],1) | Yearly depreciation allowance for the first year (4,090.91)
30000 | 7500 | 10 | =SYD([Cost],[Salvage],[Life],10) | Yearly depreciation allowance for the tenth year (409.09)


IS

This section describes the nine functions used for testing the type of a value or column reference.

Each of these functions, referred to collectively as the IS functions, checks the type of value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if value is a column reference that is empty; otherwise it returns FALSE.

Syntax

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNULL(value)
ISNUMBER(value)
ISTEXT(value)

Value is the value you want tested. Value can be a blank, an error, logical, text, number, or column reference.

Function | Returns TRUE if
ISBLANK | Value refers to an empty column reference.
ISERR | Value refers to any error value except #N/A.
ISERROR | Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL | Value refers to a logical value.
ISNA | Value refers to the #N/A (value not available) error value.
ISNONTEXT | Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank column reference.)
ISNULL | Value refers to the #NULL! value.
ISNUMBER | Value refers to a number.
ISTEXT | Value refers to text.

Remarks
  • The value arguments of the IS functions are not converted. For example, in most other functions where a number is required, the text value "19" is converted to the number 19. However, in the formula ISNUMBER("19"), "19" is not converted from a text value, and the ISNUMBER function returns FALSE.
  • The IS functions are useful in formulas for testing the outcome of a calculation. When combined with the IF function, they provide a method for locating errors in formulas (see the following examples).

Example 1

Formula | Description (Result)
=ISLOGICAL(TRUE) | Checks whether TRUE is a logical value (TRUE)
=ISLOGICAL("TRUE") | Checks whether "TRUE" is a logical value (FALSE)
=ISNUMBER(4) | Checks whether 4 is a number (TRUE)

Example 2

Col1 | Formula | Description (Result)
Gold | =ISBLANK([Col1]) | Checks whether the value in Col1 is blank (FALSE)
#REF! | =ISERROR([Col1]) | Checks whether #the value in Col1 is an error (TRUE)
#REF! | =ISNA([Col1]) | Checks whether the value in Col1 is the #N/A error (FALSE)
#N/A | =ISNA([Col1]) | Checks whether the value in Col1is the #N/A error (TRUE)
#N/A | =ISERR([Col1]) | Checks whether the value in Col1 is an error (FALSE)
#NULL! | =ISNULL([Col1]) | Checks whether the value in Col 1 is #NULL! (Yes)
330.92 | =ISNUMBER([Col1]) | Checks whether the value in Col1 is a number (TRUE)
Region1 | =ISTEXT([Col1]) | Checks whether the value in Col1 is text (TRUE)


Me

Returns the current user name.

Syntax

Me

Remarks

  • The Me function can only be used with default values; you cannot use the Me function in a calculated column.


AND

Returns the logical value TRUE or Yes if all its arguments evaluate to TRUE; returns FALSE or No if one or more arguments evaluate to FALSE.

Syntax

AND(logical1,logical2,...)

Logical1, logical2, ... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

Remarks

  • The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be column references that contain logical values.
  • If a column reference argument contains text, AND returns the #VALUE! error value; if it is empty, it returns FALSE.
  • If the arguments contain no logical values, AND returns the #VALUE! error value.
  • If one or more arguments contain #NULL!, AND returns #NULL!.

Example 1

Formula | Description (Result)
=AND(TRUE, TRUE) | All arguments are TRUE (TRUE)
=AND(TRUE, FALSE) | One argument is FALSE (FALSE)
=AND(2+2=4, 2+3=5) | All arguments evaluate to TRUE (TRUE)
=AND(TRUE, #NULL!) | First argument evaluates to TRUE and one argument contains #NULL! (#NULL!)

Example 2

Col1 | Col2 | Formula | Description (Result)
50 | 104 | =AND(1<[Col1], [Col1]<100) | Because 50 is between 1 and 100 (TRUE)
50 | 104 | =IF(AND(1<[Col2], [Col2]<100), [Col2], "The value is out of range.") | Displays the second number, if it is between 1 and 100, otherwise displays a message (The value is out of range.)
50 | 104 | =IF(AND(1<[Col1], [Col1]<100), [Col1], "The value is out of range.") | Displays the first number, if it is between 1 and 100, otherwise displays a message (50)


CHOOSE

Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 29 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

Syntax

CHOOSE(index_num,value1,value2,...)

Index_num specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or column reference containing a number between 1 and 29.

  • If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.

  • If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.

  • If index_num is a fraction, it is truncated to the lowest integer before being used.

Value1,value2,... are 1 to 29 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, column references, formulas, functions, or text.

Remarks

Example 1

Col1 | Col2 | Col3 | Col4 | Formula | Description (Result)
1st | 2nd | 3rd | Finished | =CHOOSE(2,[Col1],[Col2],[Col3],[Col4]) | Value from the second argument Col2 (2nd)
Nails | Screws | Nuts | Bolts | =CHOOSE(4,[Col1],[Col2],[Col3],[Col4]) | Value from the fourth argument Col4 (Bolts)

Example 2

Col1 | Col2 | Col3 | Col4 | Formula | Description (Result)
23 | 45 | 12 | 10 | =SUM([Col1], CHOOSE(2,[Col2],[Col3],[Col4])) | Sums the two arguments Col1 and the result of the Choose function (35)


FALSE

Returns the logical value FALSE.

Syntax

FALSE( )

Remark

You can also type the word FALSE directly into the formula, and it is interpreted as the logical value FALSE.


IF

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, [Quarter1]=100 is a logical expression; if the value in one row of the column, [Quarter1], is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Remarks

  • Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples.
  • When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
  • If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.

Example 1

Col1 | Col2 | Col3 | Expense | Formula | Description (Result)
 |  |  | 50 | =IF([Expense]<=100,"Within budget","Over budget") | If the number is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget". (Within budget)
23 | 45 | 89 | 50 | =IF([Expense]=100,SUM([Col1],[Col2],[Col3]),"") | If the number is 100, then the three values are added. Otherwise, empty text ("") is returned. ()

Example 2

ActualExpenses | PredictedExpenses | Formula | Description (Result)
1500 | 900 | =IF([ActualExpenses]>[PredictedExpenses],"Over Budget","OK") | Checks whether the first row is over budget (Over Budget)
500 | 900 | =IF([ActualExpenses]>[PredictedExpenses],"Over Budget","OK") | Checks whether the second row is over budget (OK)

Example 3

Score | Formula | Description (Result)
45 | =IF([Score]>89,"A",IF([Score]>79,"B", IF([Score]>69,"C",IF([Score]>59,"D","F")))) | Assigns a letter grade to the first score (F)
90 | =IF([Score]>89,"A",IF([Score]>79,"B", IF([Score]>69,"C",IF([Score]>59,"D","F")))) | Assigns a letter grade to the second score (A)
78 | =IF([Score]>89,"A",IF([Score]>79,"B", IF([Score]>69,"C",IF([Score]>59,"D","F")))) | Assigns a letter grade to the third score (C)

In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test ([Score]>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

The letter grades are assigned to numbers using the following key (assuming use of integers only).

If Score is | Then return
Greater than 89 | A
From 80 to 89 | B
From 70 to 79 | C
From 60 to 69 | D
Less than 60 | F


NOT

Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

Syntax

NOT(logical)

Logical is a value or expression that can be evaluated to TRUE or FALSE.

Remark

If logical is FALSE, NOT returns TRUE; if logical is TRUE, NOT returns FALSE.

Example

Formula | Description (Result)
=NOT(FALSE) | Reverses FALSE (TRUE)
=NOT(1+1=2) | Reverses an equation that evaluates to TRUE (FALSE)


OR

Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

Syntax

OR(logical1,logical2,...)

Logical1, logical2, ... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

Remarks

  • The arguments must evaluate to logical values such as TRUE or FALSE, or be arrays or references that contain logical values.
  • If a column reference argument contains text or is empty, those values are ignored.
  • If the specified range contains no logical values, OR returns the #VALUE! error value.
  • • If one or more arguments contain #NULL!, OR returns #NULL!.

Example

Formula | Description (Result)
=OR(TRUE) | One argument is TRUE (TRUE)
=OR(1+1=1,2+2=5) | All arguments evaluate to FALSE (FALSE)
=OR(TRUE,FALSE,TRUE) | At least one argument is TRUE (TRUE)
=OR(#NULL!,#NULL!) | Both arguments contain #NULL! (#NULL!)
=OR(1+1=1,#NULL!) | First argument evaluates to FALSE and #NULL! is the other argument (#NULL!)


TRUE

Returns the logical value TRUE.

Syntax

TRUE( )

Remark

You can enter the value TRUE directly without using this function. The TRUE function is provided primarily for compatibility with other programs.


ABS

Returns the absolute value of a number. The absolute value of a number is the number without its sign.

Syntax

ABS(number)

Number is the real number of which you want the absolute value.

Examples

Col1 | Formula | Description (Result)
-4 | =ABS([Col1]) | Absolute value of -4 (4)


ACOS

Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi.

Syntax

ACOS(number)

Number is the cosine of the angle you want and must be from -1 to 1.

Remark

If you want to convert the result from radians to degrees, multiply it by 180/PI() or use the DEGREES function.

Example

Formula | Description (Result)
=ACOS(-0.5) | Arccosine of -0.5 in radians, 2*pi/3 (2.094395)
=ACOS(-0.5)*180/PI() | Arccosine of -0.5 in degrees (120)
=DEGREES(ACOS(-0.5)) | Arccosine of -0.5 in degrees (120)


ACOSH

Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number.

Syntax

ACOSH(number)

Number is any real number equal to or greater than 1.

Example

Formula | Description (Result)
=ACOSH(1) | Inverse hyperbolic cosine of 1 (0)
=ACOSH(10) | Inverse hyperbolic cosine of 10 (2.993223)


ASIN

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2.

Syntax

ASIN(number)

Number is the sine of the angle you want and must be from -1 to 1.

Remark

To express the arcsine in degrees, multiply the result by 180/PI() or use the DEGREES function.

Formula | Description (Result)
=ASIN(-0.5) | Arcsine of -0.5 in radians, -pi/6 (-0.5236)
=ASIN(-0.5)*180/PI() | Arcsine of -0.5 in degrees (-30)
=DEGREES(ASIN(-0.5)) | Arcsine of -0.5 in degrees (-30)


ASINH

Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number, so ASINH(SINH(number)) equals number.

Syntax

ASINH(number)

Number is any real number.

Example

Formula | Description (Result)
=ASINH(-2.5) | Inverse hyperbolic sine of -2.5 (-1.64723)
=ASINH(10) | Inverse hyperbolic sine of 10 (2.998223)


ATAN

Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2.

Syntax

ATAN (number)

Number is the tangent of the angle you want.

Remark

To express the arctangent in degrees, multiply the result by 180/PI() or use the DEGREES function.

Example

Formula | Description (Result)
=ATAN(1) | Arctangent of 1 in radians, pi/4 (0.785398)
=ATAN(1)*180/PI() | Arctangent of 1 in degrees (45)
=DEGREES(ATAN(1)) | Arctangent of 1 in degrees (45)


ATAN2

Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi.

Syntax

ATAN2(x_num,y_num)

X_num is the x-coordinate of the point.

Y_num is the y-coordinate of the point.

Remarks

  • A positive result represents a counterclockwise angle from the x-axis; a negative result represents a clockwise angle.
  • ATAN2(a,b) equals ATAN(b/a), except that a can equal 0 in ATAN2.
  • If both x_num and y_num are 0, ATAN2 returns the #DIV/0! error value.
  • To express the arctangent in degrees, multiply the result by 180/PI( ) or use the DEGREES function.

Example

Formula | Description (Result)
=ATAN2(1, 1) | Arctangent of the point 1,1 in radians, pi/4 (0.785398)
=ATAN2(-1, -1) | Arctangent of the point -1,-1 in radians, -3*pi/4 (-2.35619)
=ATAN2(-1, -1)*180/PI() | Arctangent of the point 1,1 in degrees (-135)
=DEGREES(ATAN2(-1, -1)) | Arctangent of the point 1,1 in degrees (-135)


ATANH

Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number; ATANH(TANH(number)) equals number.

Syntax

ATANH(number)

Number is any real number between 1 and -1.

Example

Formula | Description (Result)
=ATANH(0.76159416) | Inverse hyperbolic tangent of 0.76159416 (1, approximately)
=ATANH(-0.1) | Inverse hyperbolic tangent of -0.1 (-0.10034)


CEILING

Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

Syntax

CEILING(number,significance)

Number is the value you want to round.

Significance is the multiple to which you want to round.

Remarks

  • If either argument is nonnumeric, CEILING returns the #VALUE! error value.
  • Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
  • If number and significance have different signs, CEILING returns the #NUM! error value.

Example

Formula | Description (Result)
=CEILING(2.5, 1) | Rounds 2.5 up to nearest multiple of 1 (3)
=CEILING(-2.5, -2) | Rounds -2.5 up to nearest multiple of -2 (-4)
=CEILING(-2.5, 2) | Returns an error, because -2.5 and 2 have different signs (#NUM!)
=CEILING(1.5, 0.1) | Rounds 1.5 up to the nearest multiple of 0.1 (1.5)
=CEILING(0.234, 0.01) | Rounds 0.234 up to the nearest multiple of 0.01 (0.24)


COS

Returns the cosine of the given angle.

Syntax

COS(number)

Number is the angle in radians for which you want the cosine.

Remark

If the angle is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.

Example

Formula | Description (Result)
=COS(1.047) | Cosine of 1.047 radians (0.500171)
=COS(60*PI()/180) | Cosine of 60 degrees (0.5)
=COS(RADIANS(60)) | Cosine of 60 degrees (0.5)


COSH

Returns the hyperbolic cosine of a number.

Syntax

COSH(number)

Number is any real number for which you want to find the hyperbolic cosine.

Remark

The formula for the hyperbolic cosine is:

Example

Formula | Description (Result)
=COSH(4) | Hyperbolic cosine of 4 (27.30823)
=COSH(EXP(1)) | Hyperbolic cosine of the base of the natural logarithm (7.610125)


DEGREES

Converts radians into degrees.

Syntax

DEGREES(angle)

Angle is the angle in radians that you want to convert.

Example

Formula | Description (Result)
=DEGREES(PI()) | Degrees of pi radians (180)


EVEN

Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts rows of one or two items. The crate is full when the number of items, rounded up to the nearest two, matches the crate's capacity.

Syntax

EVEN(number)

Number is the value to round.

Remarks

  • If number is nonnumeric, EVEN returns the #VALUE! error value.
  • Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an even integer, no rounding occurs.

Example

Formula | Description (Result)
=EVEN(1.5) | Rounds 1.5 up to the nearest even integer (2)
=EVEN(3) | Rounds 3 up to the nearest even integer (4)
=EVEN(2) | Rounds 2 up to the nearest even integer (2)
=EVEN(-1) | Rounds -1 up to the nearest even integer (-2)


EXP

Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.

Syntax

EXP(number)

Number is the exponent applied to the base e.

Remarks

  • To calculate powers of other bases, use the exponentiation operator (^).
  • EXP is the inverse of LN, the natural logarithm of number.

Example

Formula | Description (Result)
=EXP(1) | Approximate value of e (2.718282)
=EXP(2) | Base of the natural logarithm e raised to the power of 2 (7.389056)


FACT

Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.

Syntax

FACT(number)

Number is the nonnegative number you want the factorial of. If number is not an integer, it is truncated.

Example

Formula | Description (Result)
=FACT(5) | Factorial of 5, or 1*2*3*4*5 (120)
=FACT(1.9) | Factorial of the integer of 1.9 (1)
=FACT(0) | Factorial of 0 (1)
=FACT(-1) | Negative numbers cause an error value (#NUM!)
=FACT(1) | Factorial of 1 (1)


FLOOR

Rounds number down, toward zero, to the nearest multiple of significance.

Syntax

FLOOR(number,significance)

Number is the numeric value you want to round.

Significance is the multiple to which you want to round.

Remarks

  • If either argument is nonnumeric, FLOOR returns the #VALUE! error value.
  • If number and significance have different signs, FLOOR returns the #NUM! error value.
  • Regardless of the sign of number, a value is rounded down when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

Example

Formula | Description (Result)
=FLOOR(2.5, 1) | Rounds 2.5 down to nearest multiple of 1 (2)
=FLOOR(-2.5, -2) | Rounds -2.5 down to nearest multiple of -2 (-2)
=FLOOR(-2.5, 2) | Returns an error, because -2.5 and 2 have different signs (#NUM!)
=FLOOR(1.5, 0.1) | Rounds 1.5 down to the nearest multiple of 0.1 (1.5)
=FLOOR(0.234, 0.01) | Rounds 0.234 down to the nearest multiple of 0.01 (0.23)


INT

Rounds a number down to the nearest integer.

Syntax

INT(number)

Number is the real number you want to round down to an integer.

Example 1

Formula | Description (Result)
=INT(8.9) | Rounds 8.9 down (8)
=INT(-8.9) | Rounds -8.9 down (-9)

Example 2

Col1 | Formula | Description (Result)
19.5 | =[Col1]-INT([Col1]) | Returns the decimal part of a positive real number in Col1 (0.5)


LN

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

Syntax

LN(number)

Number is the positive real number for which you want the natural logarithm.

Remark

LN is the inverse of the EXP function.

Example

Formula | Description (Result)
=LN(86) | Natural logarithm of 86 (4.454347)
=LN(2.7182818) | Natural logarithm of the value of the constant e (1)
=LN(EXP(3)) | Natural logarithm of e raised to the power of 3 (3)


LOG

Returns the logarithm of a number to the base you specify.

Syntax

LOG(number,base)

Number is the positive real number for which you want the logarithm.

Base is the base of the logarithm. If base is omitted, it is assumed to be 10.

Example

Formula | Description (Result)
=LOG(10) | Logarithm of 10 (1)
=LOG(8, 2) | Logarithm of 8 with base 2 (3)
=LOG(86, 2.7182818) | Logarithm of 86 with base e (4.454347)


LOG10

Returns the base-10 logarithm of a number.

Syntax

LOG10(number)

Number is the positive real number for which you want the base-10 logarithm.

Example

Formula | Description (Result)
=LOG10(86) | Base-10 logarithm of 86 (1.934498451)
=LOG10(10) | Base-10 logarithm of 10 (1)
=LOG10(1E5) | Base-10 logarithm of 1E5 (5)
=LOG10(10^5) | Base-10 logarithm of 10^5 (5)


MOD

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Syntax

MOD(number,divisor)

Number is the number for which you want to find the remainder.

Divisor is the number by which you want to divide number.

Remarks

  • If divisor is 0, MOD returns the #DIV/0! error value.

  • The MOD function can be expressed in terms of the INT function:

    MOD(n, d) = n - d*INT(n/d)

Example

Formula | Description (Result)
=MOD(3, 2) | Remainder of 3/2. (1)
=MOD(-3, 2) | Remainder of -3/2. The sign is the same as divisor. (1)
=MOD(3, -2) | Remainder of 3/-2. The sign is the same as divisor. (-1)
=MOD(-3, -2) | Remainder of -3/-2. The sign is the same as divisor. (-1)


ODD

Returns number rounded up to the nearest odd integer.

Syntax

ODD(number)

Number is the value to round.

Remarks

  • If number is nonnumeric, ODD returns the #VALUE! error value.
  • Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an odd integer, no rounding occurs.

Example

Formula | Description (Result)
=ODD(1.5) | Rounds 1.5 up to the nearest odd integer (3)
=ODD(3) | Rounds 3 up to the nearest odd integer (3)
=ODD(2) | Rounds 2 up to the nearest odd integer (3)
=ODD(-1) | Rounds -1 up to the nearest odd integer (-1)
=ODD(-2) | Rounds -2 up to the nearest odd integer (-3)


PI

Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.

Syntax

PI( )

Example

Formula | Description (Result)
=PI() | Pi (3.14159265358979)
=PI()/2 | Pi/2 (1.570796327)
=PI()*(3^2) | Area of a circle, with the radius (28.27433388)


POWER

Returns the result of a number raised to a power.

Syntax

POWER(number,power)

Number is the base number. It can be any real number.

Power is the exponent to which the base number is raised.

Remark

The "^" operator can be used instead of POWER to indicate to what power the base number is to be raised, such as in 5^2.

Example

Formula | Description (Result)
=POWER(5,2) | 5 squared (25)
=POWER(98.6,3.2) | 98.6 raised to the power of 3.2 (2401077)
=POWER(4,5/4) | 4 raised to the power of 5/4 (5.656854)


PRODUCT

Multiplies all the numbers given as arguments and returns the product.

Syntax

PRODUCT(number1,number2,...)

Number1, number2, ... are 1 to 30 numbers that you want to multiply.

Remarks

  • Arguments that are numbers, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers cause errors.
  • If an argument is a column reference, only numbers are counted. Empty arguments, logical values, text, or error values are ignored.

Example

Col1 | Col2 | Col3 | Formula | Description (Result)
5 | 15 | 30 | =PRODUCT([Col1], [Col2], [Col3]) | Multiplies the numbers (2250)
5 | 15 | 30 | =PRODUCT([Col1], [Col2], [Col3],2) | Multiplies the numbers and 2 (4500)


RADIANS

Converts degrees to radians.

Syntax

RADIANS(angle)

Angle is an angle in degrees that you want to convert.

Example

Formula | Description (Result)
=RADIANS(270) | 270 degrees as radians (4.712389 or 3π/2 radians)


ROMAN

Converts an arabic numeral to roman, as text.

Syntax

ROMAN(number,form)

Number is the arabic numeral you want converted.

Form is a number specifying the type of roman numeral you want. The roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases. See the example following

ROMAN(499,0)
below.

Form | Type
0 or omitted | Classic.
1 | More concise. See example below.
2 | More concise. See example below.
3 | More concise. See example below.
4 | Simplified.
TRUE | Classic.
FALSE | Simplified.

Remarks

  • If number is negative, the #VALUE! error value is returned.
  • If number is greater than 3999, the #VALUE! error value is returned.

Example

Formula | Description (Result)
=ROMAN(499,0) | Classic roman numeral style for 499 (CDXCIX)
=ROMAN(499,1) | More concise version for 499 (LDVLIV)
=ROMAN(499,2) | More concise version for 499 (XDIX)
=ROMAN(499,3) | More concise version for 499 (VDIV)
=ROMAN(499,4) | More concise version for 499 (ID)
=ROMAN(2013,0) | Classic roman numeral style for 2013 (MMXIII)


ROUND

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

Rounds a number to a specified number of digits.

Syntax

ROUND(number,num_digits)

Number is the number you want to round.

Num_digits specifies the number of digits to which you want to round the number.

Remarks

  • If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
  • If num_digits is 0, then number is rounded to the nearest integer.
  • If num_digits is less than 0, then number is rounded to the left of the decimal point.

Example

Formula | Description (Result)
=ROUND(2.15, 1) | Rounds 2.15 to one decimal place (2.2)
=ROUND(2.149, 1) | Rounds 2.149 to one decimal place (2.1)
=ROUND(-1.475, 2) | Rounds -1.475 to two decimal places (-1.48)
=ROUND(21.5, -1) | Rounds 21.5 to one decimal place to the left of the decimal point (20)


ROUNDDOWN

Rounds a number down, toward zero.

Syntax

ROUNDDOWN(number,num_digits)

Number is any real number that you want rounded down.

Num_digits is the number of digits to which you want to round the number.

Remarks

  • ROUNDDOWN behaves like ROUND, except that it always rounds a number down.
  • If num_digits is greater than 0 (zero), then number is rounded down to the specified number of decimal places.
  • If num_digits is 0, then number is rounded down to the nearest integer.
  • If num_digits is less than 0, then number is rounded down to the left of the decimal point.

Example

Formula | Description (Result)
=ROUNDDOWN(3.2, 0) | Rounds 3.2 down to zero decimal places (3)
=ROUNDDOWN(76.9,0) | Rounds 76.9 down to zero decimal places (76)
=ROUNDDOWN(3.14159, 3) | Rounds 3.14159 down to three decimal places (3.141)
=ROUNDDOWN(-3.14159, 1) | Rounds -3.14159 down to one decimal place (-3.1)
=ROUNDDOWN(31415.92654, -2) | Rounds 31415.92654 down to 2 decimal places to the left of the decimal (31400)


ROUNDUP

Rounds a number up, away from 0 (zero).

Syntax

ROUNDUP(number,num_digits)

Number is any real number that you want rounded up.

Num_digits is the number of digits to which you want to round the number.

Remarks

  • ROUNDUP behaves like ROUND, except that it always rounds a number up.

  • If num_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.

  • If num_digits is 0, then number is rounded up to the nearest integer.

  • If num_digits is less than 0, then number is rounded up to the left of the decimal point.

Example

Formula | Description (Result)
=ROUNDUP(3.2,0) | Rounds 3.2 up to zero decimal places (4)
=ROUNDUP(76.9,0) | Rounds 76.9 up to zero decimal places (77)
=ROUNDUP(3.14159,3) | Rounds 3.14159 up to three decimal places (3.142)
=ROUNDUP(-3.14159,1) | Rounds -3.14159 up to one decimal place (-3.2)
=ROUNDUP(31415.92654,-2) | Rounds 31415.92654 up to 2 decimal places to the left of the decimal (31500)


SIGN

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

Syntax

SIGN(number)

Number is any real number.

Example

Formula | Description (Result)
=SIGN(10) | Sign of a positive number (1)
=SIGN(4-4) | Sign of zero (0)
=SIGN(-0.00001) | Sign of a negative number (-1)


SIN

Returns the sine of the given angle.

Syntax

SIN(number)

Number is the angle in radians for which you want the sine.

Remark

If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.

Example

Formula | Description (Result)
=SIN(PI()) | Sine of pi radians (0, approximately)
=SIN(PI()/2) | Sine of pi/2 radians (1)
=SIN(30*PI()/180) | Sine of 30 degrees (0.5)
=SIN(RADIANS(30)) | Sine of 30 degrees (0.5)


SINH

Returns the hyperbolic sine of a number.

Syntax

SINH(number)

Number is any real number.

Remark

The formula for the hyperbolic sine is:

Example 1

Formula | Description (Result)
=SINH(1) | Hyperbolic sine of 1 (1.175201194)
=SINH(-1) | Hyperbolic sine of -1 (-1.175201194)

Example 2

You can use the hyperbolic sine function to approximate a cumulative probability distribution. Suppose a laboratory test value varies between 0 and 10 seconds. An empirical analysis of the collected history of experiments shows that the probability of obtaining a result, x, of less than t seconds is approximated by the following equation:

P(x<t) = 2.868 * SINH(0.0342 * t), where 0<t<10

To calculate the probability of obtaining a result of less than 1.03 seconds, substitute 1.03 for t.

Formula | Description (Result)
=2.868*SINH(0.0342*1.03) | Probability of obtaining a result of less than 1.03 seconds (0.101049063)

You can expect this result to occur about 101 times for every 1000 experiments.


SQRT

Returns a positive square root.

Syntax

SQRT(number)

Number is the number for which you want the square root.

Remark

If number is negative, SQRT returns the #NUM! error value.

Example

Formula | Description (Result)
=SQRT(16) | Square root of 16 (4)
=SQRT(-16) | Square root of 16. Because the number is negative, an error is returned (#NUM!)
=SQRT(ABS(-16)) | Square root of the absolute value of the number (4)


SUM

Adds all the numbers in the specified arguments.

Syntax

SUM(number1,number2,...)

Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

Remarks

  • Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.
  • Arguments cause errors if they are error values or text that cannot be translated into numbers.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Formula | Description (Result)
-5 | 15 | 30 |  | TRUE | =SUM(3, 2) | Adds 3 and 2 (5)
-5 | 15 | 30 |  | TRUE | =SUM("5", 15, TRUE) | Adds 5, 15 and 1, because the text values are translated into numbers, and the logical value TRUE is translated into the number 1 (21)
-5 | 15 | 30 |  | TRUE | =SUM(Col1], [Col2], [Col3]) | Adds the first three numbers in the columns (40)
-5 | 15 | 30 |  | TRUE | =SUM(Col1], [Col2], [Col3], 15) | Adds the first three numbers in the columns, and 15 (55)
-5 | 15 | 30 |  | TRUE | =SUM([Col4], [Col5], 2) | Adds the values in the last two columns and 2 (2)


SUMSQ

Returns the sum of the squares of the arguments.

Syntax

SUMSQ(number1,number2,...)

Number1, number2, ... are 1 to 30 arguments for which you want the sum of the squares.

Example

Formula | Description (Result)
=SUMSQ(3, 4) | Sum of the squares of 3 and 4 (25)


TAN

Returns the tangent of the given angle.

Syntax

TAN(number)

Number is the angle in radians for which you want the tangent.

Remark

If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.

Example

Formula | Description (Result)
=TAN(0.785) | Tangent of 0.785 radians (0.99920)
=TAN(45*PI()/180) | Tangent of 45 degrees (1)
=TAN(RADIANS(45)) | Tangent of 45 degrees (1)


TANH

Returns the hyperbolic tangent of a number.

Syntax

TANH(number)

Number is any real number.

Remark

The formula for the hyperbolic tangent is:

Example

Formula | Description (Result)
=TANH(-2) | Hyperbolic tangent of -2 (-0.96403)
=TANH(0) | Hyperbolic tangent of 0 (0)
=TANH(0.5) | Hyperbolic tangent of 0.5 (0.462117)


TRUNC

Truncates a number to an integer by removing the fractional part of the number.

Syntax

TRUNC(number,num_digits)

Number is the number you want to truncate.

Num_digits is a number specifying the precision of the truncation. The default value for num_digits is 0 (zero).

Remark

TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative numbers:

TRUNC(-4.3)
returns -4, but
INT(-4.3)
returns -5 because -5 is the lower number.

Example

Formula | Description (Result)
=TRUNC(8.9) | Integer part of 8.9 (8)
=TRUNC(-8.9) | Integer part of -8.9 (-8)
=TRUNC(PI()) | Integer part of pi (3)


AVEDEV

Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set.

Syntax

AVEDEV(number1,number2, ...)

Number1, number2, ... are 1 to 30 arguments for which you want the average of the absolute deviations.

Remarks

  • The arguments must be either numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty, those values are either ignored or return the #VALUE! error value; however, the value zero is included.
  • The equation for average deviation is:

AVEDEV is influenced by the unit of measurement in the input data.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Formula | Description (Result)
4 | 5 | 6 | 7 | 5 | 4 | 3 | =AVEDEV([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) | Average of the absolute deviations of the numbers from their mean (1.020408)


AVERAGE

Returns the average (arithmetic mean) of the arguments.

Syntax

AVERAGE(number1,number2, ...)

Number1, number2, ... are 1 to 30 numeric arguments for which you want the average.

Remarks

  • The arguments must be either numbers or references that contain numbers.
  • If an array or column reference argument contains text, logical values, or is empty, those values are either ignored or return the #VALUE! error value; however, arguments with the value zero are included.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Formula | Description (Result)
10 | 7 | 9 | 27 | 2 | =AVERAGE([Col1], [Col2], [Col3], [Col4], [Col5]) | Average of the column numbers (11)
10 | 7 | 9 | 27 | 2 | =AVERAGE([Col1], [Col2], [Col3], [Col4], [Col5], 5) | Average of the column numbers and 5 (10)


AVERAGEA

Calculates the average (arithmetic mean) of the values in the list of arguments. Unlike the AVERAGE functions, text and logical values such as TRUE and FALSE are included in the calculation.

Syntax

AVERAGEA(value1,value2, ...)

Value1, value2,... are 1 to 30 arguments for which you want the average.

Remarks

  • Arguments that contain text evaluate as 0 (zero). Empty text ("") evaluates as 0 (zero). If the calculation must not include text values in the average, use the AVERAGE function.
  • Arguments that contain TRUE evaluate as 1; arguments that contain FALSE evaluate as 0 (zero).

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Formula | Description (Result)
10 | 7 | 9 | 2 | 0 |  | =AVERAGEA([Col1],[Col2],[Col3],[Col4],[Col5]) | Average of the numbers (5.6)
10 | 10 | 9 | 2 | 1 |  | =AVERAGEA([Col1],[Col2],[Col3],[Col4],[Col6]) | Average of the numbers, and the empty argument (7.75)


BETADIST

Returns the cumulative beta probability distribution function. The cumulative beta probability distribution function is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television.

Syntax

BETADIST(x,alpha,beta,A,B)

X is the value between A and B at which to evaluate the function.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution.

A is an optional lower bound to the interval of x.

B is an optional upper bound to the interval of x.

Remarks

  • If any argument is nonnumeric, BETADIST returns the #VALUE! error value.
  • If alpha ≤ 0 or beta ≤ 0, BETADIST returns the #NUM! error value.
  • If x < A, x > B, or A = B, BETADIST returns the #NUM! error value.
  • If you omit values for A and B, BETADIST uses the standard cumulative beta distribution, so that A = 0 and B = 1.

Example

X | Alpha | Beta | A | B | Formula | Description (Result)
2 | 8 | 10 | 1 | 3 | =BETADIST([X],[Alpha],[Beta],[A],[B]) | Cumulative beta probability density function, for the parameters (0.685470581)


BETAINV

Returns the inverse of the cumulative beta probability distribution function. That is, if probability = BETADIST(x,...), then BETAINV(probability,...) = x. The cumulative beta distribution can be used in project planning to model probable completion times given an expected completion time and variability.

Syntax

BETAINV(probability,alpha,beta,A,B)

Probability is a probability associated with the beta distribution.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution.

A is an optional lower bound to the interval of x.

B is an optional upper bound to the interval of x.

Remarks

  • If any argument is nonnumeric, BETAINV returns the #VALUE! error value.
  • If alpha ≤ 0 or beta ≤ 0, BETAINV returns the #NUM! error value.
  • If probability ≤ 0 or probability > 1, BETAINV returns the #NUM! error value.
  • If you omit values for A and B, BETAINV uses the standard cumulative beta distribution, so that A = 0 and B = 1.

BETAINV uses an iterative technique for calculating the function. Given a probability value, BETAINV iterates until the result is accurate to within ±3x10-7. If BETAINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Probability | Alpha | Beta | A | B | Formula | Description (Result)
0.685470581 | 8 | 10 | 1 | 3 | =BETAINV([Probability],[Alpha],[Beta],[A],[B]) | Inverse of the cumulative beta probability density function for the parameters (2)


BINOMDIST

Returns the individual term binomial distribution probability. Use BINOMDIST in problems with a fixed number of tests or trials, when the outcomes of any trial are only success or failure, when trials are independent, and when the probability of success is constant throughout the experiment. For example, BINOMDIST can calculate the probability that two of the next three babies born are male.

Syntax

BINOMDIST(number_s,trials,probability_s,cumulative)

Number_s is the number of successes in trials.

Trials is the number of independent trials.

Probability_s is the probability of success on each trial.

Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, then BINOMDIST returns the cumulative distribution function, which is the probability that there are at most number_s successes; if FALSE, it returns the probability mass function, which is the probability that there are exactly number_s successes.

Remarks

  • Number_s and trials are truncated to integers.
  • If number_s, trials, or probability_s is nonnumeric, BINOMDIST returns the #VALUE! error value.
  • If number_s < 0 or number_s > trials, BINOMDIST returns the #NUM! error value.
  • If probability_s < 0 or probability_s > 1, BINOMDIST returns the #NUM! error value.
  • The binomial probability mass function is:

    where:

    is COMBIN(n,x).

    Note The COMBIN function is used here to illustrate the mathematical formula used by the BINOMDIST function. It is not a function that you can use in a list.

    The cumulative binomial distribution is:

Example

number_s | trials | probability_s | Formula | Description (Result)
6 | 10 | 0.5 | =BINOMDIST([number_s],[trials],[probability_s],FALSE) | Probability of exactly 6 of 10 trials being successful (0.205078)


CHIDIST

Returns the one-tailed probability of the chi-squared distribution. The γ2 distribution is associated with a γ2 test. Use the γ2 test to compare observed and expected values. For example, a genetic experiment might hypothesize that the next generation of plants will exhibit a certain set of colors. By comparing the observed results with the expected ones, you can decide whether your original hypothesis is valid.

Syntax

CHIDIST(x,degrees_freedom)

X is the value at which you want to evaluate the distribution.

Degrees_freedom is the number of degrees of freedom.

Remarks

  • If either argument is nonnumeric, CHIDIST returns the #VALUE! error value.
  • If x is negative, CHIDIST returns the #NUM! error value.
  • If degrees_freedom is not an integer, it is truncated.
  • If degrees_freedom < 1 or degrees_freedom ≥ 10^10, CHIDIST returns the #NUM! error value.
  • CHIDIST is calculated as CHIDIST = P(X>x), where X is a γ2 random variable.

Example

X | DegreesFreedom | Formula | Description (Result)
18.307 | 10 | =CHIDIST([X],[DegreesFreedom]) | One-tailed probability of the chi-squared distribution, for the specified arguments (0.050001)


CHIINV

Returns the inverse of the one-tailed probability of the chi-squared distribution. If probability = CHIDIST(x,...), then CHIINV(probability,...) = x. Use this function to compare observed results with expected ones to decide whether your original hypothesis is valid.

Syntax

CHIINV(probability,degrees_freedom)

Probability is a probability associated with the chi-squared distribution.

Degrees_freedom is the number of degrees of freedom.

Remarks

  • If either argument is nonnumeric, CHIINV returns the #VALUE! error value.
  • If probability < 0 or probability > 1, CHIINV returns the #NUM! error value.
  • If degrees_freedom is not an integer, it is truncated.
  • If degrees_freedom < 1 or degrees_freedom ≥ 10^10, CHIINV returns the #NUM! error value.

CHIINV uses an iterative technique for calculating the function. Given a probability value, CHIINV iterates until the result is accurate to within ± 3x10^-7. If CHIINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Probability | DegreesFreedom | Formula | Description (Result)
0.05 | 10 | =CHIINV([Probability],[DegreesFreedom]) | Inverse of the one-tailed probability of the chi-squared distribution (18.30703)


CONFIDENCE

Returns the confidence interval for a population mean with a normal distribution. The confidence interval is a range on either side of a sample mean. For example, if you order a product through the mail, you can determine, with a particular level of confidence, the earliest and latest the product will arrive.

Syntax

CONFIDENCE(alpha,standard_dev,size)

Alpha is the significance level used to compute the confidence level. The confidence level equals 100*(1 - alpha)%, or in other words, an alpha of 0.05 indicates a 95 percent confidence level.

Standard_dev is the population standard deviation for the data range and is assumed to be known.

Size is the sample size.

Remarks

  • If any argument is nonnumeric, CONFIDENCE returns the #VALUE! error value.
  • If alpha ≤ 0 or alpha ≥ 1, CONFIDENCE returns the #NUM! error value.
  • If standard_dev ≤ 0, CONFIDENCE returns the #NUM! error value.
  • If size is not an integer, it is truncated.
  • If size < 1, CONFIDENCE returns the #NUM! error value.
  • If we assume alpha equals 0.05, we need to calculate the area under the standard normal curve that equals (1 - alpha), or 95 percent. This value is ± 1.96. The confidence interval is therefore:

Example

Suppose we observe that, in our sample of 50 commuters, the average length of travel to work is 30 minutes with a population standard deviation of 2.5. We can be 95 percent confident that the population mean is in the interval:

Alpha | StdDev | Size | Formula | Description (Result)
0.05 | .5 | 50 | =CONFIDENCE([Alpha],[StdDev],[Size]) | Confidence interval for a population mean. In other words, the average length of travel to work equals 30 ± 0.692951 minutes, or 29.3 to 30.7 minutes. (0.692951)


COUNT

Counts the number of arguments that contain numbers.

Syntax

COUNT(value1,value2, ...)

Value1, value2, ... are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Remarks

  • Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
  • If you need to count logical values, text, or error values, use the COUNTA function.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Formula | Description (Result)
Sales | 12/8/2008 |  | 19 | 22.24 | TRUE | #DIV/0! | =COUNT([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) | Counts the number of arguments that contain numbers in the list (3)
 |  |  | 19 | 22.24 | TRUE | #DIV/0! | =COUNT([Col4],[Col5],[Col6],[Col7]) | Counts the number of arguments that contain numbers in the last 4 rows of the list (2)
Sales | 2/8/2008 |  | 19 | 22.24 | TRUE | #DIV/0! | COUNT([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7],2) | Counts the number of arguments that contain numbers in the list, and the value 2 (4)


COUNTA

Counts the number of arguments that are not empty.

Syntax

COUNTA(value1,value2, ...)

Value1, value2, ... are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text ("") but not including empty arguments. If you do not need to count logical values, text, or error values, use the COUNT function.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Formula | Description (Result)
Sales | 12/8/2008 |  | 19 | 22.24 | TRUE | #DIV/0! | =COUNTA([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) | Counts the number of nonblank arguments in the list (6)
Sales | 12/8/2008 |  | 19 | 22.24 | TRUE | #DIV/0! | =COUNTA([Col4],[Col5],[Col6],[Col7])) | Counts the number of nonblank arguments in the last 4 rows of the list (4)
Sales | 12/8/2008 |  | 19 | 22.24 | TRUE | #DIV/0! | =COUNTA([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7],2) | Counts the number of nonblank arguments in the list and the value 2 (7)
Sales | 12/8/2008 |  | 19 | 22.24 | TRUE | #DIV/0! | =COUNTA([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7], "Two") | Counts the number of nonblank arguments in the list and the value "Two" (7)


CRITBINOM

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Use this function for quality assurance applications. For example, use CRITBINOM to determine the greatest number of defective parts that are allowed to come off an assembly line run without rejecting the entire lot.

Syntax

CRITBINOM(trials,probability_s,alpha)

Trials is the number of Bernoulli trials.

Probability_s is the probability of a success on each trial.

Alpha is the criterion value.

Remarks

  • If any argument is nonnumeric, CRITBINOM returns the #VALUE! error value.
  • If trials is not an integer, it is truncated.
  • If trials < 0, CRITBINOM returns the #NUM! error value.
  • If probability_s is < 0 or probability_s > 1, CRITBINOM returns the #NUM! error value.
  • If alpha < 0 or alpha > 1, CRITBINOM returns the #NUM! error value.

Example

Trials | Probability_s | Alpha | Formula | Description (Result)
6 | 0.5 | 0.75 | =CRITBINOM([Trials],[Probability_s],[Alpha]) | Smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value (4)


DEVSQ

Returns the sum of squares of deviations of data points from their sample mean.

Syntax

DEVSQ(number1,number2, ...)

Number1,number2, ... are 1 to 30 arguments for which you want to calculate the sum of squared deviations.

Remarks

  • The arguments must be numbers or column references that contain numbers.
  • If argument contains text, logical values, or is empty, those values are ignored; however, arguments with the value zero are included.
  • The equation for the sum of squared deviations is:

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Formula | Description (Result)
4 | 5 | 8 | 7 | 11 | 4 | 3 | =DEVSQ([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) | Sum of squares of deviations of data from their sample mean (48)


EXPONDIST

Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use EXPONDIST to determine the probability that the process takes at most 1 minute.

Syntax

EXPONDIST(x,lambda,cumulative)

X is the value of the function.

Lambda is the parameter value.

Cumulative is a logical value that indicates which form of the exponential function to provide. If cumulative is TRUE, EXPONDIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Remarks

  • If x or lambda is nonnumeric, EXPONDIST returns the #VALUE! error value.
  • If x < 0, EXPONDIST returns the #NUM! error value.
  • If lambda ≤ 0, EXPONDIST returns the #NUM! error value.
  • The equation for the probability density function is:

  • The equation for the cumulative distribution function is:

Example 1

Formula | Description (Result)
=EXPONDIST(0.2,10,FALSE) | Probability exponential distribution function (1.353353)

Example 2

X | Lambda | Formula | Description (Result)
0.2 | 10 | =EXPONDIST([X,][Lambda],TRUE) | Cumulative exponential distribution function (0.864665)


FDIST

Returns the F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity. For example, you can examine test scores given to men and women entering high school and determine if the variability in the females is different from that found in the males.

Syntax

FDIST(x,degrees_freedom1,degrees_freedom2)

X is the value at which to evaluate the function.

Degrees_freedom1 is the numerator degrees of freedom.

Degrees_freedom2 is the denominator degrees of freedom.

Remarks

  • If any argument is nonnumeric, FDIST returns the #VALUE! error value.
  • If x is negative, FDIST returns the #NUM! error value.
  • If degrees_freedom1 or degrees_freedom2 is not an integer, it is truncated.
  • If degrees_freedom1 < 1 or degrees_freedom1 ≥ 10^10, FDIST returns the #NUM! error value.
  • If degrees_freedom2 < 1 or degrees_freedom2 ≥ 10^10, FDIST returns the #NUM! error value.
  • FDIST is calculated as FDIST=P( F<x ), where F is a random variable that has an F distribution.

Example

X | Degrees1 | Degrees2 | Formula | Description (Result)
15.20675 | 6 | 4 | =FDIST([X],[Degrees1],[Degrees2]) | F probability distribution for the specified arguments (0.01)


FINV

Returns the inverse of the F probability distribution. If p = FDIST(x,...), then FINV(p,...) = x.

The F distribution can be used in an F-test that compares the degree of variability in two data sets. For example, you can analyze income distributions in the United States and Canada to determine whether the two countries have a similar degree of diversity.

Syntax

FINV(probability,degrees_freedom1,degrees_freedom2)

Probability is a probability associated with the F cumulative distribution.

Degrees_freedom1 is the numerator degrees of freedom.

Degrees_freedom2 is the denominator degrees of freedom.

Remarks

  • If any argument is nonnumeric, FINV returns the #VALUE! error value.
  • If probability < 0 or probability > 1, FINV returns the #NUM! error value.
  • If degrees_freedom1 or degrees_freedom2 is not an integer, it is truncated.
  • If degrees_freedom1 < 1 or degrees_freedom1 ≥ 10^10, FINV returns the #NUM! error value.
  • If degrees_freedom2 < 1 or degrees_freedom2 ≥ 10^10, FINV returns the #NUM! error value.

FINV can be used to return critical values from the F distribution. For example, the output of an ANOVA calculation often includes data for the F statistic, F probability, and F critical value at the 0.05 significance level. To return the critical value of F, use the significance level as the probability argument to FINV.

FINV uses an iterative technique for calculating the function. Given a probability value, FINV iterates until the result is accurate to within ± 3x10^-7. If FINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Probability | Degrees1 | Degrees2 | Formula | Description (Result)
0.01 | 6 | 4 | =FINV([Probability],[Degrees1],[Degrees2]) | Inverse of the F probability distribution for the specified arguments (15.20675)


FISHER

Returns the Fisher transformation at x. This transformation produces a function that is approximately normally distributed rather than skewed. Use this function to perform hypothesis testing on the correlation coefficient.

Syntax

FISHER(x)

X is a numeric value for which you want the transformation.

Remarks

  • If x is nonnumeric, FISHER returns the #VALUE! error value.
  • If x ≤ -1 or if x ≥ 1, FISHER returns the #NUM! error value.

  • The equation for the Fisher transformation is:

Example

Formula | Description (Result)
=FISHER(0.75) | Fisher transformation at 0.75 (0.972955)


FISHERINV

Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then FISHERINV(y) = x.

Syntax

FISHERINV(y)

Y is the value for which you want to perform the inverse of the transformation.

Remarks

  • If y is nonnumeric, FISHERINV returns the #VALUE! error value.

  • The equation for the inverse of the Fisher transformation is:

Example

Formula | Description (Result)
=FISHERINV(0.972955) | Inverse of the Fisher transformation at 0.972955 (0.75)


GAMMADIST

Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis.

Syntax

GAMMADIST(x,alpha,beta,cumulative)

X is the value at which you want to evaluate the distribution.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution. If beta = 1, GAMMADIST returns the standard gamma distribution.

Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, GAMMADIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.

Remarks

  • If x, alpha, or beta is nonnumeric, GAMMADIST returns the #VALUE! error value.
  • If x < 0, GAMMADIST returns the #NUM! error value.
  • If alpha ≤ 0 or if beta ≤ 0, GAMMADIST returns the #NUM! error value.
  • The equation for the gamma distribution is:

    The standard gamma distribution is:

  • When alpha = 1, GAMMADIST returns the exponential distribution with:

  • For a positive integer n, when alpha = n/2, beta = 2, and cumulative = TRUE, GAMMADIST returns (1 - CHIDIST(x)) with n degrees of freedom.
  • When alpha is a positive integer, GAMMADIST is also known as the Erlang distribution.

Example

X | Alpha | Beta | Formula | Description (Result)
10 | 9 | 2 | =GAMMADIST([X],[Alpha],[Beta],FALSE) | Probability gamma distribution of the arguments (0.032639)
10 | 9 | 2 | =GAMMADIST([X],[Alpha],[Beta],TRUE) | Cumulative gamma distribution of the arguments (0.068094)


GAMMAINV

Returns the inverse of the gamma cumulative distribution. If p = GAMMADIST(x,...), then GAMMAINV(p,...) = x.

You can use this function to study a variable whose distribution may be skewed.

Syntax

GAMMAINV(probability,alpha,beta)

Probability is the probability associated with the gamma distribution.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution. If beta = 1, GAMMAINV returns the standard gamma distribution.

Remarks

  • If any argument is nonnumeric, GAMMAINV returns the #VALUE! error value.
  • If probability < 0 or probability > 1, GAMMAINV returns the #NUM! error value.
  • If alpha ≤ 0 or if beta ≤ 0, GAMMAINV returns the #NUM! error value.

GAMMAINV uses an iterative technique for calculating the function. Given a probability value, GAMMAINV iterates until the result is accurate to within ±3x10^-7. If GAMMAINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Probability | Alpha | Beta | Formula | Description (Result)
0.068094 | 9 | 2 | =GAMMAINV([Probability],[Alpha],[Beta]) | Inverse of the gamma cumulative distribution for the arguments (10)


GAMMALN

Returns the natural logarithm of the gamma function, Γ(x).

Syntax

GAMMALN(x)

X is the value for which you want to calculate GAMMALN.

Remarks

  • If x is nonnumeric, GAMMALN returns the #VALUE! error value.
  • If x ≤ 0, GAMMALN returns the #NUM! error value.
  • The number e raised to the GAMMALN(i) power, where i is an integer, returns the same result as (i - 1)!.
  • GAMMALN is calculated as follows:

    where:

Example

Formula | Description (Result)
=GAMMALN(4) | Natural logarithm of the gamma function at 4 (1.791759)


GEOMEAN

Returns the geometric mean of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.

Syntax

GEOMEAN(number1,number2, ...)

Number1,number2, ... are 1 to 30 arguments for which you want to calculate the mean.

Remarks

  • The arguments must be either numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty , those values are ignored; however, arguments with the value zero are included.
  • If any data point ≤ 0, GEOMEAN returns the #NUM! error value.
  • The equation for the geometric mean is:

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Formula | Description (Result)
4 | 5 | 8 | 7 | 11 | 4 | 3 | =GEOMEAN([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) | Geometric mean of the data (5.476987)


HARMEAN

Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.

Syntax

HARMEAN(number1,number2, ...)

Number1,number2, ... are 1 to 30 arguments for which you want to calculate the mean.

Remarks

  • The arguments must be either numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty, those values are ignored; however, arguments with the value zero are included.
  • If any data point ≤ 0, HARMEAN returns the #NUM! error value.
  • The harmonic mean is always less than the geometric mean, which is always less than the arithmetic mean.
  • The equation for the harmonic mean is:

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Formula | Description (Result)
4 | 5 | 8 | 7 | 11 | 4 | 3 | =HARMEAN([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7]) | Harmonic mean of the data (5.028376)


HYPGEOMDIST

Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HYPGEOMDIST for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood.

Syntax

HYPGEOMDIST(sample_s,number_sample,population_s,number_population)

Sample_s is the number of successes in the sample.

Number_sample is the size of the sample.

Population_s is the number of successes in the population.

Number_population is the population size.

Remarks

  • All arguments are truncated to integers.
  • If any argument is nonnumeric, HYPGEOMDIST returns the #VALUE! error value.
  • If sample_s < 0 or sample_s is greater than the lesser of number_sample or population_s, HYPGEOMDIST returns the #NUM! error value.
  • If sample_s is less than the larger of 0 or (number_sample - number_population + population_s), HYPGEOMDIST returns the #NUM! error value.
  • If number_sample < 0 or number_sample > number_population, HYPGEOMDIST returns the #NUM! error value.
  • If population_s < 0 or population_s > number_population, HYPGEOMDIST returns the #NUM! error value.
  • If number_population < 0, HYPGEOMDIST returns the #NUM! error value.
  • The equation for the hypergeometric distribution is:

    where:

    x = sample_s

    n = number_sample

    M = population_s

    N = number_population

    HYPGEOMDIST is used in sampling without replacement from a finite population.

Example

A sampler of chocolates contains 20 pieces. Eight pieces are caramels, and the remaining 12 are nuts. If a person selects 4 pieces at random, the following function returns the probability that exactly 1 piece is a caramel.

Sample_s | Number_sample | Population_s | Number_Population | Formula | Description (Result)
1 | 4 | 8 | 20 | =HYPGEOMDIST([Sample_s],[Number_sample],[Population_s],[Number_Population]) | Hypergeometric distribution for sample and population (0.363261)


KURT

Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.

Syntax

KURT(number1,number2, ...)

Number1,number2, ... are 1 to 30 arguments for which you want to calculate kurtosis.

Remarks

  • The arguments must be either numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty, those values are ignored; however, arguments with the value zero are included.
  • If there are fewer than four data points, or if the standard deviation of the sample equals zero, KURT returns the #DIV/0! error value.
  • Kurtosis is defined as:

    where:

    s is the sample standard deviation.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9 | Col10 | Formula | Description (Result)
3 | 4 | 5 | 2 | 3 | 4 | 5 | 6 | 4 | 7 | =KURT([Col1], [Col2], [Col3], [Col4], [Col5], [Col6],[Col7],[Col8],[Col9],[Col10]) | Kurtosis of the ata set (-0.1518)


LOGINV

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. If p = LOGNORMDIST(x,...) then LOGINV(p,...) = x.

Use the lognormal distribution to analyze logarithmically transformed data.

Syntax

LOGINV(probability,mean,standard_dev)

Probability is a probability associated with the lognormal distribution.

Mean is the mean of ln(x).

Standard_dev is the standard deviation of ln(x).

Remarks

  • If any argument is nonnumeric, LOGINV returns the #VALUE! error value.
  • If probability < 0 or probability > 1, LOGINV returns the #NUM! error value.
  • If standard_dev <= 0, LOGINV returns the #NUM! error value.

  • The inverse of the lognormal distribution function is:

Example

Probablility | Mean | StdDev | Formula | Description (Result)
0.039084 | 3.5 | 1.2 | =LOGINV([Probablility], [Mean], [StdDev]) | Inverse of the lognormal cumulative distribution function for the specified arguments (4.000028)


LOGNORMDIST

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with the parameters mean and standard_dev. Use this function to analyze data that has been logarithmically transformed.

Syntax

LOGNORMDIST(x,mean,standard_dev)

X is the value at which to evaluate the function.

Mean is the mean of ln(x).

Standard_dev is the standard deviation of ln(x).

Remarks

  • If any argument is nonnumeric, LOGNORMDIST returns the #VALUE! error value.
  • If x ≤ 0 or if standard_dev ≤ 0, LOGNORMDIST returns the #NUM! error value.
  • The equation for the lognormal cumulative distribution function is:

Example

X | Mean | StdDev | Formula | Description (Result)
4 | 3.5 | 1.2 | =LOGNORMDIST([X],[Mean],[StdDev]) | Cumulative lognormal distribution at 4 with the specified arguments (0.039084)


MAX

Returns the largest value in a set of values.

Syntax

MAX(number1,number2, ...)

Number1,number2, ... are 1 to 30 numbers for which you want to find the maximum value.

Remarks

  • You can specify arguments that are numbers, empty arguments, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.
  • If logical values and text must not be ignored, use MAXA instead.
  • If the arguments contain no numbers, MAX returns 0 (zero).

Example

Col1 | Col2 | Col3 | col4 | Col5 | Formula | Description (Result)
10 | 7 | 9 | 27 | 2 | =MAX([Col1],[Col2],[Col3],[Col4],[Col5]) | Largest of the numbers (27)
10 | 7 | 9 | 27 | 2 | =MAX(Col1],[Col2],[Col3],[Col4],[Col5],30) | Largest of the numbers and 30 (30)


MAXA

Returns the largest value in a list of arguments. Text and logical values such as TRUE and FALSE are compared as well as numbers.

MAXA is similar to MINA. For more information, see the examples for MINA.

Syntax

MAXA(value1,value2, ...)

Value1, Value2, ... are 1 to 30 values for which you want to find the largest value.

Remarks

  • You can specify arguments that are numbers, empty arguments, logical values, or text representations of numbers. Arguments that are error values cause errors. If the calculation must not include text or logical values, use MAX instead.
  • Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
  • If the arguments contain no values, MAXA returns 0 (zero).

Example

Col1 | Col2 | Col3 | Col4 | col5 | Formula | Description (Result)
0 | 0.2 | 0.5 | 0.4 | TRUE | =MAXA([Col1],[Col2],[Col3],[Col4],[Col5]) | Largest of the numbers. TRUE evaluates to 1 (1)


MEDIAN

Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.

Syntax

MEDIAN(number1,number2, ...)

Number1, number2, ... are 1 to 30 numbers for which you want the median.

Remarks

  • The arguments should be either numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty, those values are ignored; however, arguments with the value zero are included.

  • If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second formula following.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Formula | Description (Result)
1 | 2 | 3 | 4 | 5 | 6 | =MEDIAN([Col1],[Col2],[Col3],[Col4],[Col5]) | Median of the first 5 numbers in the list (3)
1 | 2 | 3 | 4 | 5 | 6 | =MEDIAN([Col1],[Col2],[Col3],[Col4],[Col5],[Col6]) | Median of all the numbers, or the average of 3 and 4 (3.5)


MIN

Returns the smallest number in a set of values.

Syntax

MIN(number1,number2, ...)

Number1, number2, ... are 1 to 30 numbers for which you want to find the minimum value.

Remarks

  • You can specify arguments that are numbers, empty, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.
  • If an argument is a column reference, only numbers are used. If logical values and text should not be ignored, use MINA instead.
  • If the arguments contain no numbers, MIN returns 0.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Formula | Description (Result)
10 | 7 | 9 | 27 | 2 | =MIN([Col1],[Col2],[Col3],[Col4],[Col5]) | Smallest of the numbers (2)
10 | 7 | 9 | 27 | 2 | =MIN([Col1],[Col2],[Col3],[Col4],[Col5],0) | Smallest of the numbers and 0 (0)


MINA

Returns the smallest value in the list of arguments. Text and logical values such as TRUE and FALSE are compared as well as numbers.

Syntax

MINA(value1,value2,...)

Value1, value2,... are 1 to 30 values for which you want to find the smallest value.

Remarks

  • You can specify arguments that are numbers, empty , logical values, or text representations of numbers. Arguments that are error values cause errors. If the calculation must not include text or logical values, use the MIN function instead.
  • Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).
  • If the arguments contain no values, MINA returns 0.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Formula | Description (Result)
FALSE | 0.2 | 0.5 | 0.4 | 0.8 | =MINA([Col1],[Col2],[Col3],[Col4],[Col5]) | Smallest of the numbers. FALSE evaluates to 0 (0)


MODE

Returns the most frequently occurring, or repetitive, value in the argument list. Like MEDIAN, MODE is a location measure.

Syntax

MODE(number1,number2, ...)

Number1, number2, ... are 1 to 30 arguments for which you want to calculate the mode.

Remarks

  • The arguments should be numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty, those values are ignored; however, arguments with the value zero are included.
  • If the data set contains no duplicate data points, MODE returns the #N/A error value.

In a set of values, the mode is the most frequently occurring value; the median is the middle value; and the mean is the average value. No single measure of central tendency provides a complete picture of the data. Suppose data is clustered in three areas, half around a single low value, and half around two large values. Both AVERAGE and MEDIAN may return a value in the relatively empty middle, and MODE may return the dominant low value.

Example

Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Formula | Description (Result)
5.6 | 4 | 4 | 3 | 2 | 4 | =MODE([Col1], [Col2], [Col3], [Col4], [Col5], [Col6]) | Mode, or most frequently occurring number (4)


NEGBINOMDIST

Returns the negative binomial distribution. NEGBINOMDIST returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent.

For example, you need to find 10 people with excellent reflexes, and you know the probability that a candidate has these qualifications is 0.3. NEGBINOMDIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates.

Syntax

NEGBINOMDIST(number_f,number_s,probability_s)

Number_f is the number of failures.

Number_s is the threshold number of successes.

Probability_s is the probability of a success.

Remarks

  • Number_f and number_s should be >= 0.
  • Number_f and number_s are truncated to integers.
  • If any argument is nonnumeric, NEGBINOMDIST returns the #VALUE! error value.
  • If probability_s < 0 or if probability > 1, NEGBINOMDIST returns the #NUM! error value.
  • The equation for the negative binomial distribution is:

    where:

    x is number_f, r is number_s, and p is probability_s.

Example

Number_f | Number_s | Probability_s | Formula | Description (Result)
10 | 5 | 0.25 | =NEGBINOMDIST([Number_f], [Number_s], [Probability_s]) | Negative binomial distribution for the specified arguments (0.055049)


NORMDIST

Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypothesis testing.

Syntax

NORMDIST(x,mean,standard_dev,cumulative)

X is the value for which you want the distribution.

Mean is the arithmetic mean of the distribution.

Standard_dev is the standard deviation of the distribution.

Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.

Remarks

  • If mean or standard_dev is nonnumeric, NORMDIST returns the #VALUE! error value.
  • If standard_dev ≤ 0, NORMDIST returns the #NUM! error value.
  • If mean = 0, standard_dev = 1, and cumulative = TRUE, NORMDIST returns the standard normal distribution, NORMSDIST.
  • The equation for the normal density function (cumulative = FALSE) is:

  • When cumulative = TRUE, the formula is the integral from negative infinity to x of the given formula.

Example

X | Mean | StdDev | Formula | Description (Result)
42 | 40 | 1.5 | =NORMDIST([X],[Mean],[StdDev],TRUE) | Cumulative distribution function for the specified arguments (0.908789)
42 | 40 | 1.5 | =NORMDIST([X],[Mean],[StdDev],FALSE) | Probability mass function for the specified arguments (0.10934005)


NORMINV

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Syntax

NORMINV(probability,mean,standard_dev)

Probability is a probability corresponding to the normal distribution.

Mean is the arithmetic mean of the distribution.

Standard_dev is the standard deviation of the distribution.

Remarks

  • If any argument is nonnumeric, NORMINV returns the #VALUE! error value.
  • If probability < 0 or if probability > 1, NORMINV returns the #NUM! error value.
  • If standard_dev ≤ 0, NORMINV returns the #NUM! error value.

  • If mean = 0 and standard_dev = 1, NORMINV uses the standard normal distribution (see NORMSINV).

NORMINV uses an iterative technique for calculating the function. Given a probability value, NORMINV iterates until the result is accurate to within ± 3x10^-7. If NORMINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Probability | Mean | StdDev | Formula | Description (Result)
0.908789 | 40 | 1.5 | =NORMINV([Probability],[Mean],[StdDev]) | Inverse of the normal cumulative distribution for the specified arguments (42)


NORMSDIST

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.

Syntax

NORMSDIST(z)

Z is the value for which you want the distribution.

Remarks

  • If z is nonnumeric, NORMSDIST returns the #VALUE! error value.
  • The equation for the standard normal density function is:

Example

Formula | Description (Result)
=NORMSDIST(1.333333) | Normal cumulative distribution function at 1.333333 (0.908789)


NORMSINV

Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.

Syntax

NORMSINV(probability)

Probability is a probability corresponding to the normal distribution.

Remarks

  • If probability is nonnumeric, NORMSINV returns the #VALUE! error value.
  • If probability < 0 or if probability > 1, NORMSINV returns the #NUM! error value.

NORMSINV uses an iterative technique for calculating the function. Given a probability value, NORMSINV iterates until the result is accurate to within ± 3x10^-7. If NORMSINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Formula | Description (Result)
=NORMSINV(0.908789) | Inverse of the standard normal cumulative distribution, with a probability of 0.908789 (1.3333)


POISSON

Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in 1 minute.

Syntax

POISSON(x,mean,cumulative)

X is the number of events.

Mean is the expected numeric value.

Cumulative is a logical value that determines the form of the probability distribution returned. If cumulative is TRUE, POISSON returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if FALSE, it returns the Poisson probability mass function that the number of events occurring will be exactly x.

Remarks

  • If x is not an integer, it is truncated.
  • If x or mean is nonnumeric, POISSON returns the #VALUE! error value.
  • If x ≤ 0, POISSON returns the #NUM! error value.
  • If mean ≤ 0, POISSON returns the #NUM! error value.
  • POISSON is calculated as follows.

    For cumulative = FALSE:

    For cumulative = TRUE:

Example

X | Mean | Formula | Description (Result)
2 | 5 | =POISSON([X],[Mean],TRUE) | Cumulative Poisson probability with the specified arguments (0.124652)
2 | 5 | =POISSON([X],[Mean],FALSE) | Poisson probability mass function with the specified arguments (0.084224)


SKEW

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribution with an asymmetric tail extending toward more positive values. Negative skewness indicates a distribution with an asymmetric tail extending toward more negative values.

Syntax

SKEW(number1,number2, ...)

Number1,number2... are 1 to 30 arguments for which you want to calculate skewness.

Remarks

  • The arguments must be either numbers or column references that contain numbers.
  • If a column reference argument contains text, logical values, or is empty , those values are ignored; however, arguments with the value zero are included.
  • If there are fewer than three data points, or the sample standard deviation is zero, SKEW returns the #DIV/0! error value.
  • The equation for skewness is defined as:

Example

D1 | D2 | D3 | D4 | D5 | D6 | D7 | D8 | D9 | D10 | Formula | Description (Result)
3 | 4 | 5 | 2 | 3 | 4 | 5 | 6 | 4 | 7 | =SKEW([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10]) | Skewness of a distribution of the data set (0.359543)


STANDARDIZE

Returns a normalized value from a distribution characterized by mean and standard_dev.

Syntax

STANDARDIZE(x,mean,standard_dev)

X is the value you want to normalize.

Mean is the arithmetic mean of the distribution.

Standard_dev is the standard deviation of the distribution.

Remarks

  • If standard_dev ≤ 0, STANDARDIZE returns the #NUM! error value.
  • The equation for the normalized value is:

Example

X | Mean | StdDev | Formula | Description (Result)
42 | 40 | 1.5 | =STANDARDIZE([X],[Mean],[StdDev]) | Normalized value of 42 for the specified arguments (1.333333)


STDEV

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEV(number1,number2,...)

Number1,number2,... are 1 to 30 number arguments corresponding to a sample of a population.

Remarks

  • Logical values such as TRUE and FALSE and text are ignored. If logical values and text must not be ignored, use the STDEVA function.
  • STDEV assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the standard deviation using STDEVP.
  • The standard deviation is calculated using the "nonbiased" or "n-1" method.
  • STDEV uses the following formula:

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =STDEV([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Standard deviation of breaking strength (27.46391572)


STDEVA

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Text and logical values such as TRUE and FALSE are included in the calculation.

Syntax

STDEVA(value1,value2, ...)

Value1,value2, ... are 1 to 30 values corresponding to a sample of a population.

Remarks

  • STDEVA assumes that its arguments are a sample of the population. If your data represents the entire population, you must compute the standard deviation using STDEVPA.
  • Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the STDEV spreadsheet function instead.
  • The standard deviation is calculated using the "nonbiased" or "n-1" method.
  • STDEVA uses the following formula:

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =STDEVA([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Standard deviation of breaking strength for all the tools (27.46391572)


STDEVP

Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEVP(number1,number2, ...)

Number1,number2, ... are 1 to 30 number arguments corresponding to a population.

Remarks

  • Logical values such as TRUE and FALSE and text are ignored. If logical values and text must not be ignored, use the STDEVPA spreadsheet function.
  • STDEVP assumes that its arguments are the entire population. If your data represents a sample of the population, then compute the standard deviation using STDEV.
  • For large sample sizes, STDEV and STDEVP return approximately equal values.
  • The standard deviation is calculated using the "biased" or "n" method.
  • STDEVP uses the following formula:

Example

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =STDEVP([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Standard deviation of breaking strength, assuming only 10 tools are produced (26.05455814)


STDEVPA

Calculates standard deviation based on the entire population given as arguments, including text and logical values. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Syntax

STDEVPA(value1,value2, ...)

Value1,value2,... are 1 to 30 values corresponding to a population.

Remarks

  • STDEVPA assumes that its arguments are the entire population. If your data represents a sample of the population, you must compute the standard deviation using STDEVA.
  • Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the STDEVP spreadsheet function instead.
  • For large sample sizes, STDEVA and STDEVPA return approximately equal values.
  • The standard deviation is calculated using the "biased" or "n" method.
  • STDEVPA uses the following formula:

Example

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =STDEVPA([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Standard deviation of breaking strength, assuming only 10 tools are produced (26.05455814)


TDIST

Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets. Use this function in place of a table of critical values for the t-distribution.

Syntax

TDIST(x,degrees_freedom,tails)

X is the numeric value at which to evaluate the distribution.

Degrees_freedom is an integer indicating the number of degrees of freedom.

Tails specifies the number of distribution tails to return. If tails = 1, TDIST returns the one-tailed distribution. If tails = 2, TDIST returns the two-tailed distribution.

Remarks

  • If any argument is nonnumeric, TDIST returns the #VALUE! error value.
  • If degrees_freedom < 1, TDIST returns the #NUM! error value.
  • The degrees_freedom and tails arguments are truncated to integers.
  • If tails is any value other than 1 or 2, TDIST returns the #NUM! error value.
  • TDIST is calculated as TDIST = p( x<abs(X)), where X is a random variable that follows the t-distribution.

Example

X | Degrees | Formula | Description (Result)
1.96 | 60 | =TDIST([X],[Degrees],2) | Two-tailed distribution (0.054644927 or 5.46 percent)
1.96 | 60 | =TDIST([X],[Degrees],1) | One-tailed distribution (0.027322463 or 2.73 percent)


TINV

Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.

Syntax

TINV(probability,degrees_freedom)

Probability is the probability associated with the two-tailed Student's t-distribution.

Degrees_freedom is the number of degrees of freedom to characterize the distribution.

Remarks

  • If either argument is nonnumeric, TINV returns the #VALUE! error value.
  • If probability < 0 or if probability > 1, TINV returns the #NUM! error value.
  • If degrees_freedom is not an integer, it is truncated.
  • If degrees_freedom < 1, TINV returns the #NUM! error value.
  • TINV is calculated as TINV = p( t<X ), where X is a random variable that follows the t-distribution.
  • A one-tailed t-value can be returned by replacing probability with 2*probability. For a probability of 0.05 and degrees of freedom of 10, the two-tailed value is calculated with TINV(0.05,10), which returns 2.28139. The one-tailed value for the same probability and degrees of freedom can be calculated with TINV(2*0.05,10), which returns 1.812462.

    Note In some tables, probability is described as (1-p).

TINV uses an iterative technique for calculating the function. Given a probability value, TINV iterates until the result is accurate to within ± 3x10^-7. If TINV does not converge after 100 iterations, the function returns the #N/A error value.

Example

Probability | Degrees | Formula | Description (Result)
0.054645 | 60 | =TINV([Probability],[Degrees]) | t-value of the Student's t-distribution for the specified arguments (1.959997462)


VAR

Estimates variance based on a sample.

Syntax

VAR(number1,number2, ...)

Number1,number2, ... are 1 to 30 number arguments corresponding to a sample of a population.

Remarks

  • VAR assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the variance using VARP.
  • Logical values such as TRUE and FALSE and text are ignored. If logical values and text must not be ignored, use the VARA function.
  • VAR uses the following formula:

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =VAR([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Variance for the breaking strength of the tools (754.2666667)


VARA

Estimates variance based on a sample. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation.

Syntax

VARA(value1,value2, ...)

Value1,value2, ... are 1 to 30 value arguments corresponding to a sample of a population.

Remarks

  • VARA assumes that its arguments are a sample of the population. If your data represents the entire population, you must compute the variance using VARPA.
  • Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use the VAR function instead.
  • VARA uses the following formula:

Example

Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength.

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =VARA([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Estimates the variance for the breaking strength (754.2666667)


VARP

Calculates variance based on the entire population.

Syntax

VARP(number1,number2, ...)

Number1,number2, ... are 1 to 30 number arguments corresponding to a population.

  • Logical values such as TRUE and FALSE and text are ignored. If logical values and text must not be ignored, use the VARPA function.

Remarks

  • VARP assumes that its arguments are the entire population. If your data represents a sample of the population, then compute the variance using VAR.
  • The equation for VARP is:

Example

Suppose all 10 tools stamped from the same machine during a production run are collected and measured for breaking strength.

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =VARP([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Variance of breaking strengths for all the tools, assuming that only 10 tools are produced (678.84)


VARPA

Calculates variance based on the entire population. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation.

Syntax

VARPA(value1,value2, ...)

Value1,value2,... are 1 to 30 value arguments corresponding to a population.

Remarks

  • VARPA assumes that its arguments are the entire population. If your data represents a sample of the population, you must compute the variance using VARA.
  • Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero). If the calculation must not include text or logical values, use VARP instead.
  • The equation for VARPA is :

Example

Suppose all 10 tools stamped from the same machine during a production run are collected and measured for breaking strength.

St1 | St2 | St3 | St4 | St5 | St6 | St7 | St8 | St9 | St10 | Formula | Description (Result)
1345 | 1301 | 1368 | 1322 | 1310 | 1370 | 1318 | 1350 | 1303 | 1299 | =VARPA([St1], [St2], [St3], [St4], [St5], [St6], [St7], [St8], [St9], [St10]) | Variance of breaking strengths for all the tools, assuming that only 10 tools are produced (678.84)


WEIBULL

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device's mean time to failure.

Syntax

WEIBULL(x,alpha,beta,cumulative)

X is the value at which to evaluate the function.

Alpha is a parameter to the distribution.

Beta is a parameter to the distribution.

Cumulative determines the form of the function.

Remarks

  • If x, alpha, or beta is nonnumeric, WEIBULL returns the #VALUE! error value.
  • If x < 0, WEIBULL returns the #NUM! error value.
  • If alpha ≤ 0 or if beta ≤ 0, WEIBULL returns the #NUM! error value.
  • The equation for the Weibull cumulative distribution function is:

  • The equation for the Weibull probability density function is:

  • When alpha = 1, WEIBULL returns the exponential distribution with:

Example

X | Alpha | Beta | Formula | Description (Result)
105 | 20 | 100 | =WEIBULL([X],[Alpha],[Beta],TRUE) | Weibull cumulative distribution function for the specified arguments (0.929581)
105 | 20 | 100 | =WEIBULL([X],[Alpha],[Beta],FALSE) | Weibull probability density function for the specified arguments (0.035589)


ASC

For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters.

Syntax

ASC(text)

Text is the text or a column reference that contains the text you want to change. If text does not contain any full-width letters, text is not changed.

Examples

=ASC("EXCEL")
equals "EXCEL"

=ASC("
Excel Excel Excel Excel
")
equals " Excel Excel Excel Excel "


CHAR

Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.

Syntax

CHAR(number)

Number is a number between 1 and 255 specifying which character you want. The character is from the character set used by your computer.

Example

Formula | Description (Result)
=CHAR(65) | Displays the 65 character in the set (A)
=CHAR(33) | Displays the 33 character in the set (!)


CLEAN

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

Syntax

CLEAN(text)

Text is any information from which you want to remove nonprintable characters.

Example

Col1 | Formula | Description (Result)
CHAR(7)&"text"&CHAR(7) | =CLEAN([Col1]) | Removes the nonprintable character, CHAR(7), from the string (text).


CODE

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.

Syntax

CODE(text)

Text is the text for which you want the code of the first character in the text string.

Example

Formula | Description (Result)
=CODE("A") | Displays the numeric code for A (65)
=CODE("!") | Displays the numeric code for ! (33)


CONCATENATE

Joins several text strings into one text string.

Syntax

CONCATENATE (text1,text2,...)

Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or column references.

Remarks

The "&" operator can be used instead of CONCATENATE to join text items.

Example

Col1 | Col2 | Col3 | Formula | Description
brook trout | species | 32 | =CONCATENATE("Stream population for ",[Col1]," ",[Col2]," is ",[Col3],"/mile") | Concatenates a sentence from the data (Stream population for brook trout species is 32/mile)


DOLLAR

Converts a number to text using currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_);($#,##0.00).

Syntax

DOLLAR(number,decimals)

Number is a number, a colimn reference containing a number, or a formula that evaluates to a number.

Decimals is the number of digits to the right of the decimal point. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, your system locale setting will be used to determine the number of decimal places.

Remark

The Dollar function uses the currency setting of your computer. If you always want to show U.S. currency, use the USDOLLAR function instead.

Example

Col1 | Formula | Description (Result)
1234.567 | =DOLLAR([Col1], 2) | Displays the first number in a currency format, 2 digits to the right of the decimal point ($1,234.57)
1234.567 | =DOLLAR([Col1], -2) | Displays the first number in a currency format, 2 digits to the left of the decimal point ($1,200)
-1234.567 | =DOLLAR([Col1], -2) | Displays the second number in a currency format, 2 digits to the left of the decimal point (($1,200))
-0.123 | =DOLLAR([Col1], 4) | Displays the third number in a currency format, 4 digits to the right of the decimal point(($0.1230))
99.888 | =DOLLAR([Col1]) | Displays the fourth number in a currency format, 2 digit to the left of the decimal point ($99.89)


EXACT

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive. Use EXACT to test text being entered into a document.

Syntax

EXACT(text1,text2)

Text1 is the first text string.

Text2 is the second text string.

Example

Col1 | Col2 | Formula | Description (Result)
word | word | =EXACT([Col1],[Col2]) | Checks whether the strings match (TRUE)
Word | word | =EXACT([Col1],[Col2] | Checks whether the strings match (FALSE)
w ord | word | =EXACT([Col1],[Col2]) | Checks whether the strings match (FALSE)


FIND

Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn't allow wildcard characters.

Syntax

FIND(find_text,within_text,start_num)

Find_text is the text you want to find.

Within_text is the text containing the text you want to find.

Start_num specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Show Tip

Use start_num to skip a specified number of characters. For example, suppose you are working with the text string "AYF0093.YoungMensApparel". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. FIND begins with character 8, finds find_text at the next character, and returns the number 9. FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.

Remarks

  • If find_text is "" (empty text), FIND matches the first character in the search string (that is, the character numbered start_num or 1).
  • Find_text cannot contain any wildcard characters.
  • If find_text does not appear in within_text, FIND returns the #VALUE! error value.
  • If start_num is not greater than zero, FIND returns the #VALUE! error value.
  • If start_num is greater than the length of within_text, FIND returns the #VALUE! error value.

Example 1

Formula | Description (Result)
=FIND("M","Miriam McGovern") | Position of the first "M" in the string (1)
=FIND("m","Miriam McGovern") | Position of the first "m" in the string (6)
=FIND("M","Miriam McGovern",3) | Position of the first "M" in the string, starting with the third character (8)

Example 2

Col1 | Formula | Description (Result)
Ceramic Insulators #124-TD45-87 | =MID([Col1],1,FIND(" #",[Col1],1)-1) | Extracts text from position 1 to the position of " #" in the string (Ceramic Insulators)
Copper Coils #12-671-6772 | =MID([Col1],1,FIND(" #",[Col1],1)-1) | Extracts text from position 1 to the position of " #" in the string (Copper Coils)
Variable Resistors #116010 | =MID([Col1],1,FIND(" #",[Col1],1)-1) | Extracts text from position 1 to the position of " #" in the string (Variable Resistors)


FIXED

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Syntax

FIXED(number,decimals,no_commas)

Number is the number you want to round and convert to text.

Decimals is the number of digits to the right of the decimal point.

No_commas is a logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Remarks

  • If decimals is negative, number is rounded to the left of the decimal point.
  • If you omit decimals, your system locale setting will be used to determine the number of decimal places.
  • If no_commas is FALSE or omitted, then the returned text includes commas as usual.

Example

Formula | Description (Result)
=FIXED(1234.567, 1) | Rounds the first number 1 digit to the right of the decimal point (1,234.6)
=FIXED(1234.567, -1) | Rounds the first number 1 digit to the left of the decimal point (1,230)
=FIXED(-1234.567, -1, TRUE) | Rounds the second number 1 digit to the left of the decimal point, without commas (-1230)
=FIXED(44.332) | Rounds the third number 2 digits to the left of the decimal point (44.33)


LEFT

LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Syntax

LEFT(text,num_chars)

Text is the text string that contains the characters you want to extract.

Num_chars specifies the number of characters you want LEFT to extract.

  • Num_chars must be greater than or equal to zero.

  • If num_chars is greater than the length of text, LEFT returns all of text.

  • If num_chars is omitted, it is assumed to be 1.

Example

Col1 | Formula | Description (Result)
Sale Price | =LEFT([Col1],4) | First four characters in the string (Sale)
Sweden | =LEFT([Col1]) | First character in the string (S)


LEN

LEN returns the number of characters in a text string.

Syntax

LEN(text)

Text is the text whose length you want to find. Spaces count as characters.

Example

Col1 | Formula | Description (Result)
Phoenix, AZ | =LEN([Col1]) | Length of the string (11)
 | =LEN([Col1]) | Length of the string (0)
A One | =LEN([Col1]) | Length of the string, which includes 5 spaces (8)


LOWER

Converts all uppercase letters in a text string to lowercase.

Syntax

LOWER(text)

Text is the text you want to convert to lowercase. LOWER does not change characters in text that are not letters.

Example

Col1 | Formula | Description (Result)
E. E. Cummings | =LOWER([Col1]) | Lower case of string (e. e. cummings)
Apt. 2B | =LOWER([Col1]) | Lower case of string (apt. 2b)


MID

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Syntax

MID(text,start_num,num_chars)

Text is the text string containing the characters you want to extract.

Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

Num_chars specifies the number of characters you want MID to return from text.

Remarks

  • If start_num is greater than the length of text, MID returns "" (empty text).
  • If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
  • If start_num is less than 1, MID returns the #VALUE! error value.

  • If num_chars is negative, MID returns the #VALUE! error value.

Example

Formula | Description (Result)
=MID("Fluid Flow",1,5) | Five characters from the string, starting at the first character (Fluid)
=MID("Fluid Flow",7,20) | Twenty characters from the string, starting at the seventh (Flow)
=MID("Fluid Flow",20,5) | Because the starting point is greater than the length of the string, empty text is returned ()


NULL

Returns the value #NULL!. Use the NULL function to indicate empty values in a calculated column.

By using NULL, you can avoid the problem of unintentionally including empty values in your calculations. When a formula refers to a cell containing #NULL!, the formula returns #NULL!.

Syntax

NULL()

Remark

The NULL function syntax has no arguments. You must include the empty parentheses with the function name. Otherwise, SharePoint Foundation 2010 will not recognize it as a function.


NZ

Checks if the value is #NULL!. If the argument evaluates to TRUE, the function returns the alternate value, otherwise it returns the value.

Syntax

NZ(value, alternate)

Argument | Description
value | Can be a column reference. If value is #NULL!, the function returns the value in alternate. Otherwise, it returns value.
alternate | Can be numbers, text, or column references. The value in alternate is returned if value is #NULL!.

Example

Col1 | Col2 | Formula | Description (Result)
#NULL! | 200 | =NZ([Col 1],[Col2]) | Checks whether the value in Col1 is the #NULL! error and returns the alternate value (200).
400 | 450 | =NZ([Col 1],[Col2]) | Checks whether the value in Col1 is the #NULL! error and returns the alternate value (400).


PROPER

Capitalizes the first letter and any other letters that follow a non-letter character in a text string. Converts all other letters in the text string to lowercase letters.

Syntax

PROPER(text)

Text is text enclosed in quotation marks, a formula that returns text, or a column reference containing the text you want to partially capitalize.

Example

Formula | Description (Result)
=PROPER("this is a TITLE") | Proper case of string (This Is A Title)
=PROPER("2-cent's worth") | Proper case of string (2-Cent'S Worth)
=PROPER("76BudGet") | Proper case of string (76Budget)


REPLACE

REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.

Syntax

REPLACE(old_text,start_num,num_chars,new_text)

Old_text is text in which you want to replace some characters.

Start_num is the position of the character in old_text that you want to replace with new_text.

Num_chars is the number of characters in old_text that you want REPLACE to replace with new_text.

New_text is the text that will replace characters in old_text.

Example

Formula | Description (Result)
=REPLACE("abcdefghijk",6,5,"*") | Replaces five characters, starting with the sixth character (abcde*k)
=REPLACE("2009",3,2,"10") | Replaces the last two digits of 2009 with 10 (2010)
=REPLACE("123456",1,3,"@") | Replaces the first three characters with @ (@456)


REPT

Repeats text a given number of times. Use REPT to add a number of instances of a text string.

Syntax

REPT(text,number_times)

Text is the text you want to repeat.

Number_times is a positive number specifying the number of times to repeat text.

Remarks

  • If number_times is 0 (zero), REPT returns "" (empty text).

  • If number_times is not an integer, it is truncated.

  • The result of the REPT function cannot be longer than 2000 characters, or REPT returns #VALUE!.

Example

Formula | Description (Result)
=REPT("*-", 3) | Displays the string 3 times (*-*-*-)
=REPT("-",10) | Displays a dash 10 times (----------)


RIGHT

RIGHT returns the last characters in a text string, based on the number of characters you specify.

Syntax

RIGHT(text,num_chars)

Text is the text string containing the characters you want to extract.

Num_chars specifies the number of characters you want RIGHT to extract.

Remarks

  • Num_chars must be greater than or equal to zero.
  • If num_chars is greater than the length of text, RIGHT returns all of text.
  • If num_chars is omitted, it is assumed to be 1.

Example

Formula | Description (Result)
=RIGHT("Sale Price",5) | Last 5 characters of the string (Price)
=RIGHT("Stock Number") | Last character of the string (r)


SEARCH

SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string so that you can use the MID or REPLACE functions to change the text.

Syntax

SEARCH(find_text,within_text,start_num)

Find_text is the text you want to find. You can use the wildcard characters — question mark (?) and asterisk (*) in find_text. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Within_text is the text in which you want to search for find_text.

Start_num is the character number in within_text at which you want to start searching.

Show Tip

Use start_num to skip a specified number of characters. For example, suppose you are working with the text string "AYF0093.YoungMensApparel". To find the number of the first "Y" in the descriptive part of the text string, set start_num equal to 8 so that the serial-number portion of the text is not searched. SEARCH begins with character 8, finds find_text at the next character, and returns the number 9. SEARCH always returns the number of characters from the start of within_text, counting the characters you skip if start_num is greater than 1.

Remarks

  • SEARCH does not distinguish between uppercase and lowercase letters when searching text.
  • SEARCH is similar to FIND except that FIND is case sensitive.
  • If find_text is not found, the #VALUE! error value is returned.
  • If start_num is omitted, it is assumed to be 1.
  • If start_num is not greater than 0 (zero) or is greater than the length of within_text, the #VALUE! error value is returned.

Example

Col1 | Col2 | Col3 | Formula | Description (Result)
Statements | Profit Margin | margin | =SEARCH("e",[Col1],6) | Position of the first "e" in the string, starting at the sixth position (7)
Statements | Profit Margin | margin | =SEARCH([Col2],[Col3]) | Position of "margin" in "Profit Margin" (8)
Statements | Profit Margin | margin | =REPLACE([Col3],SEARCH([Col3],[Col2]),6,"Amount") | Replaces "Margin" with "Amount" (Profit Amount)


T

Returns the text referred to by value.

Syntax

T(value)

Value is the value you want to test.

Remarks

  • If value is or refers to text, T returns value. If value does not refer to text, T returns "" (empty text).

  • You do not generally need to use the T function in a formula because values are generally converted as necessary.

Example

Formula | Description (Result)
=T("Rainfall") | Because the value is text, the text is returned (Rainfall)
=T(19) | Because the value is a number, empty text is returned ()
=T(TRUE) | Because the value is a logical value, empty text is returned ()


TEXT

Converts a value to text in a specific number format.

Syntax

TEXT(value,format_text)

Value is a numeric value, a formula that evaluates to a numeric value, or a column reference containing a numeric value.

Format_text is a number format in text format.

Remarks

  • Using the TEXT function converts a value to formatted text, and the result is no longer calculated as a number.

Example

Salesperson | Sales | Formula | Description (Result)
Buchanan | 2800 | =[Salesperson]&" sold "&TEXT([Sales], "$0.00;($0.00)")&" worth of units." | Combines contents into a phrase (Buchanan sold $2,800.00 worth of units.)
Dodsworth | .4 | =[Salesperson]&" sold "&TEXT([Sales],"0.00%")&" of the total sales." | Combines contents into a phrase (Dodsworth sold 40.00% of the total sales.)


TRIM

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Syntax

TRIM(text)

Text is the text from which you want spaces removed.

Example

Formula | Description (Result)
=TRIM(" First Quarter Earnings ") | Removes leading and trailing spaces from the text in the formula (First Quarter Earnings)


TRIMENDS

Removes leading and trailing spaces, such as whitespace and non-breaking space before and after the text. Use TRIMENDS on text strings that you have received from another application, and that may have extra spacing before and after the strings.

Note TRIMENDS preserves spacing within the text string. To alter spacing within the text string, use the TRIM function. For more information, see the Help topic about the TRIM function.

Syntax

TRIMENDS(text)

Text The string from which you want spaces removed.

Example

Formula | Description (Result)
=TRIMENDS (“ Record sales ”) | Removes leading and trailing spaces from the text in the formula (Record sales)


UPPER

Converts text to uppercase.

Syntax

UPPER(text)

Text is the text you want converted to uppercase. Text can be a column reference or text string.

Example

Formula | Description (Result)
=UPPER("total") | Upper case of string (TOTAL)
=UPPER("Yield") | Upper case of string (YIELD)


USDOLLAR

Converts a number to text using currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_);($#,##0.00).

Syntax

USDOLLAR(number,decimals)

Number is a number, a column reference containing a number, or a formula that evaluates to a number.

Decimals is the number of digits to the right of the decimal point. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, your system locale setting will be used to determine the number of decimal places.

Remark

The USDOLLAR function always shows U.S. currency. If you want to show the currency setting of your computer, use the DOLLAR function instead.

Example

Col1 | Formula | Description (Result)
1234.567 | =USDOLLAR([Col1], 2) | Displays the first number in a currency format, 2 digits to the right of the decimal point ($1,234.57)
1234.567 | =USDOLLAR([Col1], -2) | Displays the first number in a currency format, 2 digits to the left of the decimal point ($1,200)
-1234.567 | =USDOLLAR([Col1], -2) | Displays the second number in a currency format, 2 digits to the left of the decimal point (($1,200))
-0.123 | =USDOLLAR([Col1], 4) | Displays the third number in a currency format, 4 digits to the right of the decimal point(($0.1230))
99.888 | =USDOLLAR([Col1]) | Displays the fourth number in a currency format, 2 digit to the left of the decimal point ($99.89)


VALUE

Converts a text string that represents a number to a number.

Syntax

VALUE(text)

Text is the text enclosed in quotation marks or a column reference containing the text you want to convert.

Remarks

  • Text can be in any of the constant number, date, or time formats. If text is not in one of these formats, VALUE returns the #VALUE! error value.

Example

Formula | Description (Result)
=VALUE("$1,000") | Number equivalent of the string (1000)
=VALUE("16:48:00")-VALUE("12:00:00") | The serial number equivalent to 4 hours and 48 minutes, which is "16:48:00"-"12:00:00" (0.2 or 4:48)


About improving Access Web Datasheet performance

The performance of a Datasheet view depends on many factors, such as the size of the list, performance and configuration of the server, and network conditions. This topic lists a few guidelines for improving view performance.

The following operations might take some time to complete, so keep them to the minimum:

  • Refreshing a view that contains a large amount of data.
  • Moving or copying large sets of data.
  • Filling large numbers of cells by dragging the fill handle.
  • Changing the formula in a calculated column.
  • Attaching large documents.
  • Exporting from and linking to Microsoft Excel 2010.
  • Resolving large numbers of errors.

Do the following to improve performance of a view:

  • Remove unnecessary columns from the view. In the Edit Datasheet View page, clear the Display check box for columns that are not necessary in the view. But do not remove columns that are marked Required. You will not be able to add data if the view does not include all required columns.
  • Apply one or more filters to remove rows that are not necessary. For example, you could edit the definition of the Managers view to include a filter that hides rows of employeers who are not managers.

    In addition to saved filters, you could apply filters during a session to further reduce the amount of data in the view. For example, if you are currently updating rows of managers in Washington, hide the rows of managers from other states.

  • Configure the SharePoint server to improve performance. For more information on improving server performance, see SharePoint Foundation 2010 Help. To display Help in SharePoint Foundation 2010, click Help ( Help icon).


I get the message 'List structure has changed'

When two users make changes to the structure of a list at the same time, the user who submits a change first will succeed in committing his or her changes, but the second user will be notified of a change in list structure.

The following table summarizes the scenarios in which you will be informed of a change in list structure.

User A | User B
Deletes a column | Updates data in the same column
Changes the data type of a column | Adds or updates data with type mismatch
Adds, renames, or deletes a column, changes a column's data type or other properties | Edits a calculated column
Makes list-level changes, such as renames the list, changes its description or security settings, rearranges the columns, enables or disables attachments, or adds the list to the Quick Launch bar | Edits a calculated column
Edits a calculated column | Adds, renames, or deletes a column, changes a column's data type or other properties Note User B must be on the Add Column or Change Column page before User A starts editing the calculated column.
Edits a calculated column | Makes list-level changes, such as renames the list, changes its description or security settings, rearranges the columns, enables or disables attachments, or adds the list to the Quick Launch bar Note User B must be on the List Settings page before user A starts editing the calculated column.

You can resolve a structural conflict by refreshing the list. When the list is refreshed, your changes will be discarded, and your view will be updated with the latest version of the list. After refreshing the list, simply repeat your changes.


I get the message 'Stop submitting changes'

You are attempting to stop the process of submitting your changes to the server before refreshing, navigating away from the list, or making changes to a calculated column. You can do one of the following:

  • Click Yes to stop the list from submitting changes to the server. You will lose the changes that have not yet been saved by the server. You will also not be able to resolve conflicts or errors arising from the changes that have already been submitted to the server.
  • Click No to continue with submitting changes. You will be able to resolve resulting conflicts and errors, and resend your changes to the server.


I get the message 'Submit pending changes'

The list is attempting to submit your changes to the server before continuing with your request to refresh, navigate away from the list, or edit a calculated column. You can do one of the following:

  • Wait until all changes are submitted to the server. This will allow you to resolve conflicts and errors, and resend your changes to the server, if necessary.
  • Discard your changes by clicking Cancel. If you click Cancel, you will be asked to confirm whether you want to stop sending changes to the server. If you click Yes, you will lose the changes that have not yet been saved by the server. You will also not be able to resolve conflicts or errors arising from the changes that have already been submitted to the server.


I get the message 'You have pending changes, or unresolved conflicts and errors.'

You are attempting to refresh, navigate away from the list, or make changes to a calculated column. Some of the changes you made to the list have not yet been submitted to the server.

You can do one of the following:

  • Click Yes to wait while the changes are sent to the server. If any conflicts or errors result from submitting changes to the server, you will be able to resolve the conflicts and errors, and retry sending changes to the server.
  • Click No to continue with your last action immediately. You will lose some or all of the changes that have not yet been submitted to the server. You will also not be able to resolve conflicts or errors arising from the changes that have already been submitted to the server.


Status bar displays 'Pending changes'

When editing the contents of a list, you need not wait until your previous change is submitted to the server to make another change. While you are making changes to the data, the list keeps submitting your changes to the server in the background. If you bulk edit several cells, such as filling several rows of a column by dragging the drag fill handle, the list might take several seconds to send all the changes to the server. In such situations, it displays the Pending changes link in the status bar.

Seeing this link does not mean you need to postpone adding or editing data until all previous changes get submitted. However, if you attempt to edit the formula in a calculated column, or refresh or close the view, you will see a message requesting you to wait until your changes are submitted to the server.


Status bar displays 'Read-only' or 'This view is read-only'

The selected cell, column, row, or the entire view is read-only. You cannot edit a cell, column, or row that is marked read-only. If the entire view is read-only, you cannot edit any portion of the view.

Portions of a view, or an entire view, might be read-only due to one or more of the following reasons:

  • Certain columns, such as Created By and Modified, are always read-only. Values for these columns are entered automatically.
  • The changes you made to the selected row or column have been submitted to the server. If there is no conflict or error, you will be able to edit the row or column after the changes have been saved.
  • There is an unresolved conflict or error in the selected row or column. Resolve the conflict or error before attempting to edit the selected row or column.
  • The list is set up to require content approval, and you are viewing the list in the All Items view.
  • In the My Submissions view of a list that requires content approval, the Approval Status and Comment column are read-only. Only a user with Approver permission can edit these two columns.
  • Attachments are read-only in a list that requires content approval. You cannot view or edit attachments in any of the views.
  • The document in the current row has been checked out by a user. You cannot edit the columns in the row until the user checks the document in.
  • You do not have permission to edit the column or row. Contact the list author for more information.


Status bar displays 'Retrieving data'

The list retrieves data in batches of rows. It is attempting to fetch the next batch. This may take some time if the server is busy.

If the server encounters a problem while retrieving data, a message will appear. In that case, close the list, and try again later.


About international support

Details of international support for lists are covered in SharePoint Foundation 2010 Help.

To display Help in SharePoint Foundation 2010, click Help ( Help icon).


Reconvert a determined character string

Note This command is available only if support for Japanese, Simplified Chinese or Traditional Chinese Input Method Editor is enabled on your operation system or through Microsoft Office Language Settings.

You can reconvert a character string that has already been input and converted.

  1. Place the insertion point at the beginning or in the middle of the characters you want to reconvert.
  2. Right-click the cell, and then click Reconvert on the shortcut menu.
  3. Click the desired character, and press ENTER twice.