The 10 commandments of Excel

First: there isn’t a wrong way to perform tasks in Excel, but more efficient and productive ways of using it. Throughout my career, I have spent long working days on certain activities by working with Excel. Journeys that could be optimized if I had the knowledge of the appropriate tools and how to use them. With a few clicks, nowadays I’m able to perform tasks that would take weeks then.

Thus, I present a list of the 10 Excel commandments to contribute to the most effective guidance of the tool

1. You shall not merge

Let who never did that cast the first stone... Yes, one of the most common practices among many Excel users, merging is also one of the most limiting ones. The Excel structure is based on the crossing between lines and columns, thereby defining the cells. From the moment the user combines cells by merging, this reference is corrupted, not allowing the correct interpretation of the content and application of a series of tools (filters, formulas, selections, references, etc...). In an analogy, it is similar to the "Naval Battle" game. If it were possible to merge the fields on the board, how would the targeted vessels be identified?

Therefore, IT IS PROHIBITED TO MERGE CELLS INTO THE DATABASE! Try to use the feature precisely, as in the development of control panels (dashboards), where the information is displayed, not checked.

2. You shall not put more than one information in the same cell

Still using the "Naval Battle" analogy, could you identify and hit the desired vessel if there were several ships vessels in the same field? It's the same thing in the Excel. Placing more than one information in the same cell limits a number of features, such as filters and conditional operations formulas (SUMIFS, AVERAGEIFS, etc...).

The motto is one cell, one information.

3. You shall check the organization and consistency of the database above all things

What is the use of having a beautiful house if the foundation is rotten? In Excel, it's the same thing. The database is the foundation of the spreadsheet. There is no point in worrying about the sophisticated analysis and interesting graphics with a disorganized database. In addition to reworking and limiting the use of tools, there is also a loss of reliability. Besides the points addressed in the previous commandments, it’s necessary to standardize special characters as spaces and accents are necessary.

These treatments are necessary because Excel differentiates special characters, not grouping the data in the same "hamper", which is a premise for conditional and search/reference functions as VLOOKUP, INDEX, MATCH, IF, SUMIFS, AVERAGEIFS, among others. It is simple to test it: i) write your name in a cell; ii) write your name one more time in the cell below but this time with a space after the name (a very common error when typing); iii) in a third cell, construct a function saying the cells are equal (=CELL1=CELL2) and press ENTER. As you can see, the result will be FALSE.

Well, now imagine a budget analysis based on SUMIFS, for example, without any treatment in this regard. Will the analysis be possible? Yes, it will. Will the analysis be reliable? It won't, once the fields with space or accent will be despised. Your decision-making is not reliable.

Before you analyze it, make sure your database is standardized.

4. You shall not select entire fields for punctual analysis

It is common for users to select entire rows or columns to define the arguments from the headers when using functions and tools. It is a lazy addiction that causes the increase of file size and the slowness of the analyzes. This happens because a verification to be performed on a given set of cells is applied to 1,048,576 rows and 16,384 columns, depending on the parsing. It is as if you have to find a specific address in a given city, but selected the whole country to search. You will find it, but it will require more effort and it will take longer.

Select only what you're going to use.

5. You shall explore the reference resources

When using different features in Excel, such as functions, tools or graphing, the user basically has two options when specifying arguments: write directly to the tool or refer to a cell/data range. Well, whenever possible, choose the second option. When referring to a cell, a bond is created, in order that in case of changes the relations of interdependence will be respected. It does not occur when an argument is typed. In other words, in case of any adjustments, identification and correction are manual, time-consuming and subject to error.

For efficient manipulation of Excel, it is important that the user masters the available absolute reference derivations, given by the binding combinations, represented by the dollar sign ($) usage in the cells. Furthermore, the definition of cell names and/or ranges of cells by using "Name Manager" is a powerful feature that can be used in parallel, optimizing many tools.

Always reference a cell - mastering the available options - and never enter the argument/text/value.

6. You shall check the compatibility of resources

Improvements and updates are natural processes in the development of any program, and it isn't different in Excel. The use of new tools and/or their evolved forms, however, must be done with caution, due to the compatibility of resources. If you use a function available only in the 2016 version, for example, MAXIFS or MINIFS, and send the document to a user who has previous versions, the document will not be able to manipulate the worksheet properly. It will crash!

Another example is the CONCAT function (available from version 2016) that has replaced CONCATENATE. In practice, they do the same thing, gathering information from various cells in one. If you structure a spreadsheet by making use of CONCAT and share it with a user who uses previous versions ... CRASH!

"Okay, Bot?o, but how do I identify if the function is new or old"? There isn't a simple and practical way. It must be accessed by the Microsoft Office Support. Excel even warns you about the version, but by drawing attention to discontinued functions, from an illustration of the attention sign on the function (as the STDEV function, another function replaced). In my point of view, it could be improved.

Therefore, be aware of the version that the target user is using, or the user could create the worksheet by making use of "old" functions.

7. You shall use resources in an integrated way

The use of many resources in Excel only makes sense if used in an integrated way. Perhaps, the best example for this is the MATCH function, which looks for a specified item in a range of cells, returning the relative position of this item in the range. It is useless if used alone. After all, what is the use of knowing the item position in the data set?

However, when combined with other functions, the MATCH becomes the solution gear. A classic example is the construction of the famous VLOOKUP function, where most users manually insert the "index_num_column", thereby capturing the solution automation. In this case, the use of MATCH is extremely applicable, making the logic more flexible and less prone to error, and this is just one of the countless examples that could be mentioned.

The real deal of Excel isn’t the individualized use of the tools, but the combination of the elements. The user must seek synergies and exploit the solutions to the maximum. Hence, the user will realize that there isn’t a wrong way, but the most productive one.

As in "one swallow doesn't make a summer", a function or tool does not make a good spreadsheet. Look for the integration of functions.

8. You shall not use a macro in vain

Visual Basic for Applications (VBA) implementations - the popular "macro" - is a set of fantastic tools that let you explore the automation and customization of some actions in Excel. Mastering macro is a dream among Excel users. Many users even think that only those who know how to work with macro really know how to work in Excel. This is a myth.

Excel has a very wide range of native features distributed among tools and dedicated formulas, and it is possible to do TONS with it. Most of the programming lines that we see in practice are ready in the ribbon or function library. Knowing the purpose of these tools and knowing how to use them in an integrated way should be the user goal, as seen in the previous commandment.

Before learning about programming in VBA, try to explore the existing resources.

9. You shall read

Yes, READING! And by reading - although it is essential to seek knowledge - I don't mean books or articles. I mean the explanatory instructions that have been attached to the program functions. The use of a simple function is full of information in comments, from the moment of the insertion to the indication of the arguments. Although some explanations are confused or too simplistic, a basic mouse positioning on the tool allows us to clarify some doubts and avoid many errors.

Therefore, read more and pay attention to what Excel tells you.

10. You shall not cry for help in vain

The first reaction when we can’t perform a certain task is to ask for help from someone who is more experienced. There will always be a more experienced person, however, you must avoid immediately asking for help. During the process of learning and absorbing the content, it is crucial to "exhaust" all the possibilities individually. Obviously, you should seek solutions from support materials such as tutorials, books, etc... But do it alone, because it stimulates reasoning and could be helpful to your personal development. By doing this, you could become self-taught and not only know what to learn but how to learn.

Of course, it takes time. As I said in the introduction, it is an "investment" rather than "waste" of time. As Einstein would say in two sentences that I like very much: “The mind that opens up to a new idea never returns to its original size.” and "Insanity: doing the same thing over and over again and expecting different results".

Get out of the comfort zone and do something about it!

Jo?o Rosa (Bot?o)