Google Sheets Formula Basics

Google Sheets formulas let you perform calculations, manipulate data, and automate tasks directly in your spreadsheet. You enter a formula by typing an equals sign (=) into a cell, followed by the calculation you want to perform, such as =A1+B1 or =SUM(A1:A10). The result appears in the cell, and the formula itself remains visible in the formula bar when the cell is selected.

How Formulas Work in Google Sheets

Every formula in Google Sheets begins with an equals sign. After the =, you can type a simple arithmetic expression, a cell reference, a function, or a combination of these. For example, =5*3 returns 15, and =A2*B2 multiplies the values in cells A2 and B2. Functions are predefined formulas like SUM, AVERAGE, and IF. You can combine functions with cell references and numbers: =SUM(A1:A10)/10 adds the values in A1 through A10 and then divides the total by 10.

Google Sheets recalculates formulas automatically whenever the referenced cells change. If you edit a value in A1, any formula that uses A1 updates instantly. This live recalculation is what makes spreadsheets powerful for tracking budgets, scores, inventories, and other dynamic data.

Cell References: Relative, Absolute, and Mixed

Cell references tell a formula which cells to use. There are three types:

• Relative references (like A1) adjust when you copy the formula to another cell. If you copy =A1+B1 from row 1 to row 2, it becomes =A2+B2.
• Absolute references (like $A$1) stay fixed no matter where you copy the formula. The dollar signs lock the column and row.
• Mixed references lock only one part, such as $A1 (column fixed, row relative) or A$1 (row fixed, column relative).

Use absolute references when you want a formula to always refer to a specific cell, such as a tax rate stored in B1. Without the dollar signs, copying the formula would shift the reference and produce wrong results.

Common Functions and How to Use Them

Google Sheets includes hundreds of functions, but a handful cover most everyday needs:

• SUM – adds numbers: =SUM(A1:A10) or =SUM(5, 10, 15)
• AVERAGE – returns the mean: =AVERAGE(B1:B5)
• MIN and MAX – find the smallest or largest value: =MIN(C1:C10)
• COUNT – counts numeric entries: =COUNT(D1:D10)
• IF – returns one value if a condition is true and another if false: =IF(A1>10, "High", "Low")
• VLOOKUP – searches for a value in the first column of a range and returns a value in the same row from another column: =VLOOKUP("Apple", A2:B10, 2, FALSE)

Functions are not case-sensitive; =sum(A1:A5) works the same as =SUM(A1:A5). You can nest functions, like =IF(SUM(A1:A5)>100, "Over", "Under"), but each function needs its own opening and closing parentheses.

How to Write and Edit Formulas

To enter a formula, click a cell and type =. As you type, Google Sheets suggests matching functions and shows a brief description. Press Enter to accept. To edit an existing formula, double-click the cell or select it and click in the formula bar. You can also use the Function button (the Σ symbol) in the toolbar to insert a function from a menu.

When you type a function name followed by an opening parenthesis, Sheets displays a tooltip showing the expected arguments. For example, =VLOOKUP( shows search_key, range, index, [is_sorted]. Fill in each argument separated by commas. If an argument is optional, it appears in square brackets.

To copy a formula down a column, select the cell with the formula, then drag the small blue square (fill handle) in the bottom-right corner of the cell downward. Relative references adjust automatically, which is perfect for applying the same calculation to multiple rows.

Common Mistakes and How to Fix Them

Several errors appear frequently when working with formulas:

• #DIV/0! – you tried to divide by zero or by an empty cell. Check the divisor and ensure it is not blank or zero.
• #N/A – a lookup function (like VLOOKUP) could not find the search key. Verify the key exists and that the range is correct.
• #NAME? – Sheets does not recognize the function name or a named range. Check spelling and ensure you did not omit the equals sign.
• #REF! – a reference is invalid, often because a column or row was deleted. Undo the deletion or adjust the reference.
• #VALUE! – a function expects a number but received text, or the wrong type of argument. Check that all referenced cells contain the expected data.

Another common mistake is mixing up commas and semicolons in function arguments. Depending on your spreadsheet locale, the separator may be a comma or a semicolon. If a function returns an error like #ERROR!, try swapping the separator.

Using Named Ranges for Clarity

Named ranges let you assign a name to a cell or range, making formulas easier to read and maintain. For example, instead of =SUM(B2:B100), you can name B2:B100 “Salesâ€� and write =SUM(Sales). To create a named range, select the cells, go to Data > Named ranges, enter a name, and click Done. Names cannot contain spaces or start with a number.

Named ranges are absolute by default, so they do not change when you copy a formula. They are especially useful in large spreadsheets where formulas like =VLOOKUP(A2, ProductData, 2, FALSE) are clearer than referencing a raw range like $A$1:$D$500.

Array Formulas and Advanced Techniques

Array formulas perform calculations on multiple values at once. For example, =ARRAYFORMULA(A1:A10*B1:B10) multiplies each pair of cells in the two columns and returns an array of results. This is useful when you want to apply a formula to an entire column without dragging the fill handle.

Google Sheets also supports functions like QUERY (which uses a SQL-like syntax), IMPORTRANGE (to pull data from another spreadsheet), and SPARKLINE (to create mini charts inside a cell). These advanced tools are powerful but have a steeper learning curve. Start with basic arithmetic and common functions, then explore these as your needs grow.

Frequently Asked Questions

Why is my formula showing as text instead of calculating?

If a formula appears as plain text, the cell is likely formatted as text. Select the cell, go to Format > Number > Automatic, then re-enter the formula by pressing F2 and Enter. Also check that the formula begins with an equals sign; otherwise Sheets treats it as text.

How do I reference a cell from another sheet?

Use the sheet name followed by an exclamation mark before the cell reference, like =Sheet2!A1. If the sheet name contains spaces, enclose it in single quotes: ='My Sheet'!A1.

What is the difference between a formula and a function?

A formula is any expression that starts with = and calculates a value, such as =A1+B1. A function is a predefined formula built into Sheets, like SUM or IF. Functions are used within formulas.

Can I use the same formula for an entire column without dragging?

Yes, use ARRAYFORMULA. For example, =ARRAYFORMULA(A1:A10*B1:B10) applies the multiplication to all rows at once. Alternatively, you can double-click the fill handle to auto-fill down a column if there is adjacent data.

Why does my VLOOKUP return #N/A even though the value exists?

Check that the lookup value is in the first column of the range and that the range is correct. If the column contains numbers stored as text, the match may fail. Use VALUE() or TEXT() to convert types, or set the last argument to FALSE for an exact match.

How do I keep a cell reference constant when copying a formula?

Add dollar signs to the reference: $A$1 locks both column and row. You can press F4 while editing a reference to cycle through relative, absolute, and mixed modes.

Related

• Drought Stress Effects on Aspen Growth, Leaves, and Survival
• Mr J Improper Fractions: Meaning & Math Help
• Kildare Farm Auction: E1.06m Sale Facts