Pinterest Pixel

Creative Ways to Subtract in Excel

Although Excel doesn’t have a dedicated SUBTRACT function, it offers several alternative methods to help you get... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Creative Ways to Subtract in Excel

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:

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 

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:

See also  Use Alt Code for Checkmark in Excel - A Quick Tip

Prepare your data in the worksheet.

Choose an empty cell where you want the result to appear.
Creative Ways to Subtract in Excel

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.
Creative Ways to Subtract in Excel
Press Enter to get the subtraction result.
Creative Ways to Subtract in Excel

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.
Creative Ways to Subtract in Excel
Enter the formula: =A1-B1
Creative Ways to Subtract in Excel
Press Enter to get the subtraction result.
Creative Ways to Subtract in Excel
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.
Creative Ways to Subtract in Excel

See also  How to Reference Another Sheet in Excel - Top 3 Methods

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.
Creative Ways to Subtract in Excel

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
Creative Ways to Subtract in Excel

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.
Creative Ways to Subtract in Excel

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).
Creative Ways to Subtract in Excel

Select cells A1:A10. Right click, then choose Paste Special.

See also  2 Useful Methods for Calculating CAGR in Excel

Under the Operation option, select Subtract.
Creative Ways to Subtract in Excel
Click OK.
Creative Ways to Subtract in Excel
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!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!