Spreadsheets are everywhere – a staple of modern businesses, with 66 per cent of office-based professionals opening one at least once per hour. Powerful, versatile and easily accessible, they are often the first tool of choice whenever there is a need to capture and analyse data.
Yet the proliferation of spreadsheets is not without its problems, so it’s worth considering best practice approaches to using them as well as alternatives particularly in the Microsoft ecosystem.
A key concern relating to spreadsheets is data integrity. Errors can creep in through mistakes in manual data entry or incorrect calculations, which can be very hard to detect.
Yet their impact can be significant, and can lead to misinformed business decisions with potentially disastrous consequences. It doesn’t help that spreadsheets are usually created and maintained by individuals, with others finding it hard to verify data sources or trace built-in logic.
Collaboration generally can be challenging, with multiple versions of the same spreadsheet being maintained and shared by email and a lack of audit trace of individual updates over time. The fact that spreadsheets can be shared easily can also lead to security and compliance risks.
Some of these problems may be addressed by simply moving spreadsheets onto a cloud-based storage system, such as Microsoft OneDrive. Rather than emailing copies of spreadsheets a single file is maintained, shared with specific authorised users and providing version control automatically.
However, this may not be good enough when spreadsheet solutions are deployed to entire teams to capture significant volumes of enterprise data. A typical example in the project world would be Microsoft Excel being used to capture project risk registers and progress reports, with every project maintaining their own spreadsheet for this purpose. Inevitably this leads to significant time wasted through double-entry of data, as well as manual copy/paste operations to merge data and create reports.
Whenever spreadsheets are misused as enterprise databases in this way, it is a good idea to consider alternatives instead. In the Microsoft world particularly, there are great options that may take more effort to deploy than quickly creating a spreadsheet but will deliver significant benefit in the long run.
One such alternative is Microsoft Lists. Much like Excel, they provide a tabular format for organizing and viewing information in rows and columns but are designed for team collaboration. Multiple team members can work on a list of data simultaneously, in the browser, within Microsoft Teams or on their mobile device.
Lists are also a great foundation to step into the Microsoft Power Platform. This ‘low-code’ platform includes services such as Power Apps, enabling apps to be built easily on top of Lists that can add more complex business logic to the way data is maintained. It also features Power Automate for easy creation of workflows to automate alerts, approvals and integrations.
If data volumes and complexity call for an enterprise-level database, then the Power Platform can provide that too with its own database technology called Dataverse. This is a fully relational database, meaning that multiple tables can be created with logical relationships. Apps built on top of these datasets can ensure full data integrity while safeguarding business rules and compliance.
These technologies make it possible to replace individual spreadsheets or whole collections of them, building from scratch simple apps or complex enterprise-level solutions. When a business process has clearly outgrown spreadsheets, it is also worth looking at off-the-shelf tools.
Microsoft offers rich enterprise tools, many of them themselves deployed in the Power Platform and storing their data in Dataverse, such as Microsoft Project based apps for full PPM capability out of the box.
Improving the way data is collected is a great start on the way to becoming ‘data driven’ – making decisions using data rather than intuition, gut feeling, personal experience or tradition.
The ability to analyse, report and share data is of course equally important. Spreadsheet tools like Excel are extremely powerful when it comes to conducting in-depth data analysis, and creating charts or graphs to visually represent data. Rather than using spreadsheets for data entry and storage, they can be used solely for analytics by connecting them to data stored elsewhere.
However, the Microsoft platform offers Power BI that nowadays is the primary tool for publishing organisational reports or dashboards, and engaging users with data through interactive and collaborative features in a way that individual spreadsheets cannot.
Excel remains a powerful tool for complex data analysis and calculations, as well as providing a quick way of enabling data collection without involving IT resources or commissioning more complex infrastructure. However, reducing reliance on spreadsheets will reduce errors, improve productivity and glean more business value from data.
Whenever a process is likely to collect significant amounts of data from a collaborative team, then it is time to explore other options. For businesses that already use Microsoft 365, maximising the full potential of the Microsoft ecosystem provides an obvious route to a culture of more efficient data management.
Gero Renker is a director of Program Framework, a consultancy that specialises in solutions for enterprise project portfolio and risk management.
