Big Data and AI

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

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

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

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

Lesson 2.1 – Entering Data In this lesson, you will learn how to enter clean and structured data in Excel. Correct data entry is essential for accurate calculations, sorting, filtering, and all types of data analysis. Excel recognizes different types of data such as text, numbers, dates, and times, and each behaves differently inside the worksheet. 1. Entering Text Text is used for labels, names, categories, and descriptions. Excel aligns text to the left by default. Examples: Product A Customer Name Category To enter text, click a cell and type. Press Enter to confirm. 2. Entering Numbers Numbers are used for quantities, prices, percentages, and calculations. Excel aligns numbers to the right by default. Examples: 150 3.75 -20 Avoid adding spaces or symbols (except decimal separators), otherwise Excel may treat the value as text. 3. Entering Dates and Times Dates and times must follow a valid format...

Lesson 1.4 – Data Types in Excel: Text, Numbers, and Dates Excel interprets data differently depending on its type. Understanding how Excel classifies text, numbers, and dates is essential for accurate calculations, sorting, filtering, and data analysis. This lesson explains how each data type works and how Excel behaves when processing them. 1. Text (Strings) Text is any content that Excel does not recognize as a number or date. Text is aligned to the left by default. It is commonly used for labels, names, categories, and descriptive information. Examples of text: Product A January Invoice #4521 ABC123 Even numeric-looking values may be treated as text if they contain leading zeros, spaces, or non-numeric characters. 2. Numbers Numbers are values Excel can use in calculations. They are aligned to the right by default. Numbers can represent quantities, prices, percentages, measurements, or any numeric data. Examples of ...

Lesson 1.3 – Workbooks, Worksheets, Cells, and Ranges This lesson introduces the fundamental structural elements of Excel: workbooks, worksheets, cells, and ranges. Understanding these components is essential before working with data, formulas, or analysis tools. The concepts explained here form the foundation of all Excel operations. 1. Workbooks A workbook is the Excel file itself. Each workbook can contain one or more worksheets. Workbooks are used to organize related data within a single file. For example, a financial model, a budget, or a project plan is typically stored in one workbook with multiple worksheets. Examples of workbook names: Budget_2024.xlsx Sales_Report_Q1.xlsx Inventory_Tracking.xlsx Workbooks can be saved in different formats, such as .xlsx (standard), .xlsm (with macros), or .csv (comma-separated values). 2. Worksheets A worksheet is a single page inside a workbook. Worksheets are used to separate d...

Lesson 1.2 – The Excel Interface: Ribbon, Tabs, Groups, and Formula Bar This lesson provides a clear and structured explanation of the Excel interface. Understanding the interface is essential before entering data or using formulas. Each component of the interface has a specific purpose, and learning how these elements work will allow you to navigate Excel efficiently and perform tasks with accuracy. 1. Overview of the Excel Interface When you open Excel, the screen is divided into several functional areas. Each area is designed to help you perform specific actions such as formatting data, inserting formulas, creating charts, or managing files. The main components are: Title Bar Ribbon Tabs Groups Formula Bar Worksheet Area (Grid) Status Bar The following sections describe each component in detail. 2. Title Bar The Title Bar is located at the top of the Excel window. It displays the name of the current workbook (...

  Part II: Retrieving Data Chapter 3: Basic SELECT Queries Retrieving data is the core skill every SQL user must master. Whether you’re exploring a new dataset, debugging an application, or building reports, the SELECT statement is your primary tool. In this chapter, we’ll explore: The anatomy of a SELECT statement How to choose specific columns and rename them with aliases Filtering rows precisely with the WHERE clause Sorting results using ORDER BY Limiting output for faster testing with LIMIT and TOP By the end, you’ll have the confidence to write queries that fetch exactly the data you need—no more, no less. 1. Anatomy of the SELECT Statement Every SQL query starts with the SELECT clause, which defines what you want to see, and the FROM clause, which specifies where that data lives. A simple query looks like this: sql SELECT column1, column2 FROM table_name; Here’s the typical order of clauses in a SELECT statement: SELECT – List of columns or expressions to return. FRO...

Lesson 1.1 – What Is Excel and Why It Matters Worldwide This lesson introduces Microsoft Excel from a practical and professional perspective. The objective is to understand what Excel is, why it is used globally, and how it supports everyday work in business, finance, operations, and data analysis. The explanations are designed for learners with no prior experience. 1. What Excel Is Microsoft Excel is a spreadsheet application used to store, organize, calculate, and analyze data. It is part of the Microsoft 365 suite and is available on Windows, macOS, and the web. Excel is based on a grid structure composed of rows and columns, which allows users to work with numerical and textual information in a structured and scalable way. Excel is commonly used for: data entry and data organization mathematical and statistical calculations financial analysis and budgeting reporting and data visualization business planning and forecasting ...

Excel Basic Course – International Beginner Guide Welcome to the Excel Basic International Course , a complete beginner-friendly learning path designed to help you master the world’s most widely used spreadsheet tool. Whether you are starting from zero or refreshing your skills, this course will guide you step by step through the essential features of Microsoft Excel. By the end of this course, you will be able to create spreadsheets, use formulas, analyze data, build tables, create charts, and work efficiently using shortcuts and best practices adopted in professional environments worldwide. Why This Course Matters Excel is a global standard in business, finance, data analysis, project management, and everyday productivity. This course follows international standards aligned with: ICDL / ECDL – Spreadsheets MOS Excel Associate Microsoft 365 Fundamentals Corporate Excel onboarding programs Each lesson is clear, structured, and practical, wit...

 Retrieving data is the heart of SQL. In Part II of our beginner-friendly tutorial series, we’ll dive into the four essential techniques that let you extract, filter, summarize, and refine datasets: Basic SELECT Queries Advanced Filtering and Expressions Aggregation and Grouping Subqueries and Derived Tables Mastering these topics will empower you to answer real-world questions, from listing customer orders to calculating monthly sales trends. Basic SELECT Queries The SELECT statement is your gateway to any relational database. You’ll learn how to: Specify columns and use aliases ( SELECT first_name AS fname ) Retrieve all fields with SELECT * for quick previews Limit result sets ( LIMIT 10 , TOP 5 ) to speed up testing Sort data with ORDER BY (ascending/descending) Example: sql SELECT id, first_name, last_name FROM customers ORDER BY last_name ASC; This simple query fetches a clean, ordered list of customer names in seconds. Advanced Filtering and Expressions Once you can pull r...