Proficiency with Excel is a basic requirement in the accounting world. You would be hard put to find an accounting department not equipping everyone with Microsoft Excel. Why? Excel gets the job done. It handles many routine accounting tasks easily and quickly. And Excel has kept pace with numerous analytical tasks that have become mainstream in the recent past. These include querying complex databases, combining data from multiple sources and visualizing resulting datasets.
With all this functionality why would any accountant want to leave the safety and comfort of working with Excel and the closely related Microsoft family of products. The answer is well documented but is often unrecognized. Unrecognized that is, until Excel becomes the source of a problem. Excel suffers from some serious limitations. These limitations can drive unexpected costs and in certain cases add significant risk. Let’s explore these in detail.
COST Excel is frequently used for complex tasks. A multi-step process, such as the consolidation of financial statements for multiple companies easily consumes many hours. It may need to be executed numerous times a year. This can require a significant investment of time by a senior resource. While Excel macros may speed up the work, the true cost of the process can be high. This doesn't need to be.
Too many complex Excel procedures lack documentation. When the work is re-assigned, a new person will need to master the process. If the previous process owner is unavailable for support and there is no documentation the new accountant may struggle. Quite often they spend significant time and effort to rebuild the process from scratch. In this process of rebuilding, other work can be delayed, and deadlines missed.
Some manually driven Excel processes require the execution of many steps in a very precise order. Performing them out of order or missing a step will alter the results. This can require extra time spent by supervisors to check results. Whenever errors are discovered, the work will need to be repeated and checked again. This extra cost is often hidden and seldom tracked.
SCOPE As of 2021 Excel can handle a single data table of up to 1,048,576 rows. Importing data sets into Excel that exceed this limit will cause Excel to truncate the table and in some cases cause the application to fail and the computer to crash. When approaching its row limit Excel can become slow and unstable. Excel depends on the available memory of the computer it is running on. It also depends on the speed of the processor. There are workarounds, such as splitting a large table across multiple worksheets in the workbook. While this may be acceptable, it adds time and complexity to the exercise and adds risk.
Computer memory and processor speeds continue to grow. This alleviates the Excel scope limitations to some degree. However, the volumes of data that enterprises collect and are expected to analyze is growing even faster. The total volume of data produced globally since computers were first invented has kept doubling every two years. Accountants have not been exempted from this explosion of data. In fact, this ever-growing universe of data has greatly increased management expectations for reporting that integrates financial and operational data across numerous dimensions. This has exposed the limits of Excel at many organizations.
RISK The flexibility of an Excel spreadsheet is its greatest strength and its greatest weakness. It is all too easy to accidently overwrite an Excel formula with a fixed value. (Note: Excel even includes a function that can convert all formulas to fixed values!) When the spreadsheet is updated with new input values the results will be wrong. Errors of this nature can be difficult to detect and correct. The consequences may be serious if the organization is relying on report results to make important decisions.
When Excel workbooks are shared across a team each user may choose to make adjustments to some of the worksheets, resulting in multiple versions circulating within the organization. At the very least this creates confusion and extra work to reconcile conflicting data.
THEN WHAT? When the limitations of Excel start to appear the search for alternatives begins. Special-purpose software for performing complex tasks like consolidations, budgeting and account reconciliation may be attractive. The advantage of this approach is that these applications are designed to overcome the inherent limitation of spreadsheets. The downside is that they can be costly and time consuming to implement. They may require specialised training and skillsets to fully realize all the associated benefits. While this may be acceptable to large enterprises this strategy can impose an unnecessary burden on small to medium sized business.
Another way to overcome the limitations of Excel is to become a programmer. There are two open-source programming languages, R and Python, that are popular for serious data analysis work. While they can scale up beyond the limitations imposed by Excel they require a degree of discipline and effort that is challenging for finance professionals already carrying a large workload. And they don’t overcome all the limitations imposed by Excel.
A third avenue is to adopt some of the more advanced general-purpose data analysis tools such as Alteryx. In our next post we will explain how Alteryx can work with Excel while also overcoming many of the limitations of Excel.