Skip to main content

Posts

Pinned Post

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

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

Lesson 2.3 – Number Formats

Lesson 2.3 – Number Format Number formats control how Excel displays numeric values such as currency, percentages, dates, and measurements. Formatting does not change the underlying value — it only changes how the value appears. Understanding number formats is essential for creating clear, professional spreadsheets. 1. What Number Formats Do Excel stores numbers as raw values (e.g., 1500), but you can display them in different ways: 1,500 → with thousand separators $1,500.00 → as currency 1500% → as a percentage 15/03/2024 → as a date The value stays the same — only the appearance changes. 2. Accessing Number Formats You can apply number formats from: Home → Number group Right-click → Format Cells The Format Cells window provides the most complete set of options. 3. Common Number Formats 3.1 General The default format. Excel displays the number in the simplest way possible. 3.2 Number Used for standard nu...
Post a Comment
Continue reading