Formula to Subtract in Google Sheets: A Practical Guide
Learn a clear, step-by-step method to subtract numbers in Google Sheets using simple formulas, with real examples, edge cases, and best practices for precise results.
In Google Sheets, subtraction uses the minus operator between two values or references. The basic form is =A1-B1, returning the difference. You can subtract numbers, cell references, or function results. For more complex cases, combine with SUM to handle multiple values and use parentheses to control calculation order. This approach is reliable for budgets, date calculations, and variance analyses.
Subtraction basics in Google Sheets
Google Sheets handles subtraction with the minus operator. The essential pattern is simple: =operand1-operand2. Each operand can be a constant number, a cell reference, or a function result. This basic operation returns the difference, which is useful for calculating remaining budgets, differences between dates, or net values in a ledger. In practice, you’ll often start with two cells, such as A1 and B1, and copy the formula down a column to apply the same subtraction across rows.
=A1-B1This formula subtracts the value in B1 from A1. If A1 contains 100 and B1 contains 35, the result is 65. You can place dates in cells and subtract them; for example, =DATEVALUE("2026-02-15")-DATEVALUE("2026-01-15") yields 31 days. Google Sheets stores dates as serial numbers, so subtracting date values returns a numeric difference in days.
Key ideas:
- Use the minus operator to subtract two numbers or references
- Results are numbers representing the difference
- Dates subtract to yield days; format as number or date as needed
Steps
Estimated time: 15-25 minutes
- 1
Open your Google Sheet
Open a Google Sheet where you want to perform subtraction. Identify the two operands (numbers, dates, or cell references) you will subtract. Decide where the result should appear to keep your data organized.
Tip: Label your columns clearly so future you can understand what is being subtracted. - 2
Enter the subtraction formula
In the target cell, type a simple subtraction formula like =A1-B1. Press Enter to see the result. If you’re subtracting a constant, use a number on one side (e.g., =A1-50).
Tip: Use relative references if you plan to copy the formula down a list. - 3
Copy or fill the formula
Drag the fill handle or use Ctrl+D to apply the formula to adjacent rows. This keeps the same structure while adjusting relative references as needed.
Tip: If you don’t want a reference to shift, switch to absolute references like =$A$1-B1. - 4
Handle non-numeric data
If a cell contains text or an error, use IFERROR or VALUE to convert or gracefully handle the issue. This keeps reports clean when data quality varies.
Tip: Prefer data validation to catch non-numeric entries early. - 5
Combine with aggregation when needed
For larger datasets, you may compare totals instead of row-by-row differences, for example =SUM(A1:A10) - SUM(B1:B10).
Tip: Aggregation reduces noise and makes variance easier to spot.
Prerequisites
Required
- Required
- Basic arithmetic knowledge (addition, subtraction, order of operations)Required
- A worksheet with numeric data or dates to practice onRequired
Optional
- Optional: knowledge of relative vs absolute referencesOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaCopy the active cell or selection containing a subtraction formula | Ctrl+C |
| Paste formulaPaste the copied formula into a target cell | Ctrl+V |
| Fill down a columnAuto-fill the same formula to subsequent rows | Ctrl+D |
FAQ
How do I subtract two numbers in Google Sheets?
Use the minus operator between two values or cell references, e.g., =A1-B1. This returns the numerical difference. You can extend this with functions like SUM or unit-specific calculations as needed.
You subtract two numbers by placing a minus between them, like =A1-B1. It gives you the difference so you can see how much one value differs from another.
Can I subtract dates to get the number of days between them?
Yes. Dates are stored as serial numbers, so subtracting one date from another yields the number of days between them. Example: =DATE(2026,3,1) - DATE(2026,1,1) equals 60 days.
Subtracting dates gives you the day count between two dates.
What if I want to subtract a range from a single value?
Subtract a sum of a range from a value, e.g., =1000 - SUM(A1:A10). This is useful for budgets and totals.
You can subtract a total of a range from a fixed value by using SUM on the range.
How do absolute references affect subtraction formulas?
Absolute references (with $) lock a row or column when you copy formulas, e.g., =$A$1-B1. This keeps A1 fixed while B1 changes as you copy down.
Lock the parts you don’t want to move when you copy the formula.
What happens if a subtraction formula cell contains text?
If an operand is text, Sheets may return an error like #VALUE!. Use VALUE() to convert strings to numbers or IFERROR to handle errors gracefully.
If you have text in a subtraction, convert it to a number or handle the error in the formula.
Can I subtract values across different sheets?
Yes. Reference cells from other sheets, e.g., =Sheet2!A1 - Sheet1!A1. Ensure the sheet names are correct and you have access to both sheets.
You can subtract values from other sheets by referencing them directly in the formula.
The Essentials
- Master the minus operator for quick differences
- Use relative references for easy row-wise calculations
- Date subtraction yields days; format results appropriately
- Protect constants with absolute references when copying formulas