0

How To Subtract in Google Sheets with a Formula

Posted by Matthew on September 1, 2018

If you use spreadsheets to organize, analyze, and report on data, then you are probably familiar with Google Sheets, the spreadsheet application in Google’s web-based office suite. Although it’s a web app, Sheets competes head to head with Microsoft Excel across a number of features. While lacking the full reach and power of Microsoft’s desktop offering, Sheets still has a huge amount of functionality and is a very powerful tool for working with your information. Since it has many of the same features as Excel, some tasks can be done in both software packages in much the same way, while others require a different approach.

In this tutorial article, I will show you several different methods in Sheets for subtracting numbers from one another, a very basic spreadsheet function. Some of these ways are just the same as you would do it in Excel, while others are different.

Using the Formula bar

The simplest way to subtract two numbers in Sheets works just the same as it would work in Excel – just click on a cell, type what you want to subtract in the formula bar, and the cell will now contain the value of that computation. You can use plain numbers or cell references or both; typing “=A3-200” will give you the value of cell A3 minus 200.

The MINUS Function

One notable difference between Excel and Sheets is that Google’s spreadsheet application includes a MINUS function. The syntax for the function is: MINUS(value1, value2). Note that MINUS is limited to subtracting just two values. Value1 is the value you want to subtract from and value2 is the value you want to subtract.

To add the MINUS function to a worksheet, open a blank spreadsheet in Google Sheets. As an example, enter ‘250’ in cell B3 and ‘200’ in cell B4. Select cell B5 and enter ‘=MINUS(B3, B4)’ in the function bar. Cell B5 will return the value 50 when you press Enter.

The Subtract Formula

The MINUS function is somewhat limited, as it’s restricted to subtracting just two values. You can’t use the function to subtract numbers included in a wider range of cells. So it’s still better to subtract numbers in Google Sheets with subtraction formulas.

The subtraction formula in Google Sheets is actually much the same as how you would subtract numerical values with a calculator. The one difference is that you must always enter the equals (=) sign in the fx bar first and place the formula after it. You can include either actual numbers or spreadsheet cell references in the formula.

For an example, select the cell C3 in a Google Sheets spreadsheet. Enter the formula ‘=250-200’ in the fx bar, and press the Return key. C3 will now include the total 50.

Alternatively, you can subtract values entered in spreadsheet cells instead. For example, subtract the values you entered in cells B3 and B4 for the MINUS function. Select B6 and input ‘=B3-B4’ in the function bar. B6 will now include the total of B3-B4, which is otherwise 30.

Subtract Dates in Google Spreadsheets

Aside from integers and real numbers, you can also subtract dates with the subtraction formula. You can find the number of days between dates by entering the dates in spreadsheet cells and including their cell references in the formula. Enter the dates in U.S. format, which is mm/dd/yyyy.

For example, select cell B8 on your Sheets spreadsheet and input ‘3/25/2017’ as the first date. Click C8 and enter ‘2/17/2017’ as the date to deduct from B8. Then select D8 to include the formula in that cell. Enter ‘=B8-C8’ in the fx bar, and press the Return key. D8 will now include the value 36 as in the shot below. As you see (and can confirm by looking at a calendar), there are 36 days between the dates. This Tech Junkie post also tells you about some of the other Google Sheets functions that calculate the number of days between dates.

Subtracting Cell Range Totals

Google Sheets users can deduct cell range totals by incorporating the SUM function within a subtraction formula. As such, you don’t need to add separate SUM functions to a couple of cells first to deduct two, or more, column totals.

For an example, enter the values ’50’ and ‘150’ in cells E3 and E4 in your Sheets spreadsheet. Input ’50’ and ‘125’ in cells F3 and F4. Then your spreadsheet should match the one directly below.

Now select F7 and click within the fx bar. Enter ‘=SUM(E4:E5)-SUM(F4:F5)’ in the function bar, and press the Return key. Cell F7 will deduct the column F total from column E, which amounts to 25.

You can also include numeric values in the formula instead. Select cell F8 in the spreadsheet, and enter the formula ‘=SUM(50, 150)-SUM(50, 125)’ in fx bar. F8 will also return the same total as F7.

Subtracting Cell Values in Multiple Worksheets

Google Sheets includes an Add Sheet button that enables you to add more worksheets to a spreadsheet. If you add multiple worksheets to your spreadsheet, you might need to subtract numbers in alternative sheets. You can do that by including sheet references within a subtraction formula.

Enter the value ‘150’ in cell B10 of Sheet 1 in your spreadsheet. Then you should press the Add Sheet button at the bottom left of Google Sheets to add Sheet 2 to the spreadsheet. Click Sheet 2 and enter ‘125’ in cell B10.

Now you can add a formula to your spreadsheet that deducts B10 in Sheet 2 from B10 in the first worksheet. Select Sheet 1 and click cell B11. Enter ‘=Sheet1!B10-Sheet2!B10’ in the fx bar. B11 will include the total 25, which is the difference between 150 and 125.

So that’s how you can subtract in Google Sheets with formulas. Subtraction formulas in Sheets are flexible, so you can adjust them in various ways to deduct values as required. Check out this Tech Junkie guide for further details on Excel subtraction formulas, many of which you can also use in Google Sheets.

Leave a Reply

Your email address will not be published. Required fields are marked *