How to Subtract in Excel?- 4 Different Scenarios
(Note: This guide on how to subtract in Excel is suitable for all Excel versions, including Office 365)
Have you tried using the ‘SUBTRACT’ function in Excel and found it doesn’t appear in the list of mathematical operations?
This is because ‘Subtract’ is an operator while SUM, DIV, MIN, etc, are functions. You can use the SUM function to subtract by entering the values to be subtracted with a negative sign. For a brief example, let’s say you want to subtract the values 4 and 7 from 24; the corresponding formula using the SUM function would be
SUM(24, -4, -7).
I will guide you through the steps on how to subtract in Excel in this article.
You’ll Learn:
Related reads:
HOW TO CALCULATE TIME IN EXCEL? 2 DISTINCT TYPES
HOW TO CALCULATE PERCENTILE IN EXCEL? 3 USEFUL FORMULAS
HOW TO USE SUMPRODUCT FUNCTION IN EXCEL? 5 EASY EXAMPLES
Different Scenarios on How to Subtract in Excel
Before we see how to subtract in Excel, create a new worksheet containing the values to subtract for your reference or open an existing worksheet. For this tutorial, let us consider the following data to perform the subtract operation in different methods.
Let us go through the different scenarios and the steps involved in how to subtract in Excel.
Subtract by Applying Formulas in the Formula Bar
If you want the total difference on a particular cell, you can use the formula bar to enter the values you want to subtract directly.
- Select the cell where you want to display the result. For this instance, let us choose the cell next to Total, which is B18.
- Click on the cell and head to the Formula Bar under the Menu Tabs ribbon. This method will also work when directly entering values in the specified cell.
- Every formula in Excel starts with an ‘equal to’ sign (=), followed by the values and operation signs.
- For example, to subtract two values in B4 and B5, the corresponding formula would be =(B4-B5).
- Once you have entered the formula, hit Enter to display the result in the specified cell. The same procedure applies when entering the formula directly into the cell.
- Also, instead of entering the cell reference of the values, you can directly enter the values to be subtracted.
- For the same example, instead of =(B4-B5), select the destination cell and type =(3654-1425) to display the result.
In this method, we saw how to subtract in Excel by applying the formula in the formula bar.
Subtract Values in Two Columns with a Single Formula
Let’s say you need to find the differences between two given columns. Instead of manually entering the formula in every cell, follow these steps to do it quickly:
- To test this method, let us consider the following data list in columns A and B and display the result in column C.
- Apply the formula =(B4-C4) on the D4 cell and hit enter.
- Place the mouse near the bottom right part of the cell. Click on the Plus sign and drag it to the end of the cell range you want to apply the same formula.
- Release the click when you reach the end of the column to apply the formula to all the cells in the selected range.
- Notice that a single formula on one cell has been applied to the rest of the column, performing the same operation.
- This method comes in handy while handling large data and performing mathematical operations to save time and deliver efficient work.
In this method, we saw how to subtract in Excel by using the same formula for two columns.
Suggested reads:
How to Create an Excel Gauge Chart the Easy Way?
How to Create an Excel Gauge Chart the Easy Way?
How to Alphabetize in Excel? 2 Easy Methods
Reduce One Constant Value From All the Entries
In previous methods, we saw how to subtract values from two cells to two columns. If you want to subtract a particular constant value from the entire row, follow the steps below.
- Say you need to subtract the constant value of 50 from the entire column named A.
- Click on cell C4 and enter the formula =(B4-$F$4). Insert the dollar sign $ in front of the cell reference F4, and press Enter.
- You can enter the formula on the formula bar or the selected cell.
After you hit Enter, select the bottom right part (plus + sign) of the cell and drag it towards the end of column A to apply the same formula to the selected cell range.
- The constant value is individually subtracted from cells in the cell range.
Note: If the cell value is lesser than the constant value, Excel returns the value with a negative sign.
When you want to deduct a particular EMI from your monthly expenses, this method will help keep a record throughout the year.
In this method, we saw how to subtract a constant value from a column and apply it to the remaining.
Subtract Data Types such as Date, Time, and Percentages
The subtraction steps are the same for different data types. However, there is a list of things you must be mindful of before carrying out the mathematical operation.
- The formula remains the same when the values to subtract are in percentages. But ensure the values contain the % sign at the end.
To subtract dates, ensure that both cell values have the dates in the same format.
- For example, 01 Jan 2023 or 1 Jan 23. Any other format that doesn’t include the date, month, and year will return as an error.
- To return the subtracted value on the difference column, enter the formula =(B5-C5) in cell D5.
- To return a positive value, always place the bigger value as the first cell reference and the lesser value as the second one.
- If you have consecutive dates and want to subtract them, perform the operation on one cell and drag the plus sign to apply it to the remaining list.
We saw how to subtract in Excel in four different scenarios in simple steps. The format of the values differs, while the formula to subtract them remains the same. Ensure that the values are in the correct format before subtracting.
In this method, we saw how to subtract in Excel when the values are in percentage, date, or time.
Also read:
How to Password Protect an Excel File? 2 Effective Ways
How to Remove Comma in Excel? 5 Easy Ways
How to Add Data Bars in Excel? 2 Easy Ways
Frequently Asked Questions
Can I subtract time in Excel?
You can subtract dates in Excel using the same formula as subtracting values. Ensure that values are in this format dd: mm: yy. If not, Excel will return the value as an error.
What is the formula to subtract in Excel?
If you want to subtract values on cell 1 and cell 2, the corresponding formula is
=(Cell 1 – Cell 2).
Click on the cell and enter the formula on the formula bar or the cell itself.
How do I subtract multiple cells at once in Excel?
To subtract values from columns at once, you must perform the operation on one cell and drag it to the entire list to apply the same subtraction formula instantly.
Closing Thoughts
Learning and developing Excel skills is essential to represent data to viewers better. In this article, we discussed how to subtract in Excel in different scenarios.
Please visit our free resources center for more high-quality Excel and Microsoft Suite application guides.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT taught Excel and other business software for over ten years. You can access 150+ IT training courses for a low monthly fee.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
