5 Life-Saving Spreadsheet Formulas That Make Life Easier

5 Life-Saving Spreadsheet Formulas That Make Life Easier

Google Sheets is a very handy platform for people who work with spreadsheets. It offers an amazing set of features and tools at zero cost for small businesses and individuals. More and more people are converting to Google Sheets from the alternatives like Microsoft Excel.

It provides a clean, minimal interface with real-time progress saving, meaning you rarely lose progress in case of power loss.

A fantastic feature in Sheets is that it provides collaboration features using Google Drive with 15GB of free storage.

But, the program itself can be a little daunting. Never fear though, we have listed and explained five lifesaving spreadsheet formulas to make you feel like a Google Sheets pro. These are the 5 life-changing spreadsheet formulas that make life easier.

1) Finding Average of Values in Cells

Formula: AVERAGE(value1, [value2, …])

Frequently, your table has several values that you may need to find the average of. Using the average formula, you can easily find the average of your values in the cells. Basically, it is used to find the arithmetic mean for your chosen group of cells.

In this formula, the ‘value’ defines the numbers you want to find the average of. Alternatively, you can write the name of the cells in the brackets to find the average of these cells. Here is the formula used in the example image above:

=AVERAGE(A2:A7)

2) Combining Data From Multiple Cells

Formula: CONCATENATE(string1, [string2, …])

If your table has a lot of string data in the cells that you often need to join, this is a great formula to use. It works by taking the data from two or more cells and joining them together.

As an example, if you use this formula to join a first name and last name together, you will note that the formula does so without any spaces in between.

You have to manually add a space, or any other symbol using quotation marks. Here is the formula used in the image above:

=CONCATENATE(A2,” “,B2) 

3) Searching a Column for Data

Formula: VLOOKUP(search-key, range, index, [is-sorted])

Despite its name, this formula is often overlooked by many. You can use it to find a specific value on a large table. You can use this to find names, numbers, or any data you specify on a spreadsheet. This formula can significantly help you save time as you don’t have to go over large amounts of data manually.

In the formula:

• Search-key is the data you’re searching for
• Range defines where you’re looking for
• The index defines the column index of the value you want to return (the first column in range is represented 1)
• Is-sorted is used to indicate whether the column you’re searching is sorted. It’s generally recommended to use FALSE in most cases.

Here is an example of this formula in action:

=VLOOKUP(10003, A2:B26, 2, FALSE)

4) Calculating Sums of Different Ranges

Formula: SUMIFS(sum-range, criteria-range1, criterion1, [criteria-range2, criterion2, …])

This is very similar to the SUMIF formula, the only difference being that SUMIF works with just one condition while SUMIFS can add cells up based on two or more conditions.

So, if you know how to do a SUMIF calculation, it shouldn’t take long to learn how to use SUMIFS.

In this formula:

• Sum-range is the range that is to be summed up
• Criteria-range1 is the range of the cells that will be evaluated by criterion1
• Criterion1 is the condition that needs to be met by the cells in criteria-range1.

This function only adds the cells that meet all the conditions. The sum range and the criteria should be sized equally. Here are a few sample uses of this formula:

=SUMIFS(A1:A10, B1:B10, “>20”)

=SUMIFS(A1:A10, B1:B10, “>20”, C1:C10, “<30”)

=SUMIFS(C1:C100, E1:E100, “Yes”) 

5) Finding Unique Values

Formula: UNIQUE(range)

This formula is very powerful, especially for someone who needs to find unique values in a data set.

It works basically by looking at the range input by the user and pulling all the unique values from it. It’s very useful if you work with a huge amount of data and want to speed up your workflow.

In the example above, the UNIQUE formula is being used to find all of the unique addresses which can be seen displayed in the third column. The formula used here is:

=UNIQUE(B2:B7)

The Final Word

Google Sheets is a great free tool to have if you’re someone who works with a lot of spreadsheets. It’s also the perfect platform to learn spreadsheets on. Using these simple yet powerful formulas can help you from having to do tedious, repetitive tasks over and over and streamline your work to make your workflow faster.