Excel functions with examples necessary for work. Functions in Excel. General description, purpose. Microsoft Excel Text Functions

In Excel, functions are a key component of the entire program. However, the vast majority of users know practically nothing about them. At first, when you are just learning, it is recommended to use a special wizard, with the help of which working with functions is several times easier. In this article we will take a detailed look at the process of creating formulas and the most basic functions that are used in many professions.

To do this you need to take the following steps.

1. Select any cell. Click on the icon for opening the “Insert Function” window. Click on the drop-down list and select the desired category.

1. Then select the desired function. Let's take "COUNTIF" as an example. Immediately after this you will see short information about the selected item. For detailed help, you will need to click on the specified function. To continue, click on “OK”.

1. You will then be asked to provide "Function Arguments". The fields will always change depending on the selected formula. In this case, you need to specify the range of cells and the criterion for counting.

1. Go to the first field. Select the required number of cells.

1. In the second field, as an example, you can specify two empty quotes. This entry means empty text. That is, the cage should not contain anything.

Please note: all arguments must be filled in completely. The formula will not work if one of the fields is empty.

After that, click on “OK”.

1. Thanks to this, you will see a number. This value will correspond to the number of cells that meet your criterion. In this case, we have selected 14 empty cells.

1. If you make any changes, the result of the function will change instantly.

This formula can be edited manually. You don't have to use the Insert Function window to do this. Just click on the desired cell and go to the formula bar.

If this line seems small and inconvenient to you, you need to press the hot keys Ctrl + Shift + U. Thanks to this, its height will increase several times.

To return to the previous mode, you need to repeat the key combination Ctrl + Shift + U.

The size of the formula bar will remain unchanged even after closing Excel. The setting will work every time you start it and beyond. It can only be changed manually.

It is worth noting that you do not need to use a special wizard to create formulas. You just need to copy this code or write it yourself.

You can, if you wish, specify any range and selection criteria you need.

Mathematical and trigonometric functions

This section of formulas is very large. It can be divided into several groups. This will make it easier for you to navigate.

Most beginners think that there is only one way to round a number. But this is not true at all. There are about 15 methods for this. Let's look at each of them.

First, let's create this visual table. Here we can round the same number into one column using several functions. The tabular view will simplify the comparison process.

1. Go to the first cell in this table. Call up the “Insert Function” window. Select the "Math" category. Find the “ROUND” item there and click “OK”.

1. Enter the address of the cell in which your number is located. Then fill in the “Number of digits” field. It determines the number of decimal places after the decimal point. To save, click on “OK”.

1. Thanks to this you will see the following result.

1. Then move your cursor to the lower right corner of that cell. The appearance of your mouse pointer should change. Left-click and, without releasing your finger, swipe to the end of the table.

1. As a result, you will see the following.

1. Repeat the steps above for the remaining functions.

These functions have the following purposes:

• ROUND – rounds the specified number to a certain number of decimal places. The principle of operation is exactly the same as they teach to round in school;
• ROUNDED – rounding to the nearest (modulo) smaller value. In this case, all other characters after the specified accuracy are discarded. In our case, from 1.598 it became simply 1.59. Although according to the rules of mathematics it should be 1.6;
• ROUNDUP – rounding to the nearest (modulo) larger value. The principle of operation is exactly the same as that of “ROUND BOTTOM”;
• ROUND – Rounds a number to the nearest multiple of what is specified in the precision field. In our table, all results are multiples of 2. It was this number that was indicated in the second parameter;
• ROUND UP - the principle of operation is exactly the same as that of the “ROUND” function. Only in this case, rounding occurs to the nearest higher, and not to any multiple;
• OKRVNIZ - the same thing, only to a lesser extent;
• DROP – this function discards the entire fractional part up to the specified number of digits;
• INTEGER – rounds to the nearest smallest number. In this case, only the whole part remains;
• EVEN – the function returns the nearest even integer;
• ODD - the function returns the nearest odd integer.

As you can see, many formulas are analogues of each other.

Functions that end in "MAT" have a third additional parameter. If it is not specified, then the operating principle is exactly the same as for functions without the “MAT” prefix.

To demonstrate this library of functions, let's create a slightly different table. We will fill it out exactly according to the same principle.

These functions allow (the syntax is given specifically for our table):

• SUM – calculate the sum of all cells that are included in the specified range;

• SUMIF – calculate the sum of all cells that are included in the specified range and fulfill a certain condition;

• SUMILIMS – calculate the sum of all cells that are included in the specified range and fulfill several certain conditions;

• SUMPRODUCT – calculate the product of cells from each row of the specified range.

• SUMMKV – calculate the sum of squares of the specified arguments. You can use both prime numbers and large arrays.

• SUMMDISC – calculate the sum of the differences between the squares of the specified arrays. The following formula is used.

• SUMMSUMMQ – calculate the sum of the sums of squares of the specified arrays. The following formula is used.

• SUMMKVRAZN – calculate the sum of squared differences of the specified data arrays. The following formula is used for calculations.

Please note: in all the above cases, the number of array elements must be the same.

The result will be as follows.

Thanks to these formulas, even a teapot can find the desired result or create a financial report. You don't have to be an economist or programmer to use such tools. It is enough to know Russian. English is not needed. All functions speak for themselves.

This function is more complex - this is a task from the institute program. Therefore, we will consider it separately. It returns the sum of a complex power series, rather than a regular array. In this case, the formula uses a large number of arguments. It looks too scary, but it is more understandable than the Laplace equation.

Manually calculating a series of amounts is even more difficult. This is why the Excel editor is in demand, because using it is much easier than thinking with your own brain. And at the same time, the solution will occur instantly, even if you change something in the condition.

As an example, let's take 1 element of the 1st array as argument X. The degree N will start from 1 with a further step of 1. Let's take all the cells of the second array as coefficients.

The result will be huge because we have chosen many values ​​for the last argument. If there were few of them, the figure would decrease, but would still be the largest in the table.

In this case, everything is much simpler. Standard school trigonometric formulas are used here. And they will be called almost exactly the same. These include:

• COS – cosine of the angle;
• COSH – hyperbolic cosine of the angle;
• COT – angle cotangent;
• COTH – hyperbolic cotangent of the angle;
• CSC – angle cosecant;
• CSCH – hyperbolic cosecant of the angle;
• SEC – angle secant;
• SECH – hyperbolic secant of an angle;
• SIN – sine of the angle;
• SINH – hyperbolic sine of the angle;
• TAN – tangent of the angle;
• TANH is the hyperbolic tangent of the angle.

Please note that when calculating, you need to indicate the angle in radians, and not just a number. To convert a number to radians, use the function of the same name. We'll talk about it in detail a little further.

For example, the formula for cosine looks like this.

Using the functions described above, you can build the following table.

You can check the table for consistency and see that all values ​​are entered correctly. And the minus is where it should be, but there are still such a small percentage of errors.

Where there is a very large number or a “division by zero” error, the table value is missing. More precisely, such a value does not exist. But the editor still tries to transform the formula according to the design.

This is what the table looks like in math textbooks. Excel does not display roots - it immediately calculates the final numerical value.

In addition, there are also inverse functions. Namely:

• ACOS – arc cosine of the number;
• ACOSH – hyperbolic arc cosine of a number;
• ACOT – arc cotangent of a number (works since 2013);
• ACOTH – hyperbolic arccotangent of a number (works since 2013);
• ASIN – arcsine of the number;
• ASINH – hyperbolic arcsine of a number;
• ATAN – arctangent of the number;
• ATANH is the hyperbolic arctangent of a number.

To display the result correctly, you need to use the “DEGREES” function. The result will be as follows. You can also check it for consistency with the table values. The check will show that the auto-filling of data was completed correctly.

In some cases we see the error "#NUMBER". This is a consequence of the fact that these values ​​do not exist. For example, the ACOSH formula can use a number greater than or equal to 1. But in our table, values ​​are parsed starting from -1 - this is unacceptable in this function. Somewhere on the contrary - the range of values ​​can be from -1 to 1, and not more. That is, all our empirical results are considered taking into account the rules of mathematics.

Such formulas, as a rule, are often used by students to compose presentations or when performing various laboratory work when they are asked to tabulate a function. This means constructing a graph from table data based on some mathematical expression.

As an example, let's try to draw a graph of exponential growth based on given values.

1. We build a table and indicate the necessary data. You can fill it with any numbers. This will not mean anything critical.

1. Insert the following formula into the first cell of the second column.

Then we duplicate it in the remaining cells (pull the corner of the first result).

1. Then highlight all your values ​​in the table (highlighting the header is allowed). Go to the “Insert” tab and click on the “Graph” icon. Choose any of the proposed layout options.

1. The result will not be entirely correct, since we do not have these numbers. In this case, the values ​​were automatically distributed.

1. Right click on the diagram. In the menu that appears, click on the “Select data” item.

1. Click on the “Change” button.

1. In the window that appears, you will need to specify the range of cells we need. To continue, click on “OK”.

1. Then the same thing.

1. Now the distribution is happening as it should. All values ​​are in their place. In such moments, user control over what is happening is very important, since Excel is not perfect.

As you can see, the growth of the function is not linear at all - it increases very quickly. A small increase is observed only at the beginning. Building such a graph is quite simple.

To demonstrate these formulas, you need to add another table.

Using the "ROMAN" function we were able to write these numbers as Roman numerals. Then, using “ARAB”, we were able to return the appearance to normal for us. Moreover, the replacement took place from the previous transformation. For Excel, it does not matter what this cell will contain - a formula, static or variable text. This is why the practical potential of the functions is incredible.

In trigonometry, the following functions are used for conversion (examples were shown above):

• DEGREES – conversion of radians to degrees;
• RADIANS – convert degrees to radians.

In addition, there is another interesting and useful tool for converting various numbers from some number system to decimal. To demonstrate this function, you need to add another table.

Then you will need to enter the following formulas.

First, the cell number is indicated, then the original number system. The result will be like this.

Arithmetic functions

These formulas include:

• PRODUCT – multiplication of two numbers;
• REMAINDER – removing an integer part from a division;
• POWER – raising the specified number to the required power;
• QUANTIATE – obtaining the integer part from dividing a number;
• EXP – raising the exponent to the specified power;
• ROOT – obtaining the positive root of a specified number.

These functions are taught in middle school. You don’t need to look for any notes for this – it should all be in your head. Even if you have gaps in knowledge, this information should have been retained.

To simply subtract two values, just use the regular minus symbol (“-”). There is no special function for this.

Miscellaneous

• In addition, there are many other mathematical functions that are difficult to combine into one conditional group.
• ABS – module of the specified value;
• AGGREGATE – aggregated list expression (for more details, see the official help);
• FACT – calculation of the factorial of the specified number;
• GCD – search for the greatest common divisor;
• LCM – search for the least common multiple;
• MOPRED – allows you to find the determinant of the matrix of a data array;
• MUMULT – matrix product of numbers of two arrays;
• PI – entering the number “pi” into the formula;
• RAND – random selection of a number from 0 to 1;
• RANDBETWEEN – random value between the specified numbers;
• SIGN – allows you to determine the sign of the specified value;
• SUBTOTAL – consolidation of values ​​and summing up of a list or database.

Information functions

These formulas are mainly a tool for data analysis. It's quite easy to write them down. Their purpose is as follows:

• EMPTY – checking a cell for the presence of some value;
• UND – checking the cell for the #N/A error;
• ENUMBER – checks the value for compliance with the number format;
• ERROR – check for any error;
• ETEXT – the function returns true if the argument contains a text value;
• ENETEXT – a similar check, only in reverse;
• EOS – the function will return a true result if the cell contains any error other than #N/A;
• to check whether a value is even or odd, the formulas EVEN and UNEVEN are used;
• FORMULA – checks for the presence of a formula in the specified cell.

But there is a more complex function that is worth talking about separately.

Using this tool, you can find out all the necessary information about the specified cell. In this case, you must use a mandatory parameter – “Type of information”, with which you can find out:

• color;
• address;
• column;
• and much more.

More details can be found on the Microsoft website.

These constructions are used to build large and complex formulas.

• And – true, if all conditions are true;
• OR – true if at least one condition is true;

The following functions are used to analyze various conditions:

• IF – to check one event;
• CONDITIONS – the same thing, only with a huge number of conditions.

The last of the above appeared only in the Excel 2016 editor. Previously, the “IFS” option was used.

The following table can be used as an example.

In this case, two functions were used at once: “IF” and “OR”.

To check the operation of the formula, you can use the construction with “IFERROR”. If everything is done correctly, you will see the result of the calculations. Otherwise, you will see the entered value in text form.

Link and search functions

• SELECTION – selecting a value from a list (array) of data;
• COLUMN – displays the column number of the specified cell;
• GET.PIVORTABLE.DATA – displays the data stored in the PivotTable report;
• SPR – search in a data array;
• INDEX – selecting a value according to an additional index in the specified range of cells;
• INDIRECT – getting a link that was originally given a text value;
• VIEW – search for values ​​in an array;
• MATCH – searches for the position of specified text or value in a specific range of cells;
• OFFSET – offset of the link relative to the specified link;
• ROW – returns the line number in the specified link;
• TRANSPOSE – transposition of a data array;
• VLOOKUP - searching for a value in one array and retrieving data from a cell in the found row in a specific list of data.

Database functions

There are quite a lot of formulas in this section. Let's look at a few of the most basic and most popular. These include:

• BIZRETCH – search for a record in the database that matches the specified selection condition;
• BDSUMM – the sum of all numbers that are in the specified field and meet certain conditions;
• DMIN – search for the minimum value among the entire data sample from the database;
• DMAX – search for the maximum value among the entire data sample from the database;

This section of formulas is very interesting and useful. Using them, you can quickly analyze some information or extract certain data from a specified date.

• DAY – the day in the specified date is determined;
• MONTH – determines the month of the specified date;
• YEAR – the year in the specified date is determined;
• TODAY – displays the current date;
• WEEK NUMBER – displays the week number based on the specified date;
• TDATE – displays the current date and current time;
• HOUR – determines what hour is indicated on a certain date;
• MINUTES – determines how many minutes are indicated in a certain date;
• SECONDS – determines how many seconds are indicated in a certain date;
• WEEKDAY – the serial number of the day of the week is calculated (counting starts from Sunday, not Monday).

In addition, there are more complex formulas. These include:

• RAZNDAT – the number of years, months and days between the specified dates is calculated;
• DAYS – the number of days between the specified dates is calculated (the function appeared in 2013);
• NETWORKDAYS – the number of working days between the specified dates is calculated;
• WEEKDAY – conversion of a regular date in numeric format into a serial number of the week;
• WORKDAY – displays a date that is behind or ahead of the specified number of days.

You can read more about the latest formula on the official Microsoft website.

The syntax of this function is as follows.

And the examples are quite simple.

There is a huge range of different functions for working with text information in the Excel editor. The most popular include:

• CONNECT – in this case, various pieces are concatenated into one complete text;
• COICH – checking two values ​​for complete compliance with each other;
• FIND, FIND – search for a fragment in another text (the function searches based on the case of letters);
• SEARCH, POISKB – similar search, only case insensitive;
• LEVSIMB, LEVB – copying the first characters of a line (in one case the calculation occurs character by character, and in the other - by byte);
• PRAVSIMV, PRAVB - the same meaning, only counting from the right side;
• LENGTH, LENGTH – number of characters in a line;
• PSTR, PSTRB – copying a fragment of the required number of characters from the specified position for the report;
• REPLACE, REPLACE – replace certain characters in a text string;
• SUBSTITUTE – replacing one text with another;
• TEXT – converting a number into text format;
• COMBINE – combining various text fragments into one whole (in this case, some kind of pointer is inserted).

The last mentioned formula appeared in the latest version of Microsoft Excel 2016. Its syntax is as follows.

This section of formulas is much more complex than those described earlier. In order not to bother with data selection, we will use the official Microsoft help as an example.

PLT

Video instruction

Some users find it difficult to understand instructions in text form. Especially for them, we have prepared a video tutorial with a step-by-step description of all the actions discussed above.

Introduction

1: Microsoft Excel

1.1 Concept and capabilities of MS Excel

1.2 Basic elements of the MS Excel window

1.4 Possible errors when using functions in formulas

2: Data analysis. Using Scripts

2.1 Data analysis in MS Excel

2.2 Scenarios

2.3 Example of calculating the internal investment turnover rate

Conclusion

Bibliography

Introduction

Microsoft Office, the most popular family of office software products, includes new versions of familiar applications that support Internet technologies, allowing you to create flexible Internet solutions

Microsoft Office is a family of Microsoft software products that combines the world's most popular applications into a single environment ideal for working with information. Microsoft Office includes the Microsoft Word word processor, Microsoft Excel spreadsheets, Microsoft PowerPoint presentation tool, and the new Microsoft Outlook application. All of these applications make up the Standard Edition of Microsoft Office. The Professional edition also includes the Microsoft Access DBMS.

Microsoft Excel– a program designed to organize data in a table for documenting and graphically presenting information.

The MSExcel program is used to create complex documents in which it is necessary:

· use the same data in different worksheets;

· change and restore connections.

The advantage of MSExcel is that the program helps to operate with large volumes of information. MSExcel workbooks provide the ability to store and organize data, and calculate the sum of values ​​in cells. MsExcel provides a wide range of methods to make information easy to understand.

Nowadays, it is important for every person to know and have skills in working with Microsoft Office applications, since the modern world is saturated with a huge amount of information that you simply need to be able to work with.

This coursework will present the MSExcel application, its functions and capabilities in more detail. As well as the use of scenarios with their practical application.

1. Microsoft Excel

1.1 . Microsoft Excel . Concepts and features

The spreadsheet processor MS Excel (spreadsheets) is one of the most frequently used applications in the MS Office suite, a powerful tool in capable hands that greatly simplifies routine daily work. The main purpose of MS Excel is to solve almost any calculation problems, the input data of which can be presented in the form of tables. The use of spreadsheets simplifies working with data and allows you to obtain results without programming calculations. In combination with the Visual Basic for Application (VBA) programming language, the MS Excel spreadsheet processor becomes universal and allows you to solve any problem, regardless of its nature.

A special feature of spreadsheets is the ability to use formulas to describe the relationship between the values ​​of different cells. Calculation using specified formulas is performed automatically. Changing the contents of a cell leads to the recalculation of the values ​​of all cells that are connected to it by formula relations and, thereby, to updating the entire table in accordance with the changed data.

Main features of spreadsheets:

1. carrying out complex calculations of the same type over large data sets;

2. automation of final calculations;

3. solving problems by selecting parameter values;

4. processing (statistical analysis) of experimental results;

5. searching for optimal parameter values ​​(solving optimization problems);

6. preparation of tabular documents;

7. constructing diagrams (including summary ones) based on available data;

8. creation and analysis of databases (lists).

1.2. Basic elements of the MS Excel window

The main elements of the working window are:

1. Title bar (the name of the program is indicated in it) with buttons for controlling the program window and the document window (Collapse, Minimize to window or Expand to full screen, Close);

2. Main menu bar (each menu item is a set of commands united by a common functional focus) plus a window for searching for help information.

3. Toolbars (Standard, Formatting, etc.).

4. The formula bar, containing as elements the Name field and the Insert Function (fx) button, is intended for entering and editing values ​​or formulas in cells. The Name field displays the address of the current cell.

5. Work area (active worksheet).

6. Scroll bars (vertical and horizontal).

7. A set of shortcuts (sheet shortcuts) for moving between worksheets.

8. Status bar.

1.3 Spreadsheet structure

A file created using MS Excel is commonly called a workbook. You can create as many workbooks as the availability of free memory on the appropriate memory device allows. You can open as many workbooks as you have created. However, only one current (open) workbook can be an active workbook.

A workbook is a collection of worksheets, each of which has a tabular structure. The document window displays only the current (active) worksheet with which you are working. Each worksheet has a title, which appears on the worksheet tab at the bottom of the window. Using shortcuts, you can switch to other worksheets included in the same workbook. To rename a worksheet, you need to double-click on its tab and replace the old name with a new one or by executing the following commands: Format menu, Sheet line in the menu list, Rename. Or you can, by placing the mouse pointer on the active worksheet shortcut, right-click, then in the context menu that appears, click on the Rename line and perform the renaming. You can add (insert) new sheets to the workbook or delete unnecessary ones. Inserting a sheet can be done by executing the Insert menu command, line Sheet in the list of menu items. The sheet will be inserted before the active sheet. The above actions can also be performed using the context menu, which is activated by clicking the right mouse button, the pointer of which should be placed on the tab of the corresponding sheet. To swap worksheets, you need to place the mouse pointer on the tab of the sheet being moved, press the left mouse button and drag the tab to the desired location.

A worksheet (table) consists of rows and columns. The columns are headed with capital Latin letters and, further, with two-letter combinations. The worksheet contains a total of 256 columns, named A through IV. The lines are numbered sequentially from 1 to 65536.

Table cells are formed at the intersection of columns and rows. They are the minimum elements designed to store data. Each cell has its own address. The cell address consists of the column name and row number at the intersection of which the cell is located, for example, A1, B5, DE324. Cell addresses are used when writing formulas that define the relationship between values ​​located in different cells. At the current moment in time, only one cell can be active, which is activated by clicking on it and highlighted with a frame. This frame acts as a cursor in Excel. Data entry and editing operations are always performed only in the active cell.

Data located in adjacent cells that form a rectangular area can be referenced as a single unit in formulas. A group of cells bounded by a rectangular area is called a range. The most commonly used rectangular ranges are formed at the intersection of a group of sequential rows and a group of sequential columns. A range of cells is indicated by specifying the address of the first cell and the address of the last cell in the range, separated by a colon, for example, B5:F15. Selecting a range of cells can be done by dragging the mouse pointer from one corner cell to the opposite cell diagonally. The frame of the current (active) cell expands, covering the entire selected range.

To speed up and simplify computational work, Excel puts at the user's disposal a powerful apparatus of worksheet functions that allow almost all possible calculations to be carried out.

In total, MS Excel contains more than 400 worksheet functions (built-in functions). All of them, according to their purpose, are divided into 11 groups (categories):

1. financial functions;

2. date and time functions;

3. arithmetic and trigonometric (mathematical) functions;

4. statistical functions;

5. functions of links and substitutions;

6. database functions (list analysis);

7. text functions;

8. logical functions;

9. information functions (checking properties and values);

10.engineering functions;

11.external functions.

Writing any function into a worksheet cell must begin with the equals symbol (=). If a function is used as part of any other complex function or in a formula (megaformula), then the equal symbol (=) is written before this function (formula). Any function is accessed by specifying its name followed by an argument (parameter) or a list of parameters in parentheses. The presence of parentheses is required; they serve as a sign that the name used is the name of a function. List parameters (function arguments) are separated by semicolons (;). Their number should not exceed 30, and the length of a formula containing as many calls to functions as desired should not exceed 1024 characters. When writing (entering) a formula, it is recommended to type all names in lowercase letters, then correctly entered names will be displayed in capital letters.

1.4 Possible errors when using functions in formulas

A function is a special, pre-created formula for complex calculations, into which the user should enter only the necessary arguments. It has a name that describes its purpose (for example, SUM is the addition of several numbers) and arguments. There may be several of the latter, but they are always enclosed in parentheses. Next, we will look at the main functions of Excel and briefly describe their purpose.

Most Popular Features

1. SUM – used in Excel to add values ​​in fields.
2. IF – allows you to return different values ​​depending on the conditions met.
3. VIEW – necessary to search for values ​​located in an identical position, but in a different column or row.
4. VLOOKUP - used to search for information in the entire Excel table or its individual ranges. With its help, it is easy to find a phone number by last name or, vice versa (similar to a phone book).
5. MATCH – used to search for a given element and return its relative position to the range.
6. SELECT – is intended for selecting values ​​from the list.
7. DATE – can return its serial number to a certain period.
8. DAYS – allows you to return the number of days between specified dates.
9. FIND, FIND - detect the occurrence of text in another text string.
10. INDEX – finds a value or a link to it in a range or Excel table and returns it back.

How to enter functions into a table

Adding the required value to the worksheet can be done directly from the keyboard or through a special Excel command located in the “Insert” menu. After selecting the cell and selecting “Insert/Function”, the Wizard dialog box will appear on the screen.

First, you should select a category, and then the Excel function from the alphabetical list. The program will enter an equal sign, title, and parentheses into the cell and open the following dialog box. It should contain arguments (coordinates of the cells in which the necessary data is located). After pressing the “Ok” or Enter button on the keyboard, the resulting function will appear in the line with formulas.

Guys, we put our soul into the site. Thank you for that
that you are discovering this beauty. Thanks for the inspiration and the goosebumps.
Join us on Facebook And In contact with

Microsoft Excel is simply irreplaceable today, especially when it comes to processing large amounts of data. However, this program has so many functions that it is not easy to figure out which of them are really necessary and useful.

And that's why today website will tell you in what ways you can effectively systematize information and put everything in order.

Pivot tables

Pivot tables make it easy to sort, calculate sums, or average values ​​from spreadsheet data without having to write any formulas.

How to use:

1. Select Insert >.
2. In the dialog box Recommended PivotTables Click any PivotTable layout to see a preview of it, and then select the one that displays the data the way you want. Click the button OK.
3. Excel adds a PivotTable to a new worksheet and displays a list of fields that you can use to organize the data in the table.

Parameter selection

If you know what output you want from a formula, but you can't determine the input values ​​that will produce it, use the Parameter Finder.

How to use:

1. Select Data > Working with data > What-if analysis > Parameter selection.
2. In field Set to cell Enter a link to the cell that contains the desired formula.
3. In field Meaning Enter the desired formula result.
4. In field Changing the value cells enter a reference to the cell in which the value to be adjusted is located and click the button OK.

Conditional Formatting

Conditional formatting allows you to quickly highlight important information on a worksheet.

How to use:

On the tab home in Group Styles click the arrow next to the button Conditional Formatting and select the formula you will need.

For example, if you need to highlight all values ​​less than 100, choose Cell selection rules > Less than, and then dial 100. Before you press OK, you can select the format that will be applied to the appropriate values.

INDEX and MATCH

If VLOOKUP helps you find the necessary data only in the first column, then, thanks to the INDEX and SEARCH functions, you can search for information inside the table.

How to use:

1. Make sure that the data cells form a grid with headers and row titles.
2. Use the function SEARCH: First to find the column where the item you are looking for is located, and then again to move to the row with the answer.
3. Paste answers into INDEX, and Excel can point to the cell where those values ​​intersect.