Download Excel Workbook
Watch our free training video on How to Subtract:
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.
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 –
download excel workbook SubtractionInExcel.xlsx
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.
Choose an empty cell where you want the result to appear.
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.
Replace “number2” with the value or cell reference(s) you want to subtract. In our case, it is A2:A10.
Press Enter to get the subtraction result.
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)?
Here we have our absolute reference in cell A12. We would like to subtract that value from A1 to A10, and have the results show in column B.
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
Enter the formula: =A1-$A$12
Press ENTER.
Hover over the bottom right corner of cell B1 until you see a black cross. Click and drag until you get to B10. Let go of the mouse button.
BONUS: Paste Special
What if you want to keep your spreadsheet formula-free? This is where the Paste Special comes in handy:
Select cell B1, and Right click. Click Copy (or press CTRL = C).
Select cells A1:A10. Right click, then choose Paste Special.
Under the Operation option, select Subtract.
Click OK.
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!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
