This tutorial covers the following:
- Removing unnecessary words.
- Basic formulas.
- VLookUp formula.
Removing Redundant Words from your Dataset
- If you want to remove redundant words in a column, google sheets have a feature that will help you accomplish this goal without investing significant time. Open this sample spreadsheet about public educational organizations in Connecticut and make a copy.
When reviewing your data, you may notice that unnecessary words like “Region” appear next to each region name in the Region column. To remove these words, select the entire column, go to Edit on the menu, and click on Find and Replace. In the pop-up window, enter the word you want to find (“Region”), and in “Replace with,” enter the new word or add a space to delete it. These simple steps allow you to clean up your data and streamline your analysis quickly.
Short Instructional Video
Video 1: “How to remove unnecessary words in your data with Google Spreadsheet features,” Self-hosted video, 00:15 seconds, posted by Myri Ayala.
Your Guide to Basic Data Analysis Formulas in Google Sheets
Google Spreadsheets are great for analyzing data. This section will teach you how to create simple formulas to find patterns and outliers. We’ll also provide a list of additional formulas to explore.
To create a formula in Google Spreadsheets, type an equals sign “=” in a cell followed by the formula name you want to use. The good news is that the program will auto-fill based on your typing, so you don’t have to memorize all the formulas. In this section, we’ll cover the basic formulas for data analysis, including addition, averaging, counting, and finding minimum and maximum values. However, remember that you can use even more complex calculations to analyze your data. Let’s get started!
- To add up a range of cells, follow these simple steps:
- Go to the Sum function column in your Google Sheet and click on the first cell where you want to display the result.
- Type an equals sign “=” to begin creating the formula.
- Select the first cell in the range of cells you want to add.
- Type a plus sign “+” to indicate that you want to add the next cell in the range.
- Select the second cell in the range, and continue adding additional cells as needed using the same format.
To calculate the average of a set of numbers, follow these simple steps:
- Select the cell where you want the result to be displayed.
- Next, type the equals sign “=” to start the formula.
- Type “AVERAGE(” to begin the AVERAGE function.
- Select the range of cells that you want to include in the calculation. You can select the cells individually or click and drag to select a range of cells.
- Close the function by typing “)” and pressing Enter on your keyboard.
To count the number of cells with numbers within a specific range, follow these simple steps:
- Select the cell where you want the result to be displayed.
- Next, type the equals sign “=” to start the formula.
- Type “COUNT(” to begin the COUNT function.
- Select the range of cells that you want to include in the calculation. You can select the cells individually or click and drag to select a range of cells.
- Close the function by typing “)” and pressing Enter on your keyboard.
To find the maximum value in a range of cells, follow these simple steps:
- Select the cell where you want the result to be displayed.
- Next, type the equals sign “=” to start the formula.
- Type “MAX(” to begin the MAX function.
- Select the range of cells that you want to include in the calculation. You can select the cells individually or click and drag to select a range of cells.
- Close the function by typing “)” and pressing Enter on your keyboard.
To find the minimum value in a range of cells, follow these simple steps:
- Select the cell where you want the result to be displayed.
- Next, type the equals sign “=” to start the formula.
- Type “MIN(” to begin the MIN function.
- Select the range of cells that you want to include in the calculation. You can select the cells individually or click and drag to select a range of cells.
- Close the function by typing “)” and pressing Enter on your keyboard.
Go to the official Google Sheets function list to find the complete list of formulas available. You can filter formulas by typing keywords in the search section.
Short Instructional Video
Video 2: “How to use basic formulas to analyze your data with Google Spreadsheet,” Self-hosted video, 00:45 seconds, posted by Myri Ayala.
VlookUp formula
VLOOKUP finds and extracts information from a larger dataset by looking up a specific value in one column and returning a corresponding value from another in the same row. This function is commonly used in data analysis.
- Open this sample spreadsheet about public educational organizations in Connecticut and make a copy.
- To locate information in the Regions sheet on the second page/sheet, we can use the VLOOKUP formula. This formula searches for specific data (value) in a table and returns corresponding information (matched value). For instance, if we search for “Niantic” in the town’s column of the Regions sheet, the VLOOKUP function will locate this value and return the region where the town is, such as “New London.”
The VLOOKUP function says:
=VLOOKUP(search_key, range, index, [is_sorted]) which means
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match)
For Is_sorted, if you type false it means it will only return an exact match. If you type true, it will return an approximate value if the exact one cannot be found.
Short Instructional Video