Although Excel doesn’t have a dedicated SUBTRACT function, it offers several alternative methods to help you get the job done effortlessly. In this article, we will explore the various techniques to Subtract in Excel, catering to both beginners and seasoned users.
Watch our free training video on How to Subtract:
Table of Contents
You might also want to check out this article on the Top 20 Common Problems in Excel! Don’t forget to take advantage of this Free Microsoft Excel Online Course – 20+ Hours Beginner to Advanced Course!
Download the Excel Workbook below to follow along and understand the Creative Ways to Subtract in Excel –
Now, let’s explore each of these steps individually!
SUM Function for Subtraction
Of course, manually inputing each cell reference will take a long time if you have a larger dataset. This is where the SUM function comes in handy. Let’s say we want to subtract the values in the range A2:A10 from A1. Follow there steps to use the SUM function:
Prepare your data in the worksheet.
Enter the formula: =SUM(number1, -number2)
Replace “number1” with the value or cell reference you want to subtract from. In our case, it is A1.
Subtract from Another Column
To subtract values from another column in Excel, you can use a formula that references cells from different columns. Follow these steps:
Prepare your data in the spreadsheet.
Select an empty cell for the result. In our case, it is C1.
Enter the formula: =A1-B1
Press Enter to get the subtraction result.
As you can see, the result is 966. Hover over the bottom right corner of cell C1 until you see a black cross. Click and drag until you get to C10. Let go of the mouse button.
Subtraction Using an Absolute Reference
What if we want to subtract one value (absolute reference) from multiple values (relative reference)?
Firstly, subtract A12 from A1. We will be able to make A12 as the absolute reference by putting the $ symbol in front of the column letter and the row number. $A$12
BONUS: Paste Special
What if you want to keep your spreadsheet formula-free? This is where the Paste Special comes in handy:
Select cells A1:A10. Right click, then choose Paste Special.
Under the Operation option, select Subtract.
There you have it! By learning these methods, you can easily subtract numbers in Excel, which will help you save time and work more efficiently when analyzing data or managing finances. Happy subtracting!
Below you will find 120+ Excel formulas & functions examples for key formulas & functions like XLOOKUP, VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more. Let us start learning for free!