A few basic Google Sheets formulas can save you time and energy when analysing large blocks of data. Whether you need to sum up values, average numbers or search for specific text, these nifty formulas will help you work faster.
Like all formulas, they begin with an equal sign and the name of the function (for example, =SUM(value1:value20). Some are static while others adapt to their data.
SUM
One of the most basic and common Google Sheets formulas is SUM, which calculates the total of a range of numbers. You can use it to sum up values in columns, rows, or cells that are spread randomly across your spreadsheet.
To use the SUM function, select the cell where you want to see the total. Then, type SUM followed by the cell’s address in parenthesis. The cell address is a letter-number combination that denotes the column and row of a specific cell in your spreadsheet.
SUM only adds numerical values, so numbers that have been formatted as text will not be included in the calculation. You can also use the SUM function with a criterion to only include rows that meet a certain criteria. For example, you can sum all rows with Pen orders by adding the SUMIF function.
AVERAGE
The AVERAGE function is a built-in Google Sheets formula that calculates the average (arithmetic mean) of a set of data values. The AVERAGE function is simple to use, and it can be used in a variety of real-world scenarios.
When using the AVERAGE function, make sure that all arguments are numbers. Arguments that are logical values or text representations of numbers will cause errors. Also, the AVERAGE function does not take into account empty cells, but it does count cell values that are equal to zero.
You can also use the AVERAGEIF function to find the average of a range of values that meet a specific criteria. The AVERAGEIF function requires three arguments: a range to test against, a criterion, and an average range. For more complex scenarios, you can also use the AVERAGEIFS function. This allows you to test multiple ranges, criterions, and average ranges simultaneously.
COUNT
The COUNT function calculates the number of cells in a range that contain non-blank data. This includes numeric values, text, logical values (TRUE or FALSE), and error values. It does not count completely empty cells, but it does include cells that contain a leading apostrophe (‘) – a symbol that Google Sheets treats as text and processes differently from an actual blank.
The criterion can be a cell, range of cells, or named range. For more complex criteria, use the COUNTIF function to test values against a set of conditions.
When testing a condition with the COUNTIF function, be sure to use double quotes around the value or range of values. Otherwise, the function will treat the criterion as a single value instead of a range. This can lead to unexpected results.
PERCENTAGE
Whether you’re leaving a tip at a restaurant or figuring out how much to buy that pair of shoes, percentages are everywhere. Understanding the formulas that make them work will help you make better decisions with data.
Percentage is a fraction or ratio in which the denominator is always 100. For example, 30% means 30 parts out of 100, or (30:100).
When solving percentage problems, you usually have two numbers and want to know the third value. The formula for this is simple: (numerator/denominator) * 100. For example, 55/100 * 100 is equal to 5.5. To solve this problem, first simplify the fraction to a decimal by moving the decimal point two spaces to the left. Then divide the numerator by the denominator. The result will be the third number. It’s as easy as that!
VLOOKUP
VLOOKUP is a very powerful lookup function that allows you to retrieve data from another sheet or worksheet based on unique identifiers. These identifiers can be text, numbers, dates or even logical values.
The first argument in the VLOOKUP formula is the lookup value. Next comes the table array, which contains the data that you want to look up. Finally, the column index number is the last argument in the formula.
The column index number is used to find a specific column within the table array. The is_sorted argument indicates whether the first column in the table array should be sorted (TRUE) or not (FALSE). If you do not sort the table array, the VLOOKUP function will stop searching as soon as it finds a close match smaller than the lookup value, leading to weird results and #N/A errors.
HLOOKUP
The HLOOKUP function is similar to Excel’s VLOOKUP but it searches data horizontally across rows, rather than vertically down columns. It requires three arguments: the lookup_value, the table_array and the col_index_num.
The lookup_value can be a value you type directly into the function or a cell reference. The table_array refers to the range of cells that comprise the lookup table. The column_index_num is the row number in the table_array from which you want to retrieve a value. The optional range_lookup argument can be added to tell the HLOOKUP function whether you need an approximate or exact match.
HLOOKUP is limited to searching horizontally across rows, so you may need to use more powerful and faster functions such as XLOOKUP or INDEX to find data that is arranged vertically. However, it is still a good option for many common business scenarios.
SUBTOTAL
SUBTOTAL is a handy function that helps you perform multiple arithmetic operations with a single function. This makes working with large amounts of data easier and eliminates the need to remember different functions and their arguments.
SUBTOTAL ignores cells that have been filtered out and also excludes rows that have been hidden manually from the calculation. This is helpful when using Excel tables and filtered data.
To use the SUBTOTAL function, simply type =SUBTOTAL(function_num, ref1) in a cell. For example, the formula =SUBTOTAL(2, D2:D5) returns 4 as the result. The consolidated results of all 11 SUBTOTAL operations can be seen in the image that follows this paragraph. You can click the image to enlarge it. SUBTOTAL is a great tool for making groupings of your data. It’s easy to apply and provides a clean, organized look for your spreadsheet.
COUNTIF
The COUNTIF function counts the number of cells in a selected range that meet a specified criteria. The criterion can be a text string, a numeric value, or a cell reference. When the criterion is a cell reference, it must be enclosed in double quotes. If the criterion is a comparison operator, it must be joined to the cell reference with an ampersand (&).
You can use wildcard characters—a question mark or asterisk—in the criterion to search for any character or sequence of characters. The COUNTIF function is case-insensitive. To count cells that contain both upper- and lowercase text, you can combine the COUNTIF function with the ISNUMBER and FIND functions. This is the formula: =SUMPRODUCT(–(ISNUMBER(FIND(“text”,range))+ISNUMBER(FIND(“second text”,range))))). This returns a total for all pairs of criteria. The result is the number of cells that match both conditions.
IF
IF is a powerful tool, as is something like a Google Sheets AI, for performing logical tests on data. It’s often used in conjunction with the OR and NOT functions, which allow you to combine multiple conditions.
To use the COUNTIF function, start by selecting an empty cell and entering your condition and range in quotation marks. Then, add a comma and specify the value you want to search for. For example, if you’re looking for the number of people who live in West, your formula might look like this: =COUNTIF(A2:A500, “executive”).
Google Sheets will often display an error message when a function isn’t correctly formatted. To avoid these errors, double-check your formulas regularly. You can do this by pressing ESC while in the Formula view. This will take you back to the Result view.
SUMMARY
Google Sheets is a powerful tool for managing data and performing calculations. It’s essential for digital PRs to understand how to use spreadsheet formulas in order to streamline processes, maximise data-led campaigns and get the most out of the platform.
A Google Sheets formula is a set of built-in operations that manipulate cells, rows, columns, and ranges to generate a specific end result. Formulas always begin with an equal sign (=) and can include a combination of numbers, cell references, mathematical operators, and text formatting.
For example, if you have a list of prospect names in a column and want to break them up into first and last name, you can use the SPLIT() formula. This will automatically separate each word and put the resulting fragments into different columns. It also makes sure that all of the text is in title case, saving you time and effort from manually reformatting each entry.
