Lab 1: Excel Basics with Product Inventory Dataset

Master essential Excel formulas, conditional logic, and VLOOKUP with hands-on practice

Important: Excel vs Google Sheets

This lab can be completed using either Microsoft Excel (desktop application) or Google Sheets (web-based). Most formulas work the same way, but there are some differences in formatting and array formulas:

Key Differences:

Feature	Microsoft Excel	Google Sheets
Format Cells	Right-click → Format Cells → Currency	Toolbar “123” dropdown → Currency, or Format → Number → Currency
Array Formulas	Requires Ctrl+Shift+Enter (Windows) or Cmd+Shift+Enter (Mac)	Works automatically, no special keystroke needed
Keyboard Shortcut for Currency	Ctrl+Shift+4 (Windows) or Cmd+Shift+4 (Mac)	Same: Ctrl+Shift+4 (Windows) or Cmd+Shift+4 (Mac)

Instructions Format:

Throughout this lab, instructions will be shown as:

Excel: [Microsoft Excel instructions]
Google Sheets: [Google Sheets instructions]

If instructions are the same for both, they will be shown without labels.

Dataset Overview

Introduction

This lab uses a Product Inventory dataset - a simplified, single-table dataset designed to help you master Excel basics. Unlike complex datasets with thousands of rows, this inventory dataset is manageable and allows you to verify your formulas manually.

Note: If you’re using Google Sheets, you can upload the Product-Inventory.xlsx file to Google Drive and open it with Google Sheets. Google Sheets will automatically convert the Excel file format.

Dataset Structure

The workbook contains two sheets:

Inventory Sheet (Main data table)
- Contains 50 products with the following columns:
  - Product ID: Unique identifier (e.g., PRD-001, PRD-002)
  - Product Name: Name of the product
  - Category: Product category (Electronics, Clothing, Food, Books, Sports)
  - Supplier Code: Supplier identifier (SUP-A, SUP-B, SUP-C, SUP-D)
  - Unit Price: Price per unit (in dollars)
  - Quantity in Stock: Current inventory quantity
  - Reorder Level: Minimum quantity before reordering is needed
  - Supplier Name: Full supplier name
Suppliers Sheet (Lookup table)
- Contains supplier information for VLOOKUP exercises:
  - Supplier Code: Supplier identifier
  - Supplier Name: Full supplier name

Learning Objectives

By completing this lab, you will:

Master basic Excel formulas (SUM, AVERAGE, COUNT, MAX, MIN)
Understand conditional logic (IF, COUNTIF)
Learn VLOOKUP for data lookup operations
Distinguish between relative and absolute cell references
Apply formulas to answer real business questions

Function Reference: Detailed Explanations

This section provides comprehensive explanations of all functions used in this lab. Refer to this section when you need to understand how a function works, its syntax, parameters, and usage.

Basic Aggregation Functions

SUM Function

Purpose: Adds all numbers in a specified range or set of cells.

Syntax:

=SUM(number1, [number2], [number3], ...)
=SUM(range)
=SUM(range1, range2, ...)

Parameters:

number1, number2, ...: Individual numbers or cell references to add
range: A range of cells (e.g., E2:E51)

Examples:

=SUM(E2:E51) - Sums all values in cells E2 through E51
=SUM(10, 20, 30) - Returns 60
=SUM(E2:E51, F2:F51) - Sums values from two different ranges

Notes:

SUM ignores text and empty cells
Can sum up to 255 individual arguments
Works with arrays: =SUM(E2:E51*F2:F51) multiplies corresponding cells then sums results

Use Cases:

Calculate total sales, total inventory value, total expenses
Sum values across multiple ranges
Perform calculations within the SUM function

AVERAGE Function

Purpose: Calculates the arithmetic mean (average) of a set of numbers.

Syntax:

=AVERAGE(number1, [number2], [number3], ...)
=AVERAGE(range)

Parameters:

number1, number2, ...: Individual numbers or cell references
range: A range of cells containing numbers

Examples:

=AVERAGE(E2:E51) - Calculates average of all values in E2:E51
=AVERAGE(10, 20, 30, 40) - Returns 25 (100/4)

Notes:

Ignores empty cells and text values
AVERAGE = SUM of values / COUNT of numeric values
Returns #DIV/0! if no numeric values found

Use Cases:

Find average price, average sales, average score
Compare individual values to the average
Calculate central tendency for data analysis

COUNT Function

Purpose: Counts the number of cells in a range that contain numbers.

Syntax:

=COUNT(value1, [value2], ...)
=COUNT(range)

Parameters:

value1, value2, ...: Individual values or cell references
range: A range of cells to count

Examples:

=COUNT(E2:E51) - Counts how many cells in E2:E51 contain numbers
=COUNT(A1:A10) - If A1:A10 has 7 numbers and 3 text values, returns 7

Notes:

Only counts numeric values (numbers, dates, times)
Ignores text, logical values (TRUE/FALSE), and empty cells
Use COUNTA if you want to count all non-empty cells including text

Use Cases:

Count numeric entries in a dataset
Count how many products have prices
Count completed entries in a form

COUNTA Function

Purpose: Counts the number of cells in a range that are not empty (counts numbers, text, dates, logical values, and errors).

Syntax:

=COUNTA(value1, [value2], ...)
=COUNTA(range)

Parameters:

value1, value2, ...: Individual values or cell references
range: A range of cells to count

Examples:

=COUNTA(A2:A51) - Counts all non-empty cells in A2:A51 (including text like Product IDs)
=COUNTA(A1:B10) - Counts all non-empty cells in the range A1:B10

Notes:

Counts any non-empty cell: numbers, text, dates, TRUE/FALSE, error values
Only ignores truly empty cells
Use this when you need total count including text values

Use Cases:

Count total number of products (including text IDs)
Count total entries in a form (regardless of data type)
Count all non-blank cells in a dataset

MAX Function

Purpose: Returns the largest (maximum) value from a set of numbers.

Syntax:

=MAX(number1, [number2], ...)
=MAX(range)

Parameters:

number1, number2, ...: Individual numbers or cell references
range: A range of cells containing numbers

Examples:

=MAX(E2:E51) - Returns the highest value in range E2:E51
=MAX(10, 25, 5, 30) - Returns 30

Notes:

Ignores text and empty cells
Works with dates and times (returns most recent date/time)
Returns 0 if no numbers found (not an error)

Use Cases:

Find highest price, maximum sales, peak value
Identify outliers or extremes in data
Compare values to maximum

MIN Function

Purpose: Returns the smallest (minimum) value from a set of numbers.

Syntax:

=MIN(number1, [number2], ...)
=MIN(range)

Parameters:

number1, number2, ...: Individual numbers or cell references
range: A range of cells containing numbers

Examples:

=MIN(E2:E51) - Returns the lowest value in range E2:E51
=MIN(10, 25, 5, 30) - Returns 5

Notes:

Ignores text and empty cells
Works with dates and times (returns earliest date/time)
Returns 0 if no numbers found (not an error)

Use Cases:

Find lowest price, minimum sales, lowest score
Identify minimum threshold values
Calculate range: MAX - MIN

Conditional Functions

IF Function

Purpose: Performs a logical test and returns one value if TRUE, another if FALSE.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Parameters:

logical_test: A condition that evaluates to TRUE or FALSE (e.g., F2<G2, A1>100)
value_if_true: Value returned if condition is TRUE
value_if_false: Value returned if condition is FALSE

Examples:

=IF(F2<G2, "Reorder", "OK") - Returns “Reorder” if F2 is less than G2, otherwise “OK”
=IF(E2>100, "Expensive", "Affordable") - Returns “Expensive” if price > 100
=IF(A1="Yes", 1, 0) - Returns 1 if A1 contains “Yes”, otherwise 0

Nested IF Examples:

=IF(J2>5000, "High", IF(J2>2000, "Medium", "Low")) - Three-level classification
- If J2 > 5000, returns “High”
- Else if J2 > 2000, returns “Medium”
- Else returns “Low”

Notes:

Can nest up to 64 IF functions (though readability decreases with more nesting)
Logical operators: =, <, >, <=, >=, <>
Can use AND/OR functions for complex conditions: =IF(AND(A1>10, B1<20), "OK", "No")

Use Cases:

Categorize data (High/Medium/Low, Pass/Fail)
Flag conditions (Reorder needed, Alert, Warning)
Conditional calculations based on criteria

COUNTIF Function

Purpose: Counts the number of cells within a range that meet a single criterion.

Syntax:

=COUNTIF(range, criteria)

Parameters:

range: The range of cells to evaluate
criteria: The condition that defines which cells to count (can be number, expression, text, or cell reference)

Examples:

=COUNTIF(C2:C51, "Electronics") - Counts cells containing exactly “Electronics”
=COUNTIF(E2:E51, ">100") - Counts cells greater than 100
=COUNTIF(I2:I51, "Reorder") - Counts cells containing “Reorder”
=COUNTIF(C2:C51, A10) - Counts cells matching value in cell A10

Criteria Examples:

Text: "Electronics", "High"
Number: 100
Comparison: ">100", "<50", ">=200"
Wildcards: "*Electronics*" (contains Electronics), "E*" (starts with E)
Cell reference: A10 (uses value in A10)

Notes:

Criteria must be in quotes for text and operators (“>100”)
Use cell references (A10) to make formulas dynamic
Case-sensitive for text (in Excel, not in Google Sheets by default)

Use Cases:

Count how many products match a category
Count items above/below threshold
Count occurrences of specific values
Frequency analysis

COUNTIFS Function

Purpose: Counts the number of cells that meet multiple criteria across different ranges.

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters:

criteria_range1: First range to evaluate
criteria1: First condition
criteria_range2, criteria2, ...: Additional range/criteria pairs (up to 127 pairs)

Examples:

=COUNTIFS(C2:C51, "Electronics", I2:I51, "Reorder") - Counts products that are Electronics AND need Reorder
=COUNTIFS(E2:E51, ">100", F2:F51, "<50") - Counts where price > 100 AND quantity < 50

Notes:

All criteria must be TRUE (AND logic)
Each criteria_range must have the same number of rows/columns
Can combine different types of criteria (text, numbers, comparisons)

Use Cases:

Count items meeting multiple conditions
Multi-dimensional filtering and counting
Complex conditional counting

SUMIF Function

Purpose: Sums the values in a range that meet a single criterion.

Syntax:

=SUMIF(range, criteria, [sum_range])

Parameters:

range: Range of cells to evaluate against criteria
criteria: Condition that defines which cells to sum
sum_range: (Optional) Range of cells to sum. If omitted, sums the cells in range

Examples:

=SUMIF(C2:C51, "Electronics", I2:I51) - Sums values in I2:I51 where C2:C51 equals “Electronics”
=SUMIF(E2:E51, ">100") - Sums all values in E2:E51 that are greater than 100
=SUMIF(C2:C51, A10, E2:E51) - Sums E2:E51 where C2:C51 matches cell A10

Notes:

sum_range must be the same size as range
If sum_range is omitted, sums the cells in range itself
Criteria follow same rules as COUNTIF (quotes for text/operators)

Use Cases:

Sum values for specific categories
Conditional summing based on criteria
Calculate category totals

AVERAGEIF Function

Purpose: Calculates the average of values in a range that meet a single criterion.

Syntax:

=AVERAGEIF(range, criteria, [average_range])

Parameters:

range: Range of cells to evaluate against criteria
criteria: Condition that defines which cells to average
average_range: (Optional) Range to average. If omitted, averages the cells in range

Examples:

=AVERAGEIF(C2:C51, "Electronics", E2:E51) - Averages E2:E51 where C2:C51 equals “Electronics”
=AVERAGEIF(C2:C51, A10, E2:E51) - Averages prices where category matches A10
=AVERAGEIF(E2:E51, ">100") - Averages all values in E2:E51 greater than 100

Notes:

average_range must be the same size as range
Returns #DIV/0! if no cells match criteria
Criteria follow same rules as COUNTIF

Use Cases:

Calculate average by category
Average values meeting specific conditions
Compare category averages

SUMPRODUCT Function

Purpose: Multiplies corresponding components in arrays and returns the sum of those products.

Syntax:

=SUMPRODUCT(array1, [array2], [array3], ...)

Parameters:

array1, array2, ...: Arrays (ranges) of equal size to multiply and sum

Examples:

=SUMPRODUCT(E2:E51, F2:E51) - Multiplies each price by quantity, then sums all results
=SUMPRODUCT((C2:C51="Electronics")*(E2:E51)*(F2:F51)) - Multiplies E*F where C=”Electronics”, then sums
=SUMPRODUCT((A2:A10>50)*(B2:B10)) - Sums B2:B10 where A2:A10 > 50

Notes:

Arrays must be the same size
Can use boolean logic: (condition)*array converts TRUE/FALSE to 1/0
More powerful than SUMIF for complex conditions
Works great for conditional multiplication and summing

Advanced Usage:

=SUMPRODUCT((C2:C51="Electronics")*(E2:E51)*(F2:F51)) breaks down as:
1. (C2:C51="Electronics") creates array of TRUE/FALSE
2. Multiplies TRUE/FALSE (treated as 1/0) by prices and quantities
3. Only non-zero results remain (where condition is TRUE)
4. Sums all products

Use Cases:

Calculate weighted sums
Conditional multiplication and summing
Complex multi-condition calculations
Alternative to array formulas

Lookup Functions

VLOOKUP Function

Purpose: Searches for a value in the first column of a table and returns a value in the same row from a specified column.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameters:

lookup_value: The value to search for (must be in first column of table_array)
table_array: The table of data to search (must include lookup column and return column)
col_index_num: Column number in table_array to return (1 = first column, 2 = second column, etc.)
range_lookup: (Optional) TRUE for approximate match, FALSE for exact match. Default is TRUE.

Examples:

=VLOOKUP(B26, Inventory!A2:H51, 8, FALSE) - Finds B26 in column A, returns value from column H (8th column)
=VLOOKUP("PRD-015", Inventory!A2:H51, 5, FALSE) - Finds “PRD-015”, returns Unit Price (5th column)
=VLOOKUP(D2, Suppliers!A2:B5, 2, FALSE) - Looks up D2 in Suppliers sheet, returns Supplier Name

Important Rules:

Lookup value must be in the first column of table_array
Column index counts from the left of table_array, not the worksheet
Use FALSE for exact match with text lookups
Use TRUE (or omit) for approximate match with sorted numeric data

How VLOOKUP Works:

Searches for lookup_value in the first column of table_array
Finds matching row (exact or approximate depending on range_lookup)
Moves to the column specified by col_index_num
Returns the value at that intersection

Column Index Explanation: If table_array is A2:H51:

Column 1 = Column A (Product ID)
Column 2 = Column B (Product Name)
Column 3 = Column C (Category)
Column 5 = Column E (Unit Price)
Column 8 = Column H (Supplier Name)

Common Errors:

#N/A: Lookup value not found (check spelling, ensure value exists)
#REF!: Column index number exceeds table columns (e.g., col_index_num = 10 but only 8 columns)
Wrong value: Using approximate match (TRUE) when exact match (FALSE) needed

Use Cases:

Look up product information by ID
Retrieve related data from lookup tables
Join data from different sheets
Create reference tables and reports

</section>

Verification & Expected Results

Quick Verification Checklist

After completing all exercises, verify your results:

Total Inventory Value: Should be a large dollar amount (varies by dataset)
Average Unit Price: Should be reasonable (typically $30-$100 range)
Total Products: Should be 50
Stock Status: Should have some “Reorder” and some “OK” entries
VLOOKUP: Should correctly retrieve supplier names and prices
Category counts: Should sum to 50 (10 products per category)
Summary table: All formulas should calculate correctly

Common Errors & Troubleshooting

Error: #N/A in VLOOKUP

Cause: Lookup value not found in first column of table array

Fix: Check spelling, ensure lookup value exists, verify table array range

Error: #VALUE!

Cause: Wrong data type in formula (e.g., text in math operation)

Fix: Ensure cells contain numbers where numeric operations are expected

Error: #REF!

Cause: Formula references deleted or invalid cell

Fix: Check cell references, verify sheets exist

Formula shows as text, not calculated

Cause: Missing equals sign (=) at the beginning, or cell formatted as text

Fix: Add = at the start of the formula. If still showing as text:

Excel: Change cell format from Text to General, then re-enter formula
Google Sheets: Change cell format from Plain text to Automatic, then re-enter formula

Wrong results from COUNTIF/SUMIF

Cause: Criteria syntax incorrect (missing quotes for text)

Fix: Use quotes for text criteria: "Electronics" not Electronics

Conclusion

Congratulations! You have completed Lab 1 covering:

✅ Basic formulas (SUM, AVERAGE, COUNT, MAX, MIN)
✅ Conditional logic (IF, COUNTIF, COUNTIFS)
✅ VLOOKUP for data lookup
✅ Relative and absolute cell references
✅ Combining multiple formulas for analysis

Next Steps

Practice these formulas on different datasets
Experiment with variations of the formulas
Try creating your own analysis questions
Prepare for next week’s topics: PivotTables and data visualization

Lab Completed: _______
**Date:** _______
Instructor Signature: _________

Data Analysis with Spreadsheet Program

Lab 1: Excel Basics with Product Inventory Dataset

Important: Excel vs Google Sheets

Key Differences:

Instructions Format:

Dataset Overview

Introduction

Dataset Structure

Learning Objectives

Function Reference: Detailed Explanations

Basic Aggregation Functions

SUM Function

AVERAGE Function

COUNT Function

COUNTA Function

MAX Function

MIN Function

Conditional Functions

IF Function

COUNTIF Function

COUNTIFS Function

SUMIF Function

AVERAGEIF Function

SUMPRODUCT Function

Lookup Functions

VLOOKUP Function

Verification & Expected Results

Quick Verification Checklist

Common Errors & Troubleshooting

Error: #N/A in VLOOKUP

Error: #VALUE!

Error: #REF!

Formula shows as text, not calculated

Wrong results from COUNTIF/SUMIF

Conclusion

Next Steps