How to Use Formulas in Google Sheets

Google Sheets is far more than a simple spreadsheet tool. Its real power comes from formulas, which allow you to perform calculations, analyze data, automate tasks, and make smart decisions—all in real time.

Whether you’re a beginner learning the basics or an intermediate user wanting to improve productivity, this guide will walk you through how to use formulas in Google Sheets step by step, with clear examples and practical use cases.

What Are Formulas in Google Sheets?

A formula is an expression that performs calculations or actions using values in cells. Every formula in Google Sheets starts with an equals sign (=).

Example:

=A1 + B1

This formula adds the values in cells A1 and B1.

Why Use Formulas in Google Sheets?

Formulas help you:

  • Perform automatic calculations

  • Save time and reduce errors

  • Analyze large datasets

  • Create dynamic reports

  • Update results instantly when data changes

Once you learn formulas, Google Sheets becomes a powerful data tool instead of just a table.

Basic Formula Structure

A formula usually includes:

  • = (equals sign)

  • Cell references (A1, B2, etc.)

  • Operators (+, −, *, /)

  • Functions (SUM, AVERAGE, IF)

Example:

=SUM(A1:A10)

How to Enter a Formula in Google Sheets

  1. Click a cell where you want the result

  2. Type =

  3. Enter your formula

  4. Press Enter

Google Sheets will calculate and display the result instantly.

Basic Arithmetic Formulas

Addition

=A1 + B1

Subtraction

=A1 - B1

Multiplication

=A1 * B1

Division

=A1 / B1

Most Common Google Sheets Functions

SUM – Add Numbers

=SUM(A1:A10)

Adds all numbers from A1 to A10.

AVERAGE – Find the Mean

=AVERAGE(B1:B10)

Calculates the average value.

MIN and MAX

=MIN(C1:C10)
=MAX(C1:C10)

Finds the smallest and largest values.

COUNT – Count Numbers

=COUNT(A1:A10)

Counts only numeric values.

COUNTA – Count Non-Empty Cells

=COUNTA(A1:A10)

Counts all non-empty cells (text + numbers).

Using Cell References Correctly

Relative References

=A1+B1

Changes automatically when copied.

Absolute References

=$A$1+$B$1

Does not change when copied.

Mixed References

=$A1
=A$1

Used in advanced calculations.

How to Copy Formulas

  1. Click the cell with the formula

  2. Drag the fill handle (small blue square)

  3. Release to copy

Google Sheets adjusts references automatically.

Logical Formulas (IF Statements)

IF Function

=IF(A1>50,"Pass","Fail")

Returns “Pass” if value is greater than 50.

Nested IF

=IF(A1>=90,"A",IF(A1>=75,"B","C"))

Useful for grading systems.

Text Formulas in Google Sheets

CONCATENATE / CONCAT

=CONCAT(A1," ",B1)

Joins text from multiple cells.

TEXTJOIN

=TEXTJOIN(" ",TRUE,A1:C1)

Joins multiple cells with a separator.

UPPER, LOWER, PROPER

=UPPER(A1)
=LOWER(A1)
=PROPER(A1)

Formats text capitalization.

Date and Time Formulas

TODAY

=TODAY()

Displays today’s date.

NOW

=NOW()

Displays current date and time.

DATEDIF

=DATEDIF(A1,B1,"D")

Calculates days between dates.

Lookup and Reference Formulas

VLOOKUP

=VLOOKUP(A2,A:B,2,FALSE)

Finds matching data vertically.

HLOOKUP

=HLOOKUP(A1,A1:D5,2,FALSE)

Searches horizontally.

XLOOKUP (Modern & Recommended)

=XLOOKUP(A2,A:A,B:B)

More flexible than VLOOKUP.

Filtering Data Using Formulas

FILTER Function

=FILTER(A2:C10,B2:B10="IT")

Displays rows that match criteria.

UNIQUE

=UNIQUE(A2:A20)

Removes duplicate values.

SORT

=SORT(A2:B20,2,TRUE)

Sorts data dynamically.

Error Handling in Formulas

IFERROR

=IFERROR(A1/B1,"Error")

Prevents error messages from displaying.

Common Formula Errors and Fixes

Error Meaning Fix
#DIV/0! Division by zero Check denominator
#N/A Value not found Verify lookup
#VALUE! Wrong data type Check cell format
#REF! Invalid reference Restore deleted cells

Using Functions Menu (No Typing Needed)

  1. Click a cell

  2. Click Insert → Function

  3. Choose category

  4. Select function

Perfect for beginners.

Best Practices for Using Formulas

  • Use clear column headers

  • Avoid hard-coding values

  • Use absolute references when needed

  • Break complex formulas into steps

  • Test formulas with sample data

Formulas vs Functions: What’s the Difference?

  • Formula: Any calculation starting with =

  • Function: Pre-built formula like SUM or IF

All functions are formulas, but not all formulas are functions.

Who Should Learn Google Sheets Formulas?

Formulas are essential for:

  • Bloggers

  • Students

  • Accountants

  • Marketers

  • Business owners

  • Data analysts

  • Freelancers

Frequently Asked Questions (FAQs)

Do formulas update automatically?

Yes, results update instantly when data changes.

Can I use formulas on mobile?

Yes, but advanced editing is easier on desktop.

Are Google Sheets formulas same as Excel?

Mostly yes, but some functions differ.

Advantages of Using Formulas in Google Sheets

  • Real-time calculations

  • Cloud-based collaboration

  • Powerful built-in functions

  • Free and accessible

  • Ideal for automation

Final Thoughts

Learning how to use formulas in Google Sheets is a game-changer. From simple calculations to advanced data analysis, formulas save time, reduce errors, and make your spreadsheets dynamic and powerful.

Start with basic formulas, practice regularly, and slowly move toward advanced functions like IF, VLOOKUP, FILTER, and XLOOKUP. Once mastered, Google Sheets becomes an essential productivity tool you’ll rely on daily.

Latest Posts

Leave a Reply

Your email address will not be published. Required fields are marked *