Skip to main content

Posts

Pinned Post

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
Recent posts

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

Lesson 2.4 – AutoFill and Flash Fill

Lesson 2.4 – AutoFill and Flash Fill AutoFill and Flash Fill are two powerful Excel tools that help you enter data faster and reduce repetitive work. AutoFill extends patterns and formulas, while Flash Fill recognizes patterns automatically and fills the remaining values for you. 1. What AutoFill Does AutoFill uses the small square at the bottom-right corner of the active cell (the Fill Handle ) to copy or extend data. You can use AutoFill to: Copy values Extend sequences (1, 2, 3…) Extend dates (Mon, Tue, Wed…) Copy formulas and adjust references 2. Using AutoFill Steps: Select a cell or a range. Drag the Fill Handle down, up, left, or right. Release the mouse to apply the pattern. Examples: Type January → drag → Excel fills February, March… Type 1 and 2 → select both → drag → Excel continues 3, 4, 5… Type 15/03/2024 → drag → Excel fills the following dates 3. AutoFill with Formulas When you drag a formu...
Post a Comment
Continue reading