Advance Analytics with Python,Power BI,SQL,VBA,Excel (54 Hr)

Explore relative, absolute, and mixed cell references in Excel, learn how to freeze references with dollar signs or F4, and master how formulas adapt as you copy or drag.

Learn essential Excel shortcuts to boost efficiency: edit formulas with F2, freeze references with F4, format with Ctrl+1, and use Ctrl+Shift+L to apply filters, plus manage sheets with Shift+F11.

Learn to use the format painter in the home menu to quickly copy formatting from one cell to another, and compare it with paste special format.

Master advanced paste special in Excel, using shortcuts like alt e s to paste values, formulas, formats, comments, validation, and more with options like skip blanks and paste link.

Learn how vlookup extracts data from a table by name, retrieving right-side fields like department and total salary, with exact-match options and no data handling.

Learn to extract the product names with the highest shipping cost and highest loss, and identify the top five results using VLOOKUP, LARGE, and MIN in Excel.

Learn how the match function returns the position of a lookup value within a data range, showing row and column positions for department, total salary, and Rajesh.

Master extracting multiple columns with vlookup and match in excel, swap left/right data using choose, and build dynamic, error-handled lookups across real datasets.

Master extracting data across multiple datasets with vlookup, using nested lookups, match, and iferror to pull names, mobile numbers, qualifications, and dob across scenarios.

Discover how the hlookup function extracts data horizontally from a table using a lookup value, with exact-match options and practical tips for headers and data sets.

Master advanced data extraction with HLOOKUP and MATCH to pull data from multiple rows and columns in both horizontal and vertical orders, with dynamic, header-based lookups.

Extract data from data sets using HLOOKUP and MATCH to retrieve horizontal and vertical values across three data sets, with fixed rows and moving columns, and handle errors with IFERROR.

Learn how the index function retrieves values from a data range (array) by row and column, using the data range, row number, and column number, with automation via match.

Learn to extract data for a single column using the index function with match, retrieving department or ID from left or right of the lookup name Rajesh.

Learn how to extract data across left and right columns using index with multiple match, aligning row by name and column by header to retrieve department, total salary, and ID.

Leverage index and match to pull order date, ship date, and customer name from multiple data sets using order ID, with iferror for cross-dataset matching.

Explore data cleaning and transformation for accurate reporting. Extract relevant data from diverse sources, remove unwanted data, and model and transform for dashboarding reports.

Learn how the length function (LEN) counts characters in text, including spaces, with practical examples using names, and see how it supports other functions.

Explore the find and search functions to locate a character's position, noting find is case sensitive while search is not, and using the optional start number.

Explore the left function to extract a substring from the left of a text, with an optional character count. Make it dynamic with find and trim for clean Excel results.

Explore the right function to extract data from the end of a string in Excel, and apply dynamic last-name extraction by using total length and the position of a blank.

Master the mid function to extract a middle word from a string using start position and character count. Learn dynamic extraction with find and trim to remove extra spaces.

Explore the substitute function to replace characters or text with a new value, using mandatory parameters, optional parameters, and instance numbers, demonstrated by replacing spaces with stars.

Use the substitute function to replace words, spaces, and punctuation, handle case sensitivity, and apply chained substitutions to clean and normalize text.

Learn to count specific characters with the substitute function and length function, counting o's and spaces, and computing character counts with and without selected characters.

Explore dynamic use of the substitute function to place a star on the last space, using length, instance number, and text references in a live scenario.

Learn to use the replace function for data cleaning, substituting a string with a new text using old text, start position, and number of characters; see Kumar to Singh example.

Extract an invoice number from a description by using find to locate the keyword 'invoice number', then apply left and replace to isolate the number.

Learn to extract the last word from data using substitute, find, length, and right functions, by placing a star at the last space and calculating the final substring.

Explore the basics of the offset function to create dynamic ranges and references that adapt as data grows, with three mandatory parameters and optional height and width.

Learn to create dynamic ranges with the offset function in Excel, configuring height, width, and row and column movements to produce flexible references for functions like sum or average.

Create a dynamic chart by using the offset function to build dynamic ranges with define name for name, production, and error, so the chart updates automatically as data is added.

Use the offset function with VLOOKUP to create a dynamic range that updates as data grows. Reference data one, freeze the row, and expand width to include department.

Create a dynamic pivot table in Excel by defining a dynamic range with the offset function and counta, then refresh to include new data.

Discover how to calculate total cost using the sum product function by multiplying unit price and quantity, and selecting array ranges and data ranges for practical Excel analysis.

Explore how to count data with a single condition using countif and sumproduct in Excel, covering range and criteria, with practical examples for gender, department, and product category.

Learn to count data with multiple criteria using countifs and sumproduct. Explore department and gender, age ranges, region, and year from dates.

Compute region-based totals using sumif and sumproduct in excel, comparing both methods. Demonstrate calculating sales and profit or loss, and how to fix ranges and freezes for accurate aggregation.

Learn to aggregate data using sumifs and sumproduct under multiple conditions, such as region and product category, with data validation to display profit or loss.

Learn to apply the averageif and averageifs functions in Excel to compute average values under single and multiple conditions, using region, product category, and year examples.

Explore advanced use of countifs, averageifs, and sumifs with if functions to compute department and gender counts, totals, and averages on data sets with drop-down inputs and error handling.

Master advanced uses of the sumproduct function to count present, absent, and leave across multi-row, multi-column roster data, surpassing countifs with header-based criteria and frozen references.

Explore core Excel date functions for time series analysis, including day, month, year extraction; today and now timestamps; hour, minute, second; and text-based date construction and end-of-month results.

Explore the network day and work day functions to calculate working days and deadlines between dates, excluding weekends and holidays, with international weekend configurations and holiday lists.

Learn to calculate age and birthday in Excel using date diff and date d functions to derive years, months, and days from birth date, plus identifying today's birthday.

Use date functions and sumproduct to count birthdays by month in a live scenario, and count invoices by year and month using vendor criteria.

Learn to calculate invoice aging in Excel by computing days between payment date and invoice receipt date, then categorize into buckets like over 365, 201–365, 101–200, and 1–100 days.

Explore how to use the if function to assign grades based on total marks and calculate tax using multiple if conditions, with concrete pass/fail and percentage rules.

Apply the and operator to evaluate multiple conditions and use the if function to convert true results into pass and false results into fail, using four subject marks as examples.

Explore the or operator in Excel to test multiple conditions; learn that any true condition yields true, all-false yields false, with practical examples using subject scores and total checks.

Learn how the not function and not operator reverse conditions in if statements, turning true to false and guiding pass or fail with total marks more than 200.

Apply conditional formatting to highlight data based on conditions, such as salaries over 50,000 and sales over five lakhs, using the alt h l shortcut and greater than rule.

Highlight numbers with conditional formatting in Excel using greater than, less than, between, and equal to rules, including date highlights like yesterday, last seven days, and this month, plus duplicates.

Apply conditional formatting to text values in spreadsheets, highlighting departments such as HR and management using contain, starts with, ends with, and equals rules to visualize data.

Highlight top N values with conditional formatting, adjust top 10% and bottom 10%, and apply above or below average, color scales, and icon sets.

Apply conditional formatting in Excel using data bars, color scales, and icon sets to visualize values. Learn to configure data bars and color scales to highlight highest, lowest values.

Create a department dropdown in Excel with data validation, then apply conditional formatting to highlight matching rows or columns using an equal-to rule and a frozen column reference.

Highlight date components in Excel using day, month, and year with conditional formatting. Highlight the 10th day in green, March across years in blue, and 2023 data with and conditions.

Highlight the maximum and minimum values in a data range using Excel conditional formatting. Create rules with cell value and max/min functions, applying green for max and red for min.

Master pivot tables in Excel to summarize data sets in a tabular form. Rearrange by rows, columns, and values to explore year and month sales and department performance.

Learn to use pivot table features in Excel to present data effectively, arranging region, product category, and sales in tabular layouts, control subtotals, grand totals, and headers.

Explore pivot table features, including layout, data placement, and report filters, and learn to insert into an existing worksheet (sheet four), move, and refresh across sheets for profit insights.

Learn to use slicers to filter pivot tables and pivot charts, create and configure pivots, and apply slicers for product category and ship mode to analyze region-wise sales over time.

Group data in a pivot table by year and month, optionally by quarter, then switch to a tabular report layout to align fields like sales and profit.

Master pivot tables to present data in percentage terms, including percent of grand total, percent of a base value, differences, and running total percentages.

Explore creating calculated fields in a pivot table to derive tax values from profit, including a condition where total profit is more than three lakhs triggers 10%, otherwise 5%.