Howdy folks. Jeff here again, with my musings on the kinds of things you might put in a business case for a corporate Excel training program.
I think corporate-wide Excel training is too valuable to leave to a Learning and Development team and/or to chance (assuming an organization is large enough to have an L&D function in the first place).
- L&D likely don’t know that much about how business users are misusing Excel, so how can they offer generic training at arms-length to remedy said misuse? At the same time, they must support numerous departments, with training being just one aspect of what they do (the other is meetings. Very important meetings, I’m told) and with Excel being just one of many programs that users need training in. So L&D simply can’t specialize in Excel training to a level that’s really going to make a difference at the coal face.
- The efficiency dividend from training accrues to the units of the people being trained. So units themselves are directly incentivized to invest if it will get them a more optimal outcome…regardless of whether fostering increased human capital falls within that unit’s core job/mandate or not.
So instead of business units saying “It’s not our job to train…it’s theirs” I think they need to be thinking “We’d rather someone else do this, but we’re prepared to fill in the gaps ourselves if it helps our people to solve their business problems in ways that increase quality, improve productivity, and provide higher returns on investment.”
But what would a corporate Excel initiative look like? And how would you sell it to an organization that isn’t aware of the problems with the status quo?
I thought I’d have a crack at putting together a generic business case that addresses these questions. Not put together with any specific organization in mind…rather, this is something gleaned from practically every organization I’ve ever worked with/for, as well as from my time spend moderating a few Excel help groups and such forth.
Love to hear about suggested rewrites, omissions, etc in the comments.
- We believe that additional investment in Microsoft Excel-based training will help users solve their business problems in ways that increase quality, improve productivity, and provide a good return on investment, while decreasing some key data-related business risks.
- Consequently we propose to instigate a training program above and beyond that currently offered by Learning and Development that is primarily focused on educating users on how Excel’s deep feature set can be better utilized to solve common business issues while increasing data integrity, utilizing approaches that users would otherwise be unlikely to be exposed to; and highlighting routine practices that currently get in the way of increased efficiency.
- Given the large number of users involved, one-on-one diagnosis and training covering all users is simply not possible. Consequently this initiative primarily revolves around increasing the ability of users to self-diagnose issues and inefficiencies with their existing spreadsheet applications and practices, and to educate them on how to access resources and knowledge that will greatly assist them to self-remedy issues wherever possible. Given the resources available for this initiative, raising awareness of current bad practices and alternatives is possibly the biggest efficiency gain and risk-reducing opportunity that we can offer, at the least cost.
- Given Excel is the tool by which many people realize/express their commercial analysis, data visualization, business processes, and logic, we believe that in teaching people to use the tool itself better we can also foster increased competence in these fundamental skills/attributes.
Currently our Excel user base largely consists of users largely at the basic level, with a much smaller contingent of intermediate users. Almost all uses are largely self-taught, often with little exposure to the vast wealth of constantly evolving best-practice resources that are available both offline and online. Consequently, despite being motivated to use Excel as efficiently as they can, even intermediate users barely scratch the surface of Excel’s productivity tools. At the same time, because some of Excel’s features are so very easy to use, those same features are also easily overused to the point of misuse. Consequently:
- The majority of users spend much more time manually formatting and manipulating data than they need to, often with little or no backwards auditability.
- Sometimes our approaches to analytical problems involve much more complexity than a more suitable approach, with little or no additional benefit or confidence eventuating as a result.
- Many of our business processes hosted within Excel spreadsheets are convoluted and unclear. ‘Mission-critical’ spreadsheets are often bloated, difficult to audit, and subject to catastrophic spreadsheet failure – possibly without users realizing.
- Modelling practices and spreadsheet practices across the organisation are inconsistent, and there is little or no peer review or development in place to ensure people use the Excel tool smartly, wisely, and efficiently.
Best practices and learning are not perpetuated in a formalized way throughout the organization. The emphasis of our expert users currently remains fixing things that are broken, rather than using education to avoid bad practices in the first place.
While our Learning and Development (L&D) unit offer a number of appropriately pitched courses in place focusing on new/basic users, these are functionality-centric, rather than being business-specific. Consequently, such courses often don’t expose users to alternative practices. At the same time, L&D staff are unlikely to be fully cognizant of the vast amount of quality free resources available online, as well as some paid offerings that may prove more cost effective than the traditional course vendors that we have previously used.
As a result –
- Most people keep using the very small subset of Excel’s functionality that they know about as a hammer on problems that aren’t nails but screws
- We don’t insist on nor foster nor seek to measure incremental improvement of our analyst’s skill-set.
- We allow users to build mission-critical applications every day in Excel with little or no discipline .
The status quo incurs a very real opportunity cost to the organization, given that –
- Advanced users can often do something in a fraction of the time that it takes an intermediate user;
- The automation capability of Excel is staggering;
- It’s not actually that hard to turn basic users into intermediate ones, and intermediate users into advanced ones, so that they can do things in a fraction of the time they currently do, if not automate it completely.
We propose to train our analysts to become not just better Excel users, but also more effective analysts. Note that this initiative isn’t just about the use of Excel itself, but related disciplines such as effective data visualization, business process design, and logic. Given Excel is the tool by which people express their analysis, data visualization, and logic, then if we teach people to use the tool better, in doing so we will also give them some pointers in thinking harder about what they are using the tool to achieve.
The desired state that we seek to arrive at would be demonstrated by the following outcomes:
- Our people would leverage more fully off Excel’s rich feature-set in order to achieve better analytical outcomes with much less effort. At the same time, they should be more fully cognizant of common and potentially avoidable spreadsheet design flaws and pitfalls.
- Our people would better judge the appropriate amount of time and precision required for any given analytical task. They will be less likely to over-analyse/over-build, and more cognizant of the diminishing returns that often accompany increased complexity of approach.
- Mission-critical spreadsheets/templates would be more robust, simpler to maintain, and user-friendly, as well as easier for successors to understand and maintain. This should result in lessened business risk, via decreased risk of incorrect models and catastrophic spreadsheet failure; Increased model auditability; and a more consistent approach to spreadsheet development and use across teams.
- Once our people better realize the capability of the Excel application, they begin to actively look for other efficiency opportunities where they can leverage their new Excel skills, and share their approaches.
This initiative is largely focused on increasing human capital – which historically has been more the domain of the organization’s Learning and Development team rather than our own unit. However, we propose entering into this space due to the following factors –
- Reduction of L&D’s technical training capacity;
The opportunity cost of not realizing some fairly low-hanging efficiency dividends;
- The cost of procuring external training, and the risk that such training would not have the long-term positive impact on current practices that we seek;
- The increasing time pressures and demands on staff – meaning increased barriers to utilizing external trainers from both a financial and a time perspective; and
- The fact that we already have a strong interest in developing training materials that encompasses Excel, Modelling, VBA, and Business Process Improvement.
The primary outcomes this initiative will deliver are –
- Increased efficient/effective use of one of our main business productivity tools – Microsoft Excel – and greater awareness of current sub-optimal processes and approaches.
- Education of users to self-identify efficiency/risk issues with existing spreadsheet-based processes/approaches and give them avenues and resources to address these issues.
- Facilitation of increased peer-to-peer learning and peer-review opportunities between Excel users throughout the wider organization.
In doing this, the initiative will take a multi-pronged approach:
- Remedial mitigation of mission-critical spreadsheets/processes
‘Best Practice’ efficiency/effectiveness education
- Peer-to-peer user group
- Identification, creation, and dissemination of useful resources/templates
- Evaluation of additional tools/external training opportunities
These are covered in more detail below.
1. Remedial mitigation of mission-critical spreadsheets/processes.
We will work directly on selected mission-critical spreadsheets and spreadsheet-bases business processes in conjunction with business owners to identify design issues and prioritise remedial action.
2. ‘Best Practice’ efficiency/effectiveness education
We will deliver multiple training sessions/workshops on best-practices covering modelling, spreadsheet risk mitigation, and using the Excel application more effectively and efficiently. This will also draw on ‘lessons learned’ from the above step. This is critical given that several of the spreadsheets prioritized for investigation in the preceding step were constructed within the last 6 months. This highlighting that we have an on-going issue, and not just a problem with legacy spreadsheets.
Sessions will cover –
- How recent versions of Excel have extended its capability from mere spread-sheeting into business intelligence.
- Data organisation: Many things in Excel go from challenging to easy simply by changing how source data is organized.
- Understanding how Excel calculates, and avoiding calculation bottlenecks and overhead by wisely choosing/using formulas for efficiency.
- Leveraging pivot tables for aggregation and reporting; and utilising Excel’s dynamic table functionality to cut down on complexity.
- Using advanced functionality to augment user interfaces by incorporating slicers, advanced filtering, VBA (aka macros), and dynamic SQL queries (for simplified data retrieval and processing) into spreadsheets/models.
Conditional formatting and data validation techniques that can help to better validate user input.
- Tools (both free and commercial) that can help users to work more efficiently in the Excel environment.
- Troubleshooting and streamlining existing models that users have inherited.
- How users can get free help, advice and inputs from online resources and forums.
- Modelling/analysis best practices.
- Data visualization best practices.
- Spreadsheet development best practices, including case studies covering lessons learned from recent work.
Session attendees will also have access to associated hand-outs/workbooks with supporting content and links to further in-depth resources. Each session will be repeated multiple times on a rolling basis to facilitate/encourage maximum patronage. Managers will be encouraged to actively promote attendance at these sessions and potentially include them in staff development plans if appropriate.
3. Peer-to-peer user group/help forum.
We will set up and facilitate an internal Excel User Group – potentially with a supporting software forum/message board. This will be used to –
- Provide peer-to-peer learning and support opportunities. A wider peer-to-peer support network that extends beyond just our unit alone will provide much wider coverage than me alone can offer.
- Identify/evaluate mission-critical spreadsheets across our unit that potentially impose significant business risk and/or administrative burden, and provide owners with some options to restructure/re-engineer them accordingly.
- Provide attendees with more hands-on exposure to tools/tricks that they can use to re-engineer their own spreadsheets in need. Encouraging all users to utilize the skills of identified expert users on a consultancy basis for advice and peer review as needed – perhaps via the peer-to-peer-based user group outlined above.
4. Identification, creation, and dissemination of useful resources/templates
We will identify/create a useful repository of quality free training resources (including construction of additional in-house resources and ready-to-use macros/templates where warranted) – that supports further in-depth professional development and the same time reduces our dependency on paid external courses. This will draw heavily on the large amount of free resources and training materials available online that equal and in many cases surpass paid external training content in terms of providing learning outcomes.
We will publish a weekly ‘Productivity Hack’ article on the organizational intranet home page suitable for users of all levels. These articles may also reference a number of the outstanding productivity resources published each week on the Internet by the worldwide Excel Development/Training community (including blog posts, technical articles, training videos et cetera).
5. Evaluation of additional tools/external training opportunities
We will work with IT to evaluate new additions to Excel such as PowerPivot – a free extension to Excel that allows users can crunch, filter, and sort millions of records with very little overhead, as well as incorporate multiple data sources easily into PivotTable-based analysis, using an interface they are already familiar with. PowerPivot extends the capability of Excel to the point that it might reduce the need for some of the other apps we currently use to amalgamate discrete data sources, such as SAS.
We will also offer our assistance to Learning and Development to help them identify/rate any external training providers they will use going forwards.