Creating a spreadsheet by entering a few columns of data and applying the basic functions of mathematics is simply the tip of the iceberg. More utility and automation lies under the ocean of things that you can do on Google Sheets.
Whether for work, school, or home, you can use Google Sheets in many ways to visualize data or get insights from data.
In this article, you will learn a few crazy functions of Google Sheets and practical examples of how you can use them.
1. ARRAYFORMULA
It helps you minimize the time you invest in editing the formulas in your spreadsheet. When you need to apply functions to thousands of rows and columns in your worksheet, it’s wise to use the ARRAYFORMULA instead of non-array formulas.
Non-array functions are those that you create in one cell and then copy-paste them to another cell within the worksheet. The Google Sheets program is smart enough to modify the formula according to the cell addresses. But, doing so, you open the door for the following issues:
- The worksheet becomes slow because of individual functions in thousands of cells.
- Making any changes in the formula will need hours of editing.
- Copy-pasting non-array formulas into their designated cells is a tiresome process.
Let’s say that you’re in charge of creating a spreadsheet document with student names, their subject-wise marks, and student-wise total. Now, instead of a few students of your branch, if you need to do that for the entire state, the usual `+` operator-based formula will be time-consuming. You can use the ARRAYFORMULA as mentioned below.
=ArrayFormula(B2:B+C2:C+D2:D+E2:E+F2:F)
In this formula, the B2:B range designates infinite. You can make it finite by editing the range to B2:B10 and so on.
2. V-LOOKUP
V-LOOKUP formula helps you find the data you’re looking for from a sheet of interrelated data. It’s similar to scanning your calendar to find a specific meeting, its time, and date. This is a powerful function that retrieves matching data from another worksheet or table of the same worksheet.
Again, this formula helps you save time and brainwork. Your eyes would have to scan the table manually without this formula.
Let’s consider, you have to update the order status of food supplies in your neighborhood from your store. The delivery partner sent you the “Lookup Table”, and you’ve got the “Primary Order Table.”
The “Lookup Table” contains the orders and their present status. Instead of visually matching the tables, you can automate this task using V-LOOKUP.
You can use the following formula. You have to lock the range using “$.” Otherwise, you’ll start getting errors in a few cells.
=VLOOKUP(A3,$G$3:$H$22,2,false)
3. IMPORTRANGE
IMPORTRANGE is a useful formula if you need to pull data from different worksheets of Google Sheets. Instead of copying data from one worksheet and pasting it to the desired one, you can utilize this formula to save time. It also streamlines your data import work.
If someone other than you owns the worksheet, you must get access to the worksheet before you can start using the IMPORTRANGE function.
This process of pulling data from different worksheets is dynamic. It means any of the data changes on the source sheet will automatically reflect on the destination sheet.
Hence, this is an essential formula if you’re making a report or dashboard by sourcing data from different team members of your project team.
In the above video, you can see how easily you can import data of USA Demographics from one worksheet to another. You only need to mention the URL of the worksheet, the sheet name, and the data range that you want to import.
The formula doesn’t import any visual formatting from the source. You may use the following formula syntax if you want to try it out:
=IMPORTRANGE("URL","Sheet1!B3:R11")
4. LEN
The LEN formula helps you retrieve the length of a string of data or text in characters. It’s helpful when you want to find out the number of characters in a text without using any third-party tools. Simply put, use the LEN function and put the text within a parenthesis.
Let’s say your freelance client needs you to write the Titles, Meta Titles, and Meta Descriptions for the blogs they need. They also want you to limit the character length for these items to a specific number. You can use the LEN function to track these items and their character lengths.
If you’re a website or app developer, you’ll also find the LEN formula useful in managing content in a Google Sheets file. Use the following formula on your own and try it out.
=LEN(B2)
=LEN("Example Text")
5. IFERROR
Your spreadsheet could look messed up if there are too many errors. Errors usually happen when you apply multiple formulas across columns and sheets, but there isn’t much data to return any value.
If you share such files with the team members or clients, they may not like the look of the spreadsheet. Moreover, you’ll also find it challenging to steer away from these errors to get things done. The IFERROR formula comes to your rescue.
Put your formula inside the IFERROR function and mention which text to display should any error come up. The video above shows the usage of IFERROR in situations when you’re managing a spreadsheet on the product price or grading your students.
Here are the formula syntaxes that might come in handy when you want to try it yourself:
=iferror(D4/C4, 0)
=iferror(VLOOKUP(A23,$A$13:$G$18,7,false),"ID Mismatch")
6. COUNTIF
COUNTIF formula is quite helpful when you need to fetch the number of times a specific value appears in large datasets. It’s equally beneficial for small-scale data. This formula is a powerful tool to get customized insights from any spreadsheet.
If there are multiple columns in the data table, you can fetch values depending on different column headers. The above video shows the technique to find out the total orders for Apple or the number of orders above $50. Use the below-mentioned formula syntax if you want to try it out:
=countif($B$2:$C$21,"Apple")
=countif($B$2:$C$21,">50")
Format and Interpret Data the Easy Way
You can apply the above-mentioned Google Sheets formulas in different situations, even except the ones mentioned here. All you need to remember is the scope of the functions.
We’re sure that you can find more novel situations to apply these formulas. It'll save your time and help you create the data visualization/interpretation.