datasheet view, as explained earlier, is similar to a Microsoft Excel
datasheet and allows copying and pasting of data into the list or
library (see Figure 1).
Figure 1. A datasheet view showing a contacts list.
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 another spreadsheet application. You can edit the
properties of many list items or files by copying cells, and you can
delete many files in one action.
If you want to see the
datasheet view, there are certain requirements for your computer.
First, the datasheet component must be installed on your machine. This
component is part of the Microsoft Office installation for Microsoft
Office Professional 2003, 2007, or 2010. Second, 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 (see Figure 2), contact your administrator for assistance.
Figure 2. An
error message that appears when you’re trying to switch to a datasheet
view and Microsoft Office Datasheet Component is not installed on the
Switch to 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, you switch to the List ribbon or
Library ribbon and click the Datasheet View button in the View Format
section of the ribbon (see Figure 3). To switch back to the standard view, click the Standard View button in the same ribbon (see Figure 3).
Figure 3. The Standard View and the Datasheet View buttons.
you are trying to switch to datasheet view with a browser that is not
supported (for example, Mozilla Firefox, Google Chrome), the option to
switch to datasheet view is displayed but not enabled.
Add a List Item Using Datasheet View
You can use datasheet
view to add list items. This approach doesn’t work with document
libraries because they require you to upload a file for each row, and
the datasheet view doesn’t have an interface to upload a file. Because
list items are made strictly of data in columns, you can create new
ones from datasheet view.
When some columns are
mandatory and the view doesn’t show these columns, datasheet view
doesn’t let you create new items. To be able to create new list items
in datasheet view, you must use a view that displays all the mandatory
To create a new list item in
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 typed in. To finish adding the list item, press Enter or click
another row in datasheet view. The datasheet creates the list item (see
Figure 4. Creating a new list item using datasheet view.
If there are many rows in a
datasheet and you want to avoid scrolling down to the bottom to add a
new list item, you can quickly get to the last row by clicking the New
Row button in the List ribbon (which is available only when in
datasheet view). Clicking this button scrolls down to the new row at
Add Multiple List Items from Microsoft Excel by Using Datasheet View
A useful way to use datasheet
view is to populate a list with the data from an existing spreadsheet.
If you have a spreadsheet with many items, instead of typing them one
by one using the method described in “Create a New List Item,” earlier in this chapter, you can use datasheet view to paste in the values.
For example, if you have a
Microsoft Excel spreadsheet that contains information about products,
and you want to copy that information to a SharePoint list, you can use
datasheet view to copy the information to the list, even if the columns
are not the same. To do this, open the spreadsheet and select the
information you want to copy (see Figure 5), and then select Copy from the Edit menu.
Figure 5. Select the Microsoft Excel information that you want to copy to the SharePoint list. Then copy it.
Next, switch the SharePoint
list to datasheet view, which shows the columns you want to paste into.
Remember that the view must be showing all mandatory columns for that
If the columns in
datasheet view are not in the same order as in the Microsoft Excel
spreadsheet, you must reorder them in datasheet view. To do this,
simply drag and drop the column headers until the order is correct so
that when you paste the information, it goes in the correct columns.
When the datasheet view is
ready for the data, right-click the first cell and choose Paste to
paste the data from Microsoft Excel (see Figure 6).
Figure 6. Right-clicking the first cell in the first data column and choosing Paste.
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 create
the list items. You can see the progress on the left side of the screen
in datasheet view. If an item has not been added yet, it has an icon
showing that it still needs to be synchronized with SharePoint. You
must wait until all the items have been synchronized before you close
the window or navigate to a different page.