Migrate Microsoft Excel Tracking Spreadsheets to Office 365

Every organization needs to track business processes, and many businesses use Microsoft Excel because it’s straightforward and flexible. Plus, most computer owners already have and understand how to use this software—but there’s another option that’s even better.

The Difference Between Office 365 and Excel

Office 365 has similar functionality to Excel, but it’s all online and functions as a cloud.

The primary issues with Excel are:

• File-based setup, so you must know where the file is stored and make sure you’re using the right version. This causes files to get copied and moved around a lot.
• Confusing control and permissions settings
• Provides passive information. Someone must make a note of significance in the data and act on it.
• No integration with the actual business process.

SharePoint lists are functionally identical to a spreadsheet, as illustrated below. Information is organized in rows and columns and can be edited in an ad hoc manner just like in Excel, like below:

Additional benefits over Excel:

• Stored in one stable location with a sophisticated security model.
• List can be referenced and even displayed in Teams and Office 365.
• Easy to add columns and validation rules.
• Changes can be tracked with versioning—know who made changes and when; has the ability to restore previous versions.
• Can attach workflow automations

Features Unique to SharePoint Lists

SharePoint includes many unique features, including:

Naturally Implemented Features

These are features that exist within Excel, but they require advanced development skills. All of the following are built into SharePoint:

• Multiple views: You can hide/show and re-order columns, as well as custom sorting and filtering. Different groups of users can see exactly what they need to get their jobs done. Individual users can even create one or more custom views seen only by them.
• Integration: The People/Group picker was mentioned previously. You can do lookups into other lists to eliminate duplicate data entry. For example, picking a product from a catalog list instead of typing the SKU, name, and price over and over.
• Alerts: Notify people when items are added, removed, or changed.

Accountability

Every SharePoint list item has a Created By and Modified By columns, as well as dates and times. If you use workflows, SharePoint also tracks that information. 

This feature makes it possible for process managers to see how long various process steps take and identify bottlenecks. You can gain insight into processes which is very beneficial for your business.

Managing the Transition

There are three ways to migrate the data from Excel to SharePoint. Complete some initial data clean-up before migrating to simplify the process.

If your first couple of attempts are unsuccessful, it’s easy to delete the new list, tweak some things, and try again since SharePoint is user-friendly.

You have a few options when transferring your spreadsheets:

• Use the SharePoint spreadsheet import tool, which does all the work in one step.
  
• Create the list manually and use the clipboard to copy data from Excel into the list.
  

Study up on SharePoint list features and start fresh. Consider basing the list on the built-in task template which adds useful task management features. Investigate the field types and leverage their built-in features. For example, use the People Picker field instead of typing people’s names.

Typical Objections

Sometimes it’s hard to get your team members to change habits because they’re familiar with the systems that already exist. Here are four common objections and how to handle them:

• My managers love spreadsheets! They do charting and graphing and calculations.
  
  With one click of a toolbar button, any SharePoint list can be exported as an Excel spreadsheet, which has live data refresh functionality.  Additionally, SharePoint lists can be used as a data source in Power Query and Power BI. This makes it possible to join a list with other data sources.
• We need printed reports with headings and totals and subtotals! SharePoint lists don’t print well.
  
  Lists can be data sources in reporting tools, like Power BI and Microsoft Access, and Microsoft Excel.
• Doing a lot of data entry into a web page is tedious!
  
  Any SharePoint list can be a linked table in an Access database. SharePoint lists have a quick edit option that turns the list structure into a datasheet mode, just like Excel. It also has copy-paste, drag fill, and undo/redo functionalities.

Other Office 365 Options

SharePoint lists are functionally identical to process tracking spreadsheets. However, there are other Office 365 features that work more effectively elsewhere, like Planner Boards and To-Do Lists. 

Before investing significant time into a SharePoint list, review how these products work so you can understand the system better.

Case Study

The following spreadsheet was used by an orthopedic healthcare provider to track patients end-to-end through their process. It contains:

• 50+ columns and hundreds of rows
• Color-coding to help alert staff to important data. 
• Manual movement between tab sheets using copy-paste 

Blue Net converted this spreadsheet to a single SharePoint list with numerous views, each customized by staff that worked during different stages of the process.

This is the original spreadsheet:

Users had to roll and scroll constantly to find the information they were looking for.

Here’s an example of a view customized for use by the production management team:

Color coding is still a good idea to help identify problem areas or otherwise alert staff to important information, and SharePoint supports it.

Where Can I Get Help Switching to SharePoint?

Blue Net helps businesses of all sizes transfer their spreadsheets to SharePoint because we want to simplify your processes and decrease the amount of time spent looking for information.

Contact our team today to learn more about how we can help your business make the switch.