Spreadsheet Analysis

Spreadsheet Basics

Spreadsheets organize data in a grid of rows (numbered 1, 2, 3...) and columns (labeled A, B, C...). Each intersection is a cell, identified by its column and row (e.g., A1, B5, C12).

Formulas and Functions

Formulas are calculations you create using cell references and operators. Functions are pre-built formulas that perform specific calculations.

Basic Operators

+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponent (power)

=A1+B1        // Adds values in A1 and B1
=C5*D5        // Multiplies values in C5 and D5
=(A1+A2)/2    // Average of A1 and A2
=B3^2         // B3 squared

Common Functions

Function	Purpose	Example
`SUM`	Adds all numbers in a range	`=SUM(A1:A10)`
`AVERAGE`	Calculates the mean	`=AVERAGE(B1:B10)`
`COUNT`	Counts cells with numbers	`=COUNT(C1:C10)`
`COUNTA`	Counts non-empty cells	`=COUNTA(D1:D10)`
`MAX`	Finds the largest value	`=MAX(E1:E10)`
`MIN`	Finds the smallest value	`=MIN(F1:F10)`

The IF Function

The IF function tests a condition and returns different values based on whether it's true or false.

=IF(condition, value_if_true, value_if_false)

// Examples:
=IF(A1>=50, "Pass", "Fail")
=IF(B1>100, B1*0.1, 0)        // 10% discount if over 100
=IF(C1="Yes", 1, 0)           // Convert Yes/No to 1/0

Nested IF Statements

You can nest IF functions for multiple conditions:

=IF(A1>=80, "A", IF(A1>=60, "B", IF(A1>=40, "C", "D")))

// This assigns grades:
// 80+ = A
// 60-79 = B
// 40-59 = C
// Below 40 = D

VLOOKUP Function

VLOOKUP searches for a value in the first column of a range and returns a value from another column.

=VLOOKUP(lookup_value, table_range, column_number, exact_match)

// Example: Look up a product price
=VLOOKUP(A1, Products!A:C, 3, FALSE)

// FALSE = exact match only
// TRUE = approximate match (for ranges)

Cell Referencing

Relative References

Default references like A1 are relative. When you copy a formula, the references adjust based on the new position.

Absolute References

Use $ to lock a reference. $A$1 won't change when copied.

Reference Types:

A1 - Relative (both column and row adjust)
$A$1 - Absolute (neither adjusts)
$A1 - Mixed (column locked, row adjusts)
A$1 - Mixed (row locked, column adjusts)

Charts and Visualization

Choose the right chart type for your data:

Column/Bar Chart

Comparing values across categories

Example: Sales by product

Line Chart

Showing trends over time

Example: Temperature over a week

Pie Chart

Showing parts of a whole

Example: Budget breakdown

Scatter Plot

Showing relationships between variables

Example: Height vs. weight

Data Validation

Data validation restricts what users can enter in cells, preventing errors:

Dropdown lists - Users select from predefined options
Number ranges - Only allow values between min and max
Date restrictions - Only allow dates within a range
Text length - Limit the number of characters

Conditional Formatting

Automatically format cells based on their values:

Highlight cells above or below average
Color scales (e.g., red to green)
Data bars showing relative values
Icon sets (arrows, traffic lights)

Sorting and Filtering

Sorting

Arrange data in order:

Ascending (A-Z, smallest to largest)
Descending (Z-A, largest to smallest)
Multi-level sorting (sort by multiple columns)

Filtering

Show only rows that meet certain criteria:

Text filters (contains, begins with, equals)
Number filters (greater than, between, top 10)
Date filters (this week, last month, before)

Key Terminology

Cell - The intersection of a row and column
Range - A group of cells (e.g., A1:C10)
Formula - A calculation using cell references and operators
Function - A pre-built formula for specific calculations
Relative reference - A cell reference that adjusts when copied
Absolute reference - A cell reference that stays fixed ($A$1)
VLOOKUP - Function to find values in a table

Learning Objectives