How to Subtract Two Columns in Excel (5 Easy Methods) - ExcelDemy (2024)

Subtraction is the process of finding the difference between two numbers or integers. If we go back to our old school memory, we used to put a minus sign between two numbers. In Microsoft Excel, it’s no different. You can subtract numbers, percentages, days, minutes, texts, etc. Now let’s take a look at how you can subtract two columns in excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

5 Suitable Methods to Subtract Two Columns in Excel

In this tutorial, I am going to share with you 5 simple methods to subtract two columns in Excel.

1. Apply Subtraction Between Two Columns in Excel

Like in old school days, we used to put a minus sign between two numbers. In this method, I will show you how you can subtract between two columns just by putting a minus sign. Suppose we have a dataset of some products, their purchased price, and selling price. Now we are going to calculate the profit for each product using subtraction.

Steps:

• Select a cell (E5) to calculate.
• Put the formula in the cell-

• Press Enter.
• Subtraction output for the selected two cells will be shown.

• Drag down to get the desired result.

Here you can see we got our profit for each product using a minus sign between two cells.

Read More: How to Apply Subtraction Formula in Excel (8 Easy Ways)

2. Utilize Paste Special Feature to Subtract Two columns in Excel

Utilizing paste special tool you can subtract two columns in excel. Suppose we have a dataset of some products and their sales for two months.

Now we are going to subtract a value from these two columns.

Step 1:

• Select the value in cell (C14) to subtract from two columns.
• Press Ctrl+C to copy.

Step 2:

• Choose two columns from the dataset and click the right button on the mouse.

• A new window will appear with options.
• From the options select “Paste Special”.

• From the “Paste Special” window choose “Subtract”.
• Click OK.

• You will find new values in the two columns subtracted by the value in cell (C14).

Read More: How to Subtract Columns in Excel (6 Easy Methods)

3. Subtract Two Columns with Dates in Excel

Sometimes we might need to calculate days from two dates. In this method, I am going to show you how you can calculate days from two dates with simple subtraction formula.

Here I have two columns of Dates. Now I will calculate the total days between these dates.

Steps:

• Choose a cell. Here I have chosen cell (D5).
• Apply the formula-

• Press Enter.
• You will find the days between those two dates.
• Drag down the “Fill handle”.

• Thus you will get the total days between the dates in two columns.

Read More: How to Subtract in Excel Based on Criteria (3 Suitable Examples)

4. Subtract Two Columns with Text

Applying the TRIM, SUBSTITUTE, REPLACE, and SEARCH functions you can subtract text from two columns. In this method, I will show you case-sensitive and case-insensitive cases to subtract text from two columns.

4.1 Case-Sensitive Condition

Here we have a dataset of some product codes. Now we are going to separate the codes from this column.

Steps:

• Select a cell (D5).
• Apply the formula-

Where,

• The TRIM function removes extra spaces from a text string.
• The SUBSTITUTE function replaces a string with another string.

• Press Enter.
• You will get the product name in the result cell.
• Now drag down the “Fill handle”.

• Here we got our desired result in a new column with only the product names.

4.2 Case-Insensitive Condition

In case-insensitive cases, we will use the TRIM, REPLACE and SEARCH functions to subtract text.

Steps:

• Choose a cell and apply the formula-

Where,

• The REPLACE function will replace a part from the text string.
• The SEARCH function will look for a part in the given string.

• Press Enter.
• Drag down the “Fill handle”.

• Thus we will get our desired text from the string.

Read More: Subtraction for Whole Column in Excel (with 5 Examples)

5. Create Pivot Table to Subtract Two Columns in Excel

While working in excel often we need to subtract data in a pivot table. In this method, I am explaining a quick way to subtract two columns in a pivot table.

Let’s create a pivot table first. Suppose we have a dataset of some teams and their sales and collection report. We will create a pivot table from the list and then subtract between the columns in the pivot table.

Step 1:

• Select the whole dataset.
• Choose “Pivot Table” from the “Insert” option.

• In the “PivotTable from table or range” select “Existing Worksheet” and then the location in the same worksheet.
• Press OK.

Step 2:

• Choose all three options from the “PivotTable Fields”.

• Here we got our pivot table. Now we will subtract between these two columns.

Step 3:

• From the “PivotTable Analyze” option go to “Field, Items, & Sets” and select “Calculated Field”.

• A new window will appear named “ Insert Calculated Field”.
• In the “Name” section type “Remaining Collection” and in the “Formula” section apply the subtraction formula between the fields “Sales” and Collection”.
• Press OK.

• This way you will get the result in a new column of a pivot table.

Read More: How to Subtract Multiple Cells in Excel (6 Effective Methods)

Use Absolute Reference to Subtract a Number from Two Columns in Excel

An absolute reference is used to refer to a fixed location in a cell. Using absolute reference you can subtract a number from two columns in excel.

Here we have a dataset. Now we are going to subtract the number 10 from both of the columns by applying the subtraction formula.

Steps:

• Choose a cell. Here I have selected cell (F5).
• Apply the formula-

Where,

• We used the dollar sign($) to lock the cell which works like an absolute reference.

• Click Enter.
• Thus we got our result for the cell.
• Drag down the “Fill handle” to the left side to get the output in both columns.

• Now, pull the “fill handle” down selecting both columns.

Thus we can get our subtracted data for both columns.

Read More: How to Add and Subtract Multiple Cells in Excel (3 Examples)

Apply Matrix Subtraction in Excel

In many cases, we may need to apply matrix subtraction in excel. In this method, I will show you a simple way of doing matrix subtraction. Here we have a dataset of two matrix. Now we will subtract between this matrix.

Steps:

• Select rows and columns like the matrix rows and columns to get the output.
• Apply the formula in the cells-

• Press Enter.
• Thus we can get our output with a simple subtraction formula between the cells.

Things To Remember

• Before Applying formulas in a cell don’t forget to check the cell is in general format. If not then- select the cell and click the mouse’s right button to open options. From Options > Format Cells > General.

Conclusion

I have tried to cover all the methods of subtracting two columns in excel. Hope you find it useful. Feel free to comment in the comment section. Thanks!

Related Articles

• How to Subtract from a Total in Excel (3 Quick Methods)
• Subtract from Different Sheets in Excel (2 Suitable Ways)
• Excel VBA: Subtract One Range from Another (3 Handy Cases)