To subtract two columns in Excel:

- Select a blank cell and insert the formula with the references for the columns such as:
**=A1-B1**, then 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

Let’s dig deeper into the subject and use a few different methods to subtract columns.

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

## Method 1 – Using the Subtraction Operator

Let’s use the provided dataset to calculate the profit on each item.

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

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
**E6**. - Insert the formula:
**=D5:D12-C5:C12**

- The formula returns an
**array**format.

**Note:**

- For any older versions other than Microsoft 365, press
**Ctrl + Shift + Enter**keys for an**array**format. - The array formula returns a
**#SPILL!**error if it can’t fit its contents.

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

**Method 2 – Using the Absolute Cell Reference to Subtract**

- Select a cell D5 and insert the formula:
**=C5-$C$14** - Apply the
**Fill Handle**to copy the formula. - This subtracts the cell C14 from cells C5 to C13 and applies the result in column D.

## Method 3 – Subtracting Two Columns with Dates

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

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

- Select a blank cell and insert the following 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

## Method 4 – Using the Paste Special Feature

Suppose we have a dataset of some products and their sales for two months. Let’s subtract a single cell value from all the columns.

- Select the cell that you want to subtract from two columns and press
**Ctrl + C**to copy. - Select the data from where you want to subtract.
- Go to
**Home**, choose the**Paste**drop-down, and select**Paste****Special**. - The
**Paste Special**dialog box appears. - 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

## Method 5 – Using an Excel Pivot Table Tool to Subtract Two Columns

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 the
**Insert**tab and the**PivotTable**drop-down, then choose**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**.

### Step 2 – Add the Pivot Table Fields

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

### Step 3 – Create a New Calculated Field

- Go to
**PivotTable Analyze,**choose the**Field, Items, & Sets**drop-down and select**Calculated Field**.

- A dialog box will appear named
**Insert Calculated Field**.

- Insert field name and formula:
**=****Sales – Collection.** - Press
**Add**and click on**OK**. - You will get the result in a new column of a pivot table.

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

## Method 6 – Using Excel Functions

### Case 1 – Case-Sensitive Condition

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

**Speaker SR-752**and**Keyboard KD-02**didn’t change because of case sensitivity.

### Case 2 – Case-Insensitive Condition

- 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 the Practice Workbook**

## 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 an 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

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