(Part 3 of How to Use SharePoint series)

In the previous entry in this How to Use SharePoint blog series we took a look at utilizing SharePoint lists. A common complaint from users when making the transition from Excel is that editing information in an Excel sheet is far easier than in a SharePoint list. And for the most part, I will concede this point.

In a traditional view of a SharePoint list you have to open each record individually in order to make changes to the record. This can be quite cumbersome and tedious, especially with multiple records. There is, however, a view that emulates the form and function of an Excel sheet, Datasheet View.

On rare occasions (hopefully they’re rare) you may have to edit multiple records in a SharePoint list. A common example is when a new column is added to a list and all existing records need to be updated with new information. Another example is if a company name changes. There are many unique businesses case that require changing data for multiple records in a list. I’ve heard countless times something like this: “I had to edit all records that were listed as Closed because they wanted {whatever}.”

Management sometimes likes to do things after the fact… which is where Datasheet View becomes a time/life saver. In the later versions of SharePoint, this feature is just referred to as either “Edit” or “Quick Edit.” SharePoint 2010 refers to it as “Datasheet View.” Interestingly, the later versions also reference “Datasheet View” when creating a view. Anyway, terminology aside, let’s see how it works.

Before using Datasheet View it is often times advisable to first create a view. If you create a view that displays only the required information to perform the task you’ll save yourself time and eye strain. For example, if you are changing all items with a specific status, create a view and filter on that status. That way you won’t be looking at every record in the list before making edits.

In my example, I’m going to ensure uniformity in a column. This is a common problem when allowing multiple users to enter data. They may not all enter the information the same way. (Managed metadata would help this… but that’s a topic for a later conversation).

Have a look at my list.

Notice the difference in the Last Name column. Some entered the band as “The Drive-By,” others just have “Drive-By,” and some use a hyphen in between the words Drive and By. Although this may work from an informational standpoint, if you plan on running reports on the data in this list you may get skewed results. It’s best if all values are identical.

Before editing the records, create a view similar to I have. In this case, the view filters on all records CONTAINING Truckers. This allows me to see all the records that need changing. I could highlight each record:

Click Items > Edit Item.

Make the change on the form and click Save & Close.

That process will get old really quickly. A quicker way is just to use Datasheet View by simply clicking
edit this list.

Or you could click List > Quick Edit.

If the button is grayed, make sure you are using Internet Explorer. If it is still grayed, ensure you have permissions to the list by editing a single record the traditional way. If you verified permissions and the button is still grayed, try adding the site to your Trusted Zones in IE.

After you click the button and enter Datasheet View, the list is presented in a familiar Excel-like way.

Now change one record to the correct value and copy and paste that value into the other “cells” as you would in Excel. Because the value in the first record is what I want, I simply highlighted “Drive-By Truckers” and pressed CTRL+C. I then pasted (CTRL+V) to the other rows. The whole process took under 20 seconds.

When you’ve completed your changes, click the Stop editing this list link and you will be returned to the normal list view. Easy. Quick. Finished.

Some parting cautionary words about Datasheet View:

  1. Use Internet Explorer.
  2. The list is live when using Datasheet View. Any changes you make are saved in real time. There IS NO cancelling changes or Not Saving as there is in an Excel document.
  3. Required Fields are also required in Datasheet View. If you cannot inexplicably save a change, ensure that any required fields are presented in the View and that they all possess a value.
  4. Copying and pasting entire rows and columns may be problematic. I have had situations where this scenario works, and I have had it where it fails. There doesn’t seem to be any rhyme or reason to what exactly will work, especially when copying bulk items from an Excel document TO SharePoint.

Using Datasheet View makes it easier and efficient to change multiple records. It’s not as clean as Excel, but couple this ability with all the other benefits of a SharePoint list, and it becomes clear that the better business decision is to maintain data in a SharePoint list rather than an Excel document.

In the next entry on this series, we’ll take a look at exporting a SharePoint list to Excel. Confused? Stay tuned.

Let’s discuss your unique needs!

You can print it out for a handy reference!