Big Data and AI

Lesson 5.5 – Basic Statistics (AVERAGE, MEDIAN, MODE) Basic statistical functions help you understand the central tendency of your data. Excel provides simple functions to calculate the average value, the middle value, and the most frequent value in a dataset. These functions are widely used in business, finance, education, and data analysis. 1. What Are Basic Statistics? Basic statistics summarize your data and help you understand its general behavior. The three most common measures are: AVERAGE – The arithmetic mean MEDIAN – The middle value MODE – The most frequent value These functions are essential for analyzing trends, comparing groups, and making decisions. 2. AVERAGE Function The AVERAGE function calculates the mean of a group of numbers. Syntax: =AVERAGE(range) Example: =AVERAGE(B2:B10) Use AVERAGE when you want a general idea of the typical value in your dataset. 3. MEDIAN Function The MEDIAN functio...

Lesson 5.4 – Sorting and Filtering for Analysis Sorting and filtering are essential tools for analyzing data in Excel. They help you focus on the information that matters, identify patterns, and prepare your dataset for deeper analysis using charts or PivotTables. In this lesson, you will learn how to sort and filter data specifically for analytical purposes. 1. Why Sorting and Filtering Matter in Analysis When working with large datasets, it is difficult to understand trends or find insights by looking at raw numbers. Sorting and filtering allow you to: Identify top or bottom values Focus on specific categories Analyze trends over time Prepare clean data for charts and PivotTables These tools are the foundation of any data‑driven workflow. 2. Sorting for Analysis Sorting helps you reorganize your data to reveal patterns. • Sorting Numbers Examples: Sort sales from highest to lowest Sort expenses from smallest to largest ...

Lesson 5.3 – Introduction to PivotTables PivotTables are one of the most powerful tools in Excel. They allow you to summarize, analyze, and explore large datasets quickly — without writing formulas. With just a few clicks, you can transform raw data into meaningful insights. 1. What Is a PivotTable? A PivotTable is an interactive table that summarizes data. It helps you answer questions such as: How many sales did each product generate? Which month had the highest revenue? How many orders came from each region? What is the average value per category? PivotTables are essential in business, finance, marketing, and reporting. 2. Requirements for a Good PivotTable Before creating a PivotTable, your data should: Be organized in a clean table format Have clear column headers Contain no blank rows Use consistent data types (numbers, dates, text) Using an Excel Table is recommended for best results. 3. How to Create...

Lesson 5.2 – Quick Analysis Tool The Quick Analysis Tool is one of Excel’s most powerful features for beginners. It allows you to instantly apply formatting, create charts, add totals, and perform basic analysis with just one click. This tool helps you understand your data faster and make quick decisions without navigating multiple menus. 1. What Is the Quick Analysis Tool? The Quick Analysis Tool appears automatically when you select a range of data. It provides a small menu with shortcuts to the most common analysis features, including: Formatting – Data bars, color scales, icon sets Charts – Column, line, pie, and more Totals – Sum, average, count, running totals Tables – Convert data into an Excel Table Sparklines – Mini‑charts inside cells This tool is perfect for quick insights and fast visualizations. 2. How to Use the Quick Analysis Tool Steps: Select a range of data (at least two rows or columns). Look for the ...

Lesson 5.1 – Basic Charts Charts are one of the most effective ways to visualize data in Excel. They help you understand trends, compare values, and communicate information clearly. In this lesson, you will learn how to create the three most common chart types used worldwide: column charts, line charts, and pie charts. 1. Why Charts Matter Charts transform raw numbers into visual insights. They make it easier to: Identify patterns and trends Compare categories or time periods Highlight important values Present data in a professional way Charts are essential in business reports, presentations, dashboards, and data analysis. 2. How to Create a Chart Steps: Select the data you want to visualize (including headers). Go to Insert on the Ribbon. Choose the chart type you want to create. Excel will generate the chart automatically and place it on your worksheet. 3. Column Charts Column charts are used to compare values...

  Chapter 8: Set Operations Merge query results seamlessly: UNION vs. UNION ALL, INTERSECT, EXCEPT (or MINUS). Perform sophisticated multi-query analyses with ease. Combining multiple query outputs into a single, coherent result set is a cornerstone of advanced SQL analysis. SQL’s set operations— UNION , UNION ALL , INTERSECT , and EXCEPT (also known as MINUS in some systems)—allow you to treat query results like mathematical sets. Whether you need to deduplicate rows, find overlaps, or subtract one dataset from another, set operations streamline multi-query workflows. This detailed guide covers each operator’s syntax, performance considerations, real-world use cases, and best practices. 1. The Basics of Set Operations Before diving into each command, ensure your subqueries: Return the same number of columns Use compatible data types in each column position List columns in the same order SQL set operations follow these set-theory rules: UNION : combine two result sets and remov...

Module 5 – Basic Data Analysis Tools In this module, you will learn how to use Excel’s built‑in tools to analyze data, create visual insights, and understand information more effectively. These tools are essential for anyone working in business, finance, marketing, project management, or any role that requires data‑driven decisions. You will explore charts, quick analysis features, PivotTables, and basic statistics — all explained in a simple and practical way. What You Will Learn in This Module How to create basic charts (column, line, pie) How to use the Quick Analysis Tool for instant insights How to build your first PivotTable How to sort and filter data for analysis How to calculate basic statistics (AVERAGE, MEDIAN, MODE) These skills will help you transform raw data into clear, meaningful information. Lessons in This Module Lesson 5.1 – Basic Charts Lesson 5.2 – Quick Analysis Tool Lesson 5.3 – Introduction to Pivot...

Lesson 4.5 – Removing Duplicates Duplicate values can cause errors, incorrect calculations, and misleading analysis. Excel provides a simple and reliable tool to remove duplicates from your dataset in just a few clicks. In this lesson, you will learn how to identify and remove duplicate rows safely. SEO Description Learn how to remove duplicate values in Excel using the built‑in Remove Duplicates tool to clean data quickly and accurately. Publication date: 19 March 2025 1. What Are Duplicates? A duplicate occurs when one or more rows contain the same information. Duplicates often appear when data is imported, copied from other files, or collected from multiple sources. Examples of duplicates: Two identical customer names Repeated product codes Duplicate email addresses Rows with the same values across all columns 2. How to Remove Duplicates Steps: Select your dataset (or click inside an Excel Table). Go to Data → Remove Dupl...

Lesson 4.4 – Conditional Formatting Conditional Formatting allows Excel to automatically highlight cells based on rules. It helps you identify trends, spot errors, and visualize patterns without creating charts. In this lesson, you will learn how to apply basic conditional formatting rules used worldwide. SEO Description Learn how to use Conditional Formatting in Excel to highlight values, apply color scales, add data bars, and visualize data instantly. Publication date: 17 March 2025 1. What Is Conditional Formatting? Conditional Formatting changes the appearance of a cell based on its value. Excel can automatically apply colors, icons, or data bars when certain conditions are met. Highlight values greater than 100 Color cells containing specific text Show data bars to compare numbers visually Highlight duplicate values 2. How to Apply Conditional Formatting Select the range you want to format. Go to Home → Conditional Fo...

Lesson 4.4 – Conditional Formatting Conditional Formatting allows Excel to automatically highlight cells based on rules. It helps you identify trends, spot errors, and visualize patterns without creating charts. In this lesson, you will learn how to apply basic conditional formatting rules used worldwide. 1. What Is Conditional Formatting? Conditional Formatting changes the appearance of a cell based on its value. Excel can automatically apply colors, icons, or data bars when certain conditions are met. Examples: Highlight values greater than 100 Color cells containing specific text Show data bars to compare numbers visually Highlight duplicate values 2. How to Apply Conditional Formatting Steps: Select the range you want to format. Go to Home → Conditional Formatting . Choose the rule type you need. Excel will instantly apply the formatting based on your rule. 3. Highlight Cell Rules These rules highlight cells base...

Lesson 4.3 – Filtering Data Filtering allows you to display only the rows that match specific criteria while temporarily hiding the rest. It is one of the most useful tools in Excel for exploring, cleaning, and analyzing data without modifying or deleting anything. 1. What Is Filtering? Filtering helps you focus on the information you need by showing only the rows that meet your conditions. You can filter text, numbers, dates, and even colors. Examples: Show only “Electronics” products Display sales greater than 500 Show dates from the last 30 days Filter rows with a specific color 2. How to Apply a Filter Method 1 – Using the Ribbon: Select your dataset. Go to Home → Sort & Filter → Filter . Filter arrows will appear on each column header. Method 2 – Using a Table: If your data is formatted as an Excel Table, filters are automatically enabled. 3. Filtering Text Text filters allow you to show rows that match specif...

  Chapter 7: JOIN Operations Combining data from multiple tables is at the heart of relational database power. JOIN operations let you model real-world relationships—customers and orders, employees and managers, products and suppliers—and extract insights that single tables alone can’t provide. In this chapter, you’ll learn how each JOIN type works, see practical examples, and discover performance tips to keep your queries fast and your results accurate. Why JOIN Operations Matter In a normalized schema, related entities live in separate tables to avoid redundancy: Customers hold personal details. Orders record purchase transactions. Products list inventory items. JOINs enable you to merge these tables in a single query, pushing the heavy lifting into the database engine. This approach ensures: Data Integrity: Foreign keys and JOINs guarantee valid relationships. Maintainability: Business logic stays in SQL, not scattered across application code. Performance: Set-based joins ...

Lesson 4.2 – Sorting and Filtering in Tables Sorting and filtering are two of the most powerful features of Excel Tables. They allow you to organize, analyze, and explore your data quickly and efficiently. In questa lezione impari come ordinare e filtrare i dati in modo professionale. 1. Sorting Data in a Table Sorting means arranging your data in a specific order, such as: Alphabetical (A → Z or Z → A) Numeric (smallest → largest or largest → smallest) Date order (oldest → newest or newest → oldest) How to sort: Click the filter arrow in the column header. Select Sort A to Z or Sort Z to A . Sorting inside a Table keeps all rows aligned, preventing data corruption. 2. Filtering Data in a Table Filtering allows you to show only the rows that match specific criteria. How to filter: Click the filter arrow in the column header. Check or uncheck the values you want to display. Use Text Filters , Number Filters , or Date F...

Lesson 4.1 – Creating and Formatting Tables Excel Tables transform a simple data range into a dynamic, structured, and easy-to-manage dataset. Tables automatically expand, apply formatting, update formulas, and provide powerful tools for sorting, filtering, and analysis. In questa lezione impari come creare e formattare una Tabella in modo corretto. 1. What Is an Excel Table? An Excel Table is a structured data container with built-in features such as: Automatic formatting Filter buttons on each column Dynamic expansion when adding new rows Structured references in formulas Optional Total Row for quick calculations Tables are essential for working with real datasets, reports, and dashboards. 2. How to Create a Table Method 1 – Using the Ribbon: Select your data range (e.g., A1:D20). Go to Insert → Table . Confirm the range and check “My table has headers”. Method 2 – Using the Shortcut: Ctrl + T This is the fastest and...

Module 4 – Working with Tables Excel Tables are one of the most powerful tools for organizing, analyzing, and updating data. Unlike normal ranges, Tables automatically expand, apply formatting, update formulas, and provide structured references that make your spreadsheets more reliable and easier to maintain. In this module, you will learn how to: Convert a normal range into an Excel Table Use Table features such as sorting, filtering, and banded rows Apply and customize Table styles Use structured references in formulas Add and remove columns dynamically Use the Total Row for automatic calculations Excel Tables are essential for anyone working with real datasets, reports, dashboards, business analysis, or automation. Once you understand how they work, they become an indispensable tool in your workflow. Lessons in This Module Lesson 4.1 – Creating and Formatting Tables Lesson 4.2 – Sorting and Filtering in Tables Lesson 4.3...

  In real-world databases, data is rarely confined to a single table. To unlock deeper insights, you need to learn how to merge and compare datasets stored in different tables. In this third installment of our SQL tutorial series on Data Analyst BI, we’ll explore: JOIN Operations: Linking rows from multiple tables based on related columns Set Operations: Merging result sets via UNION, INTERSECT, and EXCEPT Mastering these techniques will enable you to build richer reports, enforce data integrity, and answer complex business questions. JOIN Operations JOINs are the cornerstone of combining data across tables . By matching rows on key columns, you can assemble comprehensive views of your data. 1. INNER JOIN Returns only rows that have matching keys in both tables. sql SELECT o.order_id, c.customer_name, o.order_date FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id; Use INNER JOIN when you need records that exist in both tables—e.g., orders with va...