Excel Course 2025 Basic To Advance (With Chat GPT AI Tools)

What You Will Learn in This Lecture:

• Format numbers with different styles (currency, date, percentage, etc.).

• Use conditional formatting to highlight data based on conditions.

• Apply custom number formats for tailored data presentation.

Activities:

• Format a data range with custom number formats for specific needs.

• Use conditional formatting to color-code cells based on value criteria.

What You Will Learn in This Lecture:

• Apply number formats like currency, date, and percentage to data cells.

• Understand and use basic conditional formatting to highlight key data.

Activities:

• Format a set of numbers using currency, date, and percentage styles.

• Apply conditional formatting to highlight cells that meet specific conditions (e.g., values above a threshold).

What You Will Learn in This Lecture:

• Explore essential formulas like SUM, AVERAGE, COUNT, MAX, and MIN.

• Learn to combine multiple functions for dynamic calculations.

Activities:

• Create a summary report with key formulas (SUM, AVERAGE, MAX, MIN).

What You Will Learn in This Lecture:

• Understand cell references (relative, absolute, and mixed).

• Create and use named ranges for efficient referencing.

• Apply the IF function for logical decision-making in formulas.

Activities:

• Practice creating named ranges and using them in formulas.

• Solve a task using the IF function with logical conditions.

What You Will Learn in This Lecture – Sorting in Excel

• • Understand the basics of sorting data in Excel.

  • Learn how to sort data in ascending and descending order.

  • Use custom sorting to arrange data based on specific criteria.

  • Sort by multiple columns for better data organization.

  • Apply sorting to text, numbers, and dates efficiently.

  • Learn how to use shortcuts and advanced sorting techniques.

  Activities:

• Basic Sorting Exercise: Sort a list of students' names alphabetically and arrange their scores in descending order.

• Custom Sorting Task: Organize sales data based on regions and product categories using multi-level sorting.

What You Will Learn in This Lecture – Filtering in Excel

• Understand the purpose and benefits of filtering data in Excel.

• Learn how to apply AutoFilter to display specific data.

• Use text, number, and date filters for precise data analysis.

• Apply advanced filtering techniques using custom criteria.

• Learn how to use the Filter feature with multiple conditions.

• Clear and reapply filters to update data views efficiently.

Activities:

• Basic Filtering Exercise: Filter a list of employees to display only those from a specific department.

• Advanced Filtering Task: Use custom filters to extract sales records for a specific date range and product category.

What You Will Learn in This Lecture – Data Validation in Excel

• Understand the importance of data validation in Excel.

• Learn how to set rules for data entry using Data Validation.

• Create dropdown lists for easy and accurate data selection.

• Apply number, text length, and date restrictions.

• Use custom formulas for advanced validation rules.

• Display input messages and error alerts to guide users.

Activities:

• Dropdown List Creation: Create a dropdown list for selecting product categories in a sales sheet.

• Custom Validation Rule: Set a rule to allow only numeric values between 1 and 100 in a score column.

What You Will Learn in This Lecture – List Validation in Excel

• Understand the purpose of list validation in Excel.

• Learn how to create a dropdown list using Data Validation.

• Source list values from a range of cells or manually enter them.

• Use dynamic lists that update automatically when new items are added.

• Restrict entries to predefined options to maintain data consistency.

• Display input messages and error alerts for better user guidance.

Activities:

• Basic Dropdown List: Create a dropdown list for selecting employee job roles in an HR sheet.

• Dynamic List Creation: Set up a dropdown list that updates automatically when new product names are added.

What You Will Learn in This Lecture – Custom Format in Excel

• Understand the purpose of custom formatting in Excel.

• Learn how to format numbers, dates, and text using custom codes.

• Apply conditional formatting for better data visualization.

• Use custom formats to display currency, percentages, and decimals.

• Create custom date and time formats for reports.

• Hide values or display specific text based on conditions.

Activities:

• Number Formatting Task: Apply a custom format to display large numbers in thousands (e.g., 10,000 as 10K).

• Date Formatting Exercise: Customize a date column to show dates as "MMM-YYYY" (e.g., Jan-2025).

What You Will Learn in This Lecture – Advanced Use of Table Format in Excel

• Understand how to create and format Excel tables for better data organization.

• Learn to use table features like structured references and automatic expansion.

• Apply table styles for better data visualization and consistency.

• Use calculated columns to apply formulas across the entire table.

• Sort and filter data within a table efficiently.

• Learn how to reference table data in formulas and charts.

Activities:

• Creating a Table: Convert a range of data into an Excel table and apply a custom table style.

• Calculated Column Task: Add a calculated column in a table to automatically calculate sales tax based on product prices.

What You Will Learn in This Lecture – Print Setup in Excel

• Understand the basics of print setup in Excel.

• Learn how to adjust page orientation, margins, and paper size.

• Set print areas to define specific ranges for printing.

• Add headers and footers to include essential information.

• Use page breaks to organize large spreadsheets for printing.

• Adjust scaling to fit your content on one page.

Activities:

• Page Setup Task: Set up a worksheet to print in landscape orientation with custom margins and a page header.

• Print Area Exercise: Define a print area for a sales report and scale it to fit on one page.

What You Will Learn in This Lecture – Types of Charts in Excel

• Understand the different types of charts available in Excel (e.g., column, line, pie, bar, etc.).

• Learn when to use each chart type based on data and analysis needs.

• Create basic charts to visualize data trends and comparisons.

• Customize chart types for better data presentation and clarity.

• Learn to switch between chart types for improved visual representation.

Activities:

• Column Chart Exercise: Create a column chart to compare sales data across different regions.

• Pie Chart Task: Create a pie chart to show the market share distribution of different products.

What You Will Learn in This Lecture – Chart Elements in Excel

• Understand the key components of a chart (title, axis, legend, data labels).

• Learn how to add and format chart elements for clarity.

• Customize the chart title, axis labels, and legend to match your data.

• Add data labels to make chart information more accessible.

• Adjust axis options for better data visualization.

Activities:

• Chart Title & Axis Labeling: Create a chart and add a custom title and axis labels to make the data clearer.

• Data Labels Exercise: Add data labels to a column chart to display exact values for each data point.

What You Will Learn in This Lecture – Advanced Chart Formatting in Excel

• Learn advanced techniques for customizing chart appearance (colors, fonts, and styles).

• Use formatting options to enhance chart readability and presentation.

• Apply gradient fills, shadows, and 3D effects to chart elements.

• Modify data series and axis formatting for better visual appeal.

• Customize gridlines, borders, and background colors for a polished look.

Activities:

• Advanced Formatting Exercise: Customize a bar chart with gradient fills and 3D effects for a professional appearance.

• Series and Axis Formatting: Adjust data series colors and modify axis scales for clear, easy-to-read chart visuals.

What You Will Learn in This Lecture – Important Charts in Excel

• Understand which chart types are most effective for different data analysis tasks.

• Learn to create key charts like line, bar, pie, scatter, and combo charts.

• Discover when to use each chart type for maximum clarity and impact.

• Customize important charts to highlight trends, comparisons, and relationships in data.

• Use secondary axes and combo charts to display multiple data series.

Activities:

• Line and Bar Chart Exercise: Create a line chart to track sales trends over time and a bar chart to compare sales by product.

• Combo Chart Task: Create a combo chart with a column and line series to visualize sales data and profit margins together.

What You Will Learn in This Lecture: IF, IFAND, IFOR

• Understand the IF function for conditional logic.

• Use IFAND to apply multiple conditions together.

• Apply IFOR to check multiple conditions with flexibility.

• Nest IF functions for complex decision-making.

• Handle errors and optimize logical formulas.

Activities:

• Student Grading System – Use IF, IFAND, and IFOR to assign grades based on scores.

• Discount Calculator – Apply conditional logic to calculate discounts based on purchase amounts.

What You Will Learn in This Lecture: COUNT, COUNTA, COUNTIF, COUNTIFS

• Understand the COUNT function to count numeric values.

• Use COUNTA to count non-empty cells, including text.

• Apply COUNTIF to count cells based on a single condition.

• Utilize COUNTIFS to count cells meeting multiple conditions.

• Learn practical applications for data analysis and reporting.

Activities:

• Attendance Tracker – Use COUNT & COUNTA to track student or employee attendance.

• Sales Performance Analysis – Apply COUNTIF & COUNTIFS to count sales above a target or by specific criteria.

What You Will Learn in This Lecture: SUM, SUMIF, SUMIFS, IFS, IFERROR

• Use SUM to calculate the total of numeric values.

• Apply SUMIF to sum values based on a single condition.

• Utilize SUMIFS to sum values meeting multiple conditions.

• Understand IFS for multiple condition-based outputs.

• Learn IFERROR to handle errors in formulas efficiently.

Activities:

• Expense Categorization – Use SUMIF & SUMIFS to calculate total expenses by category.

• Sales Bonus Calculation – Apply IFS & IFERROR to assign bonuses based on sales targets and handle errors.

What You Will Learn in This Lecture: LOWER, UPPER, PROPER, LEN, LEFT, RIGHT

• Use LOWER to convert text to lowercase.

• Apply UPPER to change text to uppercase.

• Utilize PROPER to capitalize the first letter of each word.

• Understand LEN to count the number of characters in a text.

• Use LEFT to extract characters from the beginning of a text string.

• Apply RIGHT to extract characters from the end of a text string.

Activities:

• Name Formatting – Use LOWER, UPPER, and PROPER to standardize names in a dataset.

• Extracting Codes – Apply LEN, LEFT, and RIGHT to extract specific parts of product or ID codes.

What You Will Learn in This Lecture: CONCAT, TEXTJOIN, TRIM, REPT, LARGE, ROUND

• Use CONCAT to combine multiple text strings.

• Apply TEXTJOIN to join text with a delimiter.

• Utilize TRIM to remove extra spaces from text.

• Understand REPT to repeat text or numbers multiple times.

• Use LARGE to find the highest values in a dataset.

• Apply ROUND to round numbers to a specific decimal place.

Activities:

• Data Cleaning – Use TEXTJOIN, CONCAT, and TRIM to format and organize text data.

• Top Performer Analysis – Apply LARGE, ROUND, and REPT to identify and display top scores or sales figures.

What You Will Learn in This Lecture: Design an Impressive Pivot Table

• Create and customize a PivotTable for data analysis.

• Format PivotTables with professional design elements.

• Use filters, slicers, and timelines for interactive reports.

• Apply conditional formatting to highlight key insights.

• Summarize and analyze large datasets efficiently.

• Create PivotCharts for better data visualization.

Activities:

• Sales Performance Dashboard – Build a PivotTable to analyze sales by region, product, and month.

• Employee Productivity Report – Use PivotTables to track and compare employee performance metrics.

What You Will Learn in This Lecture: Pivot Formula, Charts & Slicer

• Use PivotTable formulas like GETPIVOTDATA to extract specific data.

• Create and format PivotCharts for data visualization.

• Add and customize slicers for interactive filtering.

• Combine PivotTable and PivotChart for comprehensive analysis.

• Apply advanced slicer techniques to improve data interaction.

Activities:

• Sales Data Analysis – Use PivotTable formulas to calculate total sales and profit margins, and create a PivotChart.

• Customer Insights Report – Add slicers to filter customer data by region and product for a more dynamic report.

What You Will Learn in This Lecture: Advanced Options in PivotTable

• Explore Multiple Consolidation Ranges to combine data from different sources.

• Use Calculated Fields and Calculated Items for custom formulas in PivotTables.

• Apply Group Data by date, ranges, or text for more insightful analysis.

• Customize PivotTable Layouts to suit specific reporting needs.

• Use PowerPivot for more complex data models and relationships.

• Apply Show Values As for percentage-based calculations like % of Total.

Activities:

• Financial Report – Create a PivotTable with Calculated Fields and Show Values As to analyze profit margins.

• Sales Performance Analysis – Use Group Data and Multiple Consolidation Ranges to compare sales across multiple regions and time periods.

What You Will Learn in This Lecture: PivotTable with Two Sheets (Same Header)

• Combine data from two sheets with the same headers into a single PivotTable.

• Use Data Model to link tables from multiple sheets for analysis.

• Learn how to create relationships between different sheets using PowerPivot.

• Filter and analyze data across multiple sheets efficiently.

• Create a consolidated PivotTable from different data sources.

Activities:

• Monthly Sales Report – Combine sales data from two sheets and create a PivotTable to analyze total sales by region.

• Employee Performance Comparison – Use data from multiple sheets to create a PivotTable comparing employee productivity across departments.

What You Will Learn in This Lecture: Multi-Sheet PivotTable with Single Header

• Combine data from multiple sheets with the same header into one PivotTable.

• Use the Data Model to link data from various sheets for analysis.

• Create relationships between sheets using PowerPivot.

• Consolidate data from different sources into a unified PivotTable.

• Filter and summarize data from multiple sheets efficiently in one report.

Activities:

• Quarterly Sales Report – Create a PivotTable using sales data from multiple sheets to analyze total sales by product.

• Team Performance Comparison – Combine employee performance data from different sheets and create a PivotTable to compare team results.

What You Will Learn in This Lecture: Multi-Workbook PivotTable with Single Header

• Combine data from multiple workbooks with the same header into a single PivotTable.

• Use the Data Model to link and analyze data from different workbooks.

• Create relationships between workbooks using PowerPivot.

• Learn how to reference data from multiple workbooks in one PivotTable.

• Consolidate and summarize data across workbooks for efficient reporting.

Activities:

• Company-Wide Sales Analysis – Link data from multiple workbooks and create a PivotTable to analyze overall sales performance.

• Cross-Department Budgeting – Combine budget data from various workbooks to create a PivotTable for department-level budget analysis.

What You Will Learn in This Lecture: Power Pivot Table with Single Header

• Understand how to use Power Pivot for advanced data analysis.

• Link data from multiple sources with the same header in Power Pivot.

• Create relationships between tables in Power Pivot to enhance data modeling.

• Use DAX (Data Analysis Expressions) for custom calculations and measures.

• Create a unified Power Pivot Table for complex data analysis and reporting.

• Design PivotTables with data from multiple sources and different workbooks.

Activities:

• Sales Data Integration – Use Power Pivot to combine sales data from different departments and create a detailed analysis table.

• Financial Dashboard – Link data from multiple sources in Power Pivot to build a dynamic financial dashboard with customized measures.

What You Will Learn in This Lecture: PivotTable Dashboard

• Create an interactive PivotTable Dashboard for data visualization.

• Use PivotCharts alongside PivotTables for dynamic reporting.

• Add slicers and timelines to filter and explore data.

• Design a user-friendly dashboard layout for quick insights.

• Use multiple PivotTables to display different aspects of data in one dashboard.

• Apply conditional formatting to highlight key metrics.

Activities:

• Sales Performance Dashboard – Create a PivotTable dashboard that includes charts, slicers, and filters to analyze monthly sales data.

• Customer Insights Dashboard – Build a dynamic dashboard with PivotTables and slicers to track customer demographics and purchasing behavior.

What You Will Learn in This Lecture

Key Topics

• VLOOKUP Function

  • How to use VLOOKUP for vertical data lookup.

  • Understanding the syntax: lookup_value, table_array, col_index_num, range_lookup.

  • Practical scenarios for using VLOOKUP in Excel.

• HLOOKUP Function

  • How to use HLOOKUP for horizontal data lookup.

  • Syntax breakdown: lookup_value, table_array, row_index_num, range_lookup.

  • Examples of applying HLOOKUP in real-life cases.

• XLOOKUP Function

  • Introduction to the XLOOKUP function and its advantages over VLOOKUP and HLOOKUP.

  • Syntax explained: lookup_value, lookup_array, return_array, and optional parameters.

  • Real-world examples of XLOOKUP for dynamic data retrieval.

Activities

1. Activity 1: Create a Product Pricing Table

   • Build a table with products, prices, and categories.

   • Use VLOOKUP to find the price of a specific product.

   • Use HLOOKUP to find prices for a particular category across a range of products.

2. Activity 2: Employee Data Lookup

   • Create an employee database with columns for ID, Name, Department, and Salary.

   • Use XLOOKUP to fetch employee details using their ID.

   • Compare the flexibility of XLOOKUP with VLOOKUP for missing data handling.

What You Will Learn in This Lecture

Key Topics

• Introduction to Multisheet VLOOKUP

  • Understanding how VLOOKUP works across multiple sheets.

  • Syntax for referencing other sheets: SheetName!Range.

  • Best practices for organizing data across sheets for effective lookups.

• Advanced VLOOKUP Techniques for Multisheet Scenarios

  • Using named ranges for easier formula management.

  • Handling errors in multisheet VLOOKUP using IFERROR.

  • Combining VLOOKUP with other functions (e.g., MATCH) for dynamic data retrieval.

• Common Challenges and Solutions

  • Troubleshooting common errors in multisheet lookups.

  • Strategies for maintaining formula accuracy when adding or deleting sheets.

Activities

1. Activity 1: Student Grades Lookup

   • Create separate sheets for different subjects (e.g., Math, Science, English).

   • Use VLOOKUP to fetch a student’s grade from the relevant sheet based on their name.

   • Practice using absolute and relative references for efficient formula copying.

2. Activity 2: Sales Data Aggregation

   • Create multiple sheets for regional sales data (e.g., North, South, East, West).

   • Use VLOOKUP to consolidate sales information for a specific product across all regions into a summary sheet.

   • Add error-handling with IFERROR to address missing data in any region.

What You Will Learn in This Lecture

Key Topics

• Introduction to INDEX and MATCH Functions

  • Overview of the INDEX function: retrieving data from a specific row and column.

  • Overview of the MATCH function: finding the position of a value in a range.

  • How INDEX and MATCH work together as a powerful alternative to VLOOKUP.

• Advantages of INDEX-MATCH Over VLOOKUP

  • Flexibility in searching leftward and upward.

  • Better performance with large datasets.

  • No dependency on the column order, unlike VLOOKUP.

• Advanced Applications of INDEX-MATCH

  • Combining INDEX and MATCH for two-dimensional lookups.

  • Using MATCH with multiple criteria for complex lookups.

  • Incorporating IFERROR for error handling in INDEX-MATCH formulas.

Activities

1. Activity 1: Product Inventory Lookup

   • Create a product inventory table with columns for Product Name, SKU, and Stock Level.

   • Use INDEX and MATCH to retrieve the stock level based on the product name.

   • Explore how to use MATCH for dynamic column selection.

2. Activity 2: Employee Performance Tracker

   • Create an employee performance table with rows for employees and columns for performance metrics (e.g., Sales, Targets, Bonus).

   • Use INDEX-MATCH to retrieve a specific metric for a given employee.

   • Experiment with combining MATCH and INDEX for a two-dimensional lookup (e.g., finding the bonus for a specific employee).

What You Will Learn in This Lecture

Key Topics

• Introduction to date and time formats in Excel

• TODAY and NOW functions for real-time date and time

• Creating specific dates with the DATE function

• Extracting components using DAY, MONTH, and YEAR functions

• Working with TIME, HOUR, MINUTE, and SECOND functions

• Calculating differences with DATEDIF and NETWORKDAYS

• Formatting date and time for better readability

• Automating date calculations using AI tools

Activities

• Activity 1: Create an automated project timeline using date functions.

• Activity 2: Develop a timesheet to calculate total working hours dynamically.

What You Will Learn in This Lecture

Key Topics

• Using keyboard shortcuts for faster navigation and data entry

• AutoFill and Flash Fill to quickly complete patterns

• Efficient data selection and manipulation techniques

• Using Named Ranges to simplify formulas

• Quick data analysis with PivotTables and Conditional Formatting

• Automating repetitive tasks with Macros and AI tools

• Smart filtering and sorting techniques

• Speeding up formula entry with AutoSum and Quick Analysis

Activities

• Activity 1 Create a dynamic report using PivotTables and Conditional Formatting.

• Activity 2 Automate a repetitive task using Macros or AI-powered tools.