Excel-Life hacking for those involved in reporting and data processing




n this post, Renat Shagabutdinov, Assistant Director General of the publishing house “Mann, Ivanov and Ferber” divided classy Excel-Life hacking. The given tips will be useful for anyone involved in various reporting, data processing and the creation of presentations.Renat is not the first time appears on the guest author Layfhakere. Previously, we published an excellent material from him about how to make a plan of training: basic books and online resources, as well as incremental algorithm creating a training plan.

This article contains some simple techniques that allow to simplify the work in Excel . They are especially useful to those involved in management reporting, prepares a variety of analytical reports based on landings of 1C and other reports, forms of these presentations and charts for guidance. Do not pretend to be an absolute novelty – in one form or another, these methods certainly discussed in forums or mentioned in the article .
Simple alternatives to VLOOKUP and HLOOKUP, if not the desired values ​​in the first column of the table: view, INDEX + MATCH

VLOOKUP ( VLOOKUP ) and PGR (HLOOKUP) work only if the target values ​​are in the first column or row of the table from which you plan to get the data.

In other cases, there are two options:

Use the VIEW (LOOKUP).
She has the following syntax: LOOKUP (lookup_value, vector;). But for her to work correctly you need to value range Search vector were sorted in ascending order: excel
Use a combination of MATCH (MATCH) and INDEX (INDEX).
MATCH function returns the serial number of the element in the array (with its help, you can find out what line of the table the required element) and INDEX returns the array element with a given number (which we know by using the MATCH). excel Its syntax is:
• MATCH (lookup_value, massiv_poiska; match_type) – in our case we want a comparison of “exact matching”, it corresponds to the number 0.
• INDEX (array, linenumber; [column_num]). In this case, the column number is not needed as the array consists of one row.

How to quickly fill the empty cells in the list

The task – to fill the cells in a column with values ​​above (subject to standing in each row of the table, not just the first line of the block books on the subject):


Select the column “Subject”, click on the tape in the “Home” button “Find and highlight» → «Select a group of cells» → «Empty cells” and start entering the formula (ie put the equals sign) and refers to the cell above, simply by pressing the up arrow key. After that, press Ctrl + Enter. Thereafter, the data can be saved as a value, as the formula is no longer necessary:

How to find an error in the formula
Calculating a separate part of the formula

To understand the complex formula (which functions as arguments to other functions are used, that is, some functions are embedded in the other), or to find in it a source of error, often need to calculate its part. There are two easy ways:

To calculate the right part of the formula in the formula bar, select that part and press F9:
ezgif.com-resize (1)
In this example, there was a problem with the search function (SEARCH) – it had been reversed arguments. It is important to remember that if you do not cancel the calculation of the function and press the Enter, then the calculated part will remain number.
Press the “Calculate the formula” in the “Formula” on the tape:

Then you can calculate the formula step by step and to determine at what stage and in which there is an error function (if any):
ezgif.com-resize (2)

How to determine what determines or that refers to a formula

To determine if any of the cells depends on the formula in the “Formula” on the tape, press the “Trace Precedents”:


Arrows appear indicating what determines the result of the calculation.

If displayed, highlighted in the picture in red, the formula depends on the cells on other sheets or in other books:


Clicking on it, we can see exactly where the influence of the cell or range:


Next to the “Trace Precedents” is a button “Trace Dependents”, working the same way: it displays arrows from the active cell with the formula to cells that depend on it.

The button “Remove arrow”, located in the same block, allows you to remove Precedents, Dependents, or both types of arrows at once:

How to find the amount (quantity, average) values ​​of cells from several sheets

Let’s say you have a few sheets of the same type of data that you want to add, find or processed in some other way:

Excel Excel

To do this, the cell in which you want to see the results, enter the standard formula, such as SUM (SUM), and enter the argument followed by a colon title of the first and last pages of the list of those sheets that you need to handle:


You will receive a sum of the cells with the address B3 with sheets “Data1”, “Data2”, “data3”:


Such addressing works for sheets arranged in series. The syntax is: = function (pervyy_list: posledniy_list! Link to the range).
How to automatically build a formulaic phrases

Using the basic principles of working with text in Excel and a few simple functions, it is possible to prepare formulaic phrases for reports. Several principles of work with the text:

Combine text with the ampersand (can replace its function CONCATENATE (CONCATENATE), but this is not much use).
The text is always written in quotation marks, the cell references with the text – not always.
To get the special character “quotes”, use the function CHAR (CHAR) with an argument of 32.

Example of creating a template phrases using the formula :


The Result:


In this case, besides the function CHAR (CHAR) (to display the quotes) using the IF (IF), allows you to change the text, depending on whether there is a positive trend in sales and the function TEXT (TEXT), allowing to display a number in any format. The syntax is as follows:

TEXT (value, format)

The format is specified in quotation marks in the same way as if you entered a custom format in the “Format Cells.”

You can automate more complex texts. In my practice was to automate long, but routine comments on the management accounts in the format “INDEX weakened / strengthened by XX on the plan mainly due to the increase / decrease FAKTORA1 on XX, increase / decrease FAKTORA2 on YY …» with the changing list of factors. If you write such comments often and the process of writing can algorithmization – worth once puzzled create a formula or macro that will save you at least part of the work.
How to save the data in each cell after the merger

When you merge cells retained only one value. Excel warns you when you try to merge cells:


Accordingly, if you had a formula that depends on each cell, it will cease to work after their union (bug # N / A in lines 3-4 an example):


To merge cells, while preserving the data in each of them (perhaps you have a formula, as in this abstract example, perhaps you want to merge cells, but keep all the data in the future, or to hide their intentions), combine all cells in the worksheet , select them, and then using the “Format Painter” transfer format to cells, and that you need to combine:

ezgif.com-resize (3)
How to build a consolidated from multiple data sources

If you need to build a consolidated from multiple data sources, it is necessary to add to the tape or the Quick Access Toolbar “Master pivot tables and charts,” in which there is such an option.

You can do this as follows: “File» → «Settings» → «Quick Access Toolbar» → «All the team» → «PivotTable Wizard and diagrams» → «Add»:


After that, the tape icon appears, click on the causes of which the master himself:


When you click on it, a dialog box:


In it you will need to select “In a few bands consolidation” and click “Next”. In the next section, you can select “Create a single page field” or “Create a page field.” If you want to think up a name for each of the data sources – Select the second point:


In the next window, add all ranges, based on which to build a summary, and ask them names:

ezgif.com-resize (4)

Then in the final dialog box, specify where it will be placed PivotTable report – on an existing or a new worksheet:


PivotTable report is ready. In the filter “Page 1″ you can choose only one of the data sources if necessary:

How to calculate the number of occurrences of the text in the text A B («MTS tariff SuperMTS” – two occurrences of abbreviations MTS)

In this example, column A there are a few lines of text, and our task – to find out how many times each of them meets the desired text in the cell E1:


To solve this problem, you can use a complex formula consisting of the following functions:

LEN (LEN) – calculates the length of the text, a single argument – the text. Example: LEN (“machine”) = 6.
SUBSTITUTE (SUBSTITUTE) – replaces the text string in a specific text to others. Syntax: SUBSTITUTE (text; star_tekst; nov_tekst). Example: SUBSTITUTE (“car”, “cars”, “”) = “mobile”.
UPPER (UPPER) – replaces all characters in a string to uppercase. The only argument – the text. Example: UPPER (“machine”) = “machine”. This feature will be needed to make the search case-insensitive. After all, UPPER (“machine”) = UPPER (“Machine”)

To find a particular occurrence of the text string to another, you must remove all of its occurrences in the source and compare the length of the resulting string to the original:

LEN (“Tariff Super MTS MTS”) – LEN (“Super Charge”) = 6

And then divide the difference by the length of the line, we were looking for:

6 / LEN (“MTS”) = 2

That is twice the string “MTS” is included in the original.

It remains to be written in the language of this algorithm formulas (denoted “text” the text in which we are looking for entry, and the “desired” – the number of occurrences of which we are interested):

In: Technology & Gadgets Asked By: [18454 Red Star Level]

Answer this Question

You must be Logged In to post an Answer.

Not a member yet? Sign Up Now »

Star Points Scale

Earn points for Asking and Answering Questions!

Grey Sta Levelr [1 - 25 Grey Star Level]
Green Star Level [26 - 50 Green Star Level]
Blue Star Level [51 - 500 Blue Star Level]
Orange Star Level [501 - 5000 Orange Star Level]
Red Star Level [5001 - 25000 Red Star Level]
Black Star Level [25001+ Black Star Level]