Skip to main content

Posts

Pinned Post

Lesson 3.4 – Counting Functions (COUNT, COUNTA, COUNTBLANK)

Lesson 3.4 – Counting Functions (COUNT, COUNTA, COUNTBLANK) Counting data is one of the most common tasks in Excel. Whether you are analyzing sales, checking how many entries are missing, or counting how many values are valid, Excel provides simple functions to help you. In this lesson, you will learn three essential counting functions: COUNT , COUNTA , and COUNTBLANK . 1. COUNT – Count Numbers Only COUNT counts how many cells contain numeric values. Syntax: =COUNT(range) Example: =COUNT(A2:A10) COUNT ignores text, empty cells, and errors. Use it when you want to count numeric entries only . 2. COUNTA – Count Non‑Empty Cells COUNTA counts all non‑empty cells, regardless of content. Syntax: =COUNTA(range) Example: =COUNTA(B2:B10) COUNTA counts: Numbers Text Dates Logical values (TRUE/FALSE) Errors Use COUNTA when you want to know how many cells contain any type of data . 3. COUNTBLANK – Count Empty Ce...
Post a Comment
Continue reading
Recent posts

Lesson 3.3 – Math Functions (SUM, AVERAGE, MIN, MAX)

Lesson 3.3 – Math Functions (SUM, AVERAGE, MIN, MAX) Excel includes built‑in functions that make calculations faster, easier, and more accurate. In this lesson, you will learn four essential math functions used worldwide in business, finance, and data analysis: SUM , AVERAGE , MIN , and MAX . 1. What Is a Function? A function is a predefined formula that performs a specific calculation. All functions follow this structure: =FUNCTION_NAME(arguments) Example: =SUM(A1:A10) Functions save time and reduce errors compared to writing formulas manually. 2. SUM – Add Values SUM adds numbers in a range of cells. Syntax: =SUM(range) Example: =SUM(B2:B10) SUM is ideal for totals, budgets, sales, and any numeric aggregation. 3. AVERAGE – Calculate the Mean AVERAGE calculates the arithmetic mean of a group of numbers. Syntax: =AVERAGE(range) Example: =AVERAGE(C2:C10) Use AVERAGE to analyze performance, scores, or trends. ...
Post a Comment
Continue reading

Part II: Retrieving Data Chapter 5: Aggregation and Grouping

  Part II: Retrieving Data Chapter 5: Aggregation and Grouping Summarizing data is essential when you need high-level insights from large tables. Aggregate functions let you collapse detailed rows into single metrics—like totals, averages, or counts. Grouping then partitions those rows into buckets for segmented analysis. In this chapter, we’ll explore: Core aggregate functions: COUNT , SUM , AVG , MIN , MAX Using GROUP BY to create logical buckets Filtering groups with HAVING Handling NULL values within aggregations Practical examples for generating charts and reports 1. Aggregation Functions Overview Aggregate functions process multiple rows to produce a single summary value. They ignore row-level granularity and calculate metrics across a set: COUNT(expr) returns the number of non- NULL values or * for all rows SUM(expr) adds numeric values across rows AVG(expr) computes the average of numeric values MIN(expr) finds the smallest value MAX(expr) finds the largest value E...
Post a Comment
Continue reading

Lesson 3.2 – Relative vs Absolute References

Lesson 3.2 – Relative vs Absolute References When you copy a formula in Excel, the cell references inside it may change automatically. This behavior is extremely powerful, but only if you understand how it works. In this lesson, you will learn the difference between relative , absolute , and mixed references. 1. What Are Cell References? A cell reference tells Excel where to find the data used in a formula. Examples: A1 → column A, row 1 B5 → column B, row 5 C10 → column C, row 10 When you copy a formula, Excel may adjust these references automatically. 2. Relative References (A1) Relative references change when a formula is copied to another cell. This is the default behavior in Excel. Example: =A1 + B1 If you copy this formula from row 1 to row 2, it becomes: =A2 + B2 Excel “moves” the references based on the direction of the copy. 3. Absolute References ($A$1) Absolute references do not change when copi...
Post a Comment
Continue reading

Lesson 3.1 – How Formulas Work

Lesson 3.1 – How Formulas Work Formulas are the core of Excel. They allow you to perform calculations, analyze data, and automate tasks. Every formula in Excel follows a simple structure and always begins with an equal sign (=). Understanding how formulas work is essential before learning specific functions. 1. The Structure of a Formula All Excel formulas follow this basic pattern: =operand operator operand Example: =5 + 3 =A1 * B1 =SUM(A1:A10) Excel calculates the result and displays it in the cell, while the formula remains visible in the Formula Bar. 2. The Equal Sign (=) Every formula must start with = . Without it, Excel treats the entry as text. Examples: =10+5 → Excel calculates 10+5 → Excel shows “10+5” as text 3. Operators in Excel Operators tell Excel what type of calculation to perform. Operator Meaning Example + Addition =A1 + B1 - Subtract...
Post a Comment
Continue reading

Lesson 2.5 – Basic Data Cleaning

Lesson 2.5 – Basic Data Cleaning Clean data is essential for accurate calculations, sorting, filtering, and analysis. Even small issues—extra spaces, inconsistent capitalization, or unwanted characters— can cause formulas to fail or produce incorrect results. In this lesson, you will learn simple but powerful tools to clean data quickly using Excel functions. 1. Why Data Cleaning Matters Raw data often contains problems such as: Extra spaces before or after text Inconsistent capitalization Non-printable characters from imported files Mixed formats (text that looks like numbers) Cleaning data ensures consistency and prevents errors in formulas and analysis. 2. TRIM – Remove Extra Spaces TRIM(text) removes extra spaces from text, leaving only single spaces between words. Example: Original: “ Product A ” Formula: =TRIM(A1) Result: “Product A” TRIM is essential when working with imported or manually typed data. ...
Post a Comment
Continue reading