Calculated columns are a powerful part of the out of the box feature set of SharePoint foundation 2007, 2010, and 2013. Any SharePoint deployment can benefit from the use of this powerful tool. In this course, we will go through some real world based examples to show how these formulas can be used. The course goes in-depth in each of the supported areas. SharePoint calculated column formulas are based on the Excel functions and syntax, however only a subset of these is supported. This course is about mastering the supported formulas in a short period of time.
Spike has been working in depth with SharePoint since 2007. When he is not teaching SharePoint he is writing books, courseware, blogs, and articles on SharePoint, or implementing it as a consultant. He is the head of the Arizona SharePoint User group that meets monthly. He is in the final stages of writing a new course for SharePoint 2013, as well as his first SharePoint 2013 book.
Section Introduction Transcripts
Section Introduction Transcripts
What Are Calculated Columns? Hello, my name is Spike Xavier, and in this course I will delve into the exciting world of calculated columns in SharePoint. Calculated columns are those that get their values from a formula. This formula can be as simple as grabbing the value in another column in the list to crunching some complex mathematical formulas in order to determine the value, and many things in between. You can use calculated columns to concatenate string values, add numbers, determine the difference in times, and much, much more. This is a very valuable skill to bring to any SharePoint appointment. Calculated columns can drastically reduce manual data entry for column values, which are prone to user error. Besides all the typing saved, calculated columns are fun. In this course, I will go through different categories of calculated column formulas. I will use examples that you're free to follow along with in a test environment if you have access to one. I will cover conditional formulas, date and time formulas, mathematical formulas, text formulas, and finally, advanced calculated columns. Here, I will go over some scenarios where I've used calculated columns in a creative or outside the box way. The idea I had in making this course was to give the viewer an insight into how to get started using calculated columns and how to benefit immensely from this skill in the shortest time possible. I have used almost every single thing I will show in this course in my experience as a consultant and a trainer in SharePoint. I really enjoy calculated columns and I hope that you find this course a valuable use of your time. Calculated columns are fun.
Which Formulas Are Supported? In this module, I will go through each different category of formulas that can be used to create Calculated Columns in SharePoint. The formulas used are a subset of those formulas available to us in Excel. Not all of the formulas in Excel are supported in SharePoint Calculated Columns. I normally consult the official documentation when I'm not sure or I just try the formula in my test environment and see what happens. Calculated Columns are a class in SharePoint the SPFieldCalculated class. The formulas used by Calculated Columns to generate a value are a property of that class. So, when a formula is created, it's really setting this property. If you're developing solutions for SharePoint with code, this is a helpful concept to grasp a hold of. For the purposes of this course, it's just nice to know, in order to help to understand what is occurring behind the scenes, so to speak. Generally, the formulas fall into one of the following categories, conditional formulas, date and time formulas, mathematical formulas and text formulas. In the next few movies, we will take a look at each one of these with an example. So, in review, Calculated Columns in SharePoint are really a subset of the powerful formulas the we find in Excel. They're a property of the SPFieldCalculated class and they generally fall into one of four categories, conditional formulas, date and time formulas, mathematical formulas, and text formulas. Next, we will see some examples from each of these categories.