Adding Excel file information to the header and footer

Adding file information to the header and footer of an Excel file can be done as follows:

  • Under the page layout tab
    • click the little arrow under print titles to expand your page setup window
  • Under the header/footer tab, pick custom header or custom footer
    • For each section, click the information you want to show.
    • To figure out what information each option provides, just click on the buttons.
      • In the center section, I added the word “Page” in front of the options
      • In the right section, I added the words “Last printed: ” in front of the date field.

Excel Header and Footer

Advertisements

Create a calculated field to show due dates first

When viewing task lists ordered by due date, often the tasks with no due dates appear at the top of the list, while the items that have a due date appear at the bottom. Sorting the list in descending order doesn’t help either, because then your items that are due soon will appear in the middle of the list.

There doesn’t appear to be an out of the box setting that will display items with a due date first, followed by those without one, so we get around this by using a calculated field column within the list settings.

Click on the List tab, then on List Settings

SharePoint: List Settings

Scroll to the middle of the page and add a new column. Give that column a name, such as HasDueDate

Choose for this column to be a calculated field and use the following formula in the formula field:

=IF(ISBLANK([Due Date]),”No”,”Yes”)

Calculated field: hasduedate

Save the column.

Sorting your list to include this new HasDueDate column.

The next step is to add this field to the sorting options on your list view.

SharePoint: Modify View

Scroll to the middle of the page, where the sorting options are, and choose HasDueDate from the first column option; then set that to descending order (you want “Yes” to come before “No”).

In the second sort column, choose Due Date in ascending order.

Now your list will always sort with the due dates first, in ascending order.

Standard and friendly date views

This tip applies mostly to task lists, but also applies to document libraries.

It is standard for SharePoint Online to present dates in a friendly format, such as Sunday, or February 15, Yesterday or Tomorrow.
SharePoint: friendly dates

Some people prefer to just see the dates themselves and don’t like seeing the friendly dates.

SharePoint: standard date

Here’s how you go about changing that.

Go to click on the LIST tab and click on list settings (in a library you would go to the library tab and then library settings.)

SharePoint: List Settings

Scroll to about the middle of the page to where the list of columns are, and click on the date field you want to change.

About midway down the settings screen you will see Display Format.

SharePoint: Standard / Friendly Date option

Change this setting as you need to, to set it to Standard or Friendly format.

Then choose SAVE

Adding a new color scheme to your site

This tutorial contains the steps for adding a color scheme to your team site. Our custom color just happens to be called DSA Hokie.

Web Designer Galleries

  1. To add the DSA Hokie color scheme, first go to the gear and choose Site Settings
  2. Under Web Designer Galleries, choose Composed Looks
  3. Click on new item

Complete the information as follows:

  • Title: DSA Hokie
  • Name: DSA Hokie
  • Master Page URL: /sites/dsa/base_public/_catalogs/masterpage/seattle.master
    • replace dsa/base_public with your site’s URL
  • Theme URL: /sites/dsa/_catalogs/theme/15/DSA_Hokie.spcolor
  • Image URL:
  • Font Scheme URL:
  • Display Order: 5

Composed Looks List

Now that you have created the color palette, now add it to your site.

  1. Go to the gear, then choose Change the Look
  2. Choose DSA Hokie, then Try it out
  3. Once you see the preview, then choose Yes, keep it

Using XSL to reformat information in a list

I first discovered the use of XSL formatting when we were trying to make links in a links app open in a new window. It’s funny how SharePoint lets you open other links in a new tab, but for some reason the app that’s geared specifically for the creation of links won’t open those links in a new tab.

Using XSL allows you to step away from always displaying your content in a spreadsheet-like list, and can help display your content in a more user-friendly way.

While working with XSL files is an advanced skill, and definitely not for the faint of heart, the toughest part of the process is making sure that you have the correct field names. The field name that is shown on the List View is not always the name of the field that SharePoint uses to display the fields. Therefore you need to find the actual SharePoint field name. To find that field name, go to the List Settings for the list you’re looking to display and then right-click on the name of the column you’re looking for. Copy the link and paste it in a notepad window. Your web address will look something like this.
/sites/dsa/CIT_P/_layouts/15/FldEdit.aspx?List=%7B9610E212%2D2C1C%2D488A%2D93B7%2D14EEBEE1C9E3%7D&Field=Company

The field name in this case is Company, and so you would use @Company in the XSL file to reference it.

For hyperlinks, the field actually has 2 components: the URL and the description. So for a field such as Web Page (below), you would need @WebPage as well as @WebPage.desc

/sites/dsa/CIT_P/_layouts/15/FldEdit.aspx?List=%7B9610E212%2D2C1C%2D488A%2D93B7%2D14EEBEE1C9E3%7D&Field=WebPage

When creating XSL files, it’s best to work from an existing file. When you need to create new fields, use syntax similar to that shown here.

Sample XSL Code

Then, to use those new fields in your page, use syntax such as this:

Sample XSL Code Use

The disable-output-escaping=”yes” portion of the code is invaluable. It will make sure that descriptions or text fields appear with HTML code intact. This means that links will work, as will ampersands (&) and other such characters. If you do not include that code, you would end up with HTML code showing on the page.

Handling spaces in field names with XSL

If your field name has spaces or other characters, you’ll end up with a field name that looks like this: Field=Buildings%5Fx002d%5FResLife

It looks a little messy but it’s just SharePoint’s way of handing these characters in field names. In order to work with this kind of a field name, replace the %5F with an underscore. A space will then look like _x0020_ and a hyphen will look like _x002d_ as shown in the image below.

Sample XSL Fields Spaces

Creating Email Templates in Outlook

I needed to send individual emails to a number of users who need to activate their Office 365/SharePoint user licenses. Sending a mass email to the group did not quite yield the results we were looking for, and so we’re now sending individual emails to the users. So far I have sent 43 individual emails to these users, and I am sure there are more to come. Sending these emails manually involves copying and pasting the subject line and content 43 times and adding the copied recipients 43 times.

To help me get this accomplished a lot faster, I created an email template so I don’t have to do all this copying and pasting.

Create the template

Templates are easy to create: you simply draft the email like you normally do. In other words, click New Email from the New Group on the HOME tab, type the message, adding any attachments, pictures, formatting you need. If you always carbon copy certain people, you can add them as well.

Email Example

When the email is ready, click the FILE tab and choose Save As in the left pane
In the dialog box, give your file a name, and choose Outlook Template (*.oft) from the Save As Type drop-down.
Click Save and close the mail window. You may be prompted to save it again. You don’t have to.

Outlook: Save Email Template

Using the template

When you’re ready to use the template, instead of using New Email, use the New Items drop-down from the HOME tab, choose More Items, and then Choose Form…

Outlook: Retrieve Email Template

From the Look In: drop-down choose User Templates in File System

Outlook: User Email Template

Select your template, and then click Open. From here, add your recipient, a greeting, edit your message if need be, and hit send. While it’s a tad tedious to retrieve the templates sometimes, in the end, it does save you quite a bit of time if you’re needing to send the same message over and over again.

Outlook - Email Select Template

OneNote – the Connected Learner

Here is another SWAY showing us how OneNote can be used for collaboration and in the classroom.

Just note that this teacher is using the CLASS NOTEBOOK app that exists within Office 365, and thus there are a few features that don’t exist in the standard OneNote app, such as read only content areas, and private student sections, which exist in the CLASS and STAFF notebook versions.

You’re welcome to work with the CLASS Notebook or STAFF Notebook apps within Office 365 but there’s just a small (ok, not so small) glitch with those: you are unable to transfer the ownership of a CLASS or STAFF NOTEBOOK to another person. This is a big deal to me: I’d like Microsoft to realize people do switch jobs occasionally and that these notebooks need to be transferred in a secure and seamless way. Hopefully they will work on that soon.

In the meantime, if you like what this presentation is about, and you want to use CLASS or STAFF NOTEBOOK apps, just know that while you can back up the Notebook, it loses its connections with the online setup.