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

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.

Subtract Two Columns in Excel

Steps:

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

Apply Subtraction Between Two Columns in Excel

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

Apply Subtraction Between Two Columns in Excel

  • 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: Excel VBA: Subtract One Range from Another (3 Handy Cases)


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.

Utilize Paste Special to Subtract Two columns in Excel

Step 1:

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

Utilize Paste Special to Subtract Two columns in Excel

Step 2:

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

Utilize Paste Special to Subtract Two columns in Excel

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

Utilize Paste Special to Subtract Two columns in Excel

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

Utilize Paste Special to Subtract Two columns in Excel

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

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


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.

Subtract Two Columns with Dates in Excel

Steps:

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

Subtract Two Columns with Dates in Excel

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

Subtract Two Columns with Dates in Excel

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

Subtract Two Columns with Dates in Excel


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.

5.1 Case-Sensitive Condition

Steps:

  • Select a cell (D5).
  • Apply the formula-
=TRIM(SUBSTITUTE(B5,C5,""))

Where,

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

5.1 Case-Sensitive Condition

  • 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-
=TRIM(REPLACE(B5,SEARCH(C5,B5),LEN(C5),""))

Where,

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

5.2 Case-Insensitive Condition

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

5.2 Case-Insensitive Condition

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


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.

Create Pivot Table to Subtract Two Columns in Excel

Step 1:

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

Create Pivot Table to Subtract Two Columns in Excel

  • 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”.

Create Pivot Table to Subtract Two Columns in Excel

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

Create Pivot Table to Subtract Two Columns in Excel

Step 3:

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

Create Pivot Table to Subtract Two Columns in Excel

  • 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.

Create Pivot Table to Subtract Two Columns in Excel

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


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.

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

Steps:

  • Choose a cell. Here I have selected cell (F5).
  • Apply the formula-
=C5-$C$14

Where,

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

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

  • 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.

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

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

Thus we can get our subtracted data for both columns.


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.

Apply Matrix Subtraction in Excel

Steps:

  • Select rows and columns like the matrix rows and columns to get the output.
  • Apply the formula in the cells-
{=(B5:D7)-(F5:H7)}

Apply Matrix Subtraction in Excel


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

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo