Excel is so popular because it's easy to use. Yet, if you work with spreadsheets frequently, you would like to automate mundane tasks while remaining in control and not having to code. How to do it?
User experience is the key to Excel automation.
Coding is powerful but does not necessarily empower the end-users. The no-code solutions are gaining popularity in various industries. They are already offered for, analytics, invoicing automation or client onboarding.
Users don't have to code. The spreadsheet systems should always be created with people in mind - those who will use them, manage and maintain workbooks, and interpret the Excel outputs.
The process of developing Excel solutions (such as templates, trackers or entire systems) involves constant alignment with the end user's expectations. As Excel consultancy, we usually go through the following steps:
evaluating optimal solution scope - gaining the understanding of what the desired result is, involving practical aspects such as who will use the files, what are their skills and priorities
collecting detailed requirements - speaking with system stakeholders to get to know what the management wants to achieve, what the team lead perceives as important and what will give the hands-on specialists comfort in handling their daily workloads
spreadsheet development - designing Excel templates & files, populating them with formulas, integrating with existing systems, programming macros in VBA and internal testing
user acceptance testing - more complex projects may require additional validation on the side of the client to optimise ways they will interact with the file (e. g. through macros, user forms or control panel worksheets)
the handover: training and documentation - upon completion, implementation includes workshops with system users to show the mechanics of the workbooks and train them on its use while documentation makes Excel processes future-proof
support & expansion phase - many spreadsheet systems may need minor adjustments to facilitate changing data types and volumes or better integrated them with other software; thus, a warranty or system support is often helpful
All the above steps include the system users, so the spreadsheet solutions are aligned with their expectations and capabilities. On top of that, we aim to keep the technological complexity as low as possible. To obtain that effect, we suggest combing the below four tactics.
Use Excel templates with low-maintenance formulas
Many Excel projects can be structured to place almost all technological burdens on the developer and not the user. For example, advanced cost trackers or risk models can be interacted with through simple forms or by just loading the raw data.
Such work structuring is often enabled by cleverly used formulas that do not need constant fixing. Thoughtful workbook design would recognise and prevent all possible function evaluations and prevent errors using a mixture of nested formulas. Some of them are logical functions for handling exceptions or IFERROR. On top of that, adequate use of $ signs in cell references or even data validation allows unpacking the complexity for the end-user.
Bet on user-friendly macros
Macros automate the work in Excel, but this should never be done at the cost of control. Instead, correct use of macros deploys them to scalable tasks for which specific execution conditions can be defined while still allowing humans to understand and oversee them, even without any knowledge of coding.
This is done in several ways:
The mechanisms behind macros can be documented in simple terms
The interactions can be managed through buttons and forms
Macros can be adjusted to handle all possible outcomes
User-centric macros will give the impression of interacting with an intelligent system. For instance, the lack of data required for the report will result in a message box notification. Furthermore, cells with discrepant or missing data can get coloured or precisely located. Also, any project-specific problems can be addressed. For example, if the user forgot to perform a specific step, it could be detected and handled.
Test Excel templates and VBA macros
Testing should always be done by the developer and the user or someone who assumes the end-user role. That way, the Excel tools can be scanned for obvious technical errors and those specific to the type of work. This process also acts as the final step of aligning expectations of developers and users, allowing the first-hand experience.