Using Word Online to Create Documents in SharePoint Online

Last time, I showed how to create an initial SharePoint site using the online version of SharePoint. This week, I’ll show how you can easily add and use documents in your document library using the Office products included in Office 365.

If you remember, the home screen of the site included a web part in the lower right corner with the title Documents. This web part displays the contents of the Documents library from the current site which is initially empty. You could of course upload existing documents from your local computer folders by clicking the upload button and either entering the name of an individual document, using the Browse button to navigate to and select an existing document or if you have multiple files to upload, click the link: Upload files using Windows Explorer instead. This option lets you drag and drop files using Windows Explorer from different local folders into the SharePoint Shared Documents library of the current site.

However, the real interest part is what happens when you click the New button to create a new document.

A popup menu appears allowing you to create a document using any of the Office 365 tools which includes Word, Excel, PowerPoint and OneNote. There is also a new option that lets you create an Excel Survey. While similar to surveys that you may have created previously in SharePoint, there are some difference. I will cover creating and using Excel Surveys in a future week. Let’s assume for now that I want to create a Word document.

When I click Word document, Word Online opens as shown in the figure below. Note that the number of ribbon tabs and the contents of the ribbons while similar to those found in your desktop version of Word are similar, there are not as many features in Word Online. For most document needs, you will find that the options provided in Word Online can get you through the creation of your documents. Similar reduced functionality exists for Excel workbooks, PowerPoint presentations, and OneNote notebooks using the online tools. I cannot comment on Microsoft’s intention of eventually duplicating all of the functionality of their desktop Office tools in the Online versions. One can only hope that being online will allow Microsoft to add new functionality as soon as it becomes available, not just in fixed interval releases that can be years apart. Perhaps in the long term, software versions as we currently know them will become a thing of the past as new functionality is added immediately.

In the above figure, I created a short document and I am now ready to save it. Being familiar with the desktop version of Word, my first though is to open the File menu and select Save As. However when I do this, I see the following screen which tells me that I can download a copy of the document to my local computer or I can create a PDF which I can also download to my local computer. Then the dialog asks me, “Where’s the Save button?” Where indeed? It then says that there is no save button because the document is automatically saved.

Where is it saved? Well, remember that I started from a web part that was displaying the contents of the Shared Documents library of my online SharePoint site. That is exactly where the document is saved.

You may have also noticed that I was not asked for a document filename. It fact, it is just called Document. Not very descriptive. I’ll get to what I should have done in a moment, but remember that every document has properties and that you can always edit those properties from within your document library.

Let’s first navigate to the Shared Documents library. A fast way to do this is to click the web part title: Documents on the Home page. Now the entire page is devoted to showing just the Shared Documents library contents. Note the three dots (ellipsis) to the right of the document name. I can click on these dots to open the dialog shown below. This dialog shows me a preview of the document’s contents along with some information about the document. Finally at the bottom of the dialog are some commands to allow you to edit the document, define permissions for the document, get notifications about changes to the document by following it, and another three dots for additional commands.

Opening this second ellipsis, I see a familiar popup menu with commands that include the ability to view and edit properties of the document. In this case, I want to edit the document’s properties to change its name.

The Edit Properties page only displays two user editable properties, the document name and the document title. The difference between these two properties is that the document Name is the physical filename of the property as it is saved in the document library. Generally I recommend that the name be short and that it does not include spaces since spaces in file names require special treatment in many applications. You can see that the name includes the suffix .docx indicating that the document is a Word document. You can store almost any kind of document in a library and the file’s suffix will appear here but cannot be changed. The second property is called Title and by default is blank. However, you can enter any descriptive text you want to use to identify the document. It does not have to be short. It can include blanks. It should be user friendly. When document libraries display their contents, you can display either the Name or the Title or both. In some cases, you may want to ‘hide’ the real filename which may not be user friendly anyway from the user and only display the more descriptive Title property.

Having changed the document’s name and added a title, I may want to modify what properties of the documents I show when the library contents are listed on my home page. I can do this by clicking the ellipsis immediately after the text: All Documents. This pops up the menu shown below which allows me to either modify the current view or create a new view of the library. Let’s modify the current view.

In the Edit View dialog, you can change which columns appear as well as the order in which those columns appear. In addition, you can filter and sort the contents of the library as well as define other display properties. This dialog includes options you may already be familiar with from working in the on premise version of Sharepoint.

For this example, I will click the Display box to the left of the column name Title to include it in the view and I will define it to be the third column displayed from the left, right after the document name, as shown in the following figure.

Now when I display the contents of the document library, both the name and the title of the document appears. Note however, this will have no effect on the properties displayed for documents on the home page’s Document web part. Why? Because the view used on the home page was different when that page was created. It is not automatically updated just because we updated the view in the Shared Documents library. Think of a cookie cutter. When you use a cookie cutter to cut out a dozen cookies and then bend the metal into a different shape, your new cookies will have the new shape, but the old cookies have the old shape. View definitions are the cookie cutter used to define the fields and other properties of a list displayed on the page at the time the list was added to the page. In a future blog, I will show that you can change the view on the home page to either match the current view definition in the library or even to create a new custom view that does not exist anywhere else.

Finally, what should I have done to name the document when I created it? Perhaps you did not notice the word: Document in the heading of the Word Online screen. Maybe you just thought that Word was trying to remind you that you were creating a document. Actually, that word, Document, in the middle of the header was really the default document name. If you hover over it, a tooltip style box appears telling you that you can click on it to perform a Rename File action.

Simply select the current filename and type in a new filename as shown below.

Since I performed this last action on a new document, my home screen now shows that I have two documents in my Documents library, one that I renamed by going through the properties and one that I renamed directly from within Word Online.

That’s all for this week. C’ya next time.






Creating Your First SharePoint Site on O365

I was recently asked if I had done anything with Office 365. At the time I hadn’t, but I do have the opportunity to try it. So starting this week, we will take a series of looks at working with SharePoint online.

Obviously, the first thing you might want to do if you have Office 365 is to create a small collaboration site for you and your friends to share documents, calendars, tasks, etc. So let’s start at the beginning by opening Office 365.


The number of office tools/apps displayed in the Office 365 banner is a function of your screen design. So as in the above case, you may not see anything that looks like it might be SharePoint. However, notice the three dots (called ellipses) to the right of the work Outlook. Just as in SharePoint 2013, this icon means that other options exist and you just need to click on it to view them.


The resulting dropdown menu shows the other tools/apps available. Scroll down and click on the command Sites. This action opens a window showing you SharePoint sites you may have access to as well as sites that you may have created.


In future weeks, I will look at working with existing sites and how to share your site with others, but for today, I will create a new site by clicking on the New button in the upper left portion of the screen. As you can see below, the first thing you need to do is to assign a name to your site. For demo site, why not call it: SharePointMike and then just click Create.


You will first see a dialog on the screen informing you that this should not take too long. After a few seconds (or perhaps a minute or so), you should be returned to the initial screen you saw after clicking on the command Sites. Only now, you should see a listing for your new site that looks something like the following:


In fact, that is all you need to do to create a basic site. To enter the site to start adding content, simply click on the SharePoint icon or by clicking on the site name. When the site opens, you should see a set of icons across the top of the screen that help take you to common tasks you probably want to do in your new site.


Also notice that by default, the site include a Newsfeed web part and a web part to list the items in your Documents library. Of course, both of these are initially empty. I will come back to these in later weeks.

Let’s first click on the icon that displays the text: Your site. Your brand. This icon is the second from the right in the above image.


This page looks very familiar if you have ever worked in SharePoint on the premise. It displays sections to let you change the site title, create a site description, change the site logo from the default SharePoint logo, and even reset the web site address.

Recommendation: When I create a new site, the initial name that I enter for the site is actually the name I want to use in the URL. Then I come to this screen and change the Site Title to something that might be more user friendly.

If you have a logo you want to use for your site, now is the time to set it. If the site is going to be a personal site, you may want to upload a picture of yourself as the site logo so people coming to your site recognize you. Notice that in the section where you can insert a logo, you can upload the logo directly from your computer. You do not have to first upload the image to an images library in SharePoint before you use it. SharePoint can do that work for you. So for example, if I want to grab a logo from my local machine, the next dialog which lets me browse to my desired logo looks like the following.


I can click OK to close this dialog and then click OK on the dialog that displays the site settings to complete this set of actions. Next I might want to change the site style or color scheme. I can do this by clicking the What’s your style? Button from my site’s home page.


This first opens a page that displays a selection of different templates for your site. The following image shows only a portion of them.


After you select one of these templates you can further customize the color scheme by choosing from the available color combinations along the left side of the screen.


You may also have some options for the Site Layout and Fonts used by the pages. I will let you explore some of the different options yourself. The following image displays the options I chose to customize my site.


When I am done, I can save my changes and return to my site’s home page by clicking Home in the top banner to display my new home page which now looks something like the following.


That’s all for this week. Next time, I will drill down into using more of the features of this site such as how to use Word Online to create documents that I will then save into my SharePoint document library.

C’ya then.


SharePoint’s Calculated Columns

In SharePoint, you can create a calculated column for any list or library. A calculated column can use values from one or more of the other columns in the list. For example, one of the first uses I found for using a calculated column was when I wanted to sort by more than two columns in my view. For example, suppose I had a library of project files that included the following fields:

Calculated Columns 01

In my view, I want to sort first by [Project_Name], then by [Year] and then by [Title]. When I attempt to create a custom view, I only get to choose two levels as shown below:

Calculated Columns 02

This would sort my files first by project, then year, but the actual documents would appear randomly by document name. The way to solve this problem is to create a calculated field to concatenate two or more of the columns that will be sorted in the same direction (either both ascending or both descending). For example, I might combine project and year into a single calculated column as shown in the following figure:

Calculated Columns 03

Now I can go back to my new definition and use [Project_Year] as my first sort and [Title] as my second sort. In the columns that I display, I can still use [Project_Name] and [Year] as separate fields and not display the calculated field [Project_Year].

Calculated Columns 04

Note the use of the ampersand operator to concatenate the two string fields. (Yes, [Year] is stored as a string in this list.) If I had used a plus sign as the operator, I would get an error because the project name field does not begin with a number even though the [Year] string can be in theory converted to a number.

The net result is a view that appears to sort my files by project first, then year, and then document Title. There are other web parts where you use a similar trick such as the Content Query web part.

When to use the ‘+’ vs. the ‘&’ operator

To illustrate the difference between the plus sign (+) operator and the ampersand (&) operator, I will use a very simple table that has several string fields and several numeric fields as shown below:

Calculated Columns 05

The columns [String] and [Number] are both stored in the list as string data (note the left justification of the numbers for the [Number] column. On the other hand, both [Number_Col] and [Number_Col_2] are numeric columns as you can discern by the right justified values.

Next I am going to create several calculated columns using the following formulas:

        Calc1 = [Number] + [String]

        Calc2 = [Number] & [String]

        Calc3 = [String] + [Number_Col]

        Calc4 = [String] & [Number_Col]

        Calc5 = [Number_Col] + [Number_Col_2]

        Calc6 = [Number_Col] & [Number_Col_2]

While SharePoint allows me to create each of these formulas, displaying the resulting values in my list shows some interesting results.

Calculated Columns 06

As you can see from the results, attempting to use a plus sign (+) when concatenating two values in which at least one is a string results in an invalid value (#VALUE!). On the other hand, concatenating a string and a number is no different than concatenating two strings or even two numbers when using the ampersand (&) operator. These results were the same no matter whether I choose the final result to be a string or a numeric value when defining the calculated column.


Next I tried to use the [TODAY] variable to see if I could create a calculated column that would add the numeric value in [Number_Col] to display a future date. When I tried to save the formula for the calculated column, I got the following error:

Calculated Columns 07

Note that this error message tells me that I cannot use volatile functions like [TODAY] or [ME] in a calculated column. However, when I tried to use the function TODAY(), the calculated column was not only accepted when I saved my column definition, but the data displayed as expected when I displayed the list.

Calculated Columns 08

I also tried many of the other Excel-like functions to work with strings and numbers and found that all of the ones I tested also worked as expected as long as the function arguments (columns) were of the correct type.

That’s it for this time. C’ya next week.

Default Column Values vs. Column Validations

Last time we talked a little about default values and showed how you can calculate the month name or day of the week from the current date using TODAY. If you added a record or two to the list using the default value, perhaps you noticed that even when you came back to the record a few days later, the default value had not changed. Why? Because the default value is defined at the time the record is created, not at some future time when someone views the record. Effectively, once defined for a new record, the value remains set unless a user manually changes the value of the field.

Perhaps you tried to create a default value using the value of another column in the list. For example, suppose you had an event list in which the past due date for any money owed to the event was seven days after the date of the event. Further suppose that you had a column named EventDate already in the list. Your first thought might be to create a column for the Past Due Date to have a default value calculated as EventDate + 7 or seven days after the event date.

Additional Column Settings

When you try to save your column definition though, you will get an error like the one shown in the next figure.

Something went wrong message

Why did SharePoint not allow the use of the value from a previous column in the default value of the current column? Simply because at the time that the default values for the new record (item) are set, which is before the user enters any values for any of the columns in the new record, the EventDate column does not yet have a value and is undefined. Therefore, SharePoint cannot use the EventDate column to calculate default value of the Past Due Date column.

So while I can take predefined variables like TODAY and use them in the calculation of a default value, I cannot use values from other columns in the list because those values are not defined at the time the default value must be calculated. That also further emphasizes why default values do not change over time, even those based on variables like TODAY, because they obtain their value at the time the default is calculated and that calculation is not performed again just because someone looked at the list record.

Let’s now take a look a column validation. This feature of lists was a welcomed addition to SharePoint 2013 and provided the ability to trap bad values at the time that data is entered. As developers know, trapping bad data through data validation at the time of data entry is far superior to running reports later to discover and fix bad data after it has been entered and the user has moved on. However column validation is often overlooked in SharePoint when defining columns. Perhaps part of the reason is that the area that defines column validation is by default closed when defining the column properties. You must click the box before the words Column Validation found at the bottom of the page to open this section. Second, it is often not clear to the user how to create a column validation expression. Let’s take a look at a simple example.

The first thing to understand is that you must create an expression that is either true or false. In the text of the Column Validation it implies you can enter an expression like [Company Name] = “MyCompany”. However, I have not found this type of expression by itself to be valid. Rather you must enter an expression that begins with an equal sign and then continue with an expression that will evaluate to true or false. Therefore, a valid version of this expression might be: = [Company Name] = “MyCompany”.

Note that the field name, even when self-referencing the current field must enclose the column name in square brackets if the name contains spaces. You can omit the square brackets if the column name does not contain spaces. Also the expression to the right of the first equal sign is not considered an assignment of a string to the column, but rather as a Boolean comparison expression. We are basically asking in this example whether the Company Name is equal to “MyCompany” , not setting the Company Name to a value of “MyCompany”

Suppose you want to validate the Event Date column of the list we previously talked about. You may want to make sure that the event date occurred in the future. Your first thought might be to create an expression that checked if the Event Date was greater than the current date as defined by the variable TODAY as shown in the following figure.

Column Validation using variable TODAY

This expression would also fail because it will try to treat the variable TODAY as a column name.

Something Went Wrong When Using Variable in Column Validation

However, there is a TODAY() function in Excel. So making the following slight change to the column validation will allow the updated column definition to be saved and will guarantee that event dates entered will always be greater than today.

Column Validation Using Function TODAY()

So to summarize, what do we know at this point?

  1. You cannot reference another column in the list within a default value expression.
  2. You can use default variables like TODAY and ME within a default value expression.
  3. You can use many (I have not yet checked them all) Excel functions within a default value expression.
  4. You cannot reference another column in the list within a column validation expression but you can reference the current column.
  5. You cannot use default variables like TODAY and ME within a column validation expression.
  6. You can use many (I have not yet checked them all) Excel functions within a column validation expression.

C’ya next time when I look at Calculated Column expressions.

Default the Month

It has been awhile since I posted anything new.  Ok, it has been nearly a half year.  Perhaps as my new year’s resolution, I will try to get back on a regular basis here.  It may not be twice a week like before, but I’ll try to do something weekly.

I’ll open the new year with a short tip. Suppose I wanted to create a list which had Month as one of the fields and I wanted to default that field to the current month. Obviously I cannot simple enter the name of the month as the default value, but I notice that the Default property has the option of defining a calculated value. So I begin by creating a new field named Month2 and declare it to have the type: Choice. Why Choice? To eliminate the chance of misspelling the month name should the user need to enter a month name other than the current default month.

After choosing the data type for the field, the property settings for the column appear on the second half of the screen as shown in the next figure. Note that I entered all of the month names in the list box containing possible values. I also chose to use a drop-down box with no fill-in values. After all, the month name must be one of the values in the system. However, the important property is the default property which appears close to the bottom of the list. Notice that I chose not to allow fill-in values.

The calculation of the default value looks a lot like an Excel expression. It uses the special variable: TODAY which returns the current day’s date. Next it uses the TEXT() function which has two parameters. The first parameter is the date for which I want the month returned. In this case, I can use the special variable TODAY. The second parameter identifies how to format the date. In this case, the string “MMMM” will return the full name of the month. (Note: “MMM” returns the 3 character month abbreviation, “MM” returns the month number with a leading zero such as ’01’, ’02’, etc.)

After completing all the field properties and clicking the OK button, I can open the list and add a new record. As you can see in the figure below, the form opens with a default value in Month2 (screen images were captured back in July 2014). Note that the default value, although calculated, cannot depend on the values from another column since at the time the form is instantiated, none of the other fields for the current record have been entered.

The value on the form is a true default as you can see when you open the dropdown for Month2 which shows the month of July as being selected.

Can you add a dropdown for the day of the week as well? Sure, just change the formula to:

= TEXT(Today, “DDDD”)

You might even want to a field to default to the current year, last year, or perhaps next year.  Hope this little tip on how to work with calculated defaults for date fields helps.

C’ya next time.