Lookup Columns

A lookup column can be very useful for avoiding duplication and ensuring a consistent data set. To use lookup columns a second list on the same SharePoint site is needed to look up against. This maybe useful if you have a set of addresses and building names and based on the building name you would like to pull the full address into another column.

To work with lookup columns first build the second list with the data you desire, then go to your main list add new column select lookup and give the column a name then select the source list to lookup to, and the column you would like to display.

The last part is if you want to pull any additional information form the lookup list to your main list, to do this select the ‘More Options’ drop-down arrow on the create a column view and select the columns from the source list that you would like to display.

Now when you create a new item another field will appear with a lookup to the source list and pull any additional fields into the main list that you need.

Creating lists from Excel spreadsheets

One of the great things about lists is the ability to create lists with data you already have in Excel, the first thing is to make sure you have all the data in the Excel file is stored in a table format.

Once the Excel data is formatted in a table go to lists select new and then select the button ‘From Excel’ upload the file then go through each column and choose the column data type such as text, number, choice option or not to import, click next and you now have your Excel data in a Microsoft List.

Creating Views from Lists

Views enable a different perspective on the same data set, for example you can hide all items where a column value is equal to complete, you can have many views for the same list, filtering is one important aspect to views, but other elements include calculations on column values you can count items, if the column is numerical a sum or average value can be calculated.

A view can be used to hide columns, so the overall view is condensed to only the data you need to see, sorting information is also a part of a list view.

By default the list is All Items to create a new view select where it says all items and at the bottom of the list select ‘Create New View’ give it a name and configure any sorting, filtering, hide/show columns, group the information by a specific column or set an item limit.

Conditional Formatting

Formatting rows of data with colors based on column level logic is something all Excel users will be familiar with, in lists it is very much the same formulas as we see in Excel, conditional formatting happens at the view level so any conditional formatting is based on the view not on the entire list. To add conditional formatting select the down arrow located next to the funnel icon and select ‘Format Current View’ and then select the column which will be used to dictate the row colour then add the logic, if it’s a choice you can set this to equal to or not equal to column value, if it’s a date it can be set to a relative or a fixed date or between a set range then do this.

We also have column formatting so on the column level if the value is x then show the column box just for that column as a set colour or icon to do this click the down arrow next to the column then select ‘Column Settings’ then select ‘Format this column’.

List Settings

Behind each list is a lot more configurable options within settings, we will look at some of the key settings here:

Advanced settings some of the key features of this is the ability to allow users to only read items they created meaning they only see their own items or to create and edit all items or only the items they have created, note this is unless the user is a site owner, or custom permissions have been applied to the list.

Specify to disable comments as well as disabling the ability to upload attachments on list items and hide the list items from SharePoint search.

Validation settings allow to set conditions on what data can be added, this is based on formulas very similar to the formulas within Excel, an alert will be displayed to the user if the validation fails.

List Permissions

The default behaviour for list permissions is to inherit from the SharePoint site group permissions or team level permissions, but these can be overridden to do so follow the instructions below:

Go to the list click the settings cog to the top right, select ‘List Settings’ next select permissions for this list, then select the ‘Stop Inheriting Permissions’ Button located to the top left, a pop will appear click ok.

Now that permissions are not inherited from the main SharePoint or teams site you are free to remove the Visitors and Members and add in users directly, to add users in click the ‘Grant Permissions’ button (top left).

Note when removing permissions please don’t remove the Owner’s level permissions as this could cause problems, also ensure you are the owner of the SharePoint or Teams site when making permissions changes.