Google Sheets Cheat Sheet

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.

Click to learn about subtracting:

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
110
27
33

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:

shows a subtraction formula that uses cell references instead of hard-coded numbers updating as the inputs in the sheet update

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

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

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

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
110
2text
3#VALUE!
hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets 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:

=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

It's intuitive and easy-to-read.

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:

AB
1SalesCosts
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:

shows how google sheets intervenes when you write a formula next to a column of data assuming you want to apply that formula to the entire column which you can accept or reject

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:

close up screenshot of a selected cell highlighting the position of the fill handle in the bottom right hand corner

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:

shows how to double click the fill handle to autofill a formula to the rest of the rows in a column that are next to adjacent data-filled cells

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:

shows how to drag the fill handle to autofill a formula to however many rows 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:

shows the process of filling a formula into a selection using a keyboard shortcut that essentially copies and pastes a formula for the top cell in the selection down a column as far as you have selected

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:

ABC
1SalesCostsProfit
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):

ABC
1SalesCostsProfit
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:

shows how to automatically add an arrayformula function when you want to subtract and entire column from another

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:

ABC
1SalesCostsProfit
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:

ABC
1SalesCostsProfit
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:

AB
1DateNumber
2Dec 29 1899-1
3Dec 30 18990
4Dec 31 18991
5Jan 1 19002
6......
7datenumber

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
16/6/2000
21/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.

AB
1TimeNumber
212 hours0.5
31 hour0.041667
430 minutes0.0208333
51 minute0.00069444
630 seconds0.000347222
71 second0.0000115740740740

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

A
131:41:59
227: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.


hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets 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.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

🗙