The most basic and often used function is the subtract function. Numbers are everywhere, from calculating salary, budget allocation, inventory management, and sales forecast. The standard arithmetic operations applied to the data on the spreadsheets are addition, subtraction, multiplication, and division.
In Microsoft Excel, you have the function =SUM() to add the given set of numbers. But, for subtraction, there is no explicit function as ‘SUBTRACT’, as the ‘SUM’ function. Instead, you can use the symbol ‘-’ to subtract numbers to get the results.
Besides subtracting numbers, you can subtract two columns, multiple columns, date, time, and percentages, using the ‘-’ sign. This article aims to help you understand how to subtract in Excel, with examples.
How to Subtract Cells in Excel
In Excel, to find the difference between the given numbers, perform a simple subtraction using the formula =number1 – number2.
When you have an equation that performs various arithmetic operations, the subtract formula in Excel to do it is = (number1 – number2 )/ number n). Use parentheses to align with the order of operations in mathematics.
The example below is self-explanatory. You can follow this pattern for simple calculations in Excel.
Subtract two numbers.
Consider the example below, which has a series of numbers to be subtracted, and then display the results. The formula to do it, type the cell references separated by a minus sign like we did when subtracting multiple numbers.
For example, to subtract cells E3:E7 from E1, construct a formula as =E1-E3-E4-E5-E6-E7, or simply = E1-SUM(E3:E7).
Subtract a series of numbers.
Excel spreadsheets store vast amounts of data, and you cannot apply the minus operator when you have a series of numbers in multiple columns.
What do you do when subtracting a large set of numbers? Simple, subtract cells in Excel. Follow these steps:
- Step 1: Select the cell where you want the result to appear.
- Step 2: Enter the address of the cells to be subtracted with the minus sign.
- Step 3: Drag the formula until the end of the required data column.
Subtract cells using the formula.
How to Subtract Dates in Excel
You subtract dates the same way you would numbers, as Excel stores date values as numbers internally. Starting with 1 for the year 1900 and the consecutive numbers for the following years.
The formula to subtract dates in Excel is = End_Date – Start_Date. Use this in the cell where you want the duration between those two days to be displayed.
In the example below, another date appears when you apply the formula instead of the duration. That’s because the cells are formatted to accept and display date values.
Select the duration column, right-click, and select Format Cells, then select the category as General to display the result in numeric value instead of a date value.
Subtract Dates in Excel.
You can enter dates directly with a minus sign between the end_date and start_date to get the duration of days. Use the formula as =DATE( year, month, date) – DATE( year, month, date).
As you type, you can see Excel prompting you with the formula and the arguments it needs to get to display the result.
Subtract Dates Using Date()
How to Subtract Times in Excel
To subtract time values in Excel, use the minus(-) sign between the end time and the start time to get the time elapsed value. Time has three components to it: hours, minutes, and seconds. Change the cell format to display the time you require in the first.
The time() in Excel follows a 24-hour format, and when you enter the value in PM, Excel automatically converts the time mentioned past 12 PM to 13:00 (1 PM), and so on.
Subtract Time in Excel
How to Subtract Percentages in Excel
Subtracting values in percentages are similar to subtracting ordinary numbers in Excel. Enter the percentage values in the columns, then enter the formula mentioning the column reference with a minus sign (-) between them.
You must change the format of the cell so that it can accommodate percentage values and display the results accordingly.
To change the cell format, select the cells with the percentage values, right-click on them, and select Format Cells -> Number Tab (Category) -> Percentage.
You can also subtract a percentage from a decimal number; for instance, if there’s a deduction of x% of salary from the employee’s net salary, use the formula = salary*deduction( in %).
In the example given below, the deduction in percentage from the salary is calculated using the formula =A5*$B$5.
In Excel, percentages are interpreted as a hundredth of their value. For example, 20% is stored as 0.20, and Excel uses this underlying value in all formulas and calculations.
The formula =A5*$B$5 is equivalent to =A5 * 0.20. Just change the cell format as a percentage so that Excel calculates the percentage directly in a formula if this works better for your worksheets.
In this example, the reduction in the salary is equal for all employees, which is mentioned in cell B5. To apply that value in all the cells, make it an absolute reference so that the values remain the same when copied to other cells.
The ‘$’ can be used, $B$5, to freeze the value within the cell in the row or column reference, as it is copied to the cells below.
Subtract Percentage in Excel
How to Subtract Columns in Excel
In Excel, if you wish to subtract all the rows in the given columns, then mention the formula in the first cell, then drag the fill handle, or double-click the plus sign to copy the formula to the entire column.
In the example below, column C numbers are subtracted from column B numbers, beginning with row 2. The formula is =B2-C2.
Subtract Columns in Excel
Frequently Asked Questions
1. How to subtract in Excel?
The simple method to subtract in Excel is to use the ‘-’ sign between the numbers or the cell references. For instance, =10-5, or =b-c.
2. How can you subtract multiple cells in Excel?
When you have data in multiple cells as you have in a matrix, you can subtract the values in the corresponding cell in one matrix from the values in another matrix and display the result, as shown in the example below.
Step 1: Select a range of empty cells with the same number of rows and columns as your matrices.
Step 2: In the selected range or the formula bar, type the matrix subtraction formula as =(A2:C4)-(E2:G4).
Step 3: Press enter to get the results.
3. What is a shortcut to subtracting in Excel?
Excel subtraction does not provide a function for subtracting values in the spreadsheet. You can use the minus(-) sign between the values that you wish to subtract, as you use it for simple arithmetic operations.
For example, use =10-5 to supply the values directly or use the cell reference as =A2-B2.
Final Thoughts
Excel is a versatile tool to perform calculations and produce reports and charts that help businesses to make informed decisions. Many nuances would be worth knowing to apply in your everyday work.
You can learn how to create an absolute reference, which is useful when you work with formulas and functions. Another thing that you should know is how to calculate percentages in Excel while working with financial data for a quarter.
Are you a current job-seeker? We offer courses in Excel and Microsoft Office to advance your skills in academic and career prospects. Visit our website to learn more about these vital courses.
