WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

Subtraction in Excel

December 8, 2021
8.3K Views

Excel is a widely used software that doesn't require expertise in any particular field. With a basic understanding of the program's operations, anyone can use it.

In this guide, we will focus on the Subtract feature in Excel, which allows you to perform basic subtraction operations quickly and efficiently.

WPS Spreadsheet user interface



WPS Office offers a free Excel spreadsheet editor, making it an excellent alternative for those who prefer a free and user-friendly option. It provides a variety of features similar to those in Excel, including the Subtract feature we will discuss in this guide.

WPS Office Spreadsheet Subtract Feature

In a WPS Office Spreadsheet, you can subtract two numbers by simply typing a minus sign (-) between them. For example, to subtract 5 from 10, you would type "=(10-5)" into a cell. However, if you want to subtract the value in one cell from the value in another cell, you need to use a mixed cell reference. To do this, you need to use the dollar sign ($) to fix one part of the cell reference while allowing the other part to change.

For example, if you want to subtract the value in cell A2 from the value in cell B2 and display the result in cell C2, you would type "=B2-$A$2" into cell C2. The dollar sign before the "A" and the "2" in "$A$2" fix the row and column reference for cell A2, so that when you copy the excel subtraction formula to other cells, it always subtracts the value in cell A2.

WPS Office Spreadsheet offers multiple ways to subtract values in two cells. Here are the steps for each method:

Subtraction with Basic Operators:

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the subtraction formula in excel.

  • Click on the cell that contains the value you want to subtract from; minuend.

Box with field 1 selected




  • Type the minus sign (-).

  • Click on the cell that contains the value you want to subtract; subtrahend.

Box with field 1 selected




  • Press Enter to display the result.

SpreadSheet Total



Subtraction with Mixed Cell References

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the formula.

  • Click on the cell that contains the value you want to subtract from; minuend.

Box with field 1 selected




  • Type the minus sign (-).

  • Click on the cell that contains the value you want to subtract and make sure to add ‘$’ sign to add absolute reference.

Add $ to select absolute reference



  • Type ")" to close the function.

  • Press Enter to display the result.

Results



Subtraction with Formulas

WPS Office Spreadsheet offers a variety of built-in functions, including the SUBTRACT function. To use the SUBTRACT function, you need to specify the two values you want to subtract.

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the formula.

  • Type "IMSUB" to start the SUBTRACT function.

  • Click on the cell that contains the value you want to subtract from.

Function added




  • Type a comma (,) to separate the arguments.

  • Click on the cell that contains the value you want to subtracT.

Fields selected to calculate difference



  • Type ")" to close the function.

  • Press Enter to display the result.

Answer




How to Subtract a Percentage in Excel

  • Enter the original amount in a cell.

Box with field 1 selected




  • Enter the percentage you want to subtract in another cell, preceded by the minus sign (-).

  • In a third cell, use the following formula: =original amount * (1 - percentage).

How to add percentage function



  • The result will be the original amount minus the percentage you entered.

Results




Subtraction with Negative Numbers

WPS Office Spreadsheet can handle negative numbers just like positive numbers. To subtract a negative number, you can use the minus sign (-) as usual.

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the formula.

  • Type the minuend value.

  • Type the minus sign (-).

  • Type the negative subtrahend value (with the minus sign included).

How to subtract negative numbers




  • Press Enter to display the result

Subtraction with Dates and Times

WPS Office Spreadsheet can also perform subtraction with dates and times. When you subtract two dates, WPS Office Spreadsheet returns the number of days between the two dates. When you subtract two times, WPS Office Spreadsheet returns the difference in time as a decimal value.

For example, if you have the date January 1, 2023 in cell A1 and the date January 10, 2023 in cell A2, you can subtract the two dates by typing "=A2-A1" into a cell. The sheet will return the value "9", which represents the number of days between the two dates.

To subtract times, you can use the same methods as for numbers. For example, if you have the time 9:00 AM in cell A1 and the time 10:30 AM in cell A2, you can subtract the two times by typing "=A2-A1" into a cell. The sheet will return the value "0.0625", which represents the difference in time as a decimal value.

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the formula.

  • Click on the cell that contains the later date or time (the minuend).

Select Date 1 field



  • Type the minus sign (-).

  • Click on the cell that contains the earlier date or time (the subtrahend).

Select date 2 field



  • Press Enter to display the result.

Number of days



Note: For complex calculations, you can also use more advanced functions such as IF, SUMIF, AVERAGEIF, etc

Subtraction with Custom Time Intervals

Excel can also perform subtraction with custom time intervals, such as hours, minutes, and seconds. To do this, you need to convert the time interval to a decimal value using the appropriate conversion factor. For example, to subtract 2 hours and 30 minutes from 10:00 AM, you would type "=10:00 AM-(2/24+30/1440)" into a cell. The conversion factor for hours is 1/24, and the conversion factor for minutes is 1/1440 (the number of minutes in a day

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the formula.

  • Type the minuend time.

Select relevant field



  • Type the minus sign (-).

  • Type the decimal value of the custom time interval (e.g., 2/24 for 2 hours).

Enter hours to be subtracted






  • Press Enter to display the result.

Result



Subtraction with Conditional Formatting

Excel's conditional formatting feature allows you to highlight cells based on certain conditions. You can also use conditional formatting to subtract values in one cell from another cell and display the result in a third cell. To do this, you need to create a custom formula that subtracts the values in the two cells and returns a Boolean value (TRUE or FALSE) based on a certain condition.

Add function



For example, you can highlight cells in a column if the value in that cell is less than the value in the cell above it by using the following formula in the conditional formatting rule: "=A2-A1

Show Results



How to Subtract in Google Sheets

  • Select the cell where you want to display the result of the subtraction.

  • Type the equal sign (=) to start the formula.

  • Type the minuend (the number you want to subtract from).

Box with field 1 selected



  • Type the minus sign (-).

  • Type the subtrahend (the number you want to subtract).

Box with field 2 selected



  • Press Enter to display the result.

Results



Tips and Tricks for Subtraction in Excel

Here are some tips and tricks to make subtracting in Excel easier and more efficient:

  • Use the fill handle to copy formulas to multiple cells quickly.

  • Use named ranges to make your formulas easier to read and maintain.

  • Use the AutoSum feature to quickly add up a range of cells and subtract the total from another cell.

  • Use parentheses to control the order of operations in complex formulas.

  • Use the IF function to subtract values only if certain conditions are met.

  • Use the ROUND function to round the result of a subtraction to a certain number of decimal places.

Subtracting in Excel is a basic operation that can be performed using a variety of methods. Whether you're subtracting numbers, dates, times, or custom time intervals, Excel has the tools you need to perform accurate and efficient calculations. By following the tips and tricks outlined in this guide, you can become an expert at subtracting in Excel and improve your data management and analysis skills.

FAQ

How do I subtract in Excel without negative values?

In Excel, you can subtract without using negative values by using the MIN function to set a lower limit of zero for your result. Subtract your integers normally, then use the MIN function to return the result or zero, whichever is greater. This will ensure that no negative values appear in your output.

Is there a limit to the number of values I can subtract in Excel?

In Excel, there is no hard limit to the number of values you can subtract. If you try to conduct complex computations on very big datasets, you may find performance issues or run out of memory, depending on the version of Excel you are running and the amount of accessible memory on your machine. To manage enormous datasets, it's best to divide difficult calculations down into smaller parts or to leverage more powerful hardware or cloud-based solutions.

Can I subtract dates in WPS spreadsheets?

Yes, dates can be subtracted in WPS Spreadsheets. In truth, WPS Spreadsheets include a number of functions and operators for doing date calculations, including subtraction.

Can I subtract 2 digit numbers?

Yes, two-digit numbers can be subtracted in WPS Excel. In reality, you may use the same basic subtraction method to subtract numbers of any size. To subtract two-digit integers, just enter the numbers to be subtracted into two separate cells, then subtract them with the "-" operator.

The Perfect Alternative to Excel: WPS Spreadsheets

WPS Office Spreadsheets is an excellent alternative to Microsoft Excel. It offers a wide range of features that are easy to use and provides users with an interface that is similar to Excel. WPS Office Spreadsheets is also free to use, making it a great option for users who do not want to invest in expensive software.

With its advanced formula tools, pivot tables, and conditional formatting options, WPS Office Spreadsheets can handle all your data management and analysis needs. So, whether you're a student, a business professional, or anyone who needs to manage data and perform calculations, WPS Office Spreadsheets is the perfect solution for all your spreadsheet needs.



15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.