Shadow IT is a big problem for enterprises. Employees are getting more and more tech skills each year, and will apply these skills to solving problems - either because they need something and don't know the best way to get it built by IT teams, or because they are frustrated with going through proper channels and either being denied or given delivery dates too far in the future.

The number one most popular 'developer tool' in the world, which is used to build more 'software' than any other may surprise you - is Microsoft Excel. Millions of people build solutions to their problems using Excel, either using pages and pages of formulas, or writing VBA or JavaScript code. This may server their needs but it comes with a stack of problems:

• Backup: usually the spreadsheet is on one persons machine, if that goes their work is lost
• Version control: There usually is nothing more than naming conventions such as MySpreadsheet_12.xlsx. No history, no way of going back to a working version.
• Sharing the work: work is shared via email. This means updates to fix bugs are missed, other changes are made, and different workers can be using different sheets giving different numbers for the same inputs.

Moving away from Excel is a hard problem as it is prevalent in the workplace. Personally I spent a year at a global investment bank working on a platform to ideally replace Excel across the entire investment and retail bank. It would help, but this is still a massive undertaking. Users are also very comfortable in Excel, so if a new system was built for each of their needs then they may not like the new system and revert back to their old friends the spreadsheets.

The solutions to the Excel problem really involve migrating away from Excel piece by piece, keeping the spreadsheet as the front end that users are comfortable with but factoring out the back end and logic to a more consistent place, with proper version control, ability for fixes to be deployed etc. This will need buy in from IT, but is less work than a rebuild of a new app, and allows the users to change their front end where needed to suit the ever changing business.

But where to put this business logic? And how does it integrate with Excel? Well, Azure Functions has you covered!

Azure Functions

Azure functions are a great way to build and host code, with access control, scaling and everything you need available for a very low cost. You can build an Azure Function as a REST endpoint to host the business logic from Excel.

Once you have your logic there, you can access it from Excel using a new feature in Office both on the desktop and web - integration of Azure Functions into Excel custom functions.

You can read the announcement here:

developer.microsoft.com/office/blogs/office-add-in-development-community-pnp-october-2019-update

Want to try it out? All the code to get started is on GitHub:

github.com/OfficeDev/PnP-OfficeAddins