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

Get FREE Advanced Excel Exercises with Solutions!

To subtract two columns in Excel:

  1. Select a blank cell and insert the formula: =A1-B1
  2. 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.

Overview image of subtract two columns in Excel

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.

Sample dataset for applying Subtraction method

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

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

Using Subtraction operation

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:

  1. Select a blank cell
  2. Insert the formula: =D5:D12-C5:C12
    The formula returns an array format.
    Subracting one column from another

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:

  1. Select a cell and insert the formula: =C5-$C$14
  2. Apply the Fill Handle to copy the formula.Using Absolute Cell references

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:

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

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:

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

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:

  1. Select the cell that you want to subtract from two columns and press Ctrl+C to copy.
  2. Then, select the data from where you want to subtract.
  3. Now go to Home > Paste drop-down > Paste Special.
    Thus, the Paste Special dialog box appears.
    Utilize Paste Special to Subtract Two columns in Excel
  4. From the “Paste Special” dialog box, select the “Subtract” operation and Click OK.Selecting Subtract option from Paste Special tool

Therefore, we obtain the updated values by subtracting a fixed value from the two columns.Outcome using Paste Special tool

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.

Sample dataset for subtracting two columns using Excel Pivot Table

Step 1: Create a Pivot Table

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

    Selecting Pivot Table tool

Step 2: Add Pivot Table Fields

Check necessary components from the PivotTable Fields.
Choosing elements from PivotTable Fields

Step 3: Create a New Calculated Field

  1. Now, go to PivotTable Analyze > Field, Items, & Sets drop-down > Calculated Field.
    Selecting Calculated Field from Pivot Table Analyze tab
  2. A dialog box will appear named Insert Calculated Field.
    • Insert field name and formula: = Sales – Collection
    • Press Add > OK.

    Creating formula with Insert Calculated Field dialog box

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

Final outcome obtained from 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:

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

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

Using TRIM and SUBSTITUTE functions for case-sensitivity

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:

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

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

Using TRIM, REPLACE, SEARCH and LEN functions for case-insensitivity

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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo