Introduction:
Google Sheets is a powerful tool for managing and analyzing data, and one of its key features is the ability to use formulas. Formulas allow you to perform calculations, manipulate text, and make logical decisions within your spreadsheets. Whether you are a beginner or an experienced user, having a solid understanding of essential Google Sheets formulas is crucial for maximizing your productivity and efficiency.
In this article, we will provide you the most essential Google Sheets formulas list from beginner to pro in a single pdf. We will cover a wide range of topics, including basic mathematical calculations, conditional formatting, data manipulation, and more. Each formula will be explained in detail, along with examples of how they can be used in real-world scenarios.
We understand that learning new formulas can be overwhelming at first, so we have also included a downloadable PDF guide that summarizes all the formulas covered in this article. This guide will serve as a handy reference tool that you can keep by your side as you navigate through your Google Sheets journey.
Whether you are just starting with Google Sheets or looking to enhance your skills, this article and accompanying PDF guide will provide you with the knowledge and resources needed to become proficient in using formulas within Google Sheets.
Excel All Formulas PDF
A. Basic Math Formulas
1- SUM: Calculates the sum of a range of cells. Formula Syntax: =SUM(range)
Example: Suppose you have a column containing the sales figures for different products (A2:A10), and you want to calculate the total sales. Example Usage: =SUM(A2:A10)
2- AVERAGE: Description: Calculates the average value of a range of cells. Formula Syntax: =AVERAGE(range)
Example: Let’s say you have a column containing the test scores of students (A2:A10), and you want to find the average score. Example Usage: =AVERAGE(A2:A10)
3- COUNT: Description: Counts the number of cells in a range that contain numbers. Formula Syntax: =COUNT(range)
Example: Suppose you have a column containing the number of units sold for different products (A2:A10), and you want to count how many products were sold. Example Usage: =COUNT(A2:A10)
4- MAX: Returns the maximum value from a range of cells. Formula Syntax: =MAX(range)
Example: Let’s say you have a column containing the heights of students (A2:A10), and you want to find the tallest student’s height. Example Usage: =MAX(A2:A10)
5- MIN: Returns the minimum value from a range of cells. Formula Syntax: =MIN(range)
Example: Suppose you have a column containing the temperatures recorded throughout the day (A2:A10), and you want to find the lowest temperature. Example Usage: =MIN(A2:A10)
6- MEDIAN: Calculates the median value from a range of cells. Formula Syntax: =MEDIAN(range)
Example: Let’s say you have a column containing the ages of people (A2:A10), and you want to find the median age. Example Usage: =MEDIAN(A2:A10)
7- ROUND: Rounds a number to a specified number of digits. Formula Syntax: =ROUND(number, num_digits) Example: Suppose you have a cell (A1) containing the value 3.14159, and you want to round it to two decimal places. Example Usage: =ROUND(A1, 2)
8- PRODUCT: Calculates the product of values in a range of cells. Formula Syntax: =PRODUCT(range)
Example: Let’s say you have a column containing the quantities of different products (A2:A10), and you want to calculate the total quantity. Example Usage: =PRODUCT(A2:A10)
9- POWER: Raises a number to a specified power. Formula Syntax: =POWER(number, power)
Example: Suppose you have a cell (A1) containing the value 2, and you want to calculate 2 raised to the power of 3. Example Usage: =POWER(A1, 3)
10- SQRT: Returns the square root of a number. Formula Syntax: =SQRT(number)
Example: Let’s say you have a cell (A1) containing the value 25, and you want to find its square root. Example Usage: =SQRT(A1)
Free Templates
B. Statistical Formulas
1- STDEV: Calculates the standard deviation based on a sample from a range of cells. Formula Syntax: =STDEV(range)
Example: Suppose you have a column containing the test scores of students (A2:A10), and you want to find the standard deviation of the scores. Example Usage: =STDEV(A2:A10)
2- VAR: Calculates the variance based on a sample from a range of cells. Formula Syntax: =VAR(range)
Example: Let’s say you have a column containing the heights of students (A2:A10), and you want to find the variance of their heights. Example Usage: =VAR(A2:A10)
3- CORREL: Calculates the correlation coefficient between two ranges of cells. Formula Syntax: =CORREL(range1, range2)
Example: Suppose you have two columns containing the test scores of two different subjects (A2:A10 and B2:B10), and you want to find the correlation between the scores in both subjects. Example Usage: =CORREL(A2:A10, B2:B10)
4- COUNTIF: Counts the number of cells in a range that meet specific criteria. Formula Syntax: =COUNTIF(range, criteria)
Example: Let’s say you have a column containing the grades of students (A2:A10), and you want to count how many students received an “A” grade. Example Usage: =COUNTIF(A2:A10, “A”)
5- AVERAGEIF: Calculates the average of cells in a range that meet specific criteria. Formula Syntax: =AVERAGEIF(range, criteria, [average_range])
All Shortcuts PDF
Example: Suppose you have two columns – one containing test scores (A2:A10) and another containing subject names (B2:B10), and you want to calculate the average score for a specific subject. Example Usage: =AVERAGEIF(B2:B10, “Math”, A2:A10)
6- MAXIFS: Returns the maximum value from a range based on multiple criteria. Formula Syntax: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2], [criteria2]…)
Example: Let’s say you have three columns – one containing product names (A2:A10), one containing sales figures (B2:B10), and another containing the region (C2:C10). You want to find the maximum sales figure for a specific product in a specific region. Example Usage: =MAXIFS(B2:B10, A2:A10, “Product X”, C2:C10, “Region A”)
7- MINIFS: Returns the minimum value from a range based on multiple criteria. Formula Syntax: =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2], [criteria2]…)
Example: Suppose you have three columns – one containing product names (A2:A10), one containing sales figures (B2:B10), and another containing the region (C2:C10). You want to find the minimum sales figure for a specific product in a specific region. Example Usage: =MINIFS(B2:B10, A2:A10, “Product Y”, C2:C10, “Region B”)
8- PERCENTILE: Calculates the value at a specified percentile in a range of cells. Formula Syntax: =PERCENTILE(range, percentile)
Example: Let’s say you have a column containing the test scores of students (A2:A10), and you want to find the score at the 75th percentile. Example Usage: =PERCENTILE(A2:A10, 75)
9- RANK: Returns the rank of a number within a range of cells. Formula Syntax: =RANK(number, range, [order])
Example: Let’s say you have a list of sales figures in cells A2:A10, and you want to rank each sales figure within that range. You can use the RANK function for this purpose. Example Usage: =RANK(A2, $A$2:$A$10, 0)
Free Cheat sheets
C. Logical Formulas
1- IF: Evaluates a condition and returns a value based on the result. Formula Syntax: =IF(logical_test, value_if_true, value_if_false)
Example: Suppose you have a column containing the test scores of students (A2:A10), and you want to assign a grade based on the score. If the score is greater than or equal to 70, assign “Pass”, otherwise assign “Fail”. Example Usage: =IF(A2>=70, “Pass”, “Fail”)
2- AND: Returns TRUE if all the arguments are TRUE, and FALSE otherwise. Formula Syntax: =AND(logical1, logical2, …)
Example: Let’s say you have two columns – one containing the test scores of students (A2:A10) and another containing their attendance status (B2:B10). You want to check if both the score is greater than or equal to 70 and the attendance is “Present”. Example Usage: =AND(A2>=70, B2=”Present”)
3- OR: Returns TRUE if any of the arguments are TRUE, and FALSE otherwise. Formula Syntax: =OR(logical1, logical2, …)
Example: Suppose you have two columns – one containing the test scores of students (A2:A10) and another containing their attendance status (B2:B10). You want to check if either the score is greater than or equal to 70 or the attendance is “Present”. Example Usage: =OR(A2>=70, B2=”Present”)
4- NOT: Reverses the logical value of an argument. Formula Syntax: =NOT(logical)
Example: Let’s say you have a column containing the test scores of students (A2:A10), and you want to check if the score is not equal to 50. Example Usage: =NOT(A2=50)
5- IFERROR: Returns a value if there is an error in the formula, otherwise returns the result of the formula. Formula Syntax: =IFERROR(value, value_if_error)
Example: Suppose you have a column containing the division results of students (A2:A10), and you want to display “Pass” if the result is not an error, otherwise display “Fail”. Example Usage: =IFERROR(A2, “Fail”)
6- TRUE: Returns the logical value TRUE. Formula Syntax: =TRUE()
Example: Let’s say you have a column containing the test scores of students (A2:A10), and you want to check if the score is greater than or equal to 70. Example Usage: =A2>=70
7- FALSE: Returns the logical value FALSE. Formula Syntax: =FALSE()
Example: Suppose you have a column containing the test scores of students (A2:A10), and you want to check if the score is less than 60. Example Usage: =A2<60
8- IFNA: Returns a value if there is a #N/A error in the formula, otherwise returns the result of the formula. Formula Syntax: =IFNA(value, value_if_na)
Example: Let’s say you have a column containing student names (A2:A10), and you want to display their test scores. If a score is not available (#N/A error), display “Not Available”. Example Usage: =IFNA(VLOOKUP(A2, B2:C10, 2, FALSE), “Not Available”)
9- XOR: Returns TRUE if an odd number of arguments are TRUE, and FALSE otherwise. Formula Syntax: =XOR(logical1, logical2, …)
Example: Suppose you have two columns – one containing the test scores of students (A2:A10) and another containing their attendance status (B2:B10). You want to identify students who either scored above 80 or had perfect attendance. You can use the XOR function to achieve this. Example Formula: = XOR(A2 > 80, B2 = “Present”)
Computer Shortcut PDF
D. Text Formulas
1-CONCATENATE: Combines two or more text strings into one. Formula Syntax: =CONCATENATE(text1, text2, …)
Example: Suppose you have two columns – one containing first names (A2:A10) and another containing last names (B2:B10). You want to combine them to create a column of full names. Example Usage: =CONCATENATE(A2, ” “, B2)
2- LEFT: Returns the leftmost characters from a text string. Formula Syntax: =LEFT(text, [num_chars])
Example: Let’s say you have a column containing email addresses (A2:A10), and you want to extract the username part from each address. Example Usage: =LEFT(A2, FIND(“@”, A2)-1)
3- RIGHT: Returns the rightmost characters from a text string. Formula Syntax: =RIGHT(text, [num_chars])
Example: Suppose you have a column containing phone numbers (A2:A10), and you want to extract the last four digits from each number. Example Usage: =RIGHT(A2, 4)
4- MID: Returns a specific number of characters from a text string, starting at a specified position. Formula Syntax: =MID(text, start_num, [num_chars])
Example: Let’s say you have a column containing product codes (A2:A10), and you want to extract the middle three characters from each code. Example Usage: =MID(A2, 2, 3)
5- LEN: Returns the number of characters in a text string. Formula Syntax: =LEN(text)
Example: Suppose you have a column containing product descriptions (A2:A10), and you want to find the length of each description. Example Usage: =LEN(A2)
6- FIND: Returns the starting position of one text string within another text string. Formula Syntax: =FIND(find_text, within_text, [start_num])
Example: Let’s say you have a column containing URLs (A2:A10), and you want to find the position of the first occurrence of “.com” in each URL. Example Usage: =FIND(“.com”, A2)
7- SUBSTITUTE: Replaces specific text in a text string with new text. Formula Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Example: Suppose you have a column containing sentences (A2:A10), and you want to replace all occurrences of the word “apple” with “orange”. Example Usage: =SUBSTITUTE(A2, “apple”, “orange”)
8- UPPER: Converts all lowercase letters in a text string to uppercase. Formula Syntax: =UPPER(text)
Example: Let’s say you have a column containing names (A2:A10), and you want to convert them to uppercase. Example Usage: =UPPER(A2)
9- LOWER: Converts all uppercase letters in a text string to lowercase. Formula Syntax: =LOWER(text) Example: Suppose you have a column containing sentences (A2:A10), and you want to convert them to lowercase. Example Usage: =LOWER(A2)
10- PROPER: Capitalizes the first letter of each word in a text string. Formula Syntax: =PROPER(text)
Example: Let’s say you have a column containing names (A2:A10), and you want to capitalize the first letter of each name. Example Usage: =PROPER(A2)
Free Resume Templates
E. Date and Time Formulas
1- TODAY: Returns the current date. Formula Syntax: =TODAY()
Example: Suppose you have a column containing order dates (A2:A10), and you want to calculate the number of days since each order was placed. Example Usage: =TODAY() – A2
2- NOW: Returns the current date and time. Formula Syntax: =NOW()
Example: Let’s say you have a column containing timestamps (A2:A10), and you want to calculate the time elapsed since each timestamp. Example Usage: =NOW() – A2
3- DAY: Returns the day of the month from a given date. Formula Syntax: =DAY(date)
Example: Suppose you have a column containing dates (A2:A10), and you want to extract the day from each date. Example Usage: =DAY(A2)
4- MONTH: Returns the month from a given date. Formula Syntax: =MONTH(date)
Example: Let’s say you have a column containing dates (A2:A10), and you want to extract the month from each date. Example Usage: =MONTH(A2)
5- YEAR: Returns the year from a given date. Formula Syntax: =YEAR(date)
Example: Suppose you have a column containing dates (A2:A10), and you want to extract the year from each date. Example Usage: =YEAR(A2)
Excel All Formulas PDF
6- HOUR: Returns the hour from a given time. Formula Syntax: =HOUR(time)
Example: Let’s say you have a column containing timestamps (A2:A10), and you want to extract the hour from each timestamp. Example Usage: =HOUR(A2)
7- MINUTE: Returns the minute from a given time. Formula Syntax: =MINUTE(time)
Example: Suppose you have a column containing timestamps (A2:A10), and you want to extract the minute from each timestamp. Example Usage: =MINUTE(A2)
8- SECOND: Returns the second from a given time. Formula Syntax: =SECOND(time)
Example: Let’s say you have a column containing timestamps (A2:A10), and you want to extract the second from each timestamp. Example Usage: =SECOND(A2)
9- DATE: Combines separate day, month, and year values into a single date. Formula Syntax: =DATE(year, month, day)
Example: Suppose you have three columns – one containing the day values (A2:A10), another containing the month values (B2:B10), and the third containing the year values (C2:C10). You want to combine them to create a column of dates. Example Usage: =DATE(C2, B2, A2)
10- TIME: Combines separate hour, minute, and second values into a single time. Formula Syntax: =TIME(hour, minute, second)
Example: Let’s say you have three columns – one containing the hour values (A2:A10), another containing the minute values (B2:B10), and the third containing the second values (C2:C10). You want to combine them to create a column of times. Example Usage: =TIME(A2, B2, C2)
Download 30+ Proforma Invoice Templates For Billing in Excel
Free Invoice Templates
F. Financial Formulas
1- PV: Calculates the present value of an investment or loan. Formula Syntax: =PV(rate, nper, pmt, [fv], [type])
Example: Suppose you want to calculate the present value of an investment that will pay ₹1,000 per year for 5 years with an interest rate of 5%. Example Usage: =PV(5%, 5, -1000)
2- FV: Calculates the future value of an investment or loan. Formula Syntax: =FV(rate, nper, pmt, [pv], [type])
Example: Let’s say you want to calculate the future value of an investment that you deposit ₹500 every month for 10 years with an interest rate of 8%. Example Usage: =FV(8%, 10*12, -500)
3- PMT: Calculates the periodic payment for a loan or investment. Formula Syntax: =PMT(rate, nper, pv, [fv], [type])
Example: Suppose you want to calculate the monthly payment for a car loan of ₹20,000 with an interest rate of 6% over a period of 5 years. Example Usage: =PMT(6%/12, 5*12, -20000)
4- RATE: Calculates the interest rate per period for a loan or investment. Formula Syntax: =RATE(nper, pmt, pv, [fv], [type], [guess])
Example: Let’s say you want to calculate the interest rate for a mortgage loan with monthly payments of ₹1,500 for 30 years and a principal amount of ₹250,000. Example Usage: =RATE(30*12, -1500, 250000)
5- NPV: Calculates the net present value of an investment using a discount rate and a series of cash flows. Formula Syntax: =NPV(rate, value1, value2, …)
Example: Suppose you want to calculate the net present value of an investment with cash flows of ₹10,000, ₹20,000, and ₹30,000 over 3 years with a discount rate of 8%. Example Usage: =NPV(8%, -10000, -20000, -30000)
6- IRR: Calculates the internal rate of return for a series of cash flows. Formula Syntax: =IRR(values, [guess])
Example: Let’s say you want to calculate the internal rate of return for an investment with cash flows of -₹1000, ₹500, ₹3000, and ₹2000 over 4 years. Example Usage: =IRR(-1000, 500, 3000, 2000)
7- NPER: Calculates the number of periods required to pay off a loan or reach an investment goal. Formula Syntax: =NPER(rate, pmt, pv, [fv], [type])
Example: Suppose you want to calculate the number of months required to pay off a credit card debt of ₹5000 with monthly payments of ₹200 and an interest rate of 15%. Example Usage: =NPER(15%/12, -200, 5000)
8- CUMIPMT: Calculates the cumulative interest paid between two periods on a loan or investment. Formula Syntax: =CUMIPMT(rate, nper, pv, start_period, end_period, [type])
Example: Let’s say you want to calculate the cumulative interest paid on a car loan with monthly payments of ₹400 over 5 years at an interest rate of 6% between the 4th and 12th month. Example Usage: =CUMIPMT(6%/12, 5*12, -20000, 4
Free Templates
G. Mathematical Formulas
1- ABS: Returns the absolute value of a number. Formula Syntax: =ABS(number) Example: Suppose you have a column containing both positive and negative numbers (A2:A10), and you want to find the absolute values of these numbers. Example Usage: =ABS(A2)
2- SQRT: Returns the square root of a number. Formula Syntax: =SQRT(number) Example: Let’s say you have a column containing the areas of different squares (A2:A10), and you want to find the side length of each square. Example Usage: =SQRT(A2)
3- POWER: Raises a number to a specified power. Formula Syntax: =POWER(number, power)
Example: Suppose you have a column containing the base values (A2:A10), and another column containing the corresponding exponents (B2:B10). You want to calculate the result of raising each base to its corresponding exponent. Example Usage: =POWER(A2, B2)
4- LOG: Returns the logarithm of a number using a specified base. Formula Syntax: =LOG(number, base)
Example: Let’s say you have a column containing different values (A2:A10), and you want to calculate their logarithms with base 10. Example Usage: =LOG(A2, 10)
5- MAX: Returns the maximum value from a range of cells or numbers. Formula Syntax: =MAX(number1, number2, …)
Example: Suppose you have a column containing test scores (A2:A10), and you want to find the highest score. Example Usage: =MAX(A2:A10)
6- MIN: Returns the minimum value from a range of cells or numbers. Formula Syntax: =MIN(number1, number2, …)
Example: Let’s say you have a column containing the heights of different people (A2:A10), and you want to find the shortest person. Example Usage: =MIN(A2:A10)
7- ROUND: Rounds a number to a specified number of decimal places. Formula Syntax: =ROUND(number, num_digits)
Example: Suppose you have a column containing prices with decimal values (A2:A10), and you want to round them to 2 decimal places. Example Usage: =ROUND(A2, 2)
8- RAND: Returns a random number between 0 and 1. Formula Syntax: =RAND()
Example: Let’s say you want to generate random numbers for simulation purposes. Example Usage: =RAND()
Productivity Tools
H. Lookup and Reference Formulas
1- VLOOKUP: Searches for a value in the leftmost column of a range and returns a corresponding value from a specified column. Formula Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: Suppose you have a table with product names in column A and their corresponding prices in column B. You want to find the price of a specific product based on its name. Example Usage: =VLOOKUP(“Product A”, A2:B10, 2, FALSE)
2- HLOOKUP: Searches for a value in the top row of a range and returns a corresponding value from a specified row. Formula Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example: Let’s say you have a table with employee names in row 1 and their corresponding salaries in row 2. You want to find the salary of a specific employee based on their name. Example Usage: =HLOOKUP(“John”, A1:E2, 2, FALSE)
3- INDEX: Returns the value at the intersection of a specified row and column within a range. Formula Syntax: =INDEX(array, row_num, [column_num])
Example: Suppose you have a table with sales data, where product names are in column A and sales amounts are in column B. You want to retrieve the sales amount for a specific product. Example Usage: =INDEX(B2:B10, MATCH(“Product C”, A2:A10, 0))
4- MATCH: Searches for a specified value in an array and returns its relative position. Formula Syntax: =MATCH(lookup_value, lookup_array, [match_type])
Example: Let’s say you have a column of student names (A2:A10), and you want to find the position of a specific student in the list. Example Usage: =MATCH(“John”, A2:A10, 0)
5- OFFSET: Returns a range reference that is offset from a starting cell reference. Formula Syntax: =OFFSET(reference, rows, cols, [height], [width])
Example: Suppose you have a column of data (A2:A10), and you want to create a dynamic range that includes a certain number of cells below the starting cell. Example Usage: =OFFSET(A2, 0, 0, 5, 1)
6- INDIRECT: Returns the value of a cell specified by a text string. Formula Syntax: =INDIRECT(ref_text, [a1])
Example: Let’s say you have different named ranges in your spreadsheet (e.g., Range1, Range2). You want to refer to these ranges dynamically using a cell value. Example Usage: =INDIRECT(A1)
7- LOOKUP: Searches for a value in an array and returns a corresponding value from another array. Formula Syntax: =LOOKUP(lookup_value, lookup_vector, result_vector)
Example: Suppose you have two columns – one containing product IDs (A2:A10) and another containing product names (B2:B10). You want to find the name of a specific product based on its ID. Example Usage: =LOOKUP(“ID123”, A2:A10, B2:B10)
8- CHOOSE: Returns a value from a list of values based on its position. Formula Syntax: =CHOOSE(index_num, value1, value2, …)
Example: Let’s say you have different discount rates for different customer types. You want to assign the appropriate discount rate based on the customer type. Example Usage: =CHOOSE(A2, 0.1, 0.15, 0.2)
Free Tools
I. Conditional Formatting Formulas:
1- COUNTIFS: Counts the number of cells in a range that meet multiple conditions. Formula Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example: Suppose you have a range of sales amounts in column A, corresponding product names in column B, and customer ratings in column C. You want to count the number of sales for a specific product with a minimum rating of 4. Example Usage: =COUNTIFS(B1:B10, “Product A”, C1:C10, “>=4”)
2- SUMPRODUCT: Multiplies corresponding elements in arrays and returns the sum of those products. Formula Syntax: =SUMPRODUCT(array1, array2, …)
Example: Suppose you have two arrays of numbers in columns A and B. You want to calculate the sum of their products. Example Usage: =SUMPRODUCT(A1:A10, B1:B10)
3- IFERROR: Checks if a formula returns an error and returns a specified value if it does. Formula Syntax: =IFERROR(value, value_if_error)
Example: Suppose you have a division formula in cell A1 that might return an error if the denominator is zero. You want to display “N/A” instead of the error. Example Usage: =IFERROR(A1/B1, “N/A”)
4- ISNUMBER: Checks if a value is a number and returns TRUE if it is, or FALSE if it isn’t. Formula Syntax: =ISNUMBER(value)
Example: Suppose you have a column with different types of data in column A. You want to highlight the cells that contain numbers. Example Usage: =ISNUMBER(A1)
5- ISTEXT: Checks if a value is text and returns TRUE if it is, or FALSE if it isn’t. Formula Syntax: =ISTEXT(value)
Example: Suppose you have a column with different types of data in column A. You want to highlight the cells that contain text. Example Usage: =ISTEXT(A1)
6- ISBLANK: Checks if a cell is empty and returns TRUE if it is, or FALSE if it isn’t. Formula Syntax: =ISBLANK(cell)
Example: Suppose you have a column of dates in column A. You want to highlight the cells that are empty. Example Usage: =ISBLANK(A1)
7- INDEX: Returns the value of a cell in a specified row and column of a range. Formula Syntax: =INDEX(array, row_num, [column_num])
Example: Suppose you have a table with product names in column A and their corresponding prices in column B. You want to retrieve the price of a specific product based on its position in the table. Example Usage: =INDEX(B2:B10, 3)
8- MATCH: Searches for a specified value in a range and returns its relative position. Formula Syntax: =MATCH(lookup_value, lookup_array, [match_type])
Example: Suppose you have a list of names in column A. You want to find the position of a specific name in the list. Example Usage: =MATCH(“John”, A1:A10, 0)
Learn Excel
9- CONCATENATE: Joins two or more strings together. Formula Syntax: =CONCATENATE(string1, string2, …)
Example: Suppose you have first names in column A and last names in column B. You want to concatenate them into a full name. Example Usage: =CONCATENATE(A1, ” “, B1)
10- LEFT: Extracts a specified number of characters from the beginning of a text string. Formula Syntax: =LEFT(text, num_chars)
Example: Suppose you have a full name in cell A1. You want to extract only the first name. Example Usage: =LEFT(A1, FIND(” “, A1)-1)
11- RIGHT: Extracts a specified number of characters from the end of a text string. Formula Syntax: =RIGHT(text, num_chars)
Example: Suppose you have a phone number in cell A1. You want to extract only the last four digits. Example Usage: =RIGHT(A1, 4)
12- MID: Extracts a specific number of characters from a text string, starting at a specified position. Formula Syntax: =MID(text, start_num, num_chars)
Example: Suppose you have an email address in cell A1. You want to extract only the domain name. Example Usage: =MID(A1, FIND(“@”, A1)+1, LEN(A1)-FIND(“@”, A1))
13- SUBSTITUTE: Replaces existing text with new text in a string. Formula Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Example: Suppose you have a sentence in cell A1 with the word “apple”. You want to replace it with “banana”. Example Usage: =SUBSTITUTE(A1, “apple”, “banana”)
14- LEN: Returns the number of characters in a text string. Formula Syntax: =LEN(text)
Example: Suppose you have a sentence in cell A1. You want to count the number of characters in that sentence. Example Usage: =LEN(A1)
15- LOWER: Converts all uppercase letters in a text string to lowercase. Formula Syntax: =LOWER(text)
Example: Suppose you have names in column A. You want to convert them to lowercase. Example Usage: =LOWER(A1)
16- UPPER: Converts all lowercase letters in a text string to uppercase. Formula Syntax: =UPPER(text)
Example: Suppose you have names in column A. You want to convert them to uppercase. Example Usage: =UPPER(A1)
17- VLOOKUP: Searches for a value in the leftmost column of a range and returns a corresponding value from a specified column. Formula Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
How to Take Screenshot in Laptop: 3 Best Way to take a screenshot on Any Computer
Computer Tips
Example: Suppose you have a table with product names in column A and their corresponding prices in column B. You want to find the price of a specific product based on its name. Example Usage: =VLOOKUP(“Product A”, A2:B10, 2, FALSE)
18- SUMIF: Adds the values in a range that meet a specific condition. Formula Syntax: =SUMIF(range, criteria, [sum_range])
Example: Suppose you have a range of sales amounts in column A and corresponding product names in column B. You want to calculate the total sales amount for a specific product. Example Usage: =SUMIF(B1:B10, “Product A”, A1:A10)
19- AVERAGEIF: Calculates the average of values in a range that meet a specific condition. Formula Syntax: =AVERAGEIF(range, criteria, [average_range])
Example: Suppose you have a range of test scores in column A and corresponding student names in column B. You want to calculate the average score for a specific student. Example Usage: =AVERAGEIF(B1:B10, “John”, A1:A10)
20- ISBLANK: Checks if a cell is empty and returns TRUE if it is, or FALSE if it isn’t. Formula Syntax: =ISBLANK(cell)
Example: Suppose you have a column of dates in column A. You want to highlight the cells that are empty. Example Usage: =ISBLANK(A1)
21- SUMIFS: Adds the values in a range based on multiple conditions. Formula Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example: Suppose you have a range of sales amounts in column A, corresponding product names in column B, and customer ratings in column C. You want to calculate the total sales amount for a specific product with a minimum rating of 4. Example Usage: =SUMIFS(A1:A10, B1:B10, “Product A”, C1:C10, “>=4”)
22- MIN: Returns the smallest value in a range of numbers. Formula Syntax: =MIN(number1, [number2], …)
Example: Suppose you have a range of test scores in column A. You want to find the lowest score. Example Usage: =MIN(A1:A10)
23- MAX: Returns the largest value in a range of numbers. Formula Syntax: =MAX(number1, [number2], …)
Example: Suppose you have a range of test scores in column A. You want to find the highest score. Example Usage: =MAX(A1:A10)
24- AND: Checks if all conditions are true and returns TRUE if they are, or FALSE if they aren’t. Formula Syntax: =AND(logical_expression1, [logical_expression2], …)
Example: Suppose you have a range of numbers in column A. You want to check if all numbers are greater than 10. Example Usage: =AND(A1:A10 > 10)
Free Excel Templates
J. Array Formulas:
1- TRANSPOSE: Transposes a range of cells, switching rows and columns. Formula Syntax: =TRANSPOSE(array)
Example: Suppose you have a column of data in A1:A5, and you want to convert it into a row. Example Usage: =TRANSPOSE(A1:A5)
2- MMULT: Multiplies two matrices together and returns the resulting matrix. Formula Syntax: =MMULT(matrix1, matrix2)
Example: Suppose you have two matrices in range A1:B3 and C1:D2, and you want to multiply them together. Example Usage: =MMULT(A1:B3, C1:D2)
3- FREQUENCY: Calculates how often values occur within a range of values and returns a frequency distribution as an array. Formula Syntax: =FREQUENCY(data_array, bins_array)
Example: Suppose you have a range of values in column A, and you want to calculate the frequency distribution using specified bins in column B. Example Usage: =FREQUENCY(A1:A10, B1:B5)
4- SORT: Sorts the contents of a range or array based on specified criteria. Formula Syntax: =SORT(range, [sort_column], [is_ascending], [sort_column2], …)
Example: Suppose you have a table with names in column A and corresponding scores in column B, and you want to sort it based on scores in descending order. Example Usage: =SORT(A1:B10, 2, FALSE)
Google Sheet Project
5- FILTER: Filters a range or array based on specified criteria and returns the filtered subset. Formula Syntax: =FILTER(range, condition1, [condition2], …)
Example: Suppose you have a table with names in column A and corresponding scores in column B, and you want to filter it to show only the names of students with scores above 90. Example Usage: =FILTER(A1:A10, B1:B10 > 90)
6- UNIQUE: Returns a list of unique values from a range or array. Formula Syntax: =UNIQUE(range)
Example: Suppose you have a column of data in A1:A10 with duplicate values, and you want to extract the unique values. Example Usage: =UNIQUE(A1:A10)
7- ARRAYFORMULA: Applies a formula to an entire range or array, allowing calculations on multiple cells at once. Formula Syntax: =ARRAYFORMULA(formula)
Example: Suppose you have a column of numbers in A1:A10, and you want to calculate the square of each number in one go. Example Usage: =ARRAYFORMULA(A1:A10^2)
8- SORTBY: Sorts the contents of a range or array based on the values in another range or array. Formula Syntax: =SORTBY(range_to_sort, range_to_sort_by1, [sort_order1], [range_to_sort_by2], …)
Example: Suppose you have a table with names in column A and corresponding scores in column B, and you want to sort it based on scores in descending order. Example Usage: =SORTBY(A1:B10, B1:B10, -1)
How To Use Importrange in Google Sheets: A Step-by-Step Guide In Details.
Learn Google Sheet
Google Sheet Formula List PDF Download
Mastering Google Sheets formulas is essential for harnessing the full potential of this versatile spreadsheet tool. From basic calculations to advanced data analysis, having a solid understanding of essential formulas can significantly improve your productivity and efficiency.
Throughout this article, we have covered a comprehensive list of the most essential Google Sheets formulas from beginner to pro. By following the explanations and examples provided, you can gain confidence in using these formulas effectively in your own spreadsheets.
The downloadable PDF guide serves as a valuable resource that you can refer back to whenever you need a quick reminder or want to explore new formula possibilities. With this guide by your side, you can easily navigate through different formula categories and find the specific formula you need for your tasks.
Remember, practice is key to mastering Google Sheets formulas. As you continue to use them in your spreadsheets and explore more advanced formulas, you will become more proficient and efficient in data management and analysis. So don’t be afraid to experiment and explore the vast array of possibilities that Google Sheets formulas offer. Download the PDF by clicking the link given below.
Free Cheat sheets PDF
FAQ
- Q: How do I enter a formula in Google Sheets?
- Ans: To enter a formula in Google Sheets, start by selecting the cell where you want the result to appear. Then, begin the formula with an equal sign (=) followed by the formula syntax. For example, to add two numbers in cells A1 and B1, you would enter “=A1+B1” into the desired cell.
- Q: Can I use functions in Google Sheets formulas?
- Ans: Yes, Google Sheets provides a wide range of built-in functions that can be used within formulas. Functions allow you to perform various calculations and operations on your data. To use a function, enter the function name followed by the necessary arguments or references within parentheses.
- Q: How can I copy a formula to multiple cells in Google Sheets?
- Ans: To copy a formula to multiple cells in Google Sheets, first select the cell containing the formula. Then, click and drag the small blue square in the bottom right corner of the selected cell to extend the formula to adjacent cells. Alternatively, you can use the “Fill” handle (a small blue square) located at the bottom right corner of the selected cell and drag it across the desired range.
- Q: What is the difference between relative and absolute cell references in formulas?
- Ans: In Google Sheets formulas, relative cell references change when the formula is copied or moved to different cells. For example, if a formula refers to cell A1 and is copied to cell B2, it will automatically adjust to refer to B2 instead of A1. On the other hand, absolute cell references remain fixed regardless of where the formula is copied or moved. You can create an absolute reference by adding dollar signs ($) before both column and row references (e.g., $A$1).
- Q: How can I troubleshoot errors in Google Sheets formulas?
- Ans: If you encounter errors in your Google Sheets formulas, you can use the error checking feature to identify and resolve them. To do this, click on the cell with the error indicator (a small green triangle) and select “Show” from the drop-down menu. Google Sheets will provide suggestions or explanations to help you correct the formula and resolve the error.
