Excel is the financial manager's tool par excellence. Although, in our opinion, it suffers from many weaknesses when it comes to reporting functions, data governance, and its use as a process automation application, Excel remains unbeatable in terms of its function of analyzing and solving complex problems, performing calculations, and making it easier to build visualizations and charts.

In what situations should the use of Excel be avoided?

In financial reporting, it is common to use formulas that apply to time series. For example, in the presentation of monthly or annual results. Because Excel allows you to drag formulas down or to the right from a certain position, formulas over a time series are expected to maintain the same logical structure.

However, there is nothing to prevent a particular formula, somewhere in the middle of a time series, from being different from the others, due to negligence or lack of technical knowledge, misleading the user.

Take, for example, the following time series with the sales of a company's products between 2020 and 2029:

The formula that calculates total sales is a simple sum of the values in rows 2 through 6. If you entered the formula =SUM(C1:C6) in cell C7, it's okay to drag this formula to the right, and the formula will adjust to your columns because the original formula contains relative references.

However, if the formula in cell G7 is different from the others, there's nothing to prevent Excel from performing the calculation, even if it's inconsistent. For example, in this cell we have the following formula: =+G6+G5.

In the upper left corner of the cell, a small alert icon is visible that tells us precisely that the formula is inconsistent. Clicking on this icon is relatively easy to solve the problem by copying the formula on the left:

And this is essentially the protection that the user has to avoid the inconsistency of this type of formula. It would be safer to use matrix formulas to avoid inconsistency in formulas, but from experience, we know that most users are unaware and use them.

Alternatively, for example, Power BI uses the Data Analysis Expressions (DAX) language, which isn't as easy to learn as Excel formulas (perhaps because it's not as visual as formulas), but it imposes a more rigid structure, which avoids this kind of problem.

This is just one example of the weaknesses associated with Excel formulas for preparing financial reports. Other common examples include the following:

• Swap absolute references for relative references in Excel formulas. This is a common scenario that leads to fixing Excel rows or columns that you intended to drop or vice versa. For example, the formula =A1 * $B$1 will correct cell B1. If the formula is dragged to other cells, this reference remains fixed.
• Inappropriate use of search formulas. For example, the following formula =VLOOKUP(A1, B1:C10, 2, FALSE), specifies in its fourth argument that you want to get an exact value. By omitting this argument or replacing it with TRUE, many users get wrong results.
• Inclusion of initial investment amounts in Net Present Value (NPV) formulas. For example, the formula =NPV(0,1, C1:C5) + C0 correctly considers the initial investment (given as C0) outside the NPV function. Many experienced users make the mistake of including it within the function, thus getting wrong results.
• Difficulty using dates in Excel formulas. For example, the formula =DATEDIF(A1, B1, "D") does not guarantee that cells A1 and B1 are in the same format and can cause errors. The DAX language has a set of time intelligence expressions that are specific to dealing with dates, and that are much more robust and accurate.

Excel as an enterprise database

Another common scenario is to use Excel as if it were a database. Many companies accumulate data in Excel files that are later collected from links by other Excel files for the preparation of various reports and analyses.

Excel has a limitation on the number of rows and columns (1048576 rows and 16384 columns, respectively). Although it seems like a significant number of cells, if we take into account the long history that many companies have accumulated over the years, and if we think that the production of data increases at an ever-increasing speed, it is easy to see how limited we are in terms of data storage capacity.

Associated with this problem is also a major performance limitation. When using a lot of available Excel cells, we are going to run into slowness and jitter issues. The  common Excel glitch  that leads to all kinds of unpredictable situations.

Data Governance in Excel

While it's possible to protect Excel files with passwords and other features, it's not that difficult to unprotect them. A user committed to cracking an Excel file easily finds Google a quick way to do it. In fact, all the data contained in an Excel file is inside that same file and there is really no safe way to protect it.

Again, comparing Excel with Power BI or another BI tool that has secure separation of data layers, analytics, and visualizations, it should be considered that it will be enough to email an Excel file with data to the outside of the organization, so that it is exposed and without any protection.

Using Excel as a Process Automation Application

Because it is such a versatile tool that it can be programmed through a programming language (VBA – Visual Basic for Applications), it is also common to see Excel as an app for time and attendance, attendance registration, management of purchasing processes, preparation of calendars and vacation maps, preparation of time sheets, among other applications.

Again, we don't recommend using Excel in this way. There are other modern solutions, such as Power Platform, for example, that can be created in the cloud and made available securely on mobile devices or computers and with much more advanced features.

Other Limitations of Excel in the Financial Area

At the risk of discouraging the finance manager from learning Excel, there are even more reasons to avoid it, namely the following:

• Version control: Managing multiple versions of Excel files can be difficult, especially in environments where multiple users collaborate on preparing reports.
• Auditing and traceability: Change tracking and auditing of Excel files can be challenging, especially without the use of additional tools.

Reasons to learn and use Excel in the financial management of organizations

Excel is relatively easy to learn and allows for a wide variety of advanced calculations. It has a wide range of functions for data analysis, such as pivot tables, charts, and filtering tools, thus enabling data-driven management decision-making.

Thus, the investment in learning (and licensing) is reduced and the return considerably high. And that's why Excel is the tool of choice for many businesses, large and small. With the above-mentioned precautions, it will be worth discovering all that Excel has to offer.