Excel contains powerful formula calculations. A formula is a collection of mathematical operators, numbers, references and functions arranged in a specific order. The result of the calculation is placed in the cell containing the formula. When creating a formula, remember that it must begin with an equal sign "\u003d" then the calculated elements (operands) are placed between the signs of the operations performed (operators).

The operands can be constant values \u200b\u200b(numeric or text constants), references to cells or ranges of cells, names, booleans (such as TRUE or FALSE), and arrays. A constant is a number or text that is directly entered into a cell, for example, the text "Moskovskie Izvestia".

A formula can also include built-in functions that perform standard computational operations. Excel uses over 200 built-in functions.

By default, the spreadsheet calculates formulas as you enter them, recalculates them every time the input data changes.

Operators used in Microsoft Excel

Excel uses the following operators:

· Arithmetic operators - used when working with numbers. The result of performing an arithmetic operation is always a number.

· Comparison operators - used to compare two numbers. As a result of performing a comparison operation, a logical value is obtained: true or false.

· Text operator - used to indicate the operation of combining several sequences of characters into one sequence of characters.

The purpose of the operators is shown in table. 2.2.

Table 2.2

Assignment of operators used in Excel

Arithmetic operator	Operator appointment
+ (plus sign)	Addition
- (minus sign)	Subtraction (or unary minus like -1)
/ (slash)
* (asterisk)	Multiplication
% (percent sign)
^ (cover)	Exponentiation
Comparison Operators
\u003d (equal sign)
\u003e (greater than sign)
< (знак меньше)
\u003e \u003d (greater than sign and equal sign)	More or equal
<= (знак меньше и знак равенства)	Less or equal
<> (less sign and greater than sign)
Text Operator
& (ampersand)	Concatenate two text lines into one
Address operators
: (colon)
; (semicolon)

Arithmetic operators

Arithmetic operators are the most widely used. They provide addition, subtraction, multiplication, division, exponentiation, finding the percentage from the data given in the cells of the spreadsheet. Examples of using arithmetic operators are given in table. 2.3. It is assumed that cells A1, A2 and A3 contain numbers 4, 5 and 10, respectively.

Table 2.3

Examples of using arithmetic operators

Excel performs calculations in formulas from left to right and respects the priority of arithmetic operations accepted in mathematics. The exponentiation operations are performed first, then multiplication and division, and last of all addition and subtraction. brackets are used to change the order of operations.

The parentheses must be paired, spaces before or after the parentheses are not allowed, for example:

15 - numeric constant,

“+” And “/” are addition and division operators.

Operations in parentheses are performed first. For example, in the formula \u003d (A1 + A2 + A3) / 3, first the sum of the numbers contained in cells A1, A2 and A3 is calculated, then the found sum is divided by 3, while in the formula \u003d A1 + A2 + A3 / 3 by 3 only the last term is divided, not the whole sum. Other parentheses can be placed inside the parentheses, which is called parenthesis nesting.

Entering a formula into a cell

To enter a formula in a cell, select it and enter the "\u003d" sign in the formula bar or in the cell. The formula bar is used as a tool to create and modify formulas.

When you switch to formula input mode, the Nameon the left side of the formula bar is replaced by the button showing the last function used. The arrow button located to the right of it opens a list that contains the 10 most recently used functions and the item Other functions (fig. 2.13).

Figure: 2.13. Excel window in formula input mode

As an example, let's enter a formula in cell D4. Having finished entering the formula, press the Enter key or click on the button in the formula bar Input. The result of the calculation will be displayed in the cell containing the formula, the formula itself will be visible in the formula bar (Fig. 2.14).

Figure: 2.14. Displaying a calculation formula in the formula bar

DISPLAYING THE FORMULA IN THE CELL. In the standard setting, the formula cell displays the result of the calculation, not the formula itself. In some cases, for example, when compiling and checking complex calculations in a cell, it is more convenient to display not a numerical value, but the formula itself. To display formulas on the worksheet, choose Parameterson the menu Service, open the tab Viewand framed Window options check the box Formulas.

To return to the accepted display of formulas, clear the check box. To switch to the display mode of formulas or return to normal mode, press Ctrl + `(the backtick key is on the same key as the tilde“ ~ ”- below the Esc key).

For example, we need to calculate the sum of the numbers stored in cells A1, A2, A3. Double click cell A5 and put the formula \u003d A1 + A2 + A3 in it. To calculate the sum of the numbers stored in cells C1, C2, C3, you do not need to rewrite the formula, but copy it from cell A5 to C5. Excel will automatically change the relative links and the formula will look like \u003d C1 + C2 + C3. The modified formula can be seen by clicking cell C5. (To display formulas in the application window, check the Formulas in the tab View dialog box Parameters).

An absolute reference stores the address of a specific cell regardless of the location of the formula cell. For example, if you copy a formula from cell A7 with absolute references to cell C7, the formula will not change. The dollar sign $ is used to indicate an absolute reference. $ A $ 7, $ C $ 7 (fig. 2.15).

Figure: 2.15. Display formulas with absolute and relative references in the Excel window

In cases where, when copying or moving a formula, it is necessary to keep only the row number or only the column name unchanged, use a mixed reference, for example $ D7 or F $ 5. For more information on using an absolute reference in a formula, see the section "Copying a formula" below.

You can reference cells in other sheets of a workbook or workbook, or data from another application. Links to cells in other books are called xrefs. Links to data from other applications are called remote links.

When you enter a cell reference on another worksheet, the worksheet name is separated from the cell name with an exclamation mark. If you enter a link to another workbook, then the name of the workbook file is indicated, which is enclosed in square brackets, for example, [Book2] Sheet1! $ C $ 3.

Copying formulas

Copying the contents of one cell (block of cells) to another (block of cells) is performed to simplify the entry of the same type of data and formulas into the table. When you copy a formula, the relative cell references included in the formula are automatically changed to reflect its new position on the worksheet in the workbook. Absolute cell references are used to disable automatic address configuration. The original formula to be copied or moved is perceived as a kind of template, where the location of the input data is indicated relative to the location of the cell with the formula.

FORMULA COPY BY DRAG METHOD. Here's an example of copying a formula using the drag and drop method. Suppose we need to raise to the third power the numbers 5, 7 and 10, which are in cells A3, A4 and A5. We will write the calculation results in cells B3, B4 and B5, respectively. Select cell B3 and enter the formula \u003d A3 ^ 3 into it (the symbol “^” is used as the exponentiation operator). Press the Enter key. The result will appear in cell 125. Select cell B3 again. Place the mouse pointer over the small black square - the fill marker. Press the mouse button and drag the frame two more cells down. The selected cells will display the results of calculations: 343 and 1000. Click cell B4 - in the formula bar we will see \u003d A4 ^ 3, ie the relative cell address has changed.

If, when copying a formula, it is necessary to leave its address unchanged, then an absolute reference is used. Let's write the formula in the form \u003d $ A $ 3 ^ 3. When you copy this formula to any place in the table, the data in cell A3 will always be raised to the third power. When you copy a formula, relative links are automatically changed, absolute links do not change.

COPYING FORMULAS USING THE COMMAND COPY IN THE EDIT MENU. To copy a formula, select the cell with the formula and select from the menu Editcommand Copy. Then select the cell or range of cells where the formula will be inserted and choose the command Pasteon the menu Edit. The cells to which the formula is copied can be on another sheet or in another workbook. When copying and moving formulas, the links are automatically changed.

Excel standard functions

Excel contains an extensive list of standard functions designed to make simple and complex calculations easier. Functions are defined formulas that perform calculations on user-specified values \u200b\u200bin a specified order. For example, the INCOME function is used to calculate the yield on a bond, which is the periodic interest payments. All functions have the same notation, which includes the function name and argument list. Arguments are in the sequence defined by the function syntax and are separated by commas. The function allows you to perform calculations on sheets of a workbook and on sheets of macros.

Functions are entered into the table as part of formulas or separately. Here are some of the functions that can be used in spreadsheets:

· Mathematical;

· Statistical;

· Text;

· brain teaser;

· Financial;

· Date and time functions, etc.

Math functions are used in scientific and engineering calculations to perform various mathematical operations: calculating logarithms, trigonometric functions, converting radians to degrees, etc.

Statistical functions are used to analyze ranges of data, calculate parameters characterizing random variables represented by a set of numbers, or their distributions, for example, standard deviation, mean value, etc. In particular, we can find the equation of a straight or exponential curve that is in optimal agreement with experimental data ...

Text functions convert a numeric value to formatted text, and the result is no longer included in the calculation as a number. They allow you to calculate the length of a string, convert uppercase letters to lowercase, etc. Ampersand (&) is used to combine multiple text strings into one string.

For example, in cell B3 you can write down the name of the seller (Petrov), in cell C3 - his sales volume (5000). After writing in any cell B3 & "sold" & TEXT (C3; "0.00 rubles") & "items", the calculations will combine the contents of the cells into one phrase: Petrov sold 5,000.00 rubles of items of goods.

Logic functions are used to construct logical expressions, the result of which depends on the truth of the tested condition.

Financial functions are used in complex financial calculations, for example, determining the discount rate, the amount of monthly payments to repay a loan, determining depreciation deductions, etc.

Functions for working with dates and times... Function date calculates a numeric date as the number of days that have passed since January 1, 1900. Function Time Is a fraction of a 24 hour interval, written as a decimal fraction, which can range from 0 to 0.99999999. Zero corresponds to 12:00:00 at night. Hours range from 0 to 23, minutes and seconds from 0 to 59.

Here are some examples of the most common functions.

SUM (List) is a statistical function for determining the sum of all numeric values \u200b\u200bin a List. The list can consist of cell and block addresses, as well as numeric values.

SUM (A2: A5; C1: C8)

AvgVal (List) is a statistical function for determining the arithmetic mean of all quantities listed in the List.

AvgVal (B1: B10)

MAX (List) is a statistical function that returns the maximum value in the specified List.

MAX (A3: A8; 253)

IF (Condition, True, False) is a logical function that checks a given logical condition for truth. If the condition is met, then the result of the function is the value of the argument "True". If the condition is not met, then the result of the function becomes the value of the argument "False".

IF (B4<100, 100,200)

- if cell B4 contains a number less than 100, then the function is assigned the value 100, if this condition is not met (that is, the content of cell B4 is greater than or equal to 100), the function is assigned the value 200.

DIFFERENT OPTIONS FOR PERFORMANCE OF CALCULATIONS. In most cases, Excel provides several options for performing calculations. Let us show this with an example of data summation. To add numbers in a range of cells, use the SUM function, which is the most commonly used function.

Let us introduce the terms into cells D3, D4, D5. Summation can be done in the following ways:

Method I.

Select cell D6 and enter the formula for the calculation into it, making a reference to the cells containing the original data:

Press Enter and the result will appear in cell D6.

Method II

Enter the "\u003d" sign in cell D6, click on cell D3 - after the equal sign in cell D6, the address of cell D3 will be displayed.

Put the “+” sign and click cell D4. In cell D6, the entry “\u003d D3 + D4” appears. Enter the "+" sign, click cell D5 and press Enter. The result will appear in cell D6

III way.

Click on cell D6 and press the button Autosum on the toolbar Standard... The entry \u003d SUM (D3: D5) appears in the cell, i.e. it is proposed to sum up the data recorded in cells from D3 to D5, located in the same column with D6.

Press the Enter key and we will see the summation result. When you select a range of cells and click Autosum in the blank cell following the range, a formula will be inserted to calculate the sum of these cells.

IV method.

Select cell D6 and enter into it from the keyboard a formula containing the function \u003d SUM (D3: D5).

V method.

Let's click cell D6 and use the function wizard to enter the formula.

Using links in functions

To refer to a range of cells, enter a reference to the top-left cell of the range, insert a colon (:), and then a reference to the lower-right corner of the range. For example A2: C5. All cells between rows 6 and 15, inclusive, are referenced 6:15, and all cells in column C - C: C.

Examples of cell range entries in a function:

· \u003d SUM (E: E) - the sum of the numerical values \u200b\u200bcontained in column E is determined;

· \u003d SUM (E2: G5) - the sum of numeric values \u200b\u200blocated in the range from E2 to G5 is calculated;

· \u003d SUM (5: 5) - the data of all cells of the fifth row are summed up;

· \u003d SUM (2; 4) - the sum of 2 + 4 is calculated.

Mixed notation of addresses of cells and blocks of cells is allowed. In this case, the formula might look like this:

SUM (C8; D4; E2: E5; F5)

Performing a calculation using standard functions

A function begins by specifying its name, followed by an opening parenthesis, arguments, and a closing parenthesis. The function may have no arguments. It can be entered into a cell in a worksheet as part of a formula. The function allows you to perform calculations on sheets of a workbook and on sheets of macros.

To insert a function, press the button Inserting a function, The formula bar appears on the screen. The formula bar also appears when you click the button Inserting a function on the toolbar Standard... If the formula is not written correctly, a circular reference message may appear on the screen. Function names can be typed in lowercase letters. They will be converted to uppercase after pressing the Enter key. Functions that are an argument to another function are called nested. You can use up to seven levels of function nesting in Excel formulas.

To perform a calculation using a standard function, follow these steps:

1. Select the cell where you want to insert the function, enter “\u003d”, and then in the drop-down list Functionsin the formula bar, select the one you want from the list. A dialog box appears on the screen. Function arguments. If you select from the drop-down list Other functions, then a dialog box will open Feature Wizard - Step 1 of 2 (Fig. 2.16).

Other ways to display the dialog box Feature Wizard - Step 1 of 2:

Press the button Inserting a function in the formula bar;

Select a team Functionon the menu Insert.

Figure: 2.16. Feature Wizard Dialog Box - Step 1 of 2

2. In the dialog box Feature Wizard - Step 1 of 2 in field Search function enter a description of the action you would like to perform. For example, to find functions related to the logarithms of numbers, type log... If you don't know which category your function belongs to, then in the dropdown or categoryselect line Complete alphabetical listing and browse the list of all functions in alphabetical order. The scroll bar allows you to view the currently invisible list items. At the bottom of the window is the definition of the highlighted function and its arguments. For a description of a feature, select a feature from the list and click the link Help for this function... Select the required line in the list Select functionand press the button OK or the Enter key.

3. A dialog box will appear on the screen. Function arguments(Fig. 2.17). In the upper part of the window, there are fields for entering arguments, in the lower part - reference information: the name of the selected function, all its arguments, the purpose of the function and each argument, the current result of the function and the entire formula. In cases where the argument is in bold, it is required; if in normal, then it can be omitted.

To prevent the formula bar from covering the range of data cells, you can move it by holding down the mouse button. To enter a cell reference as an argument, click the icon located at the right edge of the field.

The dialog box height decreases. With the cells selected, click the icon to return the dialog box to its original size. If the dialog box Function argumentsallows you to enter multiple arguments, you can use the Tab key to navigate from one argument field to another. After entering the arguments, the current result will be displayed.

Figure: 2.17. Using the Formula Bar to Estimate Sample Variance

EXAMPLE 1. Excel helps you calculate various reference data without using reference data. Find the decimal logarithm of 250. Perform the exercise as follows:

Inserting a function Feature Wizard - Step 1 of 2 Category highlight the line Mathematical, and in the lower list Select function – LOG10 and press the button OK... A dialog box appears on the screen. Function arguments (fig.2.18). Enter the number 250 on the line Number dialog box and get a result of 2.398.

Figure: 2.18. Calculating the Decimal Logarithm

EXAMPLE 2. Find the values \u200b\u200bof trigonometric functions: sine, cosine, tangent, cotangent for an angle of 10 degrees.

Select the cell where you want to insert the function and press the button Inserting a function on the formula bar. In the dialog box Feature Wizard - Step 1 of 2 (see figure 2.16) in the dropdown list Category highlight the line Mathematical, and in the lower list Select function select the appropriate trigonometric function and press the button OK... Enter the number 10 on the line Number dialog box Function arguments and see the result. For reference, we present the values \u200b\u200bof trigonometric functions: sin 100 \u003d 0.17, cos100 \u003d 0.98, tg 100 \u003d 0.18, ctg 100 \u003d 5.67.

EXAMPLE 3. Determine the arithmetic mean of the following values: 5, 20, 10, 8. Place the indicated values \u200b\u200bin different cells, for example, B6, B10, B13 and B17. Place the cursor in the cell where the calculation result will be located and click the button Inserting a function on the formula bar. In the dialog box Feature Wizard - Step 1 of 2 (see figure 2.16) in the dropdown list Category highlight the line Statistical, and in the lower list Select functioninstall AVERAGEIn the window Function argumentsclick the minimize button located to the right of the Number 1 field and select the range of cells in which the numbers are located. Press the key Input... At the bottom of the window Function argumentsyou will read the mean value, which after pressing the button OK will be placed in the cell you specified.

Note

In any cell in the workbook, you can insert a note containing additional information. Select a cell and choose the command Noteon the menu Insert. A frame appears next to the cell with the name of the comment author. The box is where you enter the note text for that cell. The author of the note is indicated on the tab General in the dialog box Parameters, displayed on the screen after selecting the command of the same name in the menu Service... The size of the frame does not determine the amount of information presented in the note, but only how much of the note will be displayed on the screen. When you pause the mouse pointer in a cell containing a note icon (a red triangle in the upper right corner of the cell), a pop-up window with the note text will automatically appear.

If the tab Viewdialog box Parametersin Group Notes switch set to position Indicator only, then cells containing notes are marked in the upper right corner with an indicator that looks like a red triangle. If the switch is in position Note and indicator, then both the indicator and the part of the note bounded by the frame are visible (Fig. 2.19).

Figure: 2.19. Display indicator and cell comment text

Notes made by different users for the same cell will be displayed in the same note window and tagged with the names of the respective users. This capability can be used in conjunction with the Windows portfolio. The user can work with a copy of the shared book, disconnected from the local network, then put it in the briefcase and combine with other copies when the network connection is restored.

Error message

An error message begins with a # sign, for example, if #VALUE! Appears in a cell when performing calculations, this means that the program cannot find the original data. The type of message changes depending on the cause of the error. So, the message # DIV / 0! appears when the formula proposes to divide by zero (Fig. 2.20). When you move the mouse over the smart tag icon, a button is displayed next to them. Click the icon to open a menu from which you can find out what type of error was detected, view the calculation steps, make changes to the formula bar, and more.

Figure: 2.20. Error message

If Excel thinks that the error in the entered expression can be corrected, a window will appear with a suggestion on how to edit the formula.

Start creating formulas and using built-in functions to perform calculations and problem solving.

Important: The calculated results from formulas and some Excel worksheet functions may differ slightly on x86 or x86-64 Windows computers and ARM-based Windows RT computers.

Create a formula that references values \u200b\u200bin other cells

View Formula

Entering a formula containing a built-in function

Downloading the Formula Tutorial

We have prepared for you the book Getting Started with Formulas, which is available for download. If this is your first time using Excel, or even have some experience with the program, this tutorial will help you familiarize yourself with the most common formulas. Thanks to illustrative examples, you can calculate the amount, quantity, average value and substitute data as well as professionals.

Details of formulas

To learn more about specific formula elements, see the relevant sections below.

Parts of an Excel Formula

A formula can also contain one or more elements such as function, links, operators and constants.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, or suggest a new feature or improvement on the website.

In this lesson, you will see how to quickly calculate percentages using Excel, get acquainted with the basic formula for calculating percentages and learn a few tricks that will make your work with interest easier. For example, the formula for calculating the percentage increase, calculating the percentage of the total, and something else.

Knowing how to work with interest can be useful in a wide variety of areas of life. This will help you estimate the amount of tips in a restaurant, calculate commissions, calculate the profitability of any enterprise and the degree of your personal interest in this enterprise. Tell me honestly, would you be delighted if you were given a promo code for a 25% discount to buy a new plasma? Sounds tempting, right ?! And how much you actually have to pay, can you calculate?

In this tutorial, we will show you a few techniques to help you calculate percentages easily with Excel, as well as introduce you to the basic formulas that are used to work with percentages. You will learn some tricks and can hone your skills by sorting out solutions to practical problems by percentage.

Basic knowledge of interest

Term Percent (per cent) came from Latin (per centum) and was originally translated as FROM HUNDREDS... In school, you learned that percentage is some part of 100 parts of a whole. The percentage is calculated by division, where the required part is in the numerator of the fraction, and the integer is in the denominator, and then the result is multiplied by 100.

The basic formula for calculating interest looks like this:

(Part / Whole) * 100 \u003d Percentage

Example: You had 20 apples, of which 5 you distributed to your friends. What percentage of your apples did you give away? Having made simple calculations, we get the answer:

(5/20)*100 = 25%

This is how you were taught to calculate interest in school, and you use this formula in your daily life. Calculating percentages in Microsoft Excel is an even easier task, since many mathematical operations are performed automatically.

Unfortunately, there is no universal formula for calculating percentages for all occasions. If you ask the question: what formula for calculating percentages to use to get the desired result, then the most correct answer would be: it all depends on what result you want to get.

I want to show you some interesting formulas for working with percentage data. These are, for example, the formula for calculating the percentage increase, the formula for calculating the percentage of the total, and some more formulas that are worth paying attention to.

The basic formula for calculating percentage in Excel

The basic formula for calculating percentage in Excel looks like this:

Part / Whole \u003d Percentage

If you compare this formula from Excel with the familiar formula for percentages from a math course, you will notice that there is no multiplication by 100 in it. When calculating the percentage in Excel, you do not need to multiply the result of division by 100, as Excel will do this automatically if for a cell given Percentage format.

Now let's see how calculating percentages in Excel can help in real-world data manipulation. Suppose you have a certain number of ordered items (Ordered) in column B, and data on the number of items delivered (Delivered) is entered in column C. To calculate what proportion of orders have already been delivered, we will do the following:

• Write down the formula \u003d C2 / B2 in cell D2 and copy it down as many lines as necessary using the autocomplete marker.
• Press command Percent Style (Percentage format) to display the division results in percentage format. It's on the tab Home (Home) in the team group Number (Number).
• If necessary, adjust the number of characters displayed to the right of the decimal point.
• Done!

If you use any other formula to calculate percentages in Excel, the general sequence of steps remains the same.

In our example, column D contains values \u200b\u200bthat show, as a percentage, what proportion of the total number of orders are already delivered orders. All values \u200b\u200bare rounded to whole numbers.

Calculating the percentage of the total in Excel

In fact, the example given is a special case of calculating the percentage of the total amount. To better understand this topic, let's look at a few more tasks. You will see how you can quickly calculate the percentage of the total in Excel using the example of different data sets.

Example 1. The total amount is calculated at the bottom of the table in a specific cell

Very often, at the end of a large data table, there is a cell labeled Total, in which the total is calculated. At the same time, we are faced with the task of calculating the share of each part relative to the total amount. In this case, the formula for calculating the percentage will look the same as in the previous example, with one difference - the cell reference in the denominator of the fraction will be absolute (with $ signs in front of the row name and column name).

For example, if you have some values \u200b\u200brecorded in column B, and their total is in cell B10, then the formula for calculating percentages will be as follows:

Hint: There are two ways to make the cell reference in the denominator absolute: either enter the sign $ manually, or select the required cell reference in the formula bar and press F4.

The figure below shows the result of calculating the percentage of the total. Percentage format with two decimal places is selected to display data.

Example 2. Parts of the total amount are on several lines

Imagine a table with data like the previous example, but here the product data is scattered across multiple rows in the table. It is required to calculate what part of the total amount is made up of orders for a particular product.

In this case, we use the function SUMIF (SUMIF). This function allows you to sum up only those values \u200b\u200bthat meet a certain criterion, in our case, this is a given product. We use this result to calculate the percentage of the total.

SUMIF (range, criteria, sum_range) / total
\u003d SUMIF (range, criterion, sum_range) / total

In our example, column A contains the names of products (Product) - this is range... Column B contains information about the quantity (Ordered) - this is sum_range... In cell E1 we enter our criterion - the name of the product for which you want to calculate the percentage. The total for all products is calculated in cell B10. The working formula will look like this:

SUMIF (A2: A9, E1, B2: B9) / $ B $ 10
\u003d SUMIF (A2: A9, E1, B2: B9) / $ B $ 10

By the way, the product name can be entered directly into the formula:

SUMIF (A2: A9, "cherries", B2: B9) / $ B $ 10
\u003d SUMIF (A2: A9; "cherries"; B2: B9) / $ B $ 10

If you need to calculate how much of the total amount is made up of several different products, then you can add up the results for each of them and then divide by the total amount. For example, this is how the formula will look like if we want to calculate the result for cherries and apples:

\u003d (SUMIF (A2: A9, "cherries", B2: B9) + SUMIF (A2: A9, "apples", B2: B9)) / $ B $ 10
\u003d (SUMIF (A2: A9; "cherries"; B2: B9) + SUMIF (A2: A9; "apples"; B2: B9)) / $ B $ 10

How to calculate percentage change in Excel

One of the most popular tasks that you can accomplish with Excel is calculating percentage changes in data.

Excel formula calculating percentage change (increase / decrease)

(B-A) / A \u003d Percentage change

Using this formula in working with real data, it is very important to correctly determine what value to put in place. A, and which one - in place B.

Example: Yesterday you had 80 apples, and today you have 100 apples. This means that today you have 20 more apples than yesterday, that is, your result is an increase of 25%. If yesterday there were 100 apples, and today there are 80, then this is a 20% decrease.

So, our formula in Excel will work as follows:

(New value - Old value) / Old value \u003d Change in percentage

Now let's see how this formula works in Excel in practice.

Example 1. Calculating the percentage change between two columns

Suppose that column B contains the prices of the last month (Last month), and column C contains the prices that are current for this month (This month). In column D, we enter the following formula to calculate the change in price from last month to the current month as a percentage.

This formula calculates the percentage change (increase or decrease) in the price this month (column C) compared to the previous month (column B).

After you write the formula in the first cell and copy it to all the necessary lines by pulling the autocomplete marker, do not forget to set Percentage format for cells with formula. As a result, you should have a table similar to the one shown in the figure below. In our example, positive data, which shows an increase, is displayed in standard black, and negative values \u200b\u200b(decrease in percentage) are highlighted in red. For details on how to set up such formatting, read this article.

Example 2. Calculation of the percentage change between lines

In the case when your data is located in one column, which reflects information about sales per week or per month, the change in percentage can be calculated using the following formula:

Here C2 is the first value and C3 is the next value in order.

Comment: Please note that with such an arrangement of data in the table, the first data row must be skipped and the formula must be written from the second row. In our example, this will be cell D3.

After you write down the formula and copy it into all the necessary rows of your table, you should get something similar to this:

For example, this is how the formula for calculating the percentage change for each month in comparison with the indicator will look like January (January):

When you copy your formula from one cell to all the others, the absolute reference will remain unchanged, while the relative reference (C3) will change to C4, C5, C6, and so on.

Calculating the value and the total for a known percentage

As you can see, calculating percentages in Excel is easy! It is just as easy to calculate the value and the total amount at a known percentage.

Example 1. Calculating a value from a known percentage and total amount

Suppose you buy a new computer for $ 950, but to that price you need to add 11% VAT. The question is - how much do you need to pay extra? In other words, 11% of the indicated cost is how much in foreign currency?

This formula will help us:

Total * Percentage \u003d Amount
Total Amount * Interest \u003d Value

Let's pretend that total amount (Total) is written in cell A2, and Interest (Percent) - in cell B2. In this case, our formula will look pretty simple \u003d A2 * B2 and will give the result $104.50 :

It is important to remember: When you manually enter a numeric value into a table cell followed by a% sign, Excel understands this as hundredths of the entered number. That is, if you enter 11% from the keyboard, then in fact the cell will store the value 0.11 - this is the value Excel will use when performing calculations.

In other words, the formula \u003d A2 * 11% is equivalent to the formula \u003d A2 * 0.11... Those. in formulas, you can use either decimal values \u200b\u200bor values \u200b\u200bwith a percent sign - whichever is more convenient for you.

Example 2. Calculation of the total amount for a known percentage and value

Let's say your friend offered to buy his old computer for $ 400 and said it was 30% cheaper than its full price. Do you want to know how much this computer originally cost?

Since 30% is a decrease in the price, the first thing we do is subtract this value from 100% to calculate how much of the original price you need to pay:

Now we need a formula that will calculate the initial price, that is, find the number, 70% of which is equal to $ 400. The formula will look like this:

Amount / Percentage \u003d Total
Value / Percentage \u003d Total

To solve our problem, we get the following form:

A2 / B2 or \u003d A2 / 0.7 or \u003d A2 / 70%

How to increase / decrease a value by a percentage

With the onset of the holiday season, you notice certain changes in your usual weekly items of expenditure. You may want to make some additional adjustments to the calculation of your spending limits.

To increase the value by a percentage, use the following formula:

Value * (1 +%)

For example, the formula \u003d A1 * (1 + 20%) takes the value in cell A1 and increases it by 20%.

To decrease the value by a percentage, use the following formula:

Value * (1-%)

For example, the formula \u003d A1 * (1-20%) takes the value in cell A1 and decreases it by 20%.

In our example, if A2 is your current expenses, and B2 is the percentage by which you want to increase or decrease their value, then you need to write the following formula in cell C2:

Increase by percentage: \u003d A2 * (1 + B2)
Decrease by percentage: \u003d A2 * (1-B2)

How to increase / decrease all values \u200b\u200bin a column by a percentage

Suppose you have a whole column filled with data that needs to be increased or decreased by some percentage. In this case, you do not want to create another column with the formula and new data, but change the values \u200b\u200bin the same column.

We need only 5 steps to solve this problem:

In both formulas, we took 20% as an example, and you can use the percentage value that you need.

This will increase the values \u200b\u200bin column B by 20%.

In this way, you can multiply, divide, add or subtract a percentage from a whole column of data. Just enter the percentage you want in the blank cell and follow the steps outlined above.

These methods will help you calculate percentages in Excel. And even if percent has never been your favorite area of \u200b\u200bmathematics, knowing these formulas and techniques, you will make Excel do all the work for you.

That's all for today, thank you for your attention!

A formula is a mathematical expression that you create to calculate a result and that can depend on the contents of other cells. A formula in a cell can contain data, references to other cells, and an indication of actions to be taken.

Using cell references allows you to recalculate the result by formulas when there are changes in the contents of cells included in the formulas.

In Excel, formulas begin with an \u003d sign. Brackets () can be used to define the order of mathematical operations.

Excel supports the following operators:

• Arithmetic operations:
  • addition (+);
  • multiplication (*);
  • finding the percentage (%);
  • subtraction (-);
  • division (/);
  • exponent (^).
• Comparison operators:
  • \u003d equal;
  • < меньше;
  • \u003e more;
  • <= меньше или равно;
  • \u003e \u003d greater than or equal;
  • <> not equal.
• Telecom operators:
  • : range;
  • ; Union;
  • & operator of joining texts.

Table 22. Examples of formulas

An exercise

Insert formula -25-A1 + AZ

Pre-enter any numbers in cells A1 and A3.

1. Select the required cell, for example B1.
2. Start typing the formula with the \u003d sign.
3. Enter the number 25 followed by an operator (- sign).
4. Enter a reference to the first operand, for example, by clicking on the desired cell A1.
5. Enter the following operator (+ sign).
6. Click in the cell that is the second operand in the formula.
7. Finish entering the formula by pressing Enter... In cell B1, get the result.

Auto-summation

Button AutoSum- ∑ can be used to automatically create a formula that sums the area of \u200b\u200badjacent cells that are directly leftin the given line and directly higherin this column.

1. Select the cell where you want to place the summation result.
2. Click the AutoSum - ∑ button or press the key combination Alt + \u003d. Excel will decide which area to include in the summation range and will highlight it with a dashed moving box called a border.
3. Click Enterto accept the area that Excel selected, or select a new area with the mouse and then press Enter.

AutoSum automatically transforms when cells are added or removed within an area.

An exercise

Creating a table and calculating formulas

1. Enter numeric data into the cells as shown in the table. 23.

	AND	IN	FROM	D	B	F
1
2		Magnolia	Lily	Violet	Total
3	Higher	25	20	9
4	Secondary spec.	28	23	21
5	Vocational school	27	58	20
in	Other	8	10	9
7	Total
8	Without higher

Table 23. Source data table

1. Select cell B7 where the vertical sum will be calculated.
2. Click the AutoSum - ∑ button or press Alt + \u003d.
3. Repeat steps 2 and 3 for cells C7 and D7.

Calculate the number of employees without higher education (using the formula B7-OZ).

1. Select cell B8 and type the (\u003d) sign.
2. Click in cell B7, which is the first operand in the formula.
3. Enter the (-) sign on the keyboard and click in the OT cell, which is the second operand in the formula (the formula will be entered).
4. Click Enter(the result will be calculated in cell B8).
5. Repeat steps 5-8 to calculate the corresponding formulas in cells C8 and 08.
6. Save the file with the name Staff_Education.x1s.

Table 24.Calculation result

	AND	B	FROM	D	E	F
1	Distribution of employees by education
2		Magnolia	Lily	Violet	Total
3	Higher	25	20	9
4	Secondary spec.	28	23	21
5	Vocational school	27	58	20
6	Other	8	10	9
7	Total	88	111	59
8	Without higher	63	91	50

Duplicate formulas with a fill handle

An area of \u200b\u200bcells (cell) can be multiplied by using fill marker.As shown in the previous section, the fill handle is a control point in the lower-right corner of the selected cell.

It is often necessary to reproduce not only data, but also formulas containing address links. The process of replicating formulas using a fill marker allows you to color the formula while changing the address links in the formula.

1. Select the cell containing the formula to replicate.
2. Drag and drop fill markerin the right direction. The formula will be multiplied in all cells.

Typically, this process is used when copying formulas within rows or columns containing data of the same type. When replicating formulas using a fill marker, the so-called relative addresses of cells in the formula change (in detail, relative and absolute references will be described below).

An exercise

Replication of formulas

1.Open the file Education_Employees.x1s.

1. Enter into cell EZ the formula for autosum cells \u003d SUM (OZ: 03).
2. Copy, by dragging the fill handle, the formula into cells E4: E8.
3. Look at how the relative addresses of the cells change in the resulting formulas (Table 25) and save the file.

	AND	IN	FROM	D	E	F
1	Distribution of employees by education
2		Magnolia	Lily	Violet	Total
3	Higher	25	20	9	\u003d SUM (OT: 03)
4	Secondary spec.	28	23	21	\u003d SUM (Q4: 04)
5	Vocational school	27	58	20	\u003d SUM (B5: 05)
6	Other	8	10	9	\u003d SUM (B6: 06)
7	Total	88	111	58	\u003d SUM (B7: 07)
8	Without higher	63	91	49	\u003d SUM (Q8: 08)

Table 25. Changing addresses of cells when replicating formulas

Relative and absolute links

Formulas that implement calculations in tables use so-called references to address cells. Cell reference can be relativeor absolute.

Using relative references is analogous to indicating the direction of travel on a street - "walk three blocks north, then two blocks west." Following these instructions from different starting locations will lead to different destinations.

For example, a formula that adds numbers in a column or row is then often copied for other row or column numbers. These formulas use relative references (see the previous example in Table 25).

Absolute cell reference. Id and cell area will always refer to the same row and column address. When compared with the directions of the streets, it will be something like this: "Go to the intersection of the Arbat and the Boulevard Ring". Regardless of the starting location, this will lead to the same location. If the formula requires that the cell address remains unchanged when copied, then an absolute reference must be used (record format $ A $ 1). For example, when a formula calculates a fraction of a total, the reference to the cell containing the total should not change when copied.

A dollar sign ($) will appear both in front of a column reference and before a row reference (for example, $ C $ 2), Successive pressing of F4 will add or remove a sign before the column or row number in the reference (C $ 2 or $ C2 - the so-called mixed links).

1. Create a table similar to the one below.

Table 26. Payroll calculation

1. In cell SZ, enter the formula for calculating Ivanov's salary \u003d B1 * OT.

When replicating the formula of this example with relative references in cell C4, an error message appears (#VALUE!), Since the relative address of cell B1 will change, and the formula \u003d B2 * B4 will be copied to cell C4;

1. Specify an absolute reference to cell B1 by placing the cursor on B1 in the formula bar and pressing F4. The formula in cell C3 will look like \u003d $ B $ 1 * OT.
2. Copy the formula into cells C4 and C5.
3. Save the file (Table 27) under the name Salary.xls.

Table 27. Results of payroll calculation

Names in formulas

Names in formulas are easier to remember than cell addresses, so named areas (one or more cells) can be used instead of absolute references. The following rules must be followed when creating names:

• names can contain no more than 255 characters;
• names must start with a letter and can contain any character except a space;
• names should not look like references, such as OT, C4;
• names should not use Excel functions such as SUM, IF etc.

On the menu Insert, Name There are two different commands for creating named areas: Create and Assign.

Command Create allows you to set (enter) the required name ( only one), assign command uses the labels placed on the worksheet as area names (it is allowed to create several names at once).

Name creation

1. Select cell B1 (Table 26).
2. Select from the menu Insert, Name command Define.
3. Enter your name Hourly rate and click OK.
4. Highlight cell B1 and make sure the name field is Hourly rate.

Creating multiple names

1. Select cells B3: C5 (Table 27).
2. Select from the menu Insert, Name, Create command, a dialog box appears Create names (fig. 88).
3. Make sure the radio button in the column on the left is checked and click OK.
4. Highlight cells OT: SZ and make sure the name field is Ivanov.

Figure: 88. New Names Dialog Box

You can insert a name into a formula instead of an absolute reference.

1. On the formula bar, position the cursor where the name will be added.
2. Select from the menu Insert, Name (Insert, Name) command Paste (Paste),the Insert Names dialog box appears.

1. Select the required name from the list and press OK.

Formula errors

If a mistake is made when entering formulas or data, an error message appears in the resulting cell. The first character of all error values \u200b\u200bis the # character. The error values \u200b\u200bdepend on the type of error made.

Excel can not recognize all errors, but those that are found, you need to be able to fix.

Mistake # # # # appears when the entered number does not fit in the cell. In this case, increase the column width.

# DIV / 0 error! appears when an attempt is made to divide by zero in a formula. This most often happens when a reference to a cell containing a null or empty value is used as the divisor.

# N / A error! is an abbreviation for "undefined data". This error indicates the use of a blank cell reference in the formula.

#NAME error? appears when the name used in the formula has been deleted or has not been previously defined. To correct, define or correct the data area name, function name, etc.

Error # NULL!appears when you specify the intersection of two regions that do not actually have cells in common. More often than not, an error indicates that a mistake was made when entering references to ranges of cells.

Error #NUM! appears when an invalid format or argument value is used in a function with a numeric argument.

Error #VALUE! appears when an invalid argument or operand type is used in a formula. For example, text is entered instead of a numeric or logical value for an operator or function.

In addition to the errors listed, a circular reference may appear when entering formulas.

A circular reference occurs when a formula directly or indirectly includes references to its own cell. Cyclic reference can cause distortion in worksheet calculations and is therefore considered a bug in most applications. When you enter a circular reference, a warning message appears (Fig. 89).

To correct the error, delete the cell that caused the circular reference, edit or re-enter the formula.

Functions in Excel

More complex calculations in Excel tables are carried out using special functions (Fig. 90). The list of function categories is available when you select the Functionon the Insert menu (Insert, Function).

Financial functions perform calculations such as calculating the amount of payment on a loan, the amount of payment of profit on investments, etc.

The Date and Time functions let you work with date and time values \u200b\u200bin formulas. For example, you can use the current date in a formula using the function TODAY.

Figure: 90. Function Wizard

Mathematical functions are performed by simple and complex mathematical calculations, such as calculating the sum of a range of cells, the absolute value of a number, rounding numbers, and more.

Statistical functions allow you to perform statistical analysis of data. For example, you can determine the mean and variance for a sample, and more.

Database functions can be used to perform calculations and to conditionally select records.

Text functions provide the user with the ability to process text. For example, you can concatenate multiple strings using the function COUPLING.

Logic functions are designed to test one or more conditions. For example, the IF functionallows you to determine if a specified condition is met and returns one value if the condition is true and another if it is false.

Functions Checking Properties and Values are designed to define the data stored in a cell. These functions check the values \u200b\u200bin the cell by condition and return the value depending on the result TRUE or FALSE.

For table calculations using built-in functions, it is recommended that you use the Function Wizard. The Function Wizard dialog is available when you select the command Function in the Insert menu or pressing the button , on the standard toolbar. During the dialogue with the wizard, you need to specify the arguments of the selected function; for this, you need to fill in the fields in the dialog box with the corresponding values \u200b\u200bor addresses of the table cells.

An exercise

Calculation of the average value for each line in the Education.xls file.

1. Highlight cell F3 and click on the Function Wizard button.
2. In the first dialog box of the Function Wizard, from the Statistical category, select the function AVERAGE, click the button Further.
3. Arguments must be specified in the second dialog of the function wizard. The input cursor is in the input field of the first argument. This field contains a number as an argument! enter the address of the range B3: D3 (Fig. 91).
4. Click OK.
5. Copy the resulting formula into cells F4: F6 and save the file (Table 28).

Figure: 91. Entering an argument in the function wizard

Table 28. Table of calculation results using the function wizard

	AND	IN	FROM	D	E	F
1	Distribution of employees by education
2		Magnolia	Lily	Violet	Total	The average
3	Higher	25	20	9	54	18
4	Secondary spec.	28	23	21	72	24
8	Vocational school	27	58	20	105	35
in	Other	8	10	9	27	9
7	Total	88	111	59	258	129

To enter a range of cells in the function wizard window, you can circle this range with the mouse on the worksheet of the table (in the example B3: D3). If the function wizard window closes the desired cells, you can move the dialog box. After selecting a range of cells (B3: D3), a scrolling dotted frame will appear around it, and the address of the selected range of cells will automatically appear in the argument field.

Speaker Deck SlideShare

The basics of using formulas in Excel. Rules for entering formulas, permitted mathematical operations, calculation order. Function and formula, frequently used functions of the AutoSum group.

Microsoft Office Specialist Exam Skills (77-420):

Theoretical part:

1. Building simple formulas

Video version

Text version

It is the ability to perform various types of calculations that gained worldwide popularity for the Excel spreadsheet processor from the Microsoft Office package.

Before this lesson, we actually did not use Excel to carry out calculations, starting from this lesson we will try, if not fully disclose, then demonstrate the potential that is embedded in this program.

Formula basics

If you put an "\u003d" sign at the beginning of a cell, Excel begins to perceive the cell as a formula.

As the simplest example of a formula, you can imagine a simple operation of adding two numbers, subtracting, multiplying and dividing. Excel works with standard mathematical operations and the sequence of their application.

By default, in a cell containing a formula, the user displays the result of its calculation, if you want to see the formulas themselves, you can do this with the "Show Formulas" command, the "Formula Dependencies" group, the "Formulas" tab, or by pressing the hot combination Ctrl + ` (the tilde key is at the beginning of the number row on the keyboard).

Hot combination

Ctrl + ` toggles the display modes of data / formulas in Excel

A formula is an equation that performs calculations such as addition, subtraction, multiplication, division. In Excel, formula values \u200b\u200bcan be a number, cell address, date, text, boolean (true or false). most often it is either a number or a cell address.

Constant Is a value that is entered directly into the formula (number, text, date).

Variable Is a symbolic representation of a value that is outside the formula.

The calculation operator defines the calculation itself, in order for Excel to start calculating in the formula, you must put the "\u003d" sign at the beginning. Excel supports the following types of arithmetic operators:

During the introduction of a formula, operators and values \u200b\u200bare displayed both in the cell and in the formula itself, when the input is completed in the formula bar there will be a formula, while in the cell - the result of its work.

How the formula is calculated

When several computational operators are used in a formula, the order of their computation is subject to general mathematical rules. The following order is used when calculating a formula (written in descending order of priority of calculation):

1. Negative values \u200b\u200b(-);
2. Interest (%);
3. Exponentiation (^);
4. Multiplication (*) and division (/);
5. Sum (+) and difference (-).

If there are two or more computational operators nearby that are on the same level, then the order of their computation goes from left to right. For example, "\u003d 5 + 5 + 3-2", although here this order has a meaning purely for the logic of the calculation, since subtracting the number "2" at the end of the calculation or at the beginning will not affect the result. If it becomes necessary to increase the priority of calculating a separate part of the formula, then, as in mathematics, you need to enclose this part in parentheses.

Some examples of how calculations work in Excel:

Hot combination

If you change your mind while entering the formula, just press "Esc" and the value in the cell will not be changed. If the formula has already been changed (you finished entering with Enter), then you can return the old value using the "Undo" command on the Quick Access Toolbar or a combination Ctrl + Z.

In order to edit an existing formula, you need to place the cursor in the cell with the formula and press the "F2" key, or click in the formula bar, or double-click on the target cell.

1. Using links in formulas

Video version

Text version

It was mentioned earlier that one of the values \u200b\u200bthat are used in formulas are variables. Variables are used much more often than constants; moreover, it is recommended to replace all constants with variables (more on this in the context of considering absolute references).

Each cell is located at the intersection of rows and columns and has a corresponding designation:

• "E2" - column "E", second row;
• "F2" - column "F", second row;
• "G2" - column "F", second row.

The advantage of this notation is that when you change the values \u200b\u200bin the cells involved in the formula, the result of the formula calculation will also change. Naturally, formulas can contain references (variables) and numbers (constants) at the same time.

In the example considered, the simplest type of links was considered - relative links, the principle of its operation is easy to understand by the example of converting daily earnings into another currency.

A simple formula for recalculating current earnings into rubles is entered into cell C2, and then, using the autocomplete marker, it is stretched to the remaining days, as you can see, for cell C3 in the formula, earnings for 03/02/2016 are used, although the formula was entered "\u003d B2 * 71", those. when you move one cell down, the entered relative link also changes its address. The same thing happens if you stretch the autocomplete marker to either side (left, right, up, or down), the relative link will always be at the same distance from the cell with the formula where it is used. In this case, it is one cell to the left, that is, if you drag the autocomplete marker to the right, then the formula will use the column "C" (and the number depending on the current row).

Links in Excel formulas can be entered directly from the keyboard (just remember to indicate the column names in the English layout), but the more common way is to left-click on the cell that is supposed to be used in the formula. For example, in the example above, you can calculate the total amount of earnings simply by clicking on the target cells and adding them.

For improved visual perception of links in the formula, cells are highlighted in different colors.

In the example of recalculating earnings from dollars to rubles, we used the conversion rate as a constant, i.e. entered the number directly into the formula itself. If the course changes (and it will change), you will have to redo the formula and remember to use autocomplete to replicate it to other cells, which is rather inconvenient.

Absolute link Is a reference to a cell, the address of which does not change when ranges are autocompleted. In order to make a link absolute, you need to put a dollar sign in the reference designation before the column and row identifiers.

The dollar sign can be entered from the keyboard, but it will be faster to press the "F4" key on a relative link, i.e. the link the cursor is on. Pressing the "F4" key again will remove / add the $ sign near the row / column. Intermediate values \u200b\u200bwill appear, links with one dollar sign - these are mixed links, they will be considered a little later.

Now, when the rate in the formula is fixed with an absolute reference, then when it changes, it is enough to change cell F1 once and all formulas that use the value of this cell will be recalculated automatically, and when the formula is stretched to either side, the formula will always refer to the same specific cell.

At the beginning of the consideration of this issue, we mentioned that the use of constants in formulas is best kept to a minimum. The best solution would be to replace them with absolute links, which, moreover, can also be signed.

With autocomplete, the relative link will always change, regardless of the direction of the autocomplete, the absolute will never change. What if you want the link to change when you autocomplete the formula down or up, but not when moving to the left or right, or vice versa, when stretching to the left or right, it changes, but when moving up or down, it remains unchanged?

In this case, mixed links should be used. A mixed link is a link that is absolute in one direction (row or column) and relative in the other. Which part of the link behaves like an absolute link is determined by the dollar sign.

For example, in the considered example of transferring daily earnings, the absolute link of the ruble to dollar exchange rate can be replaced by F $ 1 and nothing will change, since we fixed the row, and the column did not change anyway. To make a more visual presentation, you can expand the table with translations into additional currencies.

Two types of mixed links are used here: the first refers to the amount of earnings in dollars and is fixed by columns, which allows you to use your earnings for each day by stretching the formula down, and the second refers to the rate fixed by rows allows you to change the rate when the formula is auto-filled to the left or to the right. It is important to understand here that to convert earnings into three currencies, the formula was introduced in total ONE! times in cell C4. All other cells were filled using autocomplete.

What other types of links are there?

All the links that we looked at referred to cells in the same sheet where the formula was located, however, the user can make references to cells in another sheet. At the same time, all the rules discussed earlier about relative, absolute and mixed links also work, for example, you can move currency rates to another sheet, the only thing that will change is that the name of the sheet will be in front of the link, which is enclosed in single quotes and separated from the link itself with an exclamation mark.

This is not all, you can refer to cells that are in other books, these are already external links, in this case the name of the book will appear in the link record.

1. Using data ranges in formulas

Video version

Text version

In Excel, a group of cells is called a range. Groups of cells can be adjacent, or they can be located at a distance from each other. You can assign names to groups of cells, change range boundaries after defining their names, and use names in formulas instead of simple unnamed cell references.

Naming data ranges

If you often work with a certain range, it is convenient enough to give it a name so that later on you can operate not with an unnamed range / cell: “C3: C15”, but with an understandable “profit_2015”, etc.

For naming and navigating named ranges and cells, use the Defined Names group on the Formulas tab, and the Name box, which we started with in the second question of Lesson 2.

The easiest way to name a range is using the Name box to name the range with the default parameters, i.e. the named range will work on all sheets of the book, if you give a name using the "Assign Name" command, then in the dialog box that appears, you can configure additional parameters, the most important of which is the scope of the named range.

By default, the scope is defined at the book level, that is, if you create a named range on one of the sheets of the book, then it can be used on any sheet of the book, and you cannot create a second named range with that name. The user can change the scope of a named range during its creation by selecting one of the existing sheets in the workbook. This sheet does not have to coincide with the sheet on which the range itself is located; in this case, you can create several ranges with the same names, but each will act on "its" sheet.

Another way to quickly create a named range is by using the Create From Selection command in the same Defined Names group. The bottom line is that the range is highlighted along with the title, which will bear the name of the range of the rest of the range. Excel, by the way, determined itself which cell to select for the range name.

Selecting the Euro column and using the Create From Selection command is equivalent to selecting the E4: E13 range and giving it the name B_Euro.

You can view all named ranges, delete unnecessary ones, change its range for any range in the name manager, which is called by the command of the same name from the "Defined names" group.

How to use named ranges

Named ranges are used in formulas along with standard ranges, but there are some peculiarities. Firstly, the named range is ALWAYS an absolute reference, and secondly, in the formula it is enough to start writing the name of the named range and Excel will offer it, if there are several similar ones ("earnings_dollars", "earnings_hryvnya", etc.), then you can use the mouse select the one you want.

You can insert a named range into a formula using the Use In Formula command in the Defined Names group.

Named ranges are ideal for using in conjunction with functions that work with ranges, we will get into functions closely a little later, and we'll start now.

For example, in the example of calculating and translating into different currencies, we calculated the total amount in dollars by simply adding daily income. In real work Excel it is wrong to do this, since there are functions that automate these operations. Let's calculate the amount of income in different currencies, using the references to the ranges, and then we will do the same, only enter the names of the ranges as arguments.

If you do not use cell names, then you can calculate the amount of earnings in one currency, and fill in others using autocomplete. With named ranges, this "trick" will not work, because the range is assigned an absolute reference, however, if you need to sum up a range that is out of sight, then using the name instead of references will be preferable.

If there are many named ranges created in the workbook, or you simply do not remember the name of the required range of cells, the "Insert Name" dialog box will come in handy, which is convenient to use when creating a formula and which is invoked using the "Use in Formula" command, the "Defined Names" group ".

Finally, on the topic of naming data ranges, you can specify rules for creating ranges:

1. Name length is no more than 255 characters.
2. The range name can begin with either a letter or the characters "_" and "\\". In the name itself, you can use numbers, letters, underscore. For example, "\\ cell_name" or "_name \\ cell.1" are valid names, but "1 cell_name" is not allowed. starts with a number.
3. The name cannot consist of single letters: "R", "r", "C", "c", since their input in the "Name" box leads to the selection of a row (row) or column (column).
4. Spaces cannot be used in names, Microsoft recommends replacing them with an underscore "_" or a period ".". For example, "income_october", "profit.year".
5. The range name cannot be the same as the link name, such as "A5" or "$ B $ 5".

1. Introduction to functions. Displaying dates and times using functions

Video version

Text version

Some calculations are performed by users more often than others, some calculations are mandatory for a certain area. In order not to enter the formula manually each time, functions have been developed in Excel. The function is actually a predefined formula, for example, the sum formula, which we have already considered a little earlier.

Excel provides a large number of functions from fairly simple ones, for example, average, counting the number of cells, minimum, sum, etc., to complex statistical or financial calculations, for example, variance, standard deviation, etc.

A convenient guide to working with functions is presented on our website:

Introduction to functions

On a note

Function names are not case sensitive, however, in Excel they are written in capital letters for readability. True, the user does not need to write in capital letters, since Excel independently converts lowercase letters in the function name to uppercase.

There are several ways to call a function to insert into a formula:

As you have already noticed, the functions of sum, average, maximum and minimum are used more often than others, so they are even displayed in a separate group. In fact, you can very quickly find out these values \u200b\u200beven without entering formulas. On the status bar, the user has the ability to display a quick calculation of the average, quantity, quantity, number, maximum and minimum (a tick marks the counts displayed on the status bar at the moment).

For the sum, there is a hot key combination "Alt + \u003d", you can just enter it near the range with data and Excel will select the most suitable (according to the program) range, which can be adjusted, or you can select the range and press the hot combination and Excel, depending on the range ( horizontal or vertical) will place the sums in the cell below or above the range.

Date and time functions

In Excel, dates are actually numeric values, but they are formatted to look like a date. The same applies to time. This statement can be easily verified simply by entering any positive number and formatting it accordingly. It is for this reason that arithmetic operations with dates are possible, for example, you can calculate the number of days between two dates by simply subtracting the other from one date.

The starting point for dates is January 1, 1900, i.e. it is on this date that the number "1" will be converted if the appropriate formatting is set, "2" \u003d this is January 2, 1900, etc. Dates can be added, subtracted, multiplied, divided in exactly the same way as it is done with numbers.

In Excel, by default, all cells are in the "General" number format, which means that the spreadsheet will try to adjust the formatting based on the data entered.

For example, by entering "03/15/2016" the number format will be set to "Date", if you then simply delete the date using the Del key (equivalent to clearing the contents) and enter a number, for example, 5, then the date will be displayed in the cell: 01/05/1900. Therefore, you need to either completely clear the cell (along with the formats), or after entering the data, change the number format of the cell.

TODAY function

Function TODAY () or TODAY () in the English version of Excel is easy to understand and its syntax is extremely simple, because arguments are missing in principle. This function returns the current date. When you open a book, the current date will always be displayed, so if you need to fix a specific date, you will have to enter it manually.

Entering any formula must begin with the "\u003d" sign, if the formula consists of one function, then it should be preceded by "\u003d", if the function is added in the middle of the formula, it is not possible to put equal again. Only one "\u003d" sign can be present in a formula.

By itself, using the TODAY function does not seem like the most interesting exercise, but you need to understand that when constructing formulas, we can use several functions. As an example, you can use TODAY when calculating your age, in this case the formula will take the form:

TDATA function (NOW)

The function TDATA () or NOW () in the English version of Excel is very similar to the previous one, with the only exception that it returns not just the current date, but also the exact time. Time in Excel is represented by a fractional number.

The current time is updated every time the file is opened, or when the sheet is recalculated.

1. Working with frequently used functions (AutoSum)

Video version

Text version

There are many functions in Excel, some of them are rarely used, some will be of interest only to a certain category of workers, and there are functions that will be useful always and everywhere, regardless of your specialty and type of calculations.

Microsoft understands this too, so they collected such functions not just in a separate set called "AutoSum" but also placed it in various places in the Excel program interface.

The "AutoSum" includes the functions:, and.

The AutoSum function group can be found on the Home tab in the Editing group.

The group of functions "AutoSum" is presented on the "Formulas" tab in the "Library of functions" group.

SUM function