How to Subtract Two Columns in Excel (4 Easy Ways)

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

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:

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.

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.

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.

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.

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.

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

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.

Download Practice Workbook

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

Subtract Two Columns.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!