You can create a simple formula to add, subtract, multiply, and divide numeric values on a worksheet. Simple formulas always begin with an equal sign ( = ), followed by constants, that is, numeric values, and evaluation operators, such as plus ( + ), minus ( - ), asterisk ( * ) and slash ( / ).
As an example, consider a simple formula.
Instead of entering constants in a formula, you can select the cells with the values you want and enter operators between them.
According to the standard order of mathematical operations, multiplication and division are performed before addition and subtraction.
Select the cell on the worksheet in which you want to enter the formula.
Enter = (equal sign), and then the constants and operators (up to 8192 characters) that you need to use in the calculation.
In our example, enter =1+1 .
Notes:
Press the key ENTER(Windows) or Return(Mac).
Let's consider another version of the simple formula. Enter =5+2*3 in another cell and press the key ENTER or Return. Excel will multiply the last two numbers and add the first number to the multiplication result.
Using AutoSum
To quickly add numbers in a column or row, you can use the AutoSum button. Select the cell next to the numbers you want to add, click the button Autosum on the tab home, and then press the key ENTER(Windows) or Return(Mac).
When you press the button Autosum Excel automatically enters a formula to sum the numbers (which uses the SUM function).
Note: You can also type ALT+= (Windows) or ALT+ += (Mac) in a cell and Excel will automatically insert the SUM function.
Example: To add up the January numbers in the Entertainment budget, select cell B7, which is directly below the number column. Then click the button Autosum. The formula appears in cell B7, and Excel highlights the cells that are being added up.
To display the result (95,94) in cell B7, press ENTER. The formula also appears in the formula bar at the top of the Excel window.
Notes:
To add numbers in a column, select the cell below the last number in the column. To add numbers in a row, select the first cell on the right.
Once you've created the formula once, you can copy it to other cells instead of typing it over and over again. For example, if you copy a formula from cell B7 to cell C7, the formula in cell C7 will automatically adjust to the new location and calculate the numbers in cells C3:C6.
In addition, you can use the AutoSum function for several cells at once. For example, you can select cells B7 and C7, click Autosum and sum two columns at the same time.
Copy the data from the table below and paste it into cell A1 of the new Excel sheet. If necessary, change the width of the columns to see all the data.
Note: To have these formulas display the result, select them and press F2, and then - ENTER(Windows) or Return(Mac).
Data |
||
|---|---|---|
|
Formula |
Description |
Result |
|
Sum of values in cells A1 and A2 |
||
|
Difference between values in cells A1 and A2 |
||
|
Divide the values in cells A1 and A2 |
||
|
Product of values in cells A1 and A2 |
||
|
The value in cell A1 to the power specified in cell A2 |
||
|
Formula |
Description |
Result |
- The most common coordinate reference is to use a letter or letters representing the column followed by the row number the cell is in: for example, A1 points to the cell in column A and row 1. If you add rows above the cell, the cell reference changes to display its new position; adding a row above cell A1 and a column to the left of it will change its reference to B2 in all formulas that use it.
- A variation of this formula is to make row or column references absolute by adding a dollar sign ($) in front of them. Although the reference to cell A1 will change if a row is added above it or a column to the left of it, the reference $A$1 will always point to the top left cell on the worksheet; Thus, in a formula, cell $A$1 may have a different or even invalid value in the formula if rows or columns are inserted into the worksheet. (If desired, you can use an absolute reference for the column or row separately, for example $A1 or A$1).
- Another way to reference a cell is by the numeric method, in the format RxCy, where "R" indicates "row," "C" indicates "column," and "x" and "y" are the row and column numbers, respectively. For example, reference R5C4 in this format points to the same location as reference $D$5. A reference of type RxCy points to a cell relative to the upper left corner of the sheet, that is, if you insert a row above the cell or a column to the left of the cell, the reference to it will change.
- If you use only an equal sign and a single cell reference in a formula, then you are essentially copying the value from another cell to a new cell. For example, entering "=A2" in cell B3 will copy the value entered in cell A2 to cell B3. To copy a value from a cell on another sheet, add the sheet name followed by an exclamation point (!). Entering "=Sheet1!B6" in Cell F7 on Sheet2 will display the value of cell B6 on Sheet1 in Cell F7 on Sheet2.
-
Use arithmetic operators for basic operations. Microsoft Excel can perform all basic arithmetic operations: addition, subtraction, multiplication and division, and exponentiation. Some operations require different characters than those we use when writing them by hand. The list of operators is given below, in order of precedence (that is, the order in which Excel processes arithmetic operations):
- Negation: Minus sign (-). This operation returns the opposite sign of a number or cell reference (this is equivalent to multiplying by -1). This operator must be placed before the number.
- Percent: Percent sign (%). This operation will return the decimal equivalent of a percentage of a numeric constant. This operator must be placed after the number.
- Exponentiation: Caret (^). This operation raises the number (or reference value) before the caret to a power equal to the number (or reference value) after the caret. For example, "=3^2" is 9.
- Multiplication: Asterisk (*). The asterisk is used for multiplication so that multiplication is not confused with the letter "x."
- Division: Slash (/). Multiplication and division have the same priority and are performed from left to right.
- Addition: Plus sign (+).
- Subtraction: Minus sign (-). Addition and subtraction have the same priority and are performed from left to right.
-
Use comparison operators to compare values in cells. Most often, you will use comparison operators with the IF function. You put a cell reference, a numeric constant, or a function that returns a numeric value on either side of the comparison operator. The comparison operators are given below:
- Equal: The equal sign (=).
- Not equal (<>).
- Less (<).
- Less or equal (<=).
- More (>).
- Greater than or equal to (>=).
-
Use ampersand (&) to connect text strings. Combining text strings into one is called concatenation, and the ampersand is the operator that does concatenation in Excel. You can use the ampersand with strings or string references; for example, entering "=A1&B2" in cell C3 will display "AUTO FACTORY" if "AUTO" is entered in cell A1 and "FACTORY" is entered in cell B2.
-
Use reference operators when working with a region of cells. You'll most often use a cell region with Excel functions such as SUM, which finds the sum of a cell region's values. Excel uses 3 reference operators:
- Area operator: colon (:). The region operator refers to all cells in a region that begins with the cell before the colon and ends with the cell after the colon. Typically, all cells are in the same row or column; "=SUM(B6:B12)" will display the result of adding the values of cells B6, B7, B8, B9, B10, B11, B12, while "=AVERAGE(B6:F6)" will display the arithmetic average of the values of cells B6 to F6 .
- Concatenation operator: comma (,). The union operator includes all cells or regions of cells before and after it; "=SUM(B6:B12, C6:C12)" sums the values of cells B6 to B12 and C6 to C12.
- Intersection operator: space(). The intersection operator looks for cells that are common to 2 or more regions; for example, "=B5:D5 C4:C6" is only the value of cell C5, since it appears in both the first and second regions.
-
Use parentheses to specify function arguments and override the order in which operators are evaluated. Parentheses in Excel are used in two cases: to define function arguments and to indicate a different calculation order.
- Functions are predefined formulas. Such as SIN, COS or TAN require one argument, while IF, SUM or AVERAGE can take many arguments. Arguments within a function are separated by a comma, for example, "=IF (A4 >=0, "POSITIVE," "NEGATIVE")" for the IF function. Functions can be nested within other functions, up to 64 levels.
- In formulas with mathematical operations, operations inside parentheses are performed before those outside them; for example, in "=A4+B4*C4," B4 is multiplied by C4 and the result is added to A4, and in "=(A4+B4)*C4," A4 and B4 are first added, and then the result is multiplied by C4. Brackets in operations can be nested within each other; the operation inside the innermost pair of brackets will be executed first.
- It doesn't matter whether nested parentheses occur in mathematical operations or in nested parentheses, always make sure that the number of opening parentheses equals the number of closing ones, otherwise you will receive an error message.
Begin any formula with an equal sign (=). The equal sign tells Excel that the set of characters you enter into a cell is a mathematical formula. If you forget the equal sign, Excel will treat your input as a set of characters.
Use coordinate references to the cells that contain the values used in the formula. Although you can enter numeric constants in your formulas, in most cases you will need to use the values in other cells (or the results of other formulas displayed in those cells) in the formulas. You access those cells using the row and column coordinate reference where the cell is located. There are several formats:
Excel is a very cool thing if you understand how to work with this program. The application has so many functions that it makes your head spin, and the average user uses almost nothing in practice. Let's take, for example, the question of how to calculate the amount in Excel. Many people don’t know this and continue to count on a calculator, and then enter ready-made values into the table.
This method has a right to life, but why expose yourself to the danger of making a mistake when you can automate the counting process by forcing the machine to add, subtract, divide, multiply, calculate percentages and perform other operations? In this case, the result does not have to be double-checked - the computer has no chance of making a mistake when it comes to simple mathematics.
Simple addition
If all the numbers are arranged in one column, then to obtain their sum you can use the most in a simple way, which does not require the use of formulas.
You can select cells different ways. If they are nearby, then just hold down the left mouse button near the first value and drag the selection to the last variable. You can also hold down the Shift key and press the down arrow on the keyboard, selecting the cells below in sequence.
If the variables are scattered across the sheet, then hold down the Ctrl key and left-click on the values that need to be added. The amount will change with each click, adding to the number that was just highlighted.
Excel can also be used as a calculator. For example, if you need to add several numbers, set the following formula in any cell: 
Be sure to put the “=” sign at the beginning, otherwise the program will not understand that you need to not only add the numbers, but also display the value on the screen. There is no need to put spaces: all characters are written together.
AutoSum function
If the addition result needs to be reflected on the same sheet, use the special button on top panel. By default, it is set to the "Sum" function, but you can use it to perform other calculations. If you select the data that is written in one column and then click on this button, the required amount will appear under the column. 
If the numbers are written on different sides of the sheet, and you need to collect them, sum them up and show the total in a certain place, then use the following algorithm:
Then you can use two methods: hold down the Ctrl key and left-click to select all the variables in sequence, or simply enter the cell addresses into the formula using the “+” sign. The choice of method depends on how many numbers need to be added. When all cell addresses are entered into the formula, press Enter. Instead of a large number of characters, the sum of the variables added to the formula will be displayed.
Using the formula
If it is assumed that there is a special cell on the sheet for the sum of a number, then the result can be displayed in it using a simple formula that schoolchildren learn in computer science lessons.
When choosing numbers, you can use two types of substitutions:
- =SUM(B1:B10) – addition of all values that are fixed in the range B1-B10.
- =SUM(B1;B10) – addition of values in cells B1 and B10.
To avoid confusing separators, minimize the window and select cells manually. Fill in the first number, the second, and so on, and then click OK. As a result of the operation, the result of the addition will be displayed in the selected cell.
A formula tells Excel what to do with numbers or values in a cell or group of cells. Without formulas, spreadsheets are not needed in principle.
Formula construction includes: constants, operators, links, functions, range names, parentheses containing arguments and other formulas. Let's look at an example practical use formulas for novice users.
Formulas in Excel for dummies
To set a formula for a cell, you need to activate it (place the cursor) and enter equals (=). You can also enter an equal sign in the formula bar. After entering the formula, press Enter. The result of the calculation will appear in the cell.
Excel uses standard mathematical operators:
The “*” symbol is required when multiplying. It is unacceptable to omit it, as is customary during written arithmetic calculations. That is, Excel will not understand the entry (2+3)5.
Excel can be used as a calculator. That is, enter numbers and mathematical calculation operators into the formula and immediately get the result.
But more often cell addresses are entered. That is, the user enters a link to the cell whose value the formula will operate on.
When values in cells change, the formula automatically recalculates the result.
The operator multiplied the value of cell B2 by 0.5. To enter a cell reference into a formula, just click on that cell.
In our example:
- Place the cursor in cell B3 and enter =.
- We clicked on cell B2 - Excel “labeled” it (the cell name appeared in the formula, a “flickering” rectangle formed around the cell).
- Enter the sign *, the value 0.5 from the keyboard and press ENTER.
If several operators are used in one formula, the program will process them in the following sequence:
- %, ^;
- *, /;
- +, -.
You can change the sequence using parentheses: Excel first calculates the value of the expression in parentheses.
How to designate a constant cell in an Excel formula
There are two types of cell references: relative and absolute. When copying a formula, these links behave differently: relative ones change, absolute ones remain constant.
Find the autofill marker in the lower right corner of the first cell of the column. Click on this point with the left mouse button, hold it and “drag” it down the column.
Release the mouse button - the formula will be copied to the selected cells with relative links. That is, each cell will have its own formula with its own arguments.
Sometimes Excel users are faced with the question of how to add the total sum of the values of several columns? The task becomes even more complicated if these columns are not located in a single array, but are scattered. Let's figure out how to sum them up in different ways.
The summing of columns in Excel occurs according to general principles adding data in this program. Of course, this procedure has some features, but they are just a part of the general pattern. Like any other summation in this table processor, adding columns can be done using a simple arithmetic formula using the built-in Excel function SUM or autosum.
Method 1: Using AutoSum
First of all, let's look at how to sum columns in Excel using a tool such as AutoSum.
For example, let's take a table that shows the daily revenue of five stores over seven days. Data for each store is located in a separate column. Our task will be to find out the total income of these outlets for the period indicated above. For this purpose, you will need to fold the columns.
Method 2: Using a Simple Math Formula
Now let's see how we can sum the columns of a table using only a simple mathematical formula. For example, we will use the same table that was used to describe the first method.
It is impossible not to notice that this method takes more time and requires more effort than the previous one, since it involves manually clicking each cell that needs to be added to display the total amount of income. If there are a lot of rows in the table, then this procedure can be tedious. At the same time, this method has one undeniable advantage: the result can be displayed in any empty cell on the sheet that the user selects. There is no such option when using autosum.
In practice, these two methods can be combined. For example, sum up the totals in each column separately using autosum, and display the total value by applying an arithmetic formula in the cell on the sheet that the user selects.
Method 3: Using the SUM function
The disadvantages of the two previous methods can be eliminated when using the built-in Excel functions entitled SUM. The purpose of this operator is precisely to sum numbers. It falls under the category of mathematical functions and has the following simple syntax:
SUM(number1;number2;…)
The arguments, the number of which can reach 255, are the summed numbers or the addresses of the cells where they are located.
Let's see how this Excel function is used in practice using the example of the same revenue table by five retail outlets in 7 days.
- We mark the element on the sheet in which the amount of income in the first column will be displayed. Click on the icon "Insert Function", which is located to the left of the formula bar.
- Activation in progress Function Wizards. Being in a category "Mathematical", looking for a name "SUM", select it and click on the button "OK" at the bottom of this window.
- The function arguments window is activated. It can have up to 255 fields with the name "Number". These fields contain the operator's arguments. But for our case, one field will be enough.
In field "Number1" you need to place the coordinates of a range that contains the cells of a column "Shop 1". This is done very simply. Place the cursor in the argument field. Next, by holding the left mouse button, select all the cells of the column "Shop 1", which contain numeric values. The address was immediately displayed in the arguments window field in the form of coordinates of the array being processed. Click on the button "OK" at the bottom of the window.
- The value of revenue for seven days for the first store will be immediately displayed in the cell that contains the function.
- Then you can do similar operations with the function SUM and for the remaining columns of the table, counting in them the amounts of revenue for 7 days for different stores. The algorithm of operations will be exactly the same as described above.
But there is an option to make the work much easier. To do this, we will use the same fill marker. Select a cell that already contains a function SUM, and stretch the marker parallel to the column headings to the end of the table. As we can see, in this case the function SUM is copied in the same way as we previously copied a simple mathematical formula.
- After this, select that empty cell on the sheet into which we intend to display the overall result of the calculation for all stores. As in previous method, this can be any free element of the sheet. After this, in a well-known way, we call Function Wizard and move to the function arguments window SUM. We have to fill in the field "Number1". As in the previous case, we place the cursor in the field, but this time with the left mouse button held down, select the entire line of revenue totals for individual stores. After the address of this line in the form of an array link has been entered into the field of the arguments window, click on the button "OK".
- As you can see, the total revenue for all stores thanks to the function SUM was displayed in a pre-designated cell of the sheet.
But sometimes there are cases when you need to display the overall result for all retail outlets without summing up subtotals for individual stores. As it turns out, the operator SUM and it can, and solving this problem is even easier than using the previous version of this method.
If we consider this method purely technical side, then we added not the columns, but the entire array. But the result turned out to be the same as if each column were added separately.
But there are situations when you need to add not all columns of the table, but only certain ones. The task becomes even more difficult if they do not border each other. Let's look at how it's made this type addition using the SUM operator using the same table as an example. Let's assume we only need to add the column values "Shop 1", "Shop 3" And "Shop 5". In this case, it is required that the result be calculated without generating subtotals for the columns.
As you can see, there are three main ways to add columns in Excel: using an autosum, a mathematical formula and a function SUM. The simplest and fastest option is to use autosum. But it is the least flexible and is not suitable in all cases. The most flexible option is to use mathematical formulas, but it is the least automated and in some cases, with a large amount of data, its implementation in practice can take considerable time. Using the function SUM can be called a “golden” mean between these two methods. This option relatively flexible and fast.
