Use the Google Sheets subtract formula to calculate the difference between numbers, dates, or values in any spreadsheet. This formula is essential for budgeting, project tracking, and data analysis.
With simple syntax and flexible design, the subtract formula integrates seamlessly with other functions. It makes real-time calculations fast and accurate.
| Operation | Syntax | Example | Result |
|---|---|---|---|
| Basic subtraction | =number1 - number2 | =10 - 4 | 6 |
| Cell reference subtraction | =A2 - B2 | =A2 - B2 | Difference between cell values |
| Subtract with DATE | =DATE(2024,12,31) - DATE(2024,1,1) | =DATE(2024,12,31) - DATE(2024,1,1) | 364 |
| Subtract array elements | =ARRAYFORMULA(C2:C5 - D2:D5) | =ARRAYFORMULA(C2:C5 - D2:D5) | Element-wise differences |
| Combine with SUM | =SUM(C2:C5 - D2:D5) | =SUM(C2:C5 - D2:D5) | Net total across ranges |
Basic Subtract Formula Syntax
Understand the basic structure of the subtract formula in Google Sheets. It relies on standard arithmetic operators rather than a dedicated function name.
Simple numerical subtraction
Use =100 - 25 to get 75 directly in a cell.
Cell reference subtraction
Write =A1 - B1 to subtract values dynamically, so updates to A1 or B1 refresh the result automatically.
Subtracting Dates and Times
Subtracting dates in Google Sheets returns the number of days between them. Time values work similarly and can be combined with dates.
Date difference for project duration
Use =B2 - A2 to find the days between start date in A2 and end date in B2.
Time difference in hours
Calculate =(C2 - B2) * 24 to get elapsed hours when cells contain time values.
Using Subtract in Complex Formulas
Combine subtraction with other functions to create powerful calculations for finance, analytics, and reporting.
Subtract and sum with SUM
Write =SUM(E2:E10 - F2:F10) to compute net differences across rows.
Nested subtraction with IF
Use =IF(G2 > H2, G2 - H2, 0) to subtract only when conditions are met.
Key Takeaways for Effective Subtraction Workflows
- Use =number1 - number2 for quick, static calculations.
- Leverage cell references like =A2 - B2 for dynamic updates.
- Apply =ARRAYFORMULA to subtract entire ranges efficiently.
- Combine subtract with SUM, IF, and DATE for advanced logic.
- Format results as percent or duration to match your reporting needs.
FAQ
Reader questions
Can I subtract an entire column from another column directly?
Use =ARRAYFORMULA(A2:A10 - B2:B10) to create a dynamic column of differences without dragging the formula.
What happens if I subtract text in Google Sheets?
You will get a #VALUE! error because text cannot be processed as numbers in arithmetic operations.
How do I subtract percentages in a cell?
Enter =1 - 0.25 to calculate remaining percentage after a 25 percent reduction, then format the cell as percent.
Can I subtract across different sheets in the same workbook?
Yes, use =Sheet1!A1 - Sheet2!B1 to reference cells on separate sheets inside the same file.