Excel & VBA Consultant's role is to convert the client's requirements into actionable systems that solve their true challenges at a minimal level of complexity necessary. Spreadsheet Experts no longer only develop models, macros and analyse data, but also gather business requirements, manage stakeholders & projects as well as design and document processes.
Your company needs to fix a system of spreadsheets containing VBA macros and you are looking for support of Spreadsheet Expert with VBA expertise. Unless you are a programmer yourself, your role will be to evaluate candidates overall fitness for the role, while their technical skills will be determined by a search company or outsourced Excel assessment tests. Let's see what candidate traits you are searching for.
THE IDEA: Choose practical consultant, not a technical coder
IT Professionals are stereotypically depicted as introverts focused solely on technical aspects of problems they attempt to solve. When their role is to create systems that only they will continue to use, we may forgive them the jargon and some degree of detachment from the other teams. However, when they are appointed to create systems on which entire departments will rely, the emphasis is placed on practicality & communication.
For the best ROI, leverage low entry cost to Excel environment. Make the spreadsheet infrastructure available to all future users, regardless of their Excel skill level, proficiency in using macros or even computer literacy.
The 5 most important skills that every Excel Expert & VBA Programmer should hold
(on top of sufficient technical knowledge)
1) Stakeholder management
Look for a person that will actively engage other project stakeholders, organising and leading meetings, sending concise emails that produce answers and inputs. As models and systems need real-life validation and user testing, any delays and difficulties should be anticipated as early as possible and communicated openly to affected stakeholders.
project management skills
being realistic about timescales and communicating difficulties
business as usual disruption
micromanagement & unproductive meetings
Ideally, you would like the VBA Developer of Excel Modeller to work in parallel to your ongoing operations. That means minimising the contact with employees to the periodical, i.e. weekly meetings instead of asking them one question at a time, 10 times a day.
You would like to bring onboard a spreadsheet specialist that will connect your colleagues with solutions and handover their ownership. The consultant needs to communicate with plain, non-technical words, having in mind the scope of the stakeholder's Excel use.
Even if the developer tests solution for a couple of days, the underlying Excel workbook might be regularly used by many employees for years. It really pays off to make it user-friendly with colour coding, automation, diagnostic messages, poka-yoke's (input constraints) and fine user experience design.
simple, clear vocabulary
clarification of any ambiguity
use of visual distinction in the spreadsheet files
technical responsibility transference
A red flag here would be the excessive use of jargon by the consultant and attempts to involve non-technical stakeholders in making decisions about the programming methods used, interfaces or other aspects of their work. Excel & VBA consultant should ask if the user would prefer filling up highlighted tables with input data over choosing report parameters in the dialogue boxes rather than asking about the backend VBE scripts versus user form control systems.
3) Business acumen
As a client or user of the spreadsheet solutions, you are looking for reliable systems that will solve your business challenges. Therefore, Excel & VBA Developers should always be the translators of the real word requirements into technical specifications of the spreadsheet systems. They have to help the client choose the solution, asking about what they want to achieve and what's the wider business picture. What format the input files are? What decisions are made based on that report? How accurate the forecasts should be? How much work does the data cleansing take and can be aided or automated?
genuine advice on what you really require
asking the right questions to understand full commercial landscape or industry standards
upselling without regard to business needs
disregarding company policies & practical considerations
Avoid those not willing to let you express your requirements, concerns and opinions. And those that know better than you even before you tell them. And especially those that seem to be programming purist and act offended if you suggest them you don't need such a complex solution and 17 extra reports.
4) Process improvement
Before programming in VBA starts, it's important to determine the business goals and obstacles. Maybe it's actually the version control that's crucial? Maybe it's data quality and not the pricing model that needs fixing? Maybe we can save extra 400 hours per year if we can send report emails directly from spreadsheet level, with a click of a button?
Also, let's be clear what are the roles in the process, what are the scopes of responsibilities and what's the right chronological order of executing procedures.
establishing the role of systems in the grand process
short interviews with users
creating & visualising process workflows, file access matrices as well as version control policies
testing & tuning solutions
"develop & dump" approach
incompatibility with wider business processes (i.e. data format discrepancies, untackled bottlenecks, etc.)
5) Procedure documentation
The vast majority of Microsoft Excel use it on a basic level. It is often because their role is based more on their knowledge and the software they use is a secondary consideration. Wide user base makes Excel quite accessible for the companies and the right documentation can change it into self-service systems and foster business continuity. Spreadsheet systems not dependent on people are virtually future-proof. If even the junior colleagues are able to read a concise set of instructions or watch a procedure recording and follow it, the company processes become resistant to absenteeism and employee retention challenges. And all you need in this case is clear in wording & referencing, precise procedure coverage and accessible form, appealing to those that prefer to read, watch or listen.
That's especially the case in Finance, Banking constrained by reporting deadlines as well as marketing, sales & consulting where any delay has a hefty price tag.
interest on who, when, how often will use the system & how knowledgable they will be
concise in-file instructions and video guides (i.e. screen recording of reporting steps with voice narration)
"no worries, you will figure it out" approach
scattered, incomplete notes/comments next to cells
Avoid consultants with a dismissive approach that see everything through their lenses and those that are never precise and orderly. If you are told that the correctness of your report depends on looking into cell BS479 and making sense of an odd comment that's there, it's time to change your spreadsheet services provider...
It goes without a question that there is no Excel or VBA Expert without technical expertise. There is however no efficiency without the right consultative approach. Therefore every Excel or VBA Consultant should also have skills of business analyst, project manager, user experience designer, software tester, scrum master, process improvement & design engineer with a sparkle of entrepreneurship and focus on people who will use and own the systems.
Whether you are looking for Excel & VBA Macro Consultants or just want to efficiently use spreadsheets, check our comprehensive guide, showing what you can achieve in Excel for your company. In that guide, we investigate Excel & VBA use from every angle, focusing on ROI, deliverable types, the departments that benefit from it and much more.
Michael Sena.The founder of SENACEA
– Microsoft Excel & VBA Consultancy
and Training Services Provider, based in London City, operating in the entire UK.