How Excel is used in Real World Data Analysis

Source: Dev.to

COUNTIF Function

Counts how many times a value appears in a range.

=COUNTIF(range, criteria)

Example: =COUNTIF(A2:A100, "Completed") counts the cells in A2:A100 that contain the word “Completed”.

IF Function

Allows Excel to make decisions based on a condition.

=IF(logical_test, value_if_true, value_if_false)

You can nest multiple conditions:

=IF(X3<2, "Poor Score",
    IF(X3<3, "Average Score",
        IF(X3<4, "Good Score", "Excellent Score")))

Lookup Functions

When data is spread across different sheets or tables, lookup functions connect the pieces.

VLOOKUP

Searches the first column of a table and returns a value from a specified column to the right.

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

Example:

=VLOOKUP(10010, A2:F13, 5, FALSE)

Finds ID 10010 in the range A2:F13 and returns the value from the 5th column.
Note: Because VLOOKUP relies on a fixed column index, inserting a column before the return column can break the formula without warning.

XLOOKUP

A newer, more flexible alternative that does not require a column index.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example:

=XLOOKUP("P-102", A:A, C:C)

Finds “P‑102” in column A and returns the corresponding value from column C.
Available in Office 365, Excel 2021, or later.

INDEX + MATCH

Works in older versions and overcomes VLOOKUP’s limitations. MATCH finds the position of a value; INDEX returns the value at that position.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example:

=INDEX(G2:G13, MATCH(10010, A1:A13, 0))

Finds ID 10010 in column A and returns the value from the matching row in column G. This combo can look left or right without breaking.

Pivot Tables

Powerful summary tools that generate totals, averages, counts, and more with a few clicks.

1. Highlight the data range.
2. Insert → Pivot Table.
3. Drag a field to Rows.
4. Drag a field to Values.

The resulting pivot table instantly provides the desired aggregations.

Reflection

After just two weeks, my relationship with data has shifted dramatically. I now feel empowered to clean up messy sheets and let the data “talk” to me. I’m still early in my learning journey and would love to hear from you:

• What surprisingly simple Excel trick blew your mind when you first discovered it?
• If you’re a seasoned pro, what golden tip do you wish you’d known when starting out?

Drop your tips, shortcuts, or “aha!” moments—I’m all ears and ready to learn!