To subtract two columns in Excel:

- Select a blank cell and insert the formula:
**=A1-B1** - Use the
**Fill Handle**tool to copy the formula.

If you are using**Microsoft 365**, insert the array formula:**=[Column 1] – [Column 2]**

For example, [Column 1] = A1:A10 and [Column 2] = B1:B10

Here, you will get the difference of two columns.

Subtraction is the process of finding the difference between two numbers, decimals, or integers. Subtraction plays a vital role in Excel for financial analysis, inventory management, and data comparison. It is instrumental in computing variances between values, monitoring changes over time, and evaluating performance. Whether it is for budgeting, time calculations, or scientific analysis, subtraction serves as a fundamental operation that facilitates accurate data interpretation and aids in decision-making within spreadsheets.

In this Excel tutorial, you will learn to subtract two columns in Excel with some examples.

Consider a dataset where we have the Purchase price and Selling price for some products. Now, subtracting the two columns, we obtain profit for each product.

There are 4 ways to subtract two columns in Excel:

**Table of Contents**Expand

## Using Subtraction Operator

To subtract two columns in Excel, you can simply use the **Subtraction** operator. It is easy to use like basic mathematics.

Consider a dataset of some products, along with their purchased price, and selling price. Now we are going to calculate the profit for each product using the subtraction method.

To subtract two columns in Excel, follow the steps below:

- Select a blank cell and insert the formula:
**=D5-C5** - Drag down the
**Fill Handle**to copy the formula.

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

Alternatively, if you have **Microsoft 365**, you can subtract two columns in Excel by selecting a whole column and subtracting it from another. Therefore, you will get the whole outcome with an array format. Here’s how:

- Select a blank cell
- Insert the formula:
**=D5:D12-C5:C12**The formula returns an

**array**format.

Therefore, profit is calculated from the difference between the Purchase price and the Selling price.

**Note:**

- For any older versions other than Microsoft 365, press
**Ctrl + Shift + Enter**keys for an**array**format. - Keep in mind that the array formula returns
**#SPILL!**error when it figures out any value or merged cell.

**Read More:** Excel formula to find difference between two numbers

**Using Absolute Cell Reference to Subtract**

While subtracting a common value from each cell, you must use an absolute cell reference. Absolute cell reference means locking a cell reference.

To use an absolute cell reference for subtracting two columns:

- Select a cell and insert the formula:
**=C5-$C$14** - Apply the
**Fill Handle**to copy the formula.

## Subtracting Two Columns with Dates

Using the Subtract operator, you can also find the difference between two dates. The subtraction formula returns the **text** format.

To subtract dates from another column, follow the steps:

- Select a cell and Apply the formula:
**=C5-B5** - Use the
**Fill Handle**tool to copy the formula in the adjacent cells.

Therefore, we obtain the duration from the difference between the Start Date and the End Date.

For further calculation, the **text** format will not work. So, converting into a **Number** format is inevitable. Using the Exccel **VALUE** function, you can subtract two columns and convert them into the number format. Here is how:

- Select a blank cell Insert formula:
**=VALUE(C5-B5)** - Use the
**Fill Handle**tool to copy the formula.

Thus, all the calculated values will be in number format.

**Read More:** How to Subtract in Excel Based on Criteria

## Using Paste Special Feature

Applying the paste special tool, you can subtract two columns in Excel. The tool offers the “**Subtract**” operation.

Suppose, we have a dataset of some products and their sales for two months.

To subtract a value from two columns using the **Paste Special** tool, follow the steps:

- Select the cell that you want to subtract from two columns and press
**Ctrl+C**to copy. - Then, select the data from where you want to subtract.
- Now go to
**Home**>**Paste**drop-down >**Paste****Special**.

Thus, the**Paste Special**dialog box appears.

- From the “
**Paste****Special**” dialog box, select the “**Subtract**” operation and Click**OK**.

Therefore, we obtain the updated values by subtracting a fixed value from the two columns.

**Read More:** How to Subtract Multiple Cells in Excel

## Using Excel Pivot Table Tool to Subtract Two Columns

You can also use **Pivot Table** to subtract two columns in Excel. Pivot Table is immensely useful when you are dealing with an extensive dataset.

Suppose we have a dataset of some teams and their sales and collection reports. We will create a pivot table from the list and then subtract between the columns.

### Step 1: Create a Pivot Table

- Select the whole
**dataset**. - Go to
**Insert**tab >**PivotTable**drop-down >**From Table/Range**. - The
**PivotTable from table or range**dialog box will appear.- Select the
**Existing Worksheet**option and a location in the same worksheet. - Press
**OK**.

- Select the

### Step 2: Add Pivot Table Fields

Check necessary components from the **PivotTable Fields**.

### Step 3: Create a New Calculated Field

- Now, go to
**PivotTable Analyze**>**Field, Items, & Sets**drop-down >**Calculated Field**.

- A dialog box will appear named
**Insert Calculated Field**.- Insert field name and formula:
**=****Sales – Collection** - Press
**Add**>**OK**.

- Insert field name and formula:

Finally, you will get the result in a new column of a pivot table.

**Read More: **Subtraction for Whole Column in Excel

## Using Excel Functions

You can subtract values from two columns by combining Excel functions in a formula. With this method, you can subtract text or a combination of both texts and numbers but you can not subtract just numbers. You can apply case-sensitive and case-insensitive cases to subtract text from two columns.

Here are the 2 cases with Excel functions:

### Case 1: Case-Sensitive Condition

A combination of the **TRIM** and **SUBSTITUTE**** **functions can be used for the case-sensitive case to subtract values from two columns.

Here are the steps to subtract values from two columns for the case-sensitive case:

- Select a blank cell and apply the formula:
**=TRIM(SUBSTITUTE(B5,C5,””))** - Use the
**Fill Handle**tool to copy the formula.

Here is the output considering the case sensitivity to subtract values from two columns.

As you can see in the image, **Speaker SR-752** and **Keyboard KD-02** didn’t change because of case sensitivity.

### Case 2: Case-Insensitive Condition

In case-insensitive cases, you can use the combination of the **TRIM**, **REPLACE**, and **SEARCH** functions to subtract two columns.

Here are the steps for the case-insensitive case to subtract values from two columns:

- Select a cell
- Insert the formula:
**=TRIM(REPLACE(B5,SEARCH(C5,B5),LEN(C5),””))** - Drag down the
**Fill Handle**tool to copy the formula.

Here is the output considering the case insensitivity to subtract values from two columns.

**Read More:** Create a Subtraction Formula in Excel

**Download Practice Workbook**

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

## Conclusion

In short, we explained how to subtract two columns in Excel. Microsoft 365 facilitates an array format where you can obtain the difference altogether. Besides, you can simply subtract a cell. The Paste Special tool also offers subtraction operation. The Pivot Table is fruitful for subtracting two columns when you are working with a gigantic dataset. Finally, the combination of Excel functions is useful for subtracting characters from a column. Feel free to share your insight, comments as well as queries in the comment section.

## Frequently Asked Questions

**Is it possible to subtract columns with different data types?**

Excel requires consistent data types for subtraction. For example, text cells need conversion to numbers before subtracting. To do so, you can use functions like VALUE or NUMBERVALUE.

**Can I automate the subtraction process if new data is added to columns?**

Using Excel **Table** (Ctrl+T), formulas will extend automatically when new data is added to the columns, facilitating the subtraction process.

**Related Articles**

- How to Subtract from a Total in Excel
- Subtract from Different Sheets in Excel
- Excel VBA: Subtract One Range from Another
- How to Subtract Sum of Several Cells from Fixed Number in Excel

**<< Go Back to Subtract in Excel | Calculate in Excel | Learn Excel**