Why Managing Workflows in Excel is Chaotic and It’s High time to Switch
How outdated are Excel workflows?
A recent Gartner report estimates that 60% of companies use Excel for planning, modelling and tracking in several parts of the organization. Excel and other spreadsheets are still a great tool for computations, what-if-analysis, financial modelling, trending, dash boarding, and goal seeking among many other business imperatives.
However, we all have at least one story of how that one time someone ruined the carefully crafted excel spreadsheet by mucking up the formulas or disturbing the formatting. Using Excel in any organisation has its own issues that we all are familiar with. What if there was a way to mitigate these shortcomings in an elegant way?
Many large corporations globally are already using solutions and tools which overcome these shortcomings, yet giving the users the flexibility of using Excel. After all we all know the power of Excel – can we really do without it?
Concurrency and version management
Typically, Excel is sent as email attachments to various users and then they can change the excel sheet by tweaking the formula, introducing new ones and adding some rows and columns and sending it back to the originator.
There is no way to reconcile the changes made. Office 365 addresses this requirement to a certain extent but not entirely. The excel automation solutions allow the users to work on the entire excel or on a part of it. If there are multiple people working on the same file, you can either open in read-only or change and upload only once the other user has released the sheet. One can also put a max time limit after which you can change and upload. If the time lapses, the other user can anyway download. If the first user who has downloaded and changed a few rows and uploads the changes, then the second user is free to download. You can work only on those rows and columns which allow changes.
This is how concurrency is managed. Whenever there is an upload after change, version number is changed. There are options to backup the last version before making the change.
Excel does not allow any access control. One has access to the entire data or nothing. Even though some or many cells or rows can be protected, there is no standard way to disseminate the information based on access rights.
Automation solutions are a set of programs and processes to enable retrieval of information from the server using a standard browser-based on what is allowed to be accessed by the user. They deploy industry-standard role and access rights for restricting users to view only a specific set of information. Data is stored in a standard database (like MS SQL Server). The users with permission can download the data any time and into standard templates, which are kept in the server that retains all the macros and formulae. Excel automation solutions can generate numerous reports. These reports are accessed like normal excel sheets, the columns are often protected to disallow unnecessary changes.
Excel does not cater to the workflow. Typically, financial computations done in any organisation require to follow the approval or rejection hierarchy. Some excel automation solutions allow unlimited levels of approval or rejection.
An approval workflow is a feature that routes items stored in a cloud server to one or more people for their approval or rejection. The workflow automates, streamlines, and standardizes the whole process.
The workflow assigns a task to each specified participant. Each participant chooses among several possible responses: to approve or reject the item, to request a change in the item, to reassign the task, or to cancel or delete the task.
It’s nearly impossible to get an idea of what happened historically in Excel. Excel cannot provide the information on who changed what. Excel automation solutions allow capturing changes in the data that has been done chronologically.
How does it all work?
Typically, organizations using Excel automation create standard formats with inbuilt formula, macros, etc. They are called templates. The templates are stored in a server. The solution is a web application. Users with access right can download the desired template and can enter only the data that they are allowed to enter or change.
After that they upload the format using the web application. It is audit trailed, or the data that the user has changed in the spreadsheet or entered gets logged in the audit trail table automatically. The excel sheet then, if required, can flow through the approval hierarchy using the built-in workflow of the application. Various types of reports are generated. Dashboards are created to visually comprehend the data.
Are the solutions off-the-shelf or plug and play?
There are some custom solutions to help you do automated workflows on Excel, but the solutions are not off-the-shelf plug and play. Depending on user requirements, the application is customised. Excel templates, reports, validations and dashboards are unique to each organisation. The core of the application comprising access right, audit trail, workflow, upload and download are part of the tool. However, some amount of customisation is required.
A better option
Excel remains a great option for lots of business use cases. However, when it comes to creating automated workflows, it is really stretching it too thin. Rather than try to force a spreadsheet to handle workflows, it’s better to use a solution built for it from the ground up. Try a workflow management tool(Kissflow Workflow) today and see how much you’ve been missing.