Master Advanced Google Sheets and Excel Formulas: Complete Guide with Practice Exercises and Sample Data

Advanced Google Sheets Practice Exercises for Data Analysts

Advanced Google Sheets Practice Exercises for Data Analysts

This guide contains practice exercises to master advanced Google Sheets formulas. Each formula is explained with multiple practice tasks progressing from basic to advanced, plus examples on how to combine formulas for powerful data analysis. Click the links to open ready-to-use Google Sheets for hands-on practice.

1. Advanced SUMIFS with Multiple Criteria & Date Ranges

Formula Syntax:

=SUMIFS(AmountRange, RegionRange, "North", CategoryRange, "Electronics", DateRange, ">=2025-07-01", DateRange, "<=2025-07-31")

Practice Questions:

  • Sum all sales where Region is "North" and Category is "Electronics" in July 2025.
  • Sum sales for Region "South" or "East" in the first quarter of 2025.
  • Calculate total sales for "Furniture" category where sale date is before 2025-06-01 and amount is greater than 1000.
Interconnected Use Case: Use SUMIFS with TEXT to sum sales by month name dynamically: 
=SUMIFS(AmountRange, RegionRange, "North", TEXT(DateRange, "MMMM"), "July")

Open Practice Sheet for SUMIFS

2. COUNTIFS with Partial Text Matching & Dates

Formula Syntax:

=COUNTIFS(RegionRange, "South", ProductRange, "*Phone*", DateRange, ">2025-07-05")

Practice Questions:

  • Count sales where Region is "South" and Product contains "Phone" after July 5, 2025.
  • Count how many orders were placed in "West" region for products starting with "T" in August 2025.
  • Count the number of sales with amount greater than 2000 where the product name contains either "Laptop" or "Tablet".
Interconnected Use Case: Combine COUNTIFS with REGEXMATCH inside ARRAYFORMULA to count cells matching complex patterns. 
=SUM(ARRAYFORMULA(REGEXMATCH(ProductRange, "Phone|Tablet") * (RegionRange="South")))

Open Practice Sheet for COUNTIFS

3. XLOOKUP with Multiple Return Values (Spill Array)

Formula Syntax:

=XLOOKUP("Product B", ProductRange, CHOOSE({1,2}, PriceRange, CategoryRange), "Not Found")

Practice Questions:

  • Find price and category of "Product B".
  • Lookup "Product C" and return price, category, and supplier name.
  • Use XLOOKUP to return all details (multiple columns) for a product, and handle cases where product is missing by returning a default message.
Interconnected Use Case: Combine XLOOKUP with IFERROR to prevent errors and return clean outputs: 
=IFERROR(XLOOKUP(ProductID, ProductList, PriceList), "Price Not Found")

Open Practice Sheet for XLOOKUP

4. INDEX + MATCH with Approximate Match for Tiered Pricing

Formula Syntax:

=INDEX(DiscountsRange, MATCH(SalesVolume, VolumeThresholds, 1))

Practice Questions:

  • Find discount percentage for a sales volume using approximate match.
  • Use INDEX + MATCH to lookup commission rates based on sales tiers.
  • Combine INDEX + MATCH with IFERROR to provide default discount when volume is below thresholds.
Interconnected Use Case: Use INDEX + MATCH with ARRAYFORMULA to apply tiered discounts to a list of sales volumes in one formula: 
=ARRAYFORMULA(IFERROR(INDEX(DiscountsRange, MATCH(SalesVolumesRange, VolumeThresholds, 1)), 0))

Open Practice Sheet for INDEX + MATCH

5. FILTER with Multiple OR Conditions

Formula Syntax:

=FILTER(DataRange, ((RegionRange = "North") + (RegionRange = "West")) * (AmountRange > 1000))

Practice Questions:

  • Filter rows where Region is "North" or "West" and sales amount is greater than 1000.
  • Filter data for Region "East" or products starting with "S" and amount less than 500.
  • Use FILTER combined with ISNUMBER to get only numeric sales amounts from mixed data.
Interconnected Use Case: Combine FILTER with ARRAYFORMULA and REGEXMATCH for advanced filtering based on text patterns: 
=FILTER(DataRange, ARRAYFORMULA(REGEXMATCH(ProductRange, "TV|Laptop")) * (AmountRange > 1000))

Open Practice Sheet for FILTER

6. QUERY with Date Filtering and Aggregation

Formula Syntax:

=QUERY(DataRange, "SELECT B, SUM(A) WHERE C > date '2025-07-10' GROUP BY B", 1)

Practice Questions:

  • Get total sales per region for sales after July 10, 2025.
  • Retrieve all rows where sales amount is above 1000 and product category is "Electronics".
  • Use QUERY to list distinct products and their average sales.
Interconnected Use Case: Use QUERY output as input for FILTER or VLOOKUP for multi-step data analysis: 
=FILTER(OriginalDataRange, OriginalDataRange[Product] = QUERY(DataRange, "SELECT Product WHERE ...", 1))

Open Practice Sheet for QUERY

7. ARRAYFORMULA with Conditional Logic

Formula Syntax:

=ARRAYFORMULA(IF(AmountRange > 1000, "High", "Low"))

Practice Questions:

  • Label sales as "High" if amount > 1000, otherwise "Low".
  • Create a new column that shows "Yes" if product category is "Electronics" and amount > 1500, else "No".
  • Combine ARRAYFORMULA with IF and ISBLANK to handle empty cells gracefully.
Interconnected Use Case: Use ARRAYFORMULA with VLOOKUP to apply lookups across entire columns without dragging formulas: 
=ARRAYFORMULA(IF(ISBLANK(ProductRange), "", VLOOKUP(ProductRange, PriceTable, 2, FALSE)))

Open Practice Sheet for ARRAYFORMULA

8. TEXT & DATE Functions for Custom Reports

Formula Syntax:

=TEXT(DateCell, "MMMM YYYY")

Practice Questions:

  • Convert date to "July 2025" format.
  • Extract day of week from a date.
  • Combine TEXT with CONCATENATE to create a full report title with dynamic month and year.
Interconnected Use Case: Use TEXT inside QUERY or FILTER to dynamically filter by month: 
=QUERY(DataRange, "SELECT * WHERE TEXT(DateColumn, 'MMMM') = 'July'")

Open Practice Sheet for TEXT & DATE Functions

9. IFS with Multiple Conditions and Default

Formula Syntax:

=IFS(Amount < 500, "Low", Amount <= 1500, "Medium", TRUE, "High")

Practice Questions:

  • Categorize sales amounts as "Low", "Medium", or "High" based on thresholds.
  • Extend IFS to include an "Exceptional" category for amounts greater than 3000.
  • Combine IFS with AND and OR for complex conditions.
Interconnected Use Case: Use IFS inside ARRAYFORMULA to categorize an entire column dynamically: 
=ARRAYFORMULA(IFS(AmountRange < 500, "Low", AmountRange <= 1500, "Medium", TRUE, "High"))

Open Practice Sheet for IFS

10. SPLIT + TRIM to Clean & Separate Names

Formula Syntax:

=ARRAYFORMULA(TRIM(SPLIT(FullNameRange, " ")))

Practice Questions:

  • Split full names into first and last names and remove extra spaces.
  • Extract middle names where available.
  • Combine SPLIT with JOIN to reformat names as "Last, First".
Interconnected Use Case: Use SPLIT and ARRAYFORMULA to clean a list of full names and then use VLOOKUP on first names.

Open Practice Sheet for SPLIT + TRIM

11. UNIQUE + SORTN to Find Top N Values Without Duplicates

Formula Syntax:

=SORTN(UNIQUE(SalesRange), 3, 0, 1, FALSE)

Practice Questions:

  • Get top 3 unique sales amounts.
  • Find top 5 selling products without duplicates.
  • Combine UNIQUE with FILTER to find top values in specific regions.
Interconnected Use Case: Use UNIQUE inside QUERY to create dynamic dropdowns of distinct product names for data validation.

Open Practice Sheet for UNIQUE + SORTN

12. IS Functions to Identify and Count Data Types

Formula Syntax:

=COUNTIF(ARRAYFORMULA(ISNUMBER(Range)), TRUE)

Practice Questions:

  • Count how many cells contain numbers.
  • Count non-empty text cells.
  • Use ISBLANK with COUNTIF to count empty cells.
Interconnected Use Case: Combine ISNUMBER with FILTER to extract only numeric entries from mixed data sets.

Open Practice Sheet for IS Functions

13. IFERROR to Avoid #DIV/0! Errors

Formula Syntax:

=IFERROR(SalesCell / UnitsCell, "N/A")

Practice Questions:

  • Calculate sales per unit and display "N/A" if units sold is zero or blank.
  • Use IFERROR to clean up VLOOKUP errors when a lookup value is missing.
  • Combine IFERROR with nested formulas to handle multiple error types gracefully.
Interconnected Use Case: Wrap complex formulas with IFERROR to maintain clean dashboards and reports without error clutter.

Open Practice Sheet for IFERROR

14. VLOOKUP for Vertical Lookup

Formula Syntax:

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

Practice Questions:

  • Find the price of "Product A" using VLOOKUP.
  • Lookup the category of a product using its name.
  • Use VLOOKUP with IFERROR to display "Not Found" if product doesn't exist.
Interconnected Use Case: Use ARRAYFORMULA with VLOOKUP to apply lookup across a full column: 
=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, ProductTable, 2, FALSE)))

Open Practice Sheet for VLOOKUP

15. HLOOKUP for Horizontal Lookup

Formula Syntax:

=HLOOKUP("Q2", A1:D3, 2, FALSE)

Practice Questions:

  • Get revenue value from Q2 row.
  • Use HLOOKUP to pull forecast values based on quarter headers.
  • Combine HLOOKUP with IF to choose between actual and forecasted values.
Interconnected Use Case: Pair HLOOKUP with MATCH to dynamically locate row numbers based on headers.

Open Practice Sheet for HLOOKUP

16. OFFSET + MATCH for Dynamic Range Selection

Formula Syntax:

=SUM(OFFSET(B2, MATCH("Region B", A2:A10, 0)-1, 1, 1, 3))

Practice Questions:

  • Use OFFSET to dynamically sum a row of quarterly data based on region name.
  • Create a dynamic reference that shifts 2 rows down and 1 column right.
  • Use OFFSET in data validation to create expandable dropdowns.
Interconnected Use Case: Use OFFSET with COUNTA for dynamic ranges that grow with data. 
=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

Open Practice Sheet for OFFSET

17. INDIRECT for Dynamic Cell References

Formula Syntax:

=INDIRECT("B" & A1)

Practice Questions:

  • Use INDIRECT to reference a cell dynamically using row number in another cell.
  • Link to a named range or cell on another sheet using INDIRECT.
  • Combine INDIRECT with R1C1 referencing style.
Interconnected Use Case: Combine INDIRECT with DATA VALIDATION to create dynamic dependent dropdowns.

Open Practice Sheet for INDIRECT

18. LET Function for Named Variables in Formulas (Excel Only)

Formula Syntax:

=LET(x, A1*2, y, A2+3, x+y)

Practice Questions:

  • Use LET to define variables for intermediate calculations.
  • Improve readability by replacing repeated expressions with LET variables.
  • Combine LET with LAMBDA for custom reusable functions (Excel only).
Interconnected Use Case: Use LET inside large formulas like XLOOKUP or IF to simplify structure and improve performance.

Open Practice Sheet for LET

14. VLOOKUP – Search Vertically in a Table

Formula Syntax:

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

Explanation:
VLOOKUP searches for a value in the **first column** of a table and returns a value from the same row in a different column.

Practice Questions:

  • Find the price of "Product A" using VLOOKUP.
  • Look up a product's category using its name.
  • Use VLOOKUP with IFERROR to avoid showing errors if data is missing.
Pro Tip: Use VLOOKUP with ARRAYFORMULA to search through multiple rows at once: 
=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, ProductTable, 2, FALSE)))

Open Practice Sheet for VLOOKUP

15. HLOOKUP – Search Horizontally in a Row

Formula Syntax:

=HLOOKUP("Q2", A1:D3, 2, FALSE)

Explanation:
HLOOKUP searches for a value in the **top row** of a table and returns data from a row underneath.

Practice Questions:

  • Get Q2 revenue from a horizontal table of quarters.
  • Pull monthly targets based on quarter names.
  • Use HLOOKUP with IF to show forecast if actual is missing.
Pro Tip: Combine HLOOKUP with CHOOSE to create flexible row lookups for reports.

Open Practice Sheet for HLOOKUP

16. XLOOKUP – Modern & Flexible Lookup

Formula Syntax:

=XLOOKUP("Product B", ProductRange, PriceRange, "Not Found")

Explanation:
XLOOKUP searches for a value in one column and returns a value from another column — left or right, top or bottom. It's easier and more flexible than VLOOKUP.

Practice Questions:

  • Find price and category of "Product B".
  • Return multiple columns like price, category, and supplier.
  • Use XLOOKUP with a default message for missing data.
Pro Tip: Use CHOOSE inside XLOOKUP to return multiple values in one formula: 
=XLOOKUP("Product B", A2:A10, CHOOSE({1,2}, B2:B10, C2:C10), "Not Found")

Open Practice Sheet for XLOOKUP

17. LOOKUP – Simple Lookup & Last Value Finder

Formula Syntax:

=LOOKUP(500, SalesRange, DiscountRange)

Explanation:
LOOKUP is used to find the **closest match** in a sorted range, or to return the **last non-empty value** in a list.

Practice Questions:

  • Find discount for a sale of $500 using approximate match.
  • Get the last value in a column using LOOKUP.
  • Compare LOOKUP with VLOOKUP or INDEX+MATCH for better control.
Pro Tip: Use this trick to return the last non-blank cell in column A: 
=LOOKUP(2,1/(A:A<>""),A:A)

Open Practice Sheet for LOOKUP

Practice these exercises by clicking the links to the Google Sheets datasets where you can see sample data and formulas pre-built. You can make copies to modify and extend for your own learning.

Comments

Anonymous said…
Helpful
August 5, 2025 at 7:34 PM

Post a Comment

Popular posts from this blog

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

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

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

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