3 Paths from Excel to a System

Previously I have discussed some of the reasons to move from managing data and process in Excel.

Here I want to discuss a few possible paths to take.

Each of the below are based on Microsoft Power Apps.

There are certainly many more options but It’s worth comparing the below options because they share many common features such as being cloud-based and integrated with Microsoft 365.

1. Power App using Excel

Power Apps can very quickly (and mostly automatically) create an app which will manage data that is in an Excel spreadsheet stored in OneDrive.

You will get a simple interface that allows you to list, add and modify items in the sheet.

From there you can tweak the app to have more functionality.

This is pretty cool for learning Power Apps and proof-of-concept.

I would warn against using this for anything other than personal productivity stuff because it retains many of the traps of using a spreadsheet for managing a business process.

When to use

• Personal productivity apps

• Proof of concept (validating an idea for an app)

Pros

• Quick to get up and running

• No premium license required

• Lots of tutorials on getting started

Cons

• Limited by many of the issues inherent to using Excel for managing processes and data

2. Power App using SharePoint list

So, this one is one step up from Excel.

You can do a lot with a SharePoint Online (SPO) list or a few lists tied together.

In fact, it can almost feel like you have a real relational database (spoiler, you don’t).

Because Power Apps can handle migrating across environments using variables, you can even get some good Application Lifecycle Management (ALM) in place.

SPO Lists are awesome. I encourage people to use them and consider where and when they can be of value.

They are like a spreadsheet with extra functionality but less formula functions.

Word of warning, the non-premium cost saving can make it very tempting to build business critical apps using SPO.

I would advise anyone to consider this carefully.

Your business-critical data should be in a proper database such as Dataverse or SQL.

The cost savings are quickly negated if you need to refactor to a proper database in the future.

When to use

• Personal productivity apps (sometimes)

• Non-business critical production apps

Pros

• No premium license required

• Can work well with development, test and production environments for stability

Cons

• Not a true relational database

• Need to consider the SharePoint administration and configuration

• Easy to mess up the permissions and expose or lose valuable data

3. Power App using Dataverse or SQL (Premium)

As far as I’m concerned this is a very solid option for any migration away from Excel.

Dataverse and SQL are true relational databases which means they have tables which relate together to manage and process the data in a more mature way.

They provide much higher levels of security and availability management.

Because you can model the data in many ways your systems will have much more functionality available to them.

A big plus for Dataverse is that you can join the data from multiple systems together to expand the functionality over time.

For example, say you build a simple app to manage inventory in a storeroom for internal equipment.

If later, you find the need to have a booking system for this equipment, much of the data is in place.

You can build some additional tables and interfaces to manage the bookings.

This can even be a separate app but the data in the background is shared with the inventory app.

When to use

• Business critical systems

• Important systems or systems sharing data

• Everywhere if you have the licences

Pros

• No need to manage infrastructure

• Security is very granular and tied to Microsoft 365

• Strong lifecycle management

• Common Data Model can improve development integration and speed

Cons

• Additional cost may be a barrier

Conclusion

There is much to consider when coming up with a strategy for moving from Excel to a more mature system.

I hope the above has helped you to have an idea of some of the factors to consider and when certain options might be valid.