How to subtract formulas in Excel. Simple calculations in Microsoft Excel. Addition and subtraction
Excel, using a tool such as formulas, allows you to perform various arithmetic operations between data in cells. Subtraction also belongs to such actions. Let's take a closer look at how this calculation can be performed in Excel.
Subtraction in Excel can be applied both to specific numbers and to the addresses of the cells in which the data is located. This action is performed using special formulas. As in other arithmetic calculations in this program, you need to set the equal sign before the subtraction formula (=) ... Then the decrement (in the form of a number or cell address) follows in sequence, the minus sign (-) , the first subtracted (in the form of a number or address), and in some cases, and subsequent subtracted.
Subtract a number from a range of cells using the special "Paste" function
With this method, you can do the following. Paste number 99 into a blank cell and copy it. The specified number 99 is subtracted from the range of cells. Then delete the number.
Subtract a number from a range of cells with formula
A simple formula can also help you here. Take the above data for example.Package subtracts a number from a range of cells without a formula
And then all the cells are extracted. And then the range of cells will be subtracted by a number. Note. If you want to create formulas, you can also select the Create Formulas checkbox. If the selected cells include formulas and you don't want to subtract the calculated results of the formulas, check the Skip Cell Formula check box.
Let's look at specific examples to see how this arithmetic operation is performed in Excel.
Method 1: subtract numbers
The simplest example is subtracting numbers. In this case, all actions are performed between specific numbers, as in a regular calculator, and not between cells.
Recommended productivity tools
Try it for free without limits for 30 days. More Download. Creating a budget is one of the simplest and most rewarding tasks you can do using a spreadsheet. Using spreadsheet Any changes you make to your budget are instantly updated across the entire budget, with totals calculated for you.
You can use the same approach to create a household budget, a budget for a trip or a specific event, etc. Here's how to create a budget. It's amazing how often you find that you had to have another column or row, and while it's easy to insert them later, it's even easier to start with a small space. This makes it easier to insert a title or reorganize a design. ... This is very different from how everything works in most other programs, but you will find that it is a compatible spreadsheet feature.
After these actions have been completed, the result is displayed in the selected cell. In our case, this number is 781. If you used other data for the calculation, then, accordingly, your result will be different.
Method 2: subtract numbers from cells
But, as you know, Excel is, first of all, a program for working with tables. Therefore, operations with cells are very important in it. In particular, they can be used for subtraction as well.
That's all for income categories, so let's think it over. This illustrates how cells in your table can contain data numbers, text, images, etc. - or formulas. Let's proceed with budgeting. Follow the same procedure to add the rest of your expenses.
To finish entering the budget information, we need to offset expenses and then figure out our net income by subtracting the total expenses from our total income. Figure. At this point, you have a summary of your working budget, which should look like a picture.
Method 3: subtracting a cell from a cell
You can perform subtraction operations without numbers at all, manipulating only the addresses of cells with data. The principle of operation is the same.
We can fix this by adding a title and formatting text and numbers. The room resolution for these headers is one of the reasons we left some space at the top and left when we first created the table. Let's make all the headlines bold. ... Since we are dealing with dollar amounts, let's format the numbers as currency, not just simple numbers. The Format Cells dialog box appears.
The situation now looks much more readable, but we can improve the situation a little more by highlighting the three most important values, total income, total expenses and net income. We could just offset these numbers just like we did in the headings, or you could choose any of several other options to highlight the values. Let's use some lines to offset these totals.
Method 4: bulk processing the subtraction operation
Quite often, when working with Excel, it happens that you need to calculate the subtraction of a whole column of cells to another column of cells. Of course, you can write a separate formula manually for each action, but this will take a significant amount of time. Fortunately, the functionality of the application is able to automate such calculations to a large extent, thanks to the autocomplete function.
One reason for this is that a workbook can contain multiple worksheets. Having descriptive names on your tabs not only makes it easier to navigate quickly between worksheets, but also helps if you are developing more complex formulasthat use values \u200b\u200bfrom multiple sheets.
- This creates a boundary between the amount of income and the total amount of income.
- Repeat the process for the total cost.
- Top up your bottom line by applying multiple double boundaries.
- Let's rename this to more accurately reflect the contents of the worksheet.
Using an example, we will calculate the profit of an enterprise in different areas, knowing the total revenue and cost of production. To do this, you need to subtract the cost from the proceeds.
It is noticeably more readable with important information... You can make him look a lot smarter if you want, but this is a good start. So the budget looks good, but is it producing the right results? You can double check that the formulas you listed for total income, total expenses, and net income are correct by making a small addition for yourself. You should also check if the formulas work by changing some of the amounts for income and expenses.
It's easy to check if your formulas work in a simple spreadsheet. The steps in this tutorial assume that you have a cell that contains the value you want to subtract. You can either subtract this value from the value in another cell, or subtract it from the number you choose.
Method 5: mass subtract one cell from a range
But sometimes you need to do just the opposite, namely, so that the address does not change when copying, but remains constant, referring to a specific cell. How can this be done?
If you want to subtract the value of a cell from a number that is not in the cell, replace one of your cells instead. The second value after the operator is. As with the first value, it can be either a different cell location or a numeric value.
- The \u003d symbol at the beginning of the formula.
- First value after \u003d.
- It can be either a cell or a numerical value.
The above example is just a special case. Similarly, you can do the opposite, so that the diminished remains constant, and the subtracted is relative and changes.
As you can see, in mastering the subtraction procedure in excel program there is nothing difficult. It follows the same laws as other arithmetic calculations in this application. Knowledge of some interesting nuances will allow the user to correctly process large amounts of data with this mathematical action, which will significantly save his time.
Subtract by adding values \u200b\u200bto a formula
If you need to display the formula instead of the result of the formula, then you show what changes you need to make to do this. Nonetheless, software has no subtraction function, which seems obvious. You don't need to enter any values \u200b\u200bin the cells of the spreadsheet to subtract numbers. Instead, you can include the values \u200b\u200bto be subtracted in the formula itself. First, select a cell to add a formula. For example, enter "\u003d 25-5" in the function line and press Enter. The formula cell will return a value.
The subtraction operation in the spreadsheet editor Microsoft Office Excel can be applied both to two specific numbers and to individual cells. In addition, it is possible to subtract the desired values \u200b\u200bfrom all cells in a column, row, or other area of \u200b\u200bthe spreadsheet. This operation can be part of any formulas, or it can itself include functions that calculate the decremented and subtracted values.
Subtracting references to spreadsheet tables
To subtract cell values, you will need to include their row and column references in the formula.
Subtract one number from each value within a range of cells
If you need to subtract one value from each number within a range of cells, you can copy the formula to other cells.Subtract the value of a range of cells from one value
What if you need to subtract the total number of columns for a group of cells from a single value?
Subtract two or more common values \u200b\u200bof a range of cells
Instead, add the cell range references to the formula and subtract them. Subtracting percentages from pictures. To subtract a percentage, such as 50%, from a number, you will need to enter the value in a cell with a percentage format. Then you can add a formula that subtracts the percentage of the number in another cell.You will need
- Microsoft Office Excel spreadsheet editor.