SharePoint lists and libraries (document, form, picture, etc.) make use of Microsoft Office code libraries to provide some of their more powerful features. If you have never used the datasheet view of a document library or list to paste metadata into columns you have been missing out on one of the most powerful features of SharePoint when it comes to mass editing and updates. (Did you know for instance that if you have a calculated column in a SharePoint list that in the datasheet view you can double click to edit the expression?)
It’s impossible in many cases to differentiate between the behavior of a SharePoint list in the datasheet view and an Excel worksheet, with the notable exception of the UI surrounding the data cells.
Now before you all post a ton of comments describing all the very specific ways in which SharePoint datasheet views and Excel spreadsheets do not behave the same, please note: I said in many cases. I didn’t say in all cases or even in the majority of cases. I simply said many. That means more than five. So please, no comments about the fact that you can’t use SharePoint list columns to do nested sorts. Guess what, there are many things you can do in Excel that you can’t do in SharePoint spreadsheet views.
Back to my point before I end up writing a lengthy blog. One of the more interesting features of SharePoint is that of the calculated column. I suspect that this feature receives far too little attention in most SharePoint implementations. Why do I say this? Because I believe that, in general, calculated columns are used for very basic calculations like =[Today]-7, which yields the date 7 days previous to today. A few calculated columns might contain something like =[Units on Hand]*[Unit Price], but I suspect not many do.
What’s more many people who create SharePoint lists and add custom columns aren’t aware that calculated columns can contain not only formulas like the product of unit-price and units-on-hand, but can also contain functions. [Today] is a function for that matter.
What functions can be used in calculated columns? Well, jump back to my first paragraph. It’s impossible in many cases to differentiate between the behavior of a SharePoint list and an Excel worksheet. Many of the functions you find in Excel can be used in SharePoint calculated columns. In my next post I will show some specifics but in the meantime go ahead, give it a shot.
