Let’s face it, Excel is a very powerful tool, and in the right hands is invaluable in providing a means to collect data, organise it, filter it and present the results. However, things can get out of hand when Excel is pushed too hard. We’ve all seen instances where the following scenarios can lead to undesirable consequences;
• Base data can come from a variety of sources, be they other Excel files, databases, or CSV extracts. This data is controlled by different stake holders or departments. Data must be accessed using file references, which can break, or has to be cut and pasted into the main work book, swelling its size.
• Calculated measures are based on cell formulas. These can get complicated very quickly. They can be difficult to understand and may not be documented fully. There may even be instances where a formula edit is not properly copied over all applicable cells.
• There will be occasions where someone has added a macro to manipulate data. This can be a powerful tool, but also requires proper coding to work reliably and efficiently. It can be hard to understand for others if the author leaves the business, and subsequent changes may break the code.
• You have a workbook, it’s got all the data, and some great charts and graphs. You want to share it, so you email it or share a folder. It gets opened and maybe someone wants to make changes? Someone takes a copy and makes some edits and breaks the formulas, or changes them. Which one is now the original? An audience is required to add its own data or comments. There are discussions on how to apply a calculation, and things change. The whole process of getting the data and manipulating it has to be repeated.
• An audience is required to add comments, revise data or add its own. A workbook can only be opened for editing by one user at a time, so getting everyone to contribute can be time consuming. Mailing everyone their own copy means changes have to be cut and pasted back into the original, or code run to pull it in. There may be instances of an out-of-date template being used, that breaks the import process.
• Excel workbooks have become data silos. Who has what data, where does it overlap, which version is ‘true’ and who decides how it is shared? What business rules are used, and where are they documented? Which elements are controlled by which department? Is the data confidential, or compliant with GDPR? How is the data version controlled? If it’s stored on a personal laptop, what happens if it’s lost or gets broken? Is it backed up regularly?
So, just a few of the ways that a growing business can get to a point where Excel is no longer the answer. If you find yourself in the position where a team of people are busy for significant periods of time building an Excel leviathan, then maybe now would be a good moment to discuss alternatives.
First of all, be sure you understand what your goals are;
• Are you acting as a Master Data source? Is the data in your Excel acting as a source for other systems in the business?
• Are you pulling together data from various sources, and reporting the results? Who is your audience, and do they need different cuts of the data?
• Are you collecting data? Are you emailing out a template file, and then collating results? Is this done with copy and paste, or is someone typing in the results?
• Are you using Excel as a data discovery tool, bringing together data from multiple sources and then trying to understand trends or patterns?
Knowing what you need to achieve will inform how a solution takes shape. There will be process benefits, but a key driver may simply be the time-saving achievable.
So why invest in new technology? Because you need the right tools to do the job. If it’s not broken, why fix it? Because you will be able to do things faster and better. And because you will be able to do things that you could never do before, which may be just what your business needs to continue to grow.
Databases need not be expensive. Cloud database services can be paid for monthly, with no capital expense. Performance of these services can be changed dynamically on an hourly basis if required, meaning you only pay for the performance you need, when you need it. There is no investment in powerful kit that only gets used to its full extent once a month.
Reporting tools are getting easier to use and setup, with no steep learning curve. Sharing is built in and needn’t rely on a company Intranet. They can be powerful data discovery tools, or they can provide dashboards and KPIs, allowing the business to spot trends and issues.
Web pages can be developed that simplify data gathering, collaboration, and reporting. They ensure a consistent view, and can include verification on data entry, ensuring only good data is collected.
We have been working with clients for over 20 years, solving just these sorts of problem. Whilst we specialise in Business Intelligence, we have a wide range of related skills that can help you move on from Excel. If you want to talk about your existing problems, the various ways they could be resolved, and what we can do to help you along the journey, then please get in touch for an informal chat.