How To Subtract In Excel With A Formula

234

This tip is about the how To Subtract In Excel With A Formula. So read this free guide, How To Subtract In Excel With A Formula step by step. If you have query related to same article you may contact us.

How To Subtract In Excel With A Formula – Guide

Many people use Excel for simple arithmetic operations like addition, subtraction, multiplication and division, despite it being an excellent tool for data analysis. You have come to the right place if you are new to Excel and want to learn how to subtract in Excel well. I will show you how to subtract in Excel in this lesson (subtract cells, ranges, columns, and more). I’ll start with the basics before moving on to sophisticated subtraction techniques in Excel. In Excel I also show how to subtract dates, times and percentages.

How to Subtract in Excel with a Formula

Subtracting in Excel using formulas

Given the plethora of ways you might need to subtract values ​​and cells in Excel.

Subtract by including Values ​​in the Formula

You don’t need to enter any values ​​into worksheet cells to subtract numbers. Instead, you can include the values ​​to be subtracted in the formula itself.

First, select a cell to add the formula to.

Then click on the Excel function bar and enter ‘=’ followed by the values ​​you need to deduct. For example, enter ‘=25-5’ in the function bar and press Enter. The formula cell will return the value 20.

Subtract Cell References from Worksheet

However, most Excel users will need to subtract the numbers entered in the columns and rows of the worksheet. To subtract cell values, you will need to include their row and column references in the formula.

For example, enter the values ​​’345′ and ‘145’ in cells B3 and B4 in a blank Excel worksheet as in the screenshot below.

Now select cell B5 and click on the fx bar to enter a formula. Enter the formula ‘=B3-B4’ and press the Return key. B5 will now return the value 200 as shown below.

Subtract a number from each value within a range of cells

If you need to subtract a single value from each number in a range of cells, you can copy the formula to other cells.

For example, enter the values ​​’25’, ’35’ and ’55’ in cells C3:C5.

Then select cell D3, enter the formula ‘=C3-5’ in the function bar and press the Return key. D3 will now subtract 5 from 25 in cell C3. Copy the formula in D3 to the other cells below by selecting D3, clicking in the lower right corner of the cell and dragging it over the cells below as shown below.

The cells directly below D3 now also subtract 5 from the numbers in C4 and C5.

Subtract a cell range total from a single value

What if you need to subtract a column total for a group of cells from a single value? One way to do this would be to insert a SUM function into a cell that sums the range and then subtracts that total with a separate formula. However, you can also subtract a cell range total by including SUM in a formula.

For example, enter the values ​​’45’, ’55’ and ’75’ in cells B7 to B9.

Then enter ‘200’ in cell B11. Select cell B12, click the fx bar and enter ‘=B11-SUM(B7:B9)’ in the formula bar. Cell B12 will return the value of 25 as shown directly below. This effectively deducts the total of cells B7:B9 from the 200 in cell B11.

Subtract two or more total cell range values

You can also subtract cell range totals without first entering the SUM functions in the worksheet. Instead, add the cell range references to a formula and subtract them.

For example, enter the numbers ’25’, ’15’ and ’35’ in cells C7:C9. You can now deduct the total of the cell range C7:C9 from the previously entered range B7:B9.

Select C11 as the cell to add the formula to and enter ‘=SUM(B7:B9)-SUM(C7:C9)’ in the function bar. Press Enter to add the function to the worksheet, which will return the value 100 in C11. So this is deducting the range of cells C7:C9 from the total of the range of cells B7:B9, which is 175 to 75.

Subtracting percentage values ​​from figures

To subtract a percentage value, 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 from a number in another cell.

As an example, enter the value ‘150’ in cell E3. You must convert cell F3 to percentage format by right-clicking the cell and selecting Format Cells > Number > Percentage > OK. Type ’50’ into F3, which will be in percentage format as shown directly below.

Now you can add a formula that subtracts 50% from 150, click on cell G3 and enter the formula ‘=E3-(F3*E3)’ in the function bar. Cell G3 will return a value of 75, which is 50% of 150. So the formula deducted 75 from 150.

Subtracting values ​​across multiple sheets

Excel doesn’t limit you to subtracting values ​​on single sheets. So you can subtract cell numbers in one worksheet from those in another.

For example, enter ’55’ in cell B14 on Sheet1 of a worksheet.

Then click on the Sheet2 tab at the bottom of the application window to open a blank sheet. Type ‘5’ in cell B3 on Sheet2. So this formula will subtract B3 on Sheet2 from B14 on Sheet1.

Now click on B4 on Sheet2 to add the formula to that cell. Type the formula ‘=Sheet1!B14-Sheet2!B3’ in the Excel function bar and press Return. Cell B4 will now deduct 5 in B3 on Sheet2 from the value of B14 on Sheet1. The cell returns a value of, as you’ve probably guessed, 50.

Final note

I hope you like the guide How To Subtract In Excel With A Formula. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.