Excel in Data Analysis

COUNT()

Counts numeric values only

COUNTA()

Counts all non-blank cells

COUNTIF()

Counts cells that meet 1 condition

COUNTIFS()

Counts cells that meet multiple conditions

SUMIF()

Adds values based on 1 condition

SUMIFS()

Adds values based on multiple conditions

AVERAGEIF()

Calculates avarage for values meeting 1 condition

Pivot Table

An excel tool to summarize, aggregate, and analyze large datasets quickly using rows, columns, values & filters

When should a Pivot Table be used?

When you need to summarize enrollment, outcomes, demographics, or trends from large datasets

What do pivot tables require in data?

A dataset with column headers, no blank rows or columns, and consistent data types.

Group Dates

Groups dates by year, quarter, month, or term

Group Numbers

Creates ranges for numeric values (ex: age groups, GPA Bands)

Cannot Group Dates Error

Occurs when dates are stored as text instead of date format

Sort Pivot Data

Orders pivot results alphabetically/numerically

Filter Pivot Data

Limits pivot results to selected criteria

Report Filter

Filters the entire pivot table at once

What are the text cleaning functions on Excel?

• TRIM()

• CLEAN()

• LEFT()

• RIGHT()

• MID()

• LEN()

• CONCAT() / TEXTJOIN()

What are the lookup and matching functions on excel?

-XLOOKUP()

-VLOOKUP()

-INDEX()

-MATCH()

-INDEX+MATCH()

XLOOKUP()

Searches for a value in a range and returns a corresponding value from another range.

VLOOKUP()

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

INDEX()

Returns the value of a cell at a specific row and column within a range.

MATCH()

Finds the position of a value within a range.

INDEX + MATCH

A flexible lookup method that finds a value by position rather than column order.

TRIM()

Removes extra spaces from text, leaving single spaces between words.

CLEAN()

Removes non-printable characters from text.

LEFT()

Extracts characters from the beginning of a text string.

RIGHT()

Extracts characters from the end of a text string.

MID()

Extracts characters from the middle of a text string.

LEN()

Counts the number of characters in a text string.

CONCAT()

ombines text from multiple cells into one cell

Join

A method used to combine data from 2 or more tables using a common field (key)

String

A sequence of characters treated as text data.

Delimiter

A character used to separate values in a text string.

Comma (,), pipe (|), dash (-), underscore (_), space.

Data Manipulation

The process of cleaning, transforming, organizing, and preparing raw data in Excel for analysis and reporting.

Calculated Field

A formula-based field used for analysis.

Grouping Values

Combining numeric values into ranges (e.g., age groups).

Recoding Values

Converting text values into standardized numeric or categorical codes.

Source Data

The original dataset used for analysis; must be clean and consistent before manipulation.

Calculated Column

A new column created using formulas to derive values from existing data.

Interquartile Range (IQR)

The range between the first quartile (Q1) and third quartile (Q3).

Quartile (Q1)

The value below which 25% of the data fall.

Quartile (Q3)

The value below which 75% of the data fall.

What tool helps find outliers on excel?

Conditional formating

-Select data

-Conditional formula

-Use formula: OR(A1<LowerBound, A1>UpperBound)