Dealing with large numbers and organizing them into different categories could mean a lot of work, and that’s only one part of doing data analysis.
Interpreting and presenting it in a visually compelling way requires another set of hard work as well, as you have to make sure that you are making your point across.
That’s what makes Google Sheet a powerful and versatile platform, as it does not just offer basic functions, but lets you take your manipulation of data skills to the next level.
In this blog, we’ll discuss Google Sheets’ huge advantage — employing advanced formulas and functions.
ARRAYFORMULA: Streamlining Operations
Step 1. Open Your Spreadsheet
Open the spreadsheet that you would want to work on.
Step 2. Input the Formula to the Cell Where You Want it to Incorporate.
Let’s say you want to get the total cost for a list of things.
You can do it with this formula =ARRAYFORMULA(B2:B * C2:C), and it will multiply the price and the quantity.
The calculated values will be automatically filled in for the full column by this one formula.
MACROS
The Macros feature allows you to record your actions and Google Sheets will automatically apply them for you. Macros can be reused quickly and easily on your data.
Here’s how you do it:
Step 1: Open your Google Sheet
- Open the Google Sheet that contains the data you need.
- Go to the “Extensions” tab and proceed with the “Macros” tab so that you can record how you want the sheet to look like every time you input new data.
Step 3: Select “Record Macros” to start recording your desired format
- You’ll see in the middle part of your screen that it is recording the steps you are doing.
- You can opt to use absolute references or relative references.
Step 4: Save New Macro
- Press the Save button once you have fulfilled the formatting that you wish.
- You can name it for easier access, as well as put a number for shortcut purposes.
- It might require some users to give access to Macros to manage the sheet.
Step 5: Save and Run the Macro
- Every time a new information is given, you can just go to the “Extensions” tab and then to the “Macros” tab where you can see your saved Macro.
- You can also do it by pressing Ctrl+Alt+Shift along with the number you’ve assigned to your Macro.
It takes off the hassle of inputting and presenting data one-by-one.
VLOOKUP and HLOOKUP: Enhanced Lookup Functions
Strong tools for searching and obtaining data from tables are VLOOKUP and HLOOKUP.
VLOOKUP returns a value from a specified column in the same row after searching for a value in the first column of a range.
Similar in operation, HLOOKUP looks in the first row of a range. These features come in especially useful when working with big datasets, and you need to get pertinent data fast.
For VLOOKUP:
Step 1. Organize your data
- Ensure that your data is arranged with the lookup value in the top row of the table.
Step 2. Select the cell for the result
- Click on the cell where you want the VLOOKUP function to display the result.
Step 3. Go to the formula bar
- Click on the formula bar.
Step 4. Enter the VLOOKUP function
- Type =VLOOKUP( in the formula bar.
Step 5. Specify the lookup value
- Select or enter the value you want to look up.
Step 6. Specify the table range
- Enter the range of the table where you want to search for the value.
Step 7. Specify the column index number
- Enter the row number in the table from which to retrieve the value.
Step 8. Specify whether you want an exact match
- Enter FALSE for an exact match or TRUE for an approximate match.
Step 9. Close the function
- Close the parentheses and press Enter.
For HLOOKUP:
Step 1. Organize your data
- Ensure that your data is arranged with the lookup value in the top row of the table.
Step 2. Select the cell for the result
- Click on the cell where you want the HLOOKUP function to display the result.
Step 3. Go to the formula bar
- Click on the formula bar.
Step 4. Enter the HLOOKUP function
- Type =HLOOKUP( in the formula bar.
Step 5. Specify the lookup value
- Select or enter the value you want to look up.
Step 6. Specify the table range
- Enter the range of the table where you want to search for the value.
Step 7. Specify the row index number
- Enter the row number in the table from which to retrieve the value.
Step 8. Specify whether you want an exact match
- Enter FALSE for an exact match or TRUE for an approximate match.
Step 9. Close the function
- Close the parentheses and press Enter.
QUERY: SQL-Like Data Retrieval
Similar to SQL queries, the QUERY function in Google Sheets is a potent tool that lets you access and work with data.
It can be applied to collect, filter, and sort data according to predetermined standards.
Step 1. Organize your Data
- Ensure that your data is organized in a table with headers in the first row.
Step 2. Select the Cell for Output
- Click on the cell where you want the QUERY results to be displayed.
Step 3. Go to the Formula Bar
- Click on the formula bar to start entering the QUERY function.
Step 4. Enter the QUERY Function
- Type =QUERY( in the formula bar.
Step 5. Specify the Data Range
- Select the range of cells that contain your data (including headers). For example, if your data is in A2:C7, enter A2:C7.
Step 6. Write the SQL-Like Query:
- After specifying the range, type your SQL-like query.
- The basic structure is SELECT [columns] FROM [range] WHERE [conditions]. For example:
=QUERY(A2:C7, “Select A, B where C > 100000”)
- This query selects columns A, B, and C where the value in column D is greater than 100.
In the video that I did, instead of putting the data range, I named the range first and encoded the named range instead.
Step 7. Customize the Query as Needed:
- You can use various SQL-like clauses such as WHERE, ORDER BY, GROUP BY, etc., to filter and manipulate data as needed.
- Refer to the QUERY documentation for more details.
Step 8. Close the Function
- Close the parentheses for the QUERY function and press Enter.
Step 9. View the Results
- The results of your query will be displayed in the selected cell.
IFERROR and IFNA: Error Handling
When working with complex datasets, error handling becomes essential.
The IFNA and IFERROR functions assist you in handling mistakes more skillfully.
If a formula yields an error, IFERROR delivers a defined value; IFNA does the same for the #N/A error.
Step 1. Understand the Scenario
- Identify a situation where you expect an #N/A error to occur in a formula result.
Step 2. Select the Cell for the Formula
- Click on the cell where you want to enter the IFNA formula.
Step 3. Go to the Formula Bar
- Click on the formula bar to start entering the formula.
Step 4. Enter the IFNA Function
- Type =IFNA( in the formula bar.
Step 5. Write the Formula to Evaluate
- Enter the formula or expression that might result in an #N/A error inside the IFNA function. For example:
=IFNA(B2:B7, “No Data”)
- In this example, if the cell has an #N/A, the IFNA function will return “No Data.”
Step 6. Close the Function
- Close the parentheses for both the inner formula and the IFNA function, then press Enter.
IMPORTRANGE: Importing Data Across Sheets
IMPORTRANGE allows you to pull data from one Google Sheet into another.
Step 1. Open the Destination Sheet
- Open the Google Sheets document where you want to import data.
Step 2. Go to the Cell for the Import
- Click on the cell where you want to import the data.
- This is typically the top-left cell of the area where you want the imported data to appear.
Step 3. Go to the Formula Bar
- Click on the formula bar to start entering the IMPORTRANGE formula.
Step 4. Enter the IMPORTRANGE Function
- Type =IMPORTRANGE( in the formula bar.
Step 5. Specify the Source Sheet URL
- Enter the URL of the source Google Sheets document within double quotation marks. For example,
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/ABC123/edit”,”Sheet1!A1:C10″)
- Replace the URL with the actual URL of your source sheet, and adjust the sheet name and range as needed.
Step 6. Authorize the Connection:
- Press Enter, and Google Sheets will ask you to authorize the connection between the two sheets.
- Click on “Allow” and grant the necessary permissions.
Step 7. Select the Range Locally
- After authorization, you will see #REF! in the cell.
- Click on the cell again, and a box will appear around the imported range.
- Click and drag to select the area where you want the data to be placed.
Step 8: Press Enter to Confirm
- Press Enter to confirm your selection.
- The imported data will now appear in the selected area of your destination sheet.
Video tutorial with the Advanced Functions
In Conclusion
Gaining proficiency with Google Sheets’ sophisticated formulas and functions offers up a world of possibilities for effective data manipulation and analysis.
These features enable you to operate more productively and intelligently whether you’re managing huge datasets, carrying out intricate computations, or working together on projects.
If you take the time to learn and apply these sophisticated capabilities, you’ll quickly become an expert user of Google Sheets.
Learn more about FileDrop by clicking here.
See also The Ultimate Guide To Use AI Inside Google Sheets With FileDrop
