SharePoint as the Ideal Platform for Citizen Development

In my 28 years of experience in the banking sector, I've learned that it's impossible to get rid of the pernicious Excel. Yes, Excel, whether we like it or not, acts like the duct tape that closes the gap between what corporate systems offer and what the user actually needs. IT is very decisive about making small system changes and simply tolerates the existence of Excel in a symbiotic relationship with users.

The problem with Excel is that it's EVERYTHING and at the same time NOTHING. It's a product that sooner or later will cause problems due to its universality. Joking aside, I always maintain that all technology projects have the same goal: to get rid of Excel.

As a Data Architect, I've realized how disruptive and dysfunctional Excel can be in organizations. It's a bipolar product that will eventually cause issues, is hard to integrate, and maintain consistency. I've written many articles on this topic on my blog, which I recommend you visit to get a laugh at how absurd the corporate world can be.

Excel is a bipolar product that, sooner or later, will give you problems.

Due to this, I've made it my personal mission to help the world finally get rid of Excel. My blog and website are called: www.GetRidofExcel.com

What are the main issues with Excel from a corporate viewpoint and from the perspective of an experienced Data Architect?

Excessive Flexibility: You can't set predefined data formats or validations for each column to prevent users from entering incorrect data. Yes, you can set cell validations, but let's be realistic, nobody does it, nor can it be made mandatory.

Limited Queryability: Data in Excel can't be queried outside of Excel. Excel is the lord and master of data analysis within its own realm, but connecting to it for running a query is very complicated, making integration into data flows difficult.

Lack of Traceability: What happens in Excel stays in Excel. You can't track who modified a cell or record. In the financial system, this is critical, as regulations demand that everything be auditable.

Easy to Ruin: Human creativity has no antidote; users can add, remove, color, and modify rows/columns, leaving an Excel totally dysfunctional.

Doesn't like teamwork: Although newer versions of Office 365 allow multiple users to edit simultaneously, using Excel for concurrent data entry is a bad idea that will always make you look bad.

Two-dimensional: Everything in Excel is rows and columns. It's impossible to create complex data models or capture multiple values in a single cell. Users perform miracles with VLOOKUP, but sooner or later, they'll make you look bad.

I think you're getting an idea of how our trusty friend Excel can be a real headache in the corporate world.

What alternative do we have to replace Excel?

Well, it turns out that SharePoint is a data management platform (for structured and unstructured data) that solves practically all the problems Excel presents.

The core of SharePoint is a relational database (ACID) that the user can customize to create simple data models for multiple use cases. Let's say it's a version of MS Access that's Low-Code/No-Code where everything is done with clicks.

The tables (known as lists) in SharePoint have the following peculiarities, which, honestly, I haven't found in any other 'corporate database', making it extremely powerful for solving use cases that IT could never address:

Auditable: Each record in SharePoint can maintain a version with the ability to revert to a previous version. Integrated with Active Directory, you can always trace who altered or deleted a record. If you want to keep compliance officers at bay, you must evaluate this functionality.

Alerts and Actions: You can set alerts and events when a record is created or a field changes, allowing integrations via Power Apps or instant or daily notifications to the list administrator.

Concurrent: All people with access to the list can enter records directly into the list (by record or in Excel-like grid) or through forms. The power of lists lies in being able to have an army capturing data without locks, unlike Excel or MS Access.

Multidimensional: In addition to defining fields to accept one or multiple predefined values, you can set LOOKUP fields to other lists, creating referential integrity and advanced data models that bring user applications to life.

Collaboration: Each record in SharePoint has the uniqueness of including a micro-chat to start a conversation or make call-outs to colleagues. Additionally, each record allows an indefinite number of attachments.

Exclusive Data Types: Beyond common data types (short text, memo, number, date), there are other types to store people or groups (from Active Directory), images, predefined values (single or multiple selection), links, calculated fields, LOOKUP fields, etc.

Advanced Visualizations: You can define views for lists to appear as calendars, Gantt charts, Kanban boards, drill-downs, with filters, cards, etc. These visualizations are visually appealing and interactive, serving as building blocks for interactive pages.

Integration: Lists can be used as data sources for Power BI in real-time and can be dynamically exported with Excel to feed tables or pivot tables. You create your Excel once, and upon opening it, new data from SharePoint updates automatically. For massive operations, one can use MS Access to connect to a list and perform SQL operations.

Queryable: Through MS Access, APIs, and ODATA connectors, data stored in SharePoint lists can be queried using SQL for reporting or to feed other data chains without needing to export to flat files (or Excel). The data is 'alive' and available.

Conclusion

We've lived for many years using Excel as the only alternative for users to bridge the gap with systems. The reality is that Excel is not only dangerous but also disrupts the organic flow of data in a team or collaboration by being a black box.

The SharePoint platform allows you to then migrate those necessary Excel files, integrating them within the context of an organization that brings a collaborative application to life without needing to bother IT. As it's an end-user solution, nothing needs to be programmed; everything is configured, but it requires a SITE-Owner acting as a Solution Architect to implement these types of solutions.

Stay tuned to my blog to learn about the different use cases where one SHOULD NOT USE EXCEL and use SharePoint instead.