Skill 04: Advanced Google Sheets

Functions
Before moving to functions in google sheets let’s see what is the basic difference between
function and formulas:

A function is a built-in command in Google Sheets that performs a specific task. Think of it as a
shortcut that lets you perform common calculations or data operations without having to write
out all the details yourself. Functions like SUM, AVERAGE, and VLOOKUP are examples; they’re
built into Google Sheets to make tasks like adding up numbers, finding averages, or searching for
data much easier.

On the other hand, a formula is something you create by combining functions, numbers, and
operators (like + or *). Formulas are a way to write out calculations or instructions for Google
Sheets to follow. For example, you might write a formula like =A1 + B1 to add the values in two
cells, or combine functions and operators to make it even more specific, like =SUM(A1:A5) * 2. In
other words, formulas are custom expressions you create, often using functions as part of the
formula to get the exact result you need.
In short, functions are the building blocks in Google Sheets, while formulas are the "recipes" you
create by combining these blocks to perform customized calculations.

Now that we are familiar with the concept of functions and formula so if you remember in the
last class we imported some data in our google sheets which is the first step of Data
Structuring.

What is Data Structing?

Data structuring is about organizing data in a logical and systematic way to make it easier to
analyze and retrieve. In Google Sheets, structuring data correctly ensures accuracy in
calculations and makes your spreadsheets more manageable.

Key Principles of Data Structuring

Organize Data in Rows and Columns: Use rows for each record (e.g., each person, product,
transaction) and columns for different fields (e.g., name, price, date).
Add Headers: The first row should contain headers, which label each column, making data
easy to understand and sort.
Avoid Merged Cells: Merged cells can disrupt data operations like sorting and filtering. Keep
cells in a straightforward grid layout.
Consistent Data Types: Ensure data in each column is consistent (e.g., only dates in a date
column, only numbers in a price column).

Example: (click on the given link

https://docs.google.com/spreadsheets/d/1pDxGR1o-
6zu3Tv0FnqL6khWB2dC_ywxTLN2EYS8C4aQ/edit?usp=sharing to open this data set in your
google sheets)
 Order ID Customer Product Quantity Order Date
Name

1001 John Smith Wireless Mouse 2 2024-10-01

1002 Jane Doe Laptop Stand 1 2024-10-02

1003 David Brown Keyboard 3 2024-10-02

1004 Emma Wilson Monitor 2 2024-10-03

1005 Michael Lee USB-C Hub 1 2024-10-03

1006 Sarah Kim Wireless Mouse 4 2024-10-04

1007 Chris Johnson Laptop Stand 1 2024-10-04

1008 Anna Garcia Monitor 2 2024-10-05

1009 James White Keyboard 1 2024-10-05

1010 Olivia Brown USB-C Hub 3 2024-10-06

1011 Liam Taylor Wireless Mouse 1 2024-10-06

1012 Sophia Miller Keyboard 2 2024-10-07

1013 Noah Davis Monitor 1 2024-10-07

1014 Mia Anderson Laptop Stand 2 2024-10-08

1015 Jacob Thomas USB-C Hub 3 2024-10-08

Suppose you’re organizing a dataset of customer orders. Structure it with the following columns:

Order ID: Unique identifier for each order

Customer Name: Name of the customer
Product: Name of the product
Quantity: Number of items ordered
Order Date: Date of the order

Now that you know Data Structing let’s see how you can filter your data
2. Filtering Data

Filtering allows you to view specific data that meets certain criteria without modifying the
dataset itself. In Google Sheets, filters are a way to quickly focus on subsets of your data.

How to Use Filters

1. Select Data Range: Highlight the range of data you want to filter, including headers.
2. Apply Filter: Go to Data > Create a Filter. A filter icon will appear next to each header.
3. Filter Data: Click the filter icon on a column header, and choose criteria for the data you
want to see. You can select specific values or apply conditions (e.g., “greater than,”
“contains,” etc.).

Example

In the "Quantity" column, you could set a filter to show only orders where the quantity is greater
than 1. This way, you’re focusing on larger orders without deleting any data.

3. Sorting Data
Sorting arranges data in a specific order, either ascending or descending. Sorting can help you
quickly find minimums, maximums, or alphabetical orders within your data.

How to Sort Data

1. Select Data Range: Highlight the data you want to sort, or simply click any cell within the
dataset if your data is structured.
2. Sort by Column:
Go to Data > Sort sheet by column (ascending or descending).
Alternatively, use Data > Sort range to sort only a specific range.

Example

To prioritize recent orders, you might sort by the "Order Date" column in descending order, so
the most recent dates appear first.

4. Basic Functions
Functions in Google Sheets allow you to perform calculations, manipulate data, and analyze
information quickly. Here are some foundational functions:

4.1. Arithmetic Functions

SUM: Adds up numbers within a specified range.

=SUM(value1, [value2, …])

AVERAGE: Calculates the average value of numbers in a range.

=AVERAGE(value1, [value2, …])

MIN and MAX: Finds the minimum or maximum value within a range.

=MIN(value1, [value2, …])

=MAX(value1, [value2, …])

4.2. Logical Functions

Logical functions allow you to set conditions and return results based on whether they are true
or false.
 IF: Tests a condition and returns one value if true, and another if false.

=IF(logical expression, value if true, value if false)

AND & OR: Used with IF to set multiple conditions.

=AND(logical_expression1, [logical_expression2, …])

Example: Find product with product name as “Monitor” and quantity more than 1.

=OR(logical_expression1, [logical_expression2, …])

Example: Find product with product name as “Monitor” or quantity more than 1.

4.4. Lookup Functions

Lookup functions help you find values within a table or dataset.

1) VLOOKUP: Searches for a value in the first column of a range and returns a value in the
same row from a specified column.

=VLOOKUP(search_key, range, index, [is_sorted])

Example:

find out what product was ordered in Order ID 1004. You can use the VLOOKUP function to
search for this Order ID in the dataset and return the product name.

Explanation

1004: This is the lookup value, the specific Order ID we want to find.
A2: This is the range of data we’re working with, from Order ID to Order Date.
3: This is the column index number, which tells VLOOKUP which column to return data from
within the specified range. Since Product is in the 3rd column of our range, we use 3 here.
FALSE: This argument specifies that we’re looking for an exact match.

Result: This formula will return "Monitor", the product associated with Order ID 1004.

2) HLOOKUP- The HLOOKUP function, or "Horizontal Lookup," searches for a value across the
top row of a range (rather than the first column, as VLOOKUP does). It’s typically used when
your data is organized horizontally.

=HLOOKUP(search_key, range, index, [is_sorted])

For this example, let’s imagine a transposed version of our dataset where the headers are
arranged vertically, and each column represents an attribute (Order ID, Customer Name,
Product, etc.).You can find this table in the same Transposed sheet. Link:
https://docs.google.com/spreadsheets/d/1pDxGR1o-
6zu3Tv0FnqL6khWB2dC_ywxTLN2EYS8C4aQ/edit?usp=sharing

Transposed Dataset Example

 Row # A B C D E F

1 Order ID 1001 1002 1003 1004 1005

2 Customer John Smith Jane Doe David Brown Emma Michael Lee
Name Wilson

3 Product Wireless Laptop Keyboard Monitor USB-C Hub

Mouse Stand

4 Quantity 2 1 3 2 1

5 Order Date 2024-10-01 2024-10-02 2024-10-02 2024-10-03 2024-10-03

Let’s say you want to find the Product for Order ID 1004 using HLOOKUP.

=HLOOKUP(1004, B1:F3, 3, FALSE)

Explanation

1004: This is the lookup value, the Order ID we’re searching for.
B1 : This is the range where we’re searching (from Order ID to Product).
3: This is the row index number in the range that contains the desired information. In this
case, 3 corresponds to the row with the Product data.
FALSE: This specifies an exact match.

Result: This formula returns "Monitor", which is the product associated with Order ID 1004.

Index-Match:

INDEX-MATCH is a powerful combination of two functions—INDEX and MATCH—used in

Google Sheets and Excel. Together, they allow you to perform flexible lookups in a dataset, with
the ability to search both horizontally and vertically. This combination is often preferred over
VLOOKUP because it doesn’t require the lookup column to be the first column in the range, and
it’s more adaptable to changes in your data structure.

How INDEX-MATCH Works

INDEX: This function returns the value of a cell in a specified range based on a given row
and column number.
MATCH: This function returns the position of a specific value within a range (either
vertically or horizontally), which can then be used to tell the INDEX function where to look.

INDEX-MATCH Syntax

1 INDEX Syntax:

INDEX(range, row_number, [column_number])

 range: The range of cells you want to search within.
row_number: The row number in the range from which to retrieve the value.
[column_number]: The column number in the range (optional).

2 MATCH Syntax:

MATCH(lookup_value, lookup_range, [match_type])

lookup_value: The value you want to find.

lookup_range: The range where you’re searching.
[match_type]: Use 0 for an exact match (most common).

3 Combined INDEX-MATCH Syntax:

INDEX(range, MATCH(lookup_value, lookup_range, 0), column_number)

Example 1: Find the Product for Order ID 1004

Let’s say you want to find out the Product for Order ID 1004.
Formula: =INDEX(C2:C7, MATCH(1004, A2:A7, 0))

Explanation:
C2: C16 is the range containing Product data (our target range).
MATCH(1004, A2:A16, 0) finds the position of 1004 in Order ID column A2:A7.
A2:A7 is the Order ID column.
1004 is the lookup_value.
0 specifies an exact match.
Result: The formula returns "Monitor".

Now that we are familiar with some of the functions let’s us see a key concept of google sheets
which is Pivot Table:

What is Pivot Table:

A Pivot Table is a powerful tool in spreadsheet software like Google Sheets that helps you
summarize, analyze, and explore large datasets quickly and easily. It allows you to reorganize and
manipulate data, creating customized views that highlight important information and reveal
trends without altering the original dataset.

Key Features of a Pivot Table:

Data Summarization: Pivot Tables can group data by categories and summarize it through
various calculations, such as sum, average, count, min, and max.
Filtering: You can filter data to display only the information you need.
Sorting: Data can be sorted based on specific fields to make patterns more visible.
Grouping: You can group data by categories, such as by date or region, to analyze it in
different ways.

Let’s Understand How you can use Pivot Table With the help of an Example:

Imagine the Dataset that we were working on

https://docs.google.com/spreadsheets/d/1pDxGR1o-
6zu3Tv0FnqL6khWB2dC_ywxTLN2EYS8C4aQ/edit?usp=sharing in that we need
to find the total quantity of each product and from that product with maximum
quantity.

This can be easily done using Pivot table lets look at the steps:

1. select complete dataset

2. Go to insert option
3. click on Pivot table
4. Drag and drop product name in rows and Quantity in values.
5. Set order to descending and sort by to sum of Quantity.
6. Top product is your product with most quantity.
Apply your Knowledge to test

Now that you know how to work with pivot table open the provided link:  Pivot tables 

You have a dataset with columns like Order ID, Customer Name, Product, Quantity, and Order
Date. With a Pivot Table, NEED TO FIND THE FOLLOWING:

“What’s the total quantity of each product sold?”

“How many orders did each customer place?”
“What’s the average quantity ordered by date?”

Why Use Pivot Tables?

Quick Analysis: They make it easy to summarize data and spot patterns.
Data Exploration: You can dynamically change what data is displayed to answer different
questions.
Automation: Once set up, a Pivot Table automatically updates when the source data
changes.

Pivot Tables are ideal for those who work with large datasets and want to make sense of the data
efficiently. They’re a key feature for anyone doing data analysis or managing business metrics.

Putting It All Together

Using these tools, you can structure your data in a clean format, apply filters to narrow down
information, sort data for better organization, and utilize functions to perform calculations and
manipulate data. These basics provide a solid foundation for using Google Sheets effectively.