A Complete Guide to Google Sheets Formulas for Work and Interviews

15 Advanced Google Sheets Formulas for Data Analysts

Mastering Google Sheets: 15 Advanced Formulas Every Data Analyst Must Know (With Real Examples)

Pro Tip: These formulas are useful in real-world data analysis and often asked in interviews. Bookmark this guide for future reference.

Table of Contents

  • SUMIF / SUMIFS
  • COUNTIF / COUNTIFS
  • VLOOKUP / XLOOKUP
  • INDEX + MATCH
  • FILTER
  • QUERY
  • ARRAYFORMULA
  • TEXT & DATE Functions
  • IF / IFS / SWITCH
  • Text Functions
  • UNIQUE / SORT / SORTN
  • IS Functions
  • IFERROR
  • REGEX Functions
  • SPARKLINE

1. SUMIF / SUMIFS – Conditional Totals

When to use: Use SUMIF when you want to add up numbers in one range based on a single condition applied to another range. Use SUMIFS when you have multiple conditions to meet.

Example: If you want to calculate the total sales where the region is "East", you can use:

=SUMIF(B:B, "East", A:A)

This sums all values in column A only when the corresponding value in column B is "East".

Example with multiple conditions: To sum sales where the region is "East" and the product is "TV", use:

=SUMIFS(A:A, B:B, "East", C:C, "TV")

This adds values in column A where column B is "East" and column C is "TV".

2. COUNTIF / COUNTIFS – Count With Conditions

When to use: Use COUNTIF to count how many cells meet a single criterion. Use COUNTIFS to count how many rows meet multiple criteria.

Example: To count how many sales values are greater than 500:

=COUNTIF(B:B, ">500")

This counts all cells in column B where the value is greater than 500.

Multiple criteria example: To count rows where the region is "West" and sales are greater than 1000:

=COUNTIFS(B:B, "West", C:C, ">1000")

This counts rows that satisfy both conditions simultaneously.

3. VLOOKUP / XLOOKUP – Vertical Data Lookup

When to use: Use VLOOKUP to find a value in the first column of a range and return a value from a specified column in the same row. XLOOKUP is a more flexible and modern alternative.

Example: To find the price of "Product A" in a list:

=VLOOKUP("Product A", A2:B10, 2, FALSE)

This looks for "Product A" in the first column of the range A2:B10 and returns the corresponding price from the second column.

Using XLOOKUP:

=XLOOKUP("Product A", A2:A10, B2:B10, "Not Found")

This searches for "Product A" in the range A2:A10 and returns the corresponding value from B2:B10. If not found, it returns "Not Found".

4. INDEX + MATCH – More Flexible Lookup

When to use: Use INDEX + MATCH when you want a more flexible alternative to VLOOKUP, such as looking up values to the left or dynamically specifying the return column.

Example: To return a value from column C that corresponds to "Product A" in column A:

=INDEX(C2:C10, MATCH("Product A", A2:A10, 0))

MATCH finds the row number of "Product A" and INDEX returns the value from column C in that row.

5. FILTER – Dynamic Row Filtering

When to use: Use FILTER to extract rows from a dataset that meet certain conditions, dynamically updating as the data changes.

Example: To get all rows where sales are greater than 1000:

=FILTER(A2:C100, C2:C100 > 1000)

This returns all rows from columns A to C where the value in column C exceeds 1000.

6. QUERY – SQL-Like Data Analysis

When to use: Use QUERY to perform powerful, SQL-style data manipulations within Google Sheets, such as grouping, filtering, and sorting.

Example: To calculate total sales per region:

=QUERY(A1:C, "SELECT B, SUM(C) GROUP BY B", 1)

This groups data by the region (column B) and sums the sales values (column C).

Filtering example: To display only rows where sales are greater than 500:

=QUERY(A1:C, "SELECT * WHERE C > 500", 1)

This shows all columns for rows where sales (column C) are greater than 500.

7. ARRAYFORMULA – Apply Formula to Whole Columns

When to use: Use ARRAYFORMULA to apply a formula to an entire range or column, avoiding the need to drag formulas down manually.

Example: To multiply all values in column A by 10%:

=ARRAYFORMULA(A2:A * 0.1)

This applies the multiplication to every cell in the range A2 downwards.

Another example: To combine first and last names from columns A and B:

=ARRAYFORMULA(A2:A & " " & B2:B)

This concatenates corresponding cells from both columns into full names.

8. TEXT & DATE Functions

When to use: Use these functions to format dates or extract parts of dates for reporting or analysis.

  • =TEXT(A2, "MMMM YYYY") – Converts a date in A2 to a readable format like "July 2025".
  • =MONTH(A2) – Extracts the month number from a date in A2 (1 for January, 2 for February, etc.).

9. IF / IFS / SWITCH – Conditional Logic

When to use: Use these functions to perform logical tests and return values based on conditions.

Example: To label sales as "High" if greater than 1000, otherwise "Low":

=IF(A2 > 1000, "High", "Low")

Multiple conditions example: To categorize sales into "High", "Medium", or "Low":

=IFS(A2 > 1000, "High", A2 > 500, "Medium", TRUE, "Low")

SWITCH example: To convert codes to text labels:

=SWITCH(A2, "A", "Alpha", "B", "Beta", "Unknown")

This returns "Alpha" if A2 is "A", "Beta" if "B", otherwise "Unknown".

10. Text Functions (SPLIT, LEFT, TRIM, UPPER/LOWER)

FunctionPurposeExample
SPLITSplits text into separate parts based on a delimiter=SPLIT(A2, " ")
LEFTExtracts a specified number of characters from the start of text=LEFT(A2, 5)
TRIMRemoves extra spaces from text=TRIM(A2)
UPPER/LOWERConverts text to uppercase or lowercase=UPPER(A2)

11. UNIQUE / SORT / SORTN – Organize Data

When to use: Use these to get unique values, sort data, or retrieve the top N rows.

Example: To get unique values from a list:

=UNIQUE(A2:A)

Sort data by column B in descending order:

=SORT(A2:B, 2, FALSE)

Get top 5 products by sales:

=SORTN(A2:B, 5, 0, 2, FALSE)

12. IS Functions – Data Validation Checks

FunctionDescription
ISNUMBERReturns TRUE if the value is a number
ISTEXTReturns TRUE if the value is text
ISBLANKReturns TRUE if the cell is empty
ISERRORReturns TRUE if the value contains an error

13. IFERROR – Graceful Error Handling

When to use: Use IFERROR to prevent your sheet from showing error messages and instead display a custom message or value.

Example: To divide A2 by B2 but show "Error" if B2 is zero or empty:

=IFERROR(A2/B2, "Error")

14. REGEX Functions – Extract or Validate Text Patterns

FunctionPurposeExample
REGEXEXTRACTExtracts matching text based on a pattern=REGEXEXTRACT(A2, "\d+")
REGEXREPLACEReplaces matching text based on a pattern=REGEXREPLACE(A2, "[0-9]", "")
REGEXMATCHReturns TRUE if text matches a pattern=REGEXMATCH(A2, "invoice")

15. SPARKLINE – Mini Chart Inside a Cell

When to use: Use SPARKLINE to visually summarize data trends within a single cell, ideal for dashboards.

Example: To create a small line chart for sales data in columns B to F:

=SPARKLINE(B2:F2)

Final Interview Tips:

  • Explain your formulas clearly and logically during interviews.
  • Demonstrate advanced skills with QUERY and ARRAYFORMULA.
  • Show data cleaning and preparation using TEXT and REGEX functions.
  • Build dynamic dashboards using SPARKLINE, FILTER, and SORT.

Want More?

If you want a free cheat sheet PDF or a mock dataset to practice these formulas, leave a comment below or email me at [your-email].

Comments

Post a Comment

Popular posts from this blog

Class 12 Physics: Electric Charge & Electronics

Image
  Electric Charge and Material Science   Fundamentals  “From Subatomic Particles to Semiconductors” 🌟 1. What Is Electric Charge? Electric charge is one of the most fundamental properties of matter. It is responsible for creating electric and magnetic fields , which in turn produce electromagnetic force —one of the four fundamental forces of nature (along with gravity, strong nuclear, and weak nuclear forces). Every object in the universe is made up of atoms, and these atoms consist of subatomic particles: Protons (positively charged) Electrons (negatively charged) Neutrons (no charge) The net electric charge of an object determines how it interacts with other charged particles. If an object has more electrons than protons, it is negatively charged . If it has more protons, it is positively charged . If both are equal, it is neutral . 🔹 2. Types of Electric Charge There are two types of electric charge : 🧲 Positive Charge: Carried by pr...
Read more

PCM in Digital Communication: Understanding Sampling, Quantization & Encoding

Image
  Pulse Code Modulation (PCM) In this blog, we’ll explore the fundamentals of Pulse Code Modulation (PCM), a widely used technique for converting analog signals into digital form. PCM is the backbone of many digital communication systems, including audio, telephony, and media streaming. PCM converts the continuous-time, continuous-amplitude analog signals into discrete-time, discrete-amplitude digital signals, allowing for more reliable and noise-resistant communication compared to traditional analog methods. The PCM process involves three key steps at the transmitter side: sampling, quantization, and encoding. By converting signals into digital bits, PCM facilitates error detection, correction, and encryption, making it indispensable in modern communication systems such as telephony, broadcasting, and digital audio We'll break down its core step i.e the processes that are involved at the transmitter end — Sampling, Quantization, and Encoding —to understand how continuous sig...
Read more

P- N JUNCTION DIODE P-N - Class 12 Physics Semiconductor Chapter

Image
  Diode or P-N Junction Theory Explained Introduction A diode is a fundamental component in electronics, widely used for its unidirectional current-carrying property. At the core of its operation lies the P-N junction —a boundary or interface between p-type and n-type semiconductors. Understanding the theory of the P-N junction, including forward and reverse biasing, depletion regions, and V-I characteristics, is crucial for anyone learning electronics or semiconductor physics. This blog explains the theory behind the P-N junction and how diodes work under various conditions, with all the important parameters and equations included.  Table of Contents What is a Diode ? Formation of P-N Junction Depletion Layer Potential Barrier and Electric Field Forward and Reverse Biasing Forward Bias Reverse Bias V-I Characteristic of Diode Ideal vs Practical Diode Conclusion What is a Diode? A diode is a non-linear , active , and unidirectional device. It allows current to flow in one dir...
Read more

Getting Started with Python: Key Concepts and Beginner-Friendly Examples

  Introduction Python is an interpreted (i.e it does not need to be compiled before execution),object-oriented , high-level programming language known for its simplicity, readability, and versatility or flexibility.Python is also a dynamic language which means that the type of variable is not determined until it is assigned a value. Python has a large number of libraries(Libraries are like a toolbox, adding features to  existing code) and frameworks(frameworks provide a structure for building new applications) . Python is used in web development, data science, artificial intelligence ,machine learning , game development, CAD. In this blog, we will explore key Python concepts, differences between versions, and hands-on code examples, These examples can be practiced using platforms like Google Colab, Spyder, or even simple editors like VB (for those transitioning from other languages). Table of Contents Why is Python  considered a high-level programming language? Difference...
Read more