Excel automation for professionals that don't code

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 having to code. How to do it?

User experience is the key to Excel automation.

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 burden 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 on 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.

How to find the right Excel Consultants?

Excel is the most popular piece of analytical software. As a result, there are many types of professionals focused on Excel. We previously described how to differentiate between different kinds of Excel service providers.

The major difference between such professionals is that most of them focus almost exclusively on a single aspect of Excel. The influencer might be good at teaching it on Youtube, but they will rarely have time to get fully involved and will likely sell your project to their colleagues on the commission.

On the other hand, freelancers on websites like Fiverr can offer low prices, but their level of accountability may be very low and skills overstated. Also, it isn't easy to find one person that will offer the complete package and give the proper analytical advice about what solution scope to choose.

To assure your desired spreadsheet solutions will be fully functional, hire an Excel Consultancy, not just a programmer or a solo specialist. If you are looking for support with a spreadsheet project, book a free call to explore possible Excel solutions.

23 views0 comments

Recent Posts

See All