Dangers of the Datasheet

DangersDatasheetThe Datasheet view is a favourite with Excel fan boys. I have seen many a controller or purchaser create one Datasheet view after another after they had learned about this fabulous functionality. And I have subsequently seen lots of accidents with Datasheets where I had to come to the rescue…

Because Datasheet Views can make it too easy to add, edit and especially delete content. Besides, they have some annoying habits…or should I call that “specific functionality”? 🙂

Let me show you the differences. This is a Standard View of a list:

Standard View
A list in Standard View

And this is the same list in Datasheet View. You will see some differences immediately, but there are invisible differences as well.

Datasheet View of the same list
Datasheet View of the same list

[Update September 2017: In SharePoint Online, you need at least Contribute permissions to even see the Datasheet View.]

It is important to design your Datasheet Views to minimize accidents and irritation. Think about purpose, process, audience, update frequency, backups and more.

Datasheet Design Do’s and Don’ts:

1. Make sure your column names are easily understood.
The Datasheet view does not show the description in New or Edit mode, so the column name is all information your users will get.

2. Do not use a Yes/No checkbox in a datasheet view.
Without a proper description it can be very unclear what a checked box means.  A “Yes/No” or “Open/Completed” choice field may be a better option.

To jog your memory, this is what the New or Edit Item looks like in Standard View:

New Item - look at the description field and the mandatory columns
New Item – look at the description field and the mandatory columns

3. Show empty required fields in your view.
This is especially important when people are expected to add new items.
Required fields that are not completed will not allow you to save your item. Besides, you can not move to the next line item before you have completed all information in the current line item. This makes quick “vertical copy” (as in Excel) impossible. See also item 4.

You have not completed the mandatory field.
You have not completed the mandatory field.

4. Add an * to the column name if the column is required.
If people only ever see the Datasheet View they will not know a column needs to be filled – it does not show as in the Standard View.

5. Avoid content approval and multiple-lines-of text fields in “enhanced rich text” format.
These can make your Datasheet View read-only. This post sums it up nicely.

This is the format to avoid:

Enhanced Rich Text format
Do not use this format for your multiple-lines-of-text column

6. Never make a Datasheet View your default view.
It is too easy to accidentally overwrite or delete items. It shows only a limited number of items on a page and you do not see any context such as hyperlinks and the item menu.
Instead, use a Standard View and inform your audience where they can find the Datasheet View.
[Update September 2017: In SharePoint Online, people who can only Read can not see the Datasheet View so an extra reason not to do this].

7. It is not possible to group in a Datasheet View.
If you want to show your content in groups, add the grouping column to the view, and sort first by this column. This way you have already used one sorting option.

You have to add the column that you group on
You have to add the column that you group on

8. If you need to bulk-edit on a regular basis, create one Standard View for editing purposes.
Use only those columns that need to be edited, so you can quickly turn it into the correct Datasheet View.
You can also create a personal view for this purpose.

9. Think 4 times about a Datasheet View if it is going to be edited by a large and unknown audience.
A large audience of unknown people is impossible to properly train or influence, so you will have to take extra preventative measures.
– Use a “Created By”, “Modified By”, or “Assigned to” =  [Me]  filter for the view.
– Create a page for each audience, filter by audience, and target the list webpart on the page, to the audience.
– Limit the public Datasheet Views in your list and hide Edit options from Standard Views.
– Remove the “delete” permissions from the Contribute role.
If those measures can not be taken, use a Standard View and ask people to edit their items individually.

10. Export your data to spreadsheet on a regular basis.
This will provide you with backup data and allows you to analyze the results and create graphs.

And do not forget training!

The Datasheet View may look like Excel, but it behaves differently. So provide training for your users to manage expectations and prevent accidents,

Training topics could be:

  • Column description, purpose and peculiarities
  • Editing frequency
  • How to import or copy data from Excel
  • Basic troubleshooting

Share your thoughts.

What are your experiences with the Datasheet View? Have I missed something? Please share!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s