# How To Subtract In Google Sheets

Enter a formula using the subtract operator '-' (the dash on your keyboard) to subtract data in referenced cells (=A1-A2) or numbers (=10-7).

This follows the order of operations, can handle as many numbers as you want, and works with decimals.

You use a formula to subtract in Google Sheets.

The formula can use mathematical operators or a dedicated subtraction function.

## How To Subtract Using Operators In Google Sheets

The subtract operator is a minus sign created using the dash on your keyboard (-).

You can use this operator to find the difference between two numbers:

=10-7

Entering this formula in a cell will output 3.

It works with decimals and can output negative numbers:

=15.49-99

Which outputs -83.51.

You don't have to stop at just two numbers. You can include as many as you need to:

=100-15-8-30-11-3

Which outputs 33.

You can use cell references that contain numbers instead of typing out the numbers (or a mix of both). In this example sheet, cell A3 contains a formula:

 A 1 10 2 7 3 3

The formula could be either of:

=10-7
=A1-A2

I prefer using =A1-A2 because it means the formula output will update if the values change:

In fact, you can use a cell like a calculator.

Google Sheets follows the order of operations and supports parentheses () and common mathematical operators:

 A B 1 Add + 2 Subtract - 3 Multiply * 4 Divide / 5 Exponent ^

This allows you to create complex formulas that execute exactly as you need them to:

=(10-7*((4-5)/5+16))*5

When using operators, blank cells are treated as 0 and text that can't be coerced into a number (e.g. "7" is automatically change into 7) will cause a #VALUE! error.

 A 1 10 2 text 3 #VALUE!

FREE RESOURCE

12 exclusive tips to make user-friendly sheets from today:

## How To Subtract Using Functions In Google Sheets

Google Sheets has a MINUS function you can use to find the difference between two numbers:

=MINUS(value1, value2)
• value1 = starting number.
• value2 = number to take away from value1.
=MINUS(10, 7)

Just like the minus operator it:

• Can be used with cell references =MINUS(A1,A2)
• Can handle decimals
• Can return negative numbers
• Treats blank cells as 0, and
• Returns a #VALUE! error when text is input

However (and this is a big however), it accepts only two arguments.

This means it can only take one number away from another number at time making it far less flexible than the minus operator (which is why the operator is my preferred choice).

Take this formula as an example:

=100-15-8-30-11-3

To get the same formula to follow the same order of operation using the MINUS function:

=MINUS(MINUS(MINUS(MINUS(MINUS(100,15),8),30),11),3)

Not many people are going to understand that and even less will want to actually use it.

## How To Subtract Entire Columns In Google Sheets

Do you want to subtract the total of each column (one output) or the individual rows for an entire column (an output for each row)?

### Subtracting Entire Columns Using the SUM Function

Say you have a long list of sales and related costs:

 A B 1 Sales Costs 2 \$505 \$105 3 \$556 \$428 4 \$690 \$309 5 \$511 \$186 6 \$991 \$311 7 \$825 \$437 8 \$973 \$184 9 \$949 \$480 10 \$756 \$298

You would like to know the profit by subtracting the costs from the sales. Here's how:

First, find the sum of each column (SUM(A2:A10)and SUM(B2:B10)) and then subtract one from the other:

=SUM(A2:A10)-SUM(B2:B10)

You get \$4,018.

You could use the MINUS function too:

=MINUS(SUM(A2:A10)-SUM(B2:B10))

But as mentioned above, this is less flexible than the subtract operator.

The SUM function ignores text. This means you can reference the entire column instead of a range within it:

=SUM(A:A)-SUM(B:B)

This saves time highlighting a large range and will automatically include additional entries in the 'Sales' and 'Costs' columns.

### Subtracting Entire Columns Using Autofill

Google Sheets autofill is an amazing tool that allows you to quickly add formulas where you need them.

There are four ways to do it:

1. Ctrl+Enter

When you type in a formula next to a column of adjacent numbers, Google Sheets automatically assumes you want to apply the new formula to the rest of the column.

When this happens you can type Ctrl+Enter or click on the tick to autofill the formula to the bottom of the column:

2. Double click the fill handle

When you select a cell or range the selection will have what's known as a 'fill handle'. It's the little blue square in the bottom right corner of the selection:

When you have a formula in a cell, double clicking on the fill handle will copy the formula into the cells below whilever the data in the column to the left continues:

3. Drag the fill handle

Instead of double clicking the fill handle like above, you can click and drag it down as far as you'd like:

4. Keyboard shortcut

You can make a selection that includes the formula you want to autofill in the top cell and empty cells below it.

Then use the keyboard shortcut Ctrl+D (for Windows) or ⌘+D (for Mac) to fill the formula into the rest of the selection:

### Subtracting Entire Columns Using the ArrayFormula Function

It allows formulas and operators that normally don't accept arrays (ranges of data) to handle them.

In the context of subtracting entire columns: ArrayFormula means we can use one formula to output in every row instead of having a formula in every row.

This is fantastic because editing one formula is much easier than changing a column's worth.

Let's get to it with some sample data:

 A B C 1 Sales Costs Profit 2 \$505 \$105 3 \$556 \$428 4 \$690 \$309 5 \$511 \$186 6 \$991 \$311 7 \$825 \$437 8 \$973 \$184 9 \$949 \$480 10 \$756 \$298

We need a single formula in C2 that outputs the profit in every row below it.

Ordinarily we would use =A2-B2 or =MINUS(A2,B2) to get a single output in C2.

With ArrayFormula we can force the subtract operator or the MINUS function to accept arrays: =A2:A10-B2:B10 or =MINUS(A2:A10,B2:B10)

If you just type these formulas into C2 you get \$400 in that cell and nothing else.

That's because the ArrayFormula function isn't in there yet:

=ArrayFormula(A2:A10-B2:B10)
=ArrayFormula(MINUS(A2:A10,B2:B10))

Now you get an output for every row from a single formula (the green cell):

 A B C 1 Sales Costs Profit 2 \$505 \$105 \$400 3 \$556 \$428 \$128 4 \$690 \$309 \$381 5 \$511 \$186 \$325 6 \$991 \$311 \$680 7 \$825 \$437 \$388 8 \$973 \$184 \$789 9 \$949 \$480 \$469 10 \$756 \$298 \$458

Instead of typing out ArrayFormula everytime you want to use it, you can type Ctrl+Shift+Enter (for Windows) or ⌘+Shift+Enter (for Mac) to add it automatically:

If you have a column to which data is constantly added, you need a more flexible formula.

Otherwise, you'll have to rewrite =ArrayFormula(A2:A10-B2:B10) to something like =ArrayFormula(A2:A11-B2:B11) to accommodate new entries as they are made.

You can handle this by including range references that reach all the way to the bottom of the column no matter how many rows it contains:

=ArrayFormula(A2:A-B2:B)

By leaving the row number out of the second half of references you tell Google Sheets you want every row below the cell in the first half of the reference. In this case it's from A2 to the bottom of column A and B2 to the bottom of column B.

When you do this your output will be:

 A B C 1 Sales Costs Profit 2 \$505 \$105 \$400 3 \$556 \$428 \$128 4 \$690 \$309 \$381 5 \$511 \$186 \$325 6 \$991 \$311 \$680 7 \$825 \$437 \$388 8 \$973 \$184 \$789 9 \$949 \$480 \$469 10 \$756 \$298 \$458 11 \$0 12 \$0 13 \$0

Remember that blank cells are treated as 0 by these functions so when column A and column B are blank the formula is calculating 0-0 and outputting 0.

Any new entries will be calculated automatically in column C but it doesn't look great to have all those zeros at the bottom.

Let's fix that.

You need to add a logic function to your ArrayFormula so that the calculation is only made when data is present in the first two columns.

First let's just say what we want and then we'll translate it into a formula:

If column A and column B are blank leave the cell blank, otherwise complete a subtraction calculation and output the result.

You now know how to do the subtraction. For the other parts, there are dedicated IF and ISBLANK functions:

=IF(logical_expression, value_if_true, value_if_false)
=ISBLANK(value)

Combining these functions into one for our example looks like this:

=ArrayFormula(IF(ISBLANK(A2:A)*ISBLANK(B2:B),,A2:A-B2:B))

This is some advanced stuff so I'll break it down:

ISBLANK(A2:A) returns TRUE if the cell is blank or FALSE if the cell contains something.

We then multiply ISBLANK(A2:A) and ISBLANK(B2:B).

What? This isn't intuitive as multiplying TRUE and/or FALSE doesn't make sense.

The trick is that when math operators are applied to them, TRUE is converted to 1 and FALSE to 0.

This means that if either ISBLANK(A2:A) or ISBLANK(B2:B) are FALSE (or 0) the outcome of our multiplication will be 0 (which is considered FALSE within the IF function).

So:

• If both are empty (ISBLANK(A2:A)*ISBLANK(B2:B) = 1*1 = 1 = TRUE) the value_if_true is returned which in the formula is blank.
• If one or both have data (ISBLANK(A2:A)*ISBLANK(B2:B) = 0*1, 1*0, or 0*0 = 0 = FALSE) the value_if_false is returned which is the calculation A2:A-B2:B.

That more complex formula makes the data look nicer while still automatically updating as more entries come in:

 A B C 1 Sales Costs Profit 2 \$505 \$105 \$400 3 \$556 \$428 \$128 4 \$690 \$309 \$381 5 \$511 \$186 \$325 6 \$991 \$311 \$680 7 \$825 \$437 \$388 8 \$973 \$184 \$789 9 \$949 \$480 \$469 10 \$756 \$298 \$458 11 12 13

## How To Subtract Dates In Google Sheets

Dates in Google Sheets are just numbers that begin at Dec 30 1899.

Dates before this are negative numbers and dates after positive numbers:

 A B 1 Date Number 2 Dec 29 1899 -1 3 Dec 30 1899 0 4 Dec 31 1899 1 5 Jan 1 1900 2 6 ... ... 7 date number

This means dates are easy to work into subtraction formulas because they're really just numbers.

If you have two dates already in cells, like this:

 A 1 6/6/2000 2 1/1/2000

You can simply take one away from the other:

=A1-A2

To find the difference between the two dates is 157 days.

You could also use a formula to find out the date a number of days before a given date:

=A1-32

Which outputs 5/5/2000. So May 5 is 32 days before June 6.

If you need to create a date within a formula you can use the DATE function:

=DATE(year, month, day)

You can then replicate the above formulas without needing to reference an existing date value in a cell:

=DATE(2000,6,6)-DATE(2000,1,1)

And

=DATE(2000,6,6)-32

## How To Subtract Time In Google Sheets

In Google Sheets whole days (24-hour periods) are equal to 1 and hours, minutes, and seconds within those days are fractions of 1.

 A B 1 Time Number 2 12 hours 0.5 3 1 hour 0.041667 4 30 minutes 0.0208333 5 1 minute 0.00069444 6 30 seconds 0.000347222 7 1 second 0.0000115740740740

This means if you have two times already in cells, like this:

 A 1 31:41:59 2 27:18:28

You can simply take one away from the other:

=A1-A2

To find the difference between the two is 4:23:31.

If you instead need to create a time within a formula you can use the TIME function:

=TIME(hour, minute, second)

Now you can create the times from the cells above using =TIME(31,41,59) and =TIME(27,18,28).

Subtracting them within a formula:

=TIME(31,41,59)-TIME(27,18,28)

This formula outputs the correct answer but using an incorrect method.

The TIME function is built to only handle periods within 24 hours. When you provide it with more than 24 hours it cuts the first 24 hours out and ignores them.

If you provide it with this:

=TIME(47,41,59)-TIME(27,18,28)

It returns 00:23:31 and ignores that there's 20 hours between the times.

Use the TIME function only when dealing with times shorter than 24 hours.

If you need a formula to handle time periods longer than 24 hours you can instead use:

=QUOTIENT(hour,24)+TIMEVALUE(TIME(hour,minute,second))

This formula returns a number that Google Sheets can understand as days and a time.

You can then format the returned value as a time to get 20:23:31 for the above numbers using the above formula:

=(QUOTIENT(47,24)+TIMEVALUE(TIME(47,41,59)))-(QUOTIENT(27,24)+TIMEVALUE(TIME(27,18,28)))

## How To Subtract Percentages In Google Sheets

To subtract a percentage of a number from that number you need to use decimals (e.g. 1 = 100%, 0.5 = 50% etc.) to calculate the number minus the percentage of the number.

Say you want to take 30% off 10:

10 - 10*30%

As a formula that's:

=10-10*0.3

You could also figure out what percentage is going to remain and multiply that by the number.

So if you're subtracting 30% you want 100%-30% of the number.

This simplifies the formula to:

=10*(1-0.3)

Or even simpler:

=10*0.7

As always you can use cell references instead of actual numbers here and this is often preferred.

FREE RESOURCE

12 exclusive tips to make user-friendly sheets from today: