Top 50 google sheet formulas which helps us in everyday life

Working with Google Sheets is all about using the right formulas to save time and gain insights from your data. Here are 50 of the most essential and powerful Google Sheets formulas, broken down by category, with a simple explanation for each.

Here are the formulas: 

Basic & Mathematical Formulas

These are the fundamental building blocks of any spreadsheet.

1. SUM: =SUM(range) - Adds up all the numbers in a range of cells.

2. AVERAGE: =AVERAGE(range) - Calculates the mean of the numbers in a range.

3. MAX: =MAX(range) - Finds the highest number in a range.

4. MIN: =MIN(range) - Finds the lowest number in a range.

5. COUNT: =COUNT(range) - Counts the number of cells that contain numerical values.

6. COUNTA: =COUNTA(range) - Counts the number of cells that are not empty.

7. COUNTIF: =COUNTIF(range, "criterion") - Counts cells within a range that meet a single condition.

8. SUMIF: =SUMIF(range, "criterion", [sum_range]) - Sums values in a range that meet a single condition.

9. IF: =IF(logical_expression, value_if_true, value_if_false) - Checks if a condition is true or false and returns a specified value.

10. ROUND: =ROUND(number, [places]) - Rounds a number to a specified number of decimal places.

Logical & Conditional Formulas

These formulas help you make decisions and perform complex checks on your data.

11. AND: =AND(condition1, condition2, ...) - Returns TRUE if all conditions are true.

12. OR: =OR(condition1, condition2, ...) - Returns TRUE if any of the conditions are true.

13. NOT: =NOT(logical_expression) - Reverses a logical value (TRUE becomes FALSE).

14. IFERROR: =IFERROR(value, [value_if_error]) - Returns a custom value if a formula results in an error.

15. COUNTIFS: =COUNTIFS(criteria_range1, criterion1, ...) - Counts cells that meet multiple criteria.

16. SUMIFS: =SUMIFS(sum_range, criteria_range1, criterion1, ...) - Sums values that meet multiple criteria.

Lookup & Reference Formulas

These are essential for finding and retrieving data from large datasets.

17. VLOOKUP: =VLOOKUP(search_key, range, index, [is_sorted]) - Searches for a value in the first column of a range and returns a value in the same row from a specified column.

18. HLOOKUP: =HLOOKUP(search_key, range, index, [is_sorted]) - The horizontal version of VLOOKUP.

19. XLOOKUP: =XLOOKUP(search_key, lookup_range, result_range) - The modern and more flexible alternative to VLOOKUP.

20. INDEX: =INDEX(reference, [row], [column]) - Returns the value of a cell at a specified row and column in a given range.

21. MATCH: =MATCH(search_key, range, [search_type]) - Returns the relative position of an item in a range.

22. FILTER: =FILTER(range, condition1, [condition2]) - Filters a range of data based on one or more conditions.

Text & String Formulas

These help you manipulate and clean up text data.

23. SPLIT: =SPLIT(text, delimiter) - Divides text into separate cells based on a specified character.

24. CONCATENATE: =CONCATENATE(string1, [string2, ...]) - Joins multiple text strings together.

25. JOIN: =JOIN(delimiter, value_or_array1, [value_or_array2, ...]) - Joins elements of an array into a single text string.

26. TRIM: =TRIM(text) - Removes leading, trailing, and repeated spaces from a text string.

27. LOWER: =LOWER(text) - Converts a string to all lowercase letters.

28. UPPER: =UPPER(text) - Converts a string to all uppercase letters.

29. PROPER: =PROPER(text) - Capitalizes the first letter of each word in a string.

30. SUBSTITUTE: =SUBSTITUTE(text, old_text, new_text, [occurrence]) - Replaces existing text with new text.

Date & Time Formulas

These formulas are used for working with dates and times.

31. TODAY: =TODAY() - Returns the current date.

32. NOW: =NOW() - Returns the current date and time.

33. DATEDIF: =DATEDIF(start_date, end_date, "unit") - Calculates the number of days, months, or years between two dates.

34. EOMONTH: =EOMONTH(start_date, months) - Returns the last day of the month a specified number of months before or after a start date.

35. YEAR: =YEAR(date) - Returns the year from a date.

36. MONTH: =MONTH(date) - Returns the month from a date.

37. DAY: =DAY(date) - Returns the day from a date.

Data & Array Formulas

These are powerful for working with entire ranges of data at once.

38. ARRAYFORMULA: =ARRAYFORMULA(array_formula) - Applies a formula to a range of cells, rather than just one.

39. QUERY: =QUERY(data, "query", [headers]) - The most powerful function in Sheets. It uses a Google Visualization API Query Language to perform complex queries on data.

40. IMPORTRANGE: =IMPORTRANGE(spreadsheet_url, range_string) - Imports a range of cells from another Google Sheet.

41. SORT: =SORT(range, sort_column, is_ascending) - Sorts a range of data by a specific column.

42. UNIQUE: =UNIQUE(range) - Returns only the unique rows from a specified range.

43. TRANSPOSE: =TRANSPOSE(array_or_range) - Swaps the rows and columns of a range.

44. FLATTEN: =FLATTEN(range1,[range2,...]) - Flattens all values from one or more ranges into a single column.

45. GOOGLEFINANCE: =GOOGLEFINANCE(ticker, [attribute]) - Fetches real-time financial data from Google Finance.

More Advanced Formulas

46. REGEXMATCH: =REGEXMATCH(text, regular_expression) - Checks if a string contains a match for a regular expression.

47. REGEXEXTRACT: =REGEXEXTRACT(text, regular_expression) - Extracts a matching substring from a text string using a regular expression.

48. SUMPRODUCT: =SUMPRODUCT(array1, [array2, ...]) - Calculates the sum of the products of corresponding entries in two equal-sized arrays.

49. INDIRECT: =INDIRECT(cell_reference_as_text) - Returns the value of a cell from a given text string.

50. SPARKLINE: =SPARKLINE(data, [options]) - Creates a small chart within a single cell.

More Blogs: https://withsrg.blogspot.com/