You are currently viewing How to use Word and Excel templates in SharePoint

How to use Word and Excel templates in SharePoint

If you are using SharePoint and want to ensure, that your end users make use of your templates in SharePoint, you can make use of content types, to use word and excel templates in SharePoint.


Prerequisites

If you want to create use templates in your across multiple SharePoint sites, you need access to the SharePoint Admin center otherwise you just can create it for single sites.

You can use templates for word and excel files, which don’t have macros. If you have macros in your templates, you cannot use them as template for your content type.


What are content types?

You can imagine a content type as an object with meta data, which describes an object.

Example: invoice.

Meta data for an invoice are e.g. invoice date and due date.

Invoice

I will show you how to configure content types in your content type hub, so you can make use of word and excel templates in SharePoint!


Create a template

Before we start to use templates in SharePoint, we have to define a template. For our invoice, I took a template of Microsoft.

Invoice template
Invoice template

Create content type in SharePoint Admin Center

To create a content type visit the admin center

https://yourdomain-admin.sharepoint.com

for my domain it is

https://devmodernworkplace-admin.sharepoint.com/

Click on Content services -> Content type gallery

SharePoint admin center

Click on create a content type

Create content type

Give it a name, optionally a description define a category and set document content types as parent category and document as content type.

I chose to locate my content type invoice in the new category “DevModernWorkplace” category. You can also use the exisiting category document content types category. I would recommend a new category, so you see all your custom content types at one place.

Conent type formular

After your content type is created, I would recommend to add site columns. These site columns will show up in the library, where you will use this content type.

Create new site column

Add site columns to content type

I have added the meta data due date from the existing site columns and added invoice date as new site columns and added the new site column to the new dedicated category “Invoice Columns”.

Add existing site columns
Add existing column
Create new site column
Create new site column

Add template to content type

Now you can add your previously created template to your content type. Click on Settings and then on Advanced settings.

Advanced settings

Click on upload a new document template and browse to your template. After selecting it, save it.

Upload document template

Publish content type

After adding the template, publish your content type.

Publish content type
Publish content types

Republish content type

If you already published your content type, click on Publish.

image 103

Click on Republish

image 101

Switch to the site, where you have added the content type and visit the site settings. I am switching back to the sales site.

Click on Content type publishing.

image 95

Check Refresh all published content types on next update and click on OK.

image 98

Add content type to your list

I have added a list invoices to the demo site and as you can see the default content types are configured.

invoice library

In order to add the invoice template, click on the gear -> Library settings.

Library settings navigation

Click on Advanced settings

Library settings

Set Allow management of content types? to Yes, scroll down to “Ok” to save your settings.

image 78

Add your content type by clicking on Add from existing site content types

image 79

If you don’t see your content type, ensure that you have published the content type and if you have published it, wait 5-10 minutes.

Add content type to library

After confirming with OK, you can see the invoice content type

image 84

image 88

Bonus: Adding Metadata to your template

If you want to add metadata to your Word/ Excel template, create a document with your configured content type.

Add invoice content type

Click on editing -> Open in the Desktop App

image 90

Mark the area, where you want to add the metadata.

Click on tab Insert -> Quick Parts -> Document Property -> “Your Metadata”

I chose Invoice date

image 91
image 92

I have added invoice date and due date

image 93

Save the file locally.

Now republish the template in your content type.

there we are!

If you change the metadata in SharePoint, the document gets updated automatically.

Before:

image 111
Previous metadata in invoice

After:

image 109
After metadata in invoice

Date fields in the Library and in Word app are different

Check out, the regional setting of your site. You might have different time zones configured for your client and your site.

Conclusio

Ensuring that Word and Excel templates are used in SharePoint online can be achieved by content types, published in the content type hub.

This Post Has 2 Comments

Leave a Reply