SharePoint 2007 : Use the Datasheet View to Add, Edit, or Delete Items and Files

Problem: You want to add, edit, or delete multiple items to a list or library in an easy way, not having to click each one to edit or delete it separately, or not wanting to click the New button many times, especially because the details that you want to modify are similar.

Solution: The datasheet view, as explained earlier, is similar to a Microsoft Excel datasheet and allows copying and pasting data into the list or library (see Figure 1).

Figure 1. A datasheet view showing a contacts list.

This interface makes adding, editing, and deleting multiple list items or files a lot easier. You can create list items by copying data from Microsoft Excel or from other spreadsheet applications; you can edit the properties of many list items or files by copying cells, or delete many files in one action.

If you want to view a datasheet view, there are certain requirements for your computer. For one, the datasheet component must be installed on your machine. This component is part of the Microsoft Office installation for Microsoft Office Professional 2003 or 2007. Additionally, the browser you are using to view the site must be compatible with the datasheet view and allow showing ActiveX controls. If you are getting error messages, contact your administrator for assistance (see Figure 2).

Figure 2. An error message that appears when you’re trying to switch to a datasheet view and Microsoft Office is not installed.

Switch to a Datasheet View

Some views are built to be datasheet views by default. In this case, just switching to the view displays the datasheet interface.

However, you can also switch to a datasheet view version of any standard view, even if the view creator did not create it as a datasheet view. To switch to the datasheet view from a standard view, open the Actions menu for the list and choose the Edit in Datasheet option from the drop-down menu (see Figure 3).

Figure 3. Switching to a datasheet view.

To switch back to the standard view, open the Actions menu again and choose Show in Standard View.

Note

If you are trying to switch to a datasheet view with a browser that is not supported (for example, Mozilla Firefox), the option to switch to the datasheet view is not displayed.

Add Multiple List Items from Microsoft Excel Using the Datasheet View

A useful way to use the datasheet view is to populate a list with the data from an existing spreadsheet.

For example, if you have a Microsoft Excel spreadsheet with information about products, and you want to copy that information to a SharePoint list, you can use the datasheet view to copy the information to the list, even if the columns are not the same. To do that, open the spreadsheet and select the information you want to copy (see Figure 4).

Figure 4. Select the information from Microsoft Excel that you want to copy to the SharePoint list, and copy it.

Now switch to the SharePoint list to the datasheet view that shows the columns that you want to paste into. Remember, the view must have all mandatory columns for that list showing.

If the columns in the datasheet view are not in the same order as they were in the Microsoft Excel spreadsheet, you must reorder them in the datasheet view. To do that, simply drag and drop the column headers until the order is correct so that when you paste the information, the correct information goes in the right column. In the preceding example, the Product Alternate Key column should be first in the datasheet view because it was first in Excel, so it needs to be moved before the Title field in the view (see Figure 5).

Figure 5. Right-click the first cell in the first data column and choose Paste.

When the datasheet view is ready for the data, just right-click the first cell and choose Paste to paste the data from Microsoft Excel (see Figure 6.39).

The information from the Microsoft Excel spreadsheet is pasted into the list. Because this can be a lot of information, SharePoint might take a while to actually create the list items. You can see the progress on the left side of the datasheet view (see Figure 6). If an item has not been added yet, it has an icon symbolizing that it still needs to be synchronized with SharePoint. You must wait until all the items have been synchronized before closing the window or navigating to a different page.

Figure 6. Adding multiple list items using the datasheet view.

Add a List Item Using the Datasheet View

You can use the datasheet view to add list items. This approach does not work with document libraries because they require you to upload a file for each row, and the datasheet view does not have an interface to upload a file. Because list items are made strictly of data in columns, you can create new ones from the datasheet view.

Note

When some columns are mandatory and the view does not show these columns, the datasheet view does not let you create new items. To be able to create new list items in the datasheet view, you must use a view that displays all the mandatory columns.

To create a new list item in the datasheet view, just type values in the last row (the one marked with an asterisk) in the datasheet. The list item is created with the values you have typed in. To finish adding the list item, press Enter or click another row in the datasheet view. The datasheet creates the list item (see Figure 7).

Figure 7. Creating a new list item using the datasheet view.

If there are many rows in the datasheet and you want to avoid scrolling down to the bottom to add a new list item, a quick way to get to the last row is to open the Actions menu and choose New Row. Doing so scrolls down to the new row at the bottom.