Start learning how Google Sheets can be used for data-science tasks. This article walks through an overview of the spreadsheet, basic use, important functions, Array Formula, conditional formatting, filters, sorting, QUERY, and VLOOKUP to help readers store, analyze, and present data.
Overview of learning Google Sheets
Reviewing basic concepts
Before going into details, review the basic concepts needed to work with Google Sheets so that you can apply them more effectively.
Why use Google Sheets?
The source article describes Google Sheets as an accessible Business Intelligence (BI) tool for beginners and people who want a straightforward way to analyze data.
Store, analyze, and present data
Google Sheets can support a workflow that includes importing and managing data, calculations, charts, and presenting results.
Excel versus Google Sheets
The source article contrasts paid Excel software with Google Sheets, which it says can be used for free through a Google Account. Software plans and terms can change, so check current product information.
Advantages of Google Sheets
The article lists ease of use, access from different places, collaboration, and a range of functions as advantages.
Google Sheets basics
Using functions
Functions are central to using Google Sheets. A function takes input and returns an output.
What a function looks like
A Sheets formula begins with an equals sign (=), followed by a function name and the inputs it needs.
Function inputs
An input is the data a function receives to calculate or process.
Required and optional arguments
Some function arguments are required, while others are optional.
The IF function
IF applies a condition and returns a result depending on whether the condition is met.
Basic functions in Google Sheets
Name cells or data
Naming a cell or range makes it easier to refer to and can make formulas easier to read.
Name a table
Naming a table can make structured data easier to manage and can support workflows using QUERY and VLOOKUP.
Entering data
Manual data entry
Manual entry means typing data directly into cells.
Using random functions
Random functions generate random numbers.
Using arrays
An array is a group of values organized in rows and columns.
Using arrays, continued
Arrays can be used to calculate multiple values at once.
Arrays with text
Arrays can process multiple text values together.
Arrays across multiple rows
Using an array across rows can calculate values for several rows at once.
Arrays with a matrix
A matrix is a table with multiple rows and columns. Arrays can be used with a matrix for more complex calculations.
Mean and median
Google Sheets includes AVERAGE and MEDIAN functions, which can be useful in data analysis.
Using Array Formula
Increasing employee salaries
An Array Formula can apply a salary increase for multiple employees at once.
Working with Array Formula
Array Formula can calculate multiple values and can help manage more complex formulas.
Creating employee email addresses
An Array Formula can create email addresses for multiple employees at once.
The UPPER function
UPPER converts text to uppercase letters.
Using conditional formatting
Conditional formatting
Conditional formatting can highlight important data or data that meets specified conditions.
Using filters and sorting
Using IF
IF applies a condition and returns a calculation or result based on that condition.
Using a filter
A filter displays data that meets selected conditions.
Naming a range
A named range can make data easier to manage and refer to.
Using filters, continued
Filters can also be used to select data based on more complex conditions.
Blank cells
The source introduces using blank cells as part of filtering data.
IFERROR
IFERROR helps handle errors that may occur in a formula.
Sorting
Sort arranges data in a chosen order.
Sorting, continued
Sorting can also be applied using more complex criteria.
Special Google Sheets function: QUERY
Using QUERY
QUERY retrieves data from a table based on specified conditions.
QUERY, continued
QUERY can retrieve data from a table using more complex criteria.
Using VLOOKUP
VLOOKUP
VLOOKUP searches for data in another table.
Joining tables
Joining tables combines data from multiple tables.
Naming a table
Giving a table a name can make structured data easier to manage.
VLOOKUP, continued
VLOOKUP can be used to look up more complex information in another table.
Consultation: Add the financial consultation account on LINE, or scan the QR code: 