Big Data and AI

Posts

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. ...

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...

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...

Part II: Retrieving Data Chapter 4: Advanced Filtering and Expressions

Chapter 4: Advanced Filtering and Expressions In this chapter, we’ll deepen your SQL toolkit by mastering advanced filtering techniques and expressions in the WHERE clause. Precise filters let you extract exactly the rows you need, avoid noisy results, and improve query performance. We’ll cover: Comparison Operators: = , <> , > , < , >= , <= Logical Operators: AND , OR , NOT Pattern Matching: LIKE with % and _ wildcards Set Membership & Ranges: IN and BETWEEN Handling Missing Values: IS NULL and COALESCE By the end, you’ll be crafting tight, readable filters that yield clean, accurate datasets. 1. Comparison Operators Comparison operators form the bedrock of row-level filtering. They compare a column’s value to a constant or another column. Operator Description Example = Equal to WHERE status = 'active' <> Not equal to WHERE rating <> 5 > Greater than WHERE price > 100 < Less than WHERE quantity < 10 >= Greater than or equ...

Lesson 2.2 – Formatting Cells

Lesson 2.2 – Formatting Cells Formatting cells is essential for making your data clear, readable, and professional. Good formatting improves communication, helps prevent errors, and ensures your spreadsheet follows international standards used in business and data analysis. 1. Font Formatting You can change the appearance of text using the Font group on the Home tab. Bold – highlight important labels Italic – emphasize specific values Underline – titles or totals Font size – adjust readability Font color – use sparingly for clarity Avoid using too many colors or styles, as this reduces readability. 2. Cell Alignment Alignment controls how content is positioned inside a cell. Left – default for text Right – default for numbers and dates Center – useful for headers Vertical alignment – top, middle, bottom Wrap Text – keeps long text visible inside the cell Merge & Center – combine cells for titles ...