One of the great advantages of Excel is the possibility of automation that it allows for its users. Still, I'm tired of seeing spreadsheets without a single formula or function. These are extremely simple things, but because of the lack of knowledge in Excel, are left aside.
For example, I've seen clients with spreadsheets that instead of adding two cells with the SUM function or even a formula (= A1 + A2) left the answer space blank, so it went and added up. Imagine the work? No human should go through that job your computer can do for you.
Smart Spreadsheet with Automated Responses
Therefore, having a Excel spreadsheet smart, fully automated, can make all the difference in analyzing the data and deciding what actions to take next. The results are presented immediately and without risk of errors making the work of the manager much more practical and efficient.
Automating an Excel spreadsheet for your use in a smart way is a very simple task and means saving time with manual calculations or other types of simple calculations, in addition to obtaining results with accuracy. This post will show you how easy and important it is to have an automated Excel spreadsheet to facilitate the work of the manager.
Automation of results with functions
A cell with a value in the worksheet will present automated results and should diverge whenever there are changes in the data entered for the measurement. Functions such as SOMASE and PROCV can contribute to the automation of the Excel worksheet.
1 - Applying the SOMASE function:
This function is conditioned to some criteria, such as: interval (area destined to the data to be analyzed), criterion (a certain category to be added, examples related to the "Book" described in the worksheet) and be summed in terms of numerical values).
In this type of worksheet the entries are defined in inputs and outputs, in kind.
The data required for the spreadsheet are classified in column "D" and "E", Classification and Chart of Accounts, and "H" representing the dates of payment. While the columns "G" and "I" represent the amount paid and the accumulated balance. Thus, to know the sum of the values considering the Chart of Accounts, the function SOMASE is applied.
In the example worksheet we have the "DRE" tab inside "Reports". The SOMASE formula is applied to display the results automatically. Below the main table, you can see a table with values by item of the chart of accounts. To have the value received in "Book" for each month, the formula SOMASE is applied.
In order to have the final result with the sum of every month, the following formula can be used:
- Example of January: "= SUMIFS (JAN! $ G $ 5: $ G $ 605; JAN! $ E $ 5: $ E $ 605; $ D19)":
JAN! $ G $ 5: $ G $ 605 - column of values in the January tab
JAN! $ E $ 5: $ E $ 605 - column "Chart of Accounts" in the January tab
$ D19 - cell containing the word "Book"
In summary, the command passed to Excel is "sum all the values in column G, when the corresponding in column E is Book". Every month they are in different tabs, but using the same formula. In this way, we can obtain the automatic result of all of them.
In this example we use our Sheet Cash Flow.
2 - Using the PROCV function:
In the PROCV function, the entire worksheet with an integrated structure will be considered. This function aims at a virtual search.
We use as an example the application of the PROCV function in a stock control sheet of a company. In it, we add the product data in the rows and columns. The "Product Name" column will be the reference to locate the information you want in the other columns.
Navigating the spreadsheet, we will notice that within "Consolidated Results", there is the "Individual Analysis" tab. In this tab, the user chooses a product and the worksheet should seek data from it in the other tabs.
Keeping the "Product Name" column always the reference for this function, we insert "Nail" and we format the formula to look for the other values.
Let's see the example of the formula used to fetch the Minimum Stock in the cell "D9", which would look like this: = PROCV ($ D $ 5; PG! $ D $ 7: $ E $ 1006; 2; FALSE)
Thus we have:
- Value_looked = $ D $ 5 (location on this tab where the product name "Nail" is located);
- Matrix_table = PG! $ D $ 7: $ E $ 1006 (columns with the name of the product and the minimum stock value, used to calculate the function in the tab PG -> Product Register);
- Index_column_number = 2 (Note that it considers the matrix with columns D and E, bearing, and, where the minimum stock would be the second);
- Search_interval = FALSE (or "0" as the accepted value).
Note that by confirming the formula entry, the minimum stock will automatically appear in the designated cell.
Continue applying the formula to extend the PROCV function to every properly filled worksheet. So it will become more efficient by adding agility when it comes to getting some information.
3 - Automating responses from results
The results can still be demonstrated automated within a report. The use of the CONCATENAR function allows you to pull this data to insert inside a text and, thus, to present an automatic response.
In this example we will use a customer prospecting worksheet.
See in the image below how you would be represented in a report text, the month that presented the best sales result, automatically.
To get an automated response like this, follow the step-by-step instructions.
Choose the worksheet cell where you want the report to present the result. In this example we choose the cell "Q17". Now go to the "Formulas" tab and choose the type of formula "Text" and then "CONCATENATE". The following window will open.
According to our sample worksheet, the data for the configuration of this formula would be populated as in the following image.
Press the "ENTER" key to confirm and you will have automatic responses in the form of reports in your Excel worksheet.
These functions are some examples of formulas that can automate the results. However, there are several other ways to make a spreadsheet intelligent and automatic from the use of the variety of formulas and functions that Excel makes available for calculations.
To have these spreadsheets and get even more information about automating results follow our blog.
Are you in doubt? Get in on LUZ Excel Forum and send your question!