How To Subtract In Google Sheets

Kieran Dixon Updated:  June 11, 2021

You use a formula to subtract in Google Sheets.

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

Click to learn about subtracting:

• Using operators (my preferred choice)
• Using a function in a formula
• Entire columns
• Dates
• Times
• Percentages

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:

Entering this formula in a cell will output 3.

It works with decimals and can output negative numbers:

Which outputs -83.51.

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

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:

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

FREE RESOURCE

Google Sheets Cheat Sheet

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

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

How To Subtract Using Functions In Google Sheets

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

• value1 = starting number.
• value2 = number to take away from value1.

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:

It's intuitive and easy-to-read.

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

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:

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:

You get $4,018.

You could use the MINUS function too:

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

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

ArrayFormula is an advanced Google Sheets 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:

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:

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

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:

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:

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:

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

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:

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:

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:

You can simply take one away from the other:

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:

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:

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

And

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.

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

You can simply take one away from the other:

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:

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:

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

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:

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:

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:

Or even simpler:

FREE RESOURCE

Google Sheets Cheat Sheet

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

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

MAX Google Sheets Function [With Quiz]

The MAX function in Google Sheets finds the maximum! It doesn't matter how many numbers, MAX will quickly find the largest / most positive.

How To Round In Google Sheets

Need to round up and down in Google Sheets? Find out how to round using both formulas and formatting on both desktop and mobile.

LOWER Google Sheets Function [With Quiz]

The LOWER function in Google Sheets makes all text characters in a cell, column, or row lowercase. Learn how to make this happen here.