The Excel program using such a tool, as formula, allows for various arithmetic actions between data in cells. This actions include subtraction. Let's analyze in detail what methods can produce this calculation in Excele.
Use of subtraction
Subtraction to Excel can be used both to specific numbers and the addresses of the cells in which the data is located. This action is performed due to special formulas. As in other arithmetic calculations in this program, before the subtraction formula, you need to establish a sign equal to (=). Then the reduced (in the form of a number or address of the cell), the minus (-) sign, the first subtractable (in the form of a number or address), and in some cases subsequent subtracted.Let's analyze on specific examples how this arithmetic action is performed in Excel.
Method 1: Subtract numbers
The easiest example is the subtraction of numbers. In this case, all actions are performed between specific numbers as in a conventional calculator, and not between cells.
- Select any cell or set the cursor in the formula string. We put the sign "equal." We print an arithmetic effect with subtraction, just as we do on paper. For example, write down the following formula:
= 895-45-69
- In order to produce the calculation procedure, press the ENTER button on the keyboard.
After these steps are made, the result is displayed in the selected cell. In our case, this is the number 781. If you used other data for calculating, then, accordingly, your result will be different.
Method 2: Subtraction of numbers from cells
But, as you know, Excel is, first of all, a program for working with tables. Therefore, the operations with cells are played very important. In particular, they can be used for subtraction.
- We highlight the cell in which the subtraction formula will be. We put the sign "=". Click on a cell that contains data. As you can see, after this action, its address is entered into the formula string and added after the "equal" sign. We print the number you need to subtract.
- As in the previous case, to obtain the results of the calculation, press the ENTER key.
Method 3: Single Cleaning Cell
You can conduct subtraction operations and generally without numbers, manipulating only cell addresses with data. The principle of action is the same.
- Select the cell to display the results of the calculations and put the "equal" sign in it. Click on a cell containing a reduced one. We put the sign "-". Click on a cell containing subtractable. In case the operation needs to be carried out with several subtractable, then also put the "minus" sign and carry out actions on the same scheme.
- After all data is entered, for the output of the result, click on the ENTER button.
Lesson: Work with Formulas in Excel
Method 4: Mass Processing External Operation
Quite often, when working with the Excel program, it happens that it is necessary to calculate the deduction of the entire column of the cells on the other cell column. Of course, it is possible for each action to write a separate formula manually, but it takes a considerable amount of time. Fortunately, the functionality of the application is able to largely automate such calculations, thanks to the autofile function.
On the example, we calculate the profit of the enterprise in different areas, knowing the overall revenue and the cost of production. For this, the proceeds must be revealed.
- We allocate the highest cell to calculate profits. We put the sign "=". Click on a cell containing revenue size in the same row. We put the sign "-". We highlight the cell with the cost.
- In order to output the profits on this line on the screen, click on the ENTER button.
- Now we need to copy this formula into the lower range to make the desired calculations there. To do this, we put the cursor on the right lower edge of a cell containing the formula. The filling marker appears. We click the left mouse button and in the clamping state by pulling the cursor down to the end of the table.
- As you can see, after these actions, the formula was copied to the entire range below. At the same time, thanks to this property, as the relativity of addresses, this copy occurred with a displacement, which made it possible to produce the correct calculation of subtraction and in adjacent cells.
Lesson: How to make autocomplete in Excel
Method 5: Mass subtraction of the data of one cell from the range
But sometimes you need to do just the opposite, namely, that the address does not change when copying, but remained constant, referring to a specific cell. How to do it?
- We become in the first cell to output the result of the range calculations. We put the sign "equal." Click on a cell in which the diminished. Install the "minus" sign. We make a click on the cell subtractable, the address of which should not be changed.
- And now we turn to the most important difference of this method from the previous one. It is the following that allows you to convert a link from relative in absolute. We put the dollar sign in front of the coordinates of the vertical and horizontal of the cell whose address should not change.
- Click on the keyboard on the Enter key, which allows you to output the calculation for the line to the screen.
- In order to make calculations and on other rows, in the same way as in the previous example, we call the filling marker and drag it down.
- As we see, the subtraction process was produced exactly as we need. That is, when moving down the address of the reduced data changed, but the subtracted remained unchanged.
The above example is only a special case. In a similar way, it can be done on the contrary, so that the reduced remains constant, and the subtracted was relative and changed.
Lesson: Absolute and relative links to Excel
As you can see, in the development of the subtraction procedure in the Excel program there is nothing complicated. It is performed according to the same laws as other arithmetic calculations in this application. Knowing some of the interesting nuances will allow the user to correctly process the mathematical action of large data arrays, which will significantly save its time.