Examples with Google Sheets Formulas
Google Sheets formulas are the backbone of data calculation and analysis. They help you perform arithmetic, text manipulation, date-time operations, and data validation efficiently. Learning formulas not only saves time but also reduces errors and makes your spreadsheets more dynamic and insightful.
From basic calculations to advanced text handling and data cleaning, this guide covers the most important Google Sheets formulas along with practical examples and troubleshooting tips.
Basic Calculations & References
Google Sheets Cell Reference
Google Sheets cell reference is used to point to a specific cell or range in formulas. For example:
=A1+B1
adds the values from cells A1 and B1. You can also use absolute references with $ like $A$1 to keep a cell fixed when copying formulas. Proper referencing is crucial for accurate calculations and data consistency.
Multiplying Data in Google Sheets
To multiply in Google Sheets, use the * operator or PRODUCT() function. Example:
=A2*B2
or
=PRODUCT(A2:A5)
This multiplies individual values or a range of cells. Multiplication formulas are widely used in financial calculations, quantity-based totals, or revenue analysis.
Google Sheets Average Formula
The average formula in Google Sheets calculates the mean of a set of numbers. For example:
=AVERAGE(B2:B10)
This returns the average of values in B2 to B10. It’s useful for analyzing test scores, sales, or any dataset requiring central tendency evaluation.
Percentage Formula in Google Sheets
To calculate percentages, use formulas like:
=A2/B2*100
or
=PERCENTAGE(A2/B2)
Percentage formula in Google Sheets helps in profit calculation, discount analysis, and performance evaluation. Ensure proper formatting to display results correctly.
Rounding, Date & Time Formulas
Rounding in Google Sheets
Use rounding in Google Sheets to simplify numeric values. Examples include:
=ROUND(A2,2) – rounds to 2 decimal places
=ROUNDUP(A2,0) – rounds up to nearest whole number
Rounding improves readability and prepares data for reporting or further calculation.
Google Sheets Date Formula
Date formula in Google Sheets helps manipulate or calculate dates. Example:
=DATE(2025,10,28)
or
=TODAY() – returns current date. You can also calculate differences using DATEDIF(start_date,end_date,”D”).
Google Sheets Time Formula
Time formula in Google Sheets manages hours, minutes, and seconds. Example:
=TIME(10,30,0) – represents 10:30 AM
=NOW() – returns current date and time. Time formulas are useful for scheduling, tracking hours, or calculating durations.
Text & String Manipulation
Change Case Google Sheets
Use change case in Google Sheets to standardize text formatting. Examples:
=UPPER(A2) – converts to uppercase
=LOWER(A2) – converts to lowercase
=PROPER(A2) – capitalizes first letters. This is helpful when cleaning names, titles, or imported text data.
Google Sheets Extract from String
Extract from string in Google Sheets allows you to retrieve specific text portions. For example:
=LEFT(A2,5) – first 5 characters
=RIGHT(A2,3) – last 3 characters
You can also use MID() to extract from the middle of a string.
Remove Characters in Google Sheets
Removing characters in Google Sheets helps clean data. Example:
=SUBSTITUTE(A2,"-","")
This formula removes all hyphens from a cell. Removing unwanted characters improves consistency for calculations and analysis.
Split Cells in Google Sheets
Splitting cells in Google Sheets separates data using a delimiter. Example:
=SPLIT(A2,",")
This divides a comma-separated list into separate cells. Useful for parsing imported data like names, addresses, or lists.
Merge Cells in Google Sheets
You can merge cells in Google Sheets to combine multiple cells into one larger cell, often used for headers or formatted tables. To do this manually, go to Format > Merge cells, and choose from options like Merge all, Merge horizontally, or Merge vertically.
If you want to achieve the same effect using a formula, try using the TEXTJOIN or CONCATENATE function to merge cell contents without physically combining cells. For example:
=TEXTJOIN(" ", TRUE, A2, B2, C2)
This formula merges the contents of cells A2, B2, and C2 into one, separated by spaces. Formula-based merging is dynamic and avoids formatting issues caused by manual merging.
Duplication, Comparison & Randomization
Find Duplicates in Google Sheets
Finding duplicates in Google Sheets means identifying repeated values. Example:
=IF(COUNTIF(A2:A20,A2)>1,"Duplicate","")
This formula finds duplicates by returning the text Duplicate for a column.
Remove Duplicates Google Sheets
You can remove duplicates in Google Sheets using formulas when you need a dynamic and automatic solution. The most common way is by using the UNIQUE function. This formula instantly filters out repeated entries from your dataset and displays only unique values. For example, if you have a list of names in cells A2:A20, enter the following formula in another cell:
=UNIQUE(A2:A20)
This will return a clean list without duplicates. Unlike the manual Remove Duplicates tool, the UNIQUE function updates automatically whenever new data is added. You can also combine it with other functions like SORT or FILTER for advanced cleanup.
Compare Data in Google Sheets
To compare data in Google Sheets, formulas are the most efficient option. You can use the IF() function to check whether two cells contain the same value. For example:
=IF(A2=B2, "Match", "No Match")
This formula compares values in columns A and B row by row and returns “Match” if they are the same.
For larger datasets, you can also use conditional formatting to highlight differences automatically. Go to Format > Conditional formatting, and set a custom formula like =A2<>B2. This helps you instantly visualize mismatched data across your spreadsheet.
Randomize in Google Sheets
Randomization of data in Google Sheets generates random numbers or shuffles data. Example:
=RAND() – returns a number between 0 and 1
=RANDBETWEEN(1,100) – returns a random integer. Useful for simulations, sampling, or test data generation.
Google Sheets Currency Conversion
Currency conversion in Google Sheets allows you to convert amounts between currencies using exchange rates. Example:
=A2*GOOGLEFINANCE("CURRENCY:USDGBP")
This multiplies USD in A2 by the USD to GBP exchange rate. Currency formulas are ideal for international finance or sales reports.
Google Sheets Formula Issues & Fixes
Working with formulas in Google Sheets can sometimes lead to errors or confusion. From incorrect ranges to syntax mistakes, even a small error can cause major calculation problems. The key to resolving these issues is understanding how Google Sheets processes data and references. Common fixes include checking cell references, ensuring consistent data formats, and using the correct formula syntax.
For instance, if you see a #REF! error, it usually means a cell reference has been deleted or moved. Similarly, the #VALUE! error occurs when Google Sheets expects a number but finds text instead. Learning how to troubleshoot such issues with formulas helps maintain clean, error-free spreadsheets.