Working Smarter – SharePoint vs. Excel

SharePoint is a platform which enables us to improve business process. On every process we collaborate with our own teams, our colleagues, the wider business and external entities. A platform like SharePoint can help us overcome boundaries caused by messy request management or geographic boundaries.

We’ve previously talked about the benefits of using SharePoint as a Document Management System (DMS) in a previous post here. The post explains the quick security, collaboration and information management advantages of SharePoint as your DMS compared to the traditional methods of Shared Drives.

However, there’s one Document Type in particular we can delve into a little more and ask ‘should it exists as a document?’. These are Spreadsheets. 99% of Spreadsheets are simply lists of information in Columns and Rows.

In the world of Excel, the biggest advantage of having those documents in SharePoint is being able to co-author – Users no longer have to notify each other when they’re in or out of a document which a whole team may use. I’ve seen these types of documents everywhere in the past decade, whether it’s tracking correspondence, customer complaints or a document index. A few years ago I witnessed a Customer Services department who had a coloured flag system which users selotaped to the top of their monitors to let the rest of the room know when they were in a certain spreadsheet – that spreadsheet tracked customer complaints.

So let’s stop and evaluate our Spreadsheets and see if we can work smarter. This post is about challenging you to think about the way you work - to work smarter. It’s all well and good taking steps forward but let’s think about how efficient our day to day processes could be. It may mean you leave on time everyday instead of updating your trackers!  

Scenario

So, I have in front of me a Spreadsheet. It’s a tracker. For every Customer the organization has, a new ‘Query Tracker’ is set up from a template (Excel Doc with 10 Columns).

1. A Customer Emails/Telephones in a Query on a piece of work.
2. The Administrator logs the Query into the Spreadsheet.
3. The Administrator emails the Project Manager the Query.
4. The Project Manager responds to the Administrator.
5. The Administrator logs the response into the spreadsheet.
6. The Administrator responds to the Customer with the PM’s response.
7. The Administrator updates the spreadsheet with response time/date.
8. Every Friday the Administrator emails a copy of the Spreadsheet to the team.

Straight away anyone can see that the efficiency here isn’t great here. Roughly estimating it may take around 30 minutes of an Administrators time per Query.

The organization having only 16 Customer Queries a week would result in a full day of time lost.

Where can SharePoint help me?

This is an introduction to SharePoint Lists. They work in the same column and row structure as an Excel Document, the Lists even have some of the Excel features to maintain them including validation and formulae.

We can input information into this List by either typing in like a spreadsheet (through ‘Datasheet’ view) or through Form Input. Using the form means information, we receive via email or the telephone could be submitted online.

Applying SharePoint to the Scenario

The ideal for our ‘Customer Queries’ is the Customer fills out an online form (we can make this available through the Internet). Customers may see this as more efficient for them rather than the call but the time taken for us deal with the queries is vastly reduced.

The electronic form allows us to get clearer data from the Customer through mandatory fields or guidance on filling out the fields which will result in saved time dealing with the queries and hopefully less backwards and forwards emailing.

The form is also saving the Administrator time logging the Query into the Spreadsheet, after all it doesn’t exist anymore – it’s a SharePoint List.

With SharePoint’s out of the box Workflow we can automatically notify the Project Manager that a Query is waiting for their response, saving the administrator another few minutes.

We can even say if the Response field is still blank after 3 days, lets send a reminder.

The Project Manager clicks the link in the email notification and provides a response in the form.

Again, coming into play is the out of the box SharePoint Workflow which pings the query and the response back to the Customer using the email address they provided in the original form. And guess what?.. no Administration time required!

Added bonus – We no longer have 100’s of spreadsheets for our customers, there’s a central place for all Customer Queries, made easy to read using SharePoint List Views.

Using the views, Completed Queries disappear from the ‘Open Queries’ view and we can search and filter by customer, type of query, project manager etc. using the filters just like in Excel.

And finally, we don’t have to share the Spreadsheet every Friday - anyone can view the Queries at any time, live in the working List. Again views come into play to show ‘Queries Closed in the last 7 Days’.

Further Thought

This is just one scenario where using SharePoint Lists instead of Excel Spreadsheets can help automate business processes. I’m not saying all Excel Documents should become SharePoint Lists, far from it. Any Excel Documents containing complex formula, pivot tables etc. should remain as Excel Documents – maybe think about surfacing those in an Excel Web Part.

However, the majority of Excel Documents throughout organisations are simple tables full of information. This is the type of Excel Document we can make useful with SharePoint.

Here’s a few other examples and a small explanation with each.

IT Assets Tracker – Normally contains Asset ID, Manufacturer, Model, Location, Assigned to User. Normally assisted by a second spreadsheet which tracks movement of Assets such as when it’s reassigned to another user. SharePoint Lists have versioning control which eliminates the second spreadsheet straight away if the first spreadsheet becomes a SharePoint List. Using the line by line security we can allow users to see Assets assigned to them for better Asset Management.

HR New Starter Form – A new starter in the business can log into a SharePoint Welcome Portal and fill out their New Starter Forms such as diversity survey, IT new user information, Company Car form etc. All these forms will create a row in the relevant lists, allowing automation through workflows and approvals to take place within the business.

IT Tickets, Reporting Safety Hazards, Requesting Purchase Orders and so on. Once the information is within a SharePoint List we can look at Workflows and line by line security in the same way as we did with Documents.