Reporting, Analytics With Excel+Macro+SQL+Power BI (43Hours)

Explore relative, absolute, and mixed references in Excel and learn how freezing with the dollar symbol or F4 keeps cells fixed when dragging.

Learn essential Excel shortcuts to boost work efficiency, including F2 for editing formulas, F4 for freezing references, Ctrl+1 for formatting, and Ctrl+Shift+L for filters.

Use the format painter in the home menu to copy formatting between cells, and learn how paste special format achieves the same result.

Explore advanced paste special in Excel, using shortcuts to paste values, formulas, formats, comments, and validations, and perform operations like multiply, add, and transpose, with skip blanks and paste link.

Learn to extract the product name with highest shipping cost and highest loss using VLOOKUP, LARGE, and MIN in Excel, including selecting top five and dynamic row techniques.

Explore how the match function returns the position of a lookup value within a data range, identifying row or column positions and handling headers versus data rows with exact matching.

Master vlookup and match to pull multiple fields from data sets, including left and right lookups, using choose and freeze techniques in practical examples like order id.

Learn advanced vlookup techniques to extract data from multiple datasets across various scenarios, using nested vlookups, match, and iferror to retrieve names, IDs, mobile numbers, DOB, salaries, and more.

Learn how the HLOOKUP function fetches data horizontally from a data set using a lookup value, four parameters, and a fixed header row with exact match.

Extract data from multiple rows and columns with HLOOKUP and MATCH to dynamically retrieve horizontal and vertical data across headers.

Extract data from multiple datasets using hlookup and match to pull header-based item values in horizontal and vertical directions, with iferror handling and fixed row and column references.

Explore the index function to fetch values from a data range using row and column numbers, with examples for names and departments, and automate using the match function.

Extract data for a single column using index and match to retrieve department or ID from a dataset; index works left or right of the name lookup.

Master extracting data for multiple columns in Excel using index and multiple match, aligning by name to fetch department, total salary, and ID from a data set.

Apply index and match to pull order date, ship date, and customer name from multiple datasets using order ids, with if error handling for unmatched ids.

Master data cleaning and transformation to support accurate reporting and dashboards. Extract relevant data from CSV, Excel, databases, and flat files, remove unused data, and model for 2023 analyses.

Learn how the length function in Excel measures the number of characters, including spaces, with practical examples using names and cell references.

Learn how to find and search for a character’s position, comparing case sensitive find with case insensitive search, and use an example to illustrate exact vs flexible matching.

Explore the left function in Excel to extract a substring from the left with an optional character count, and use find and trim to obtain first names from full names.

Learn how to use the right function in Excel to extract the last name from full names, using dynamic length and the space position.

Learn how to use the mid function to extract substrings from text, with start and length, and apply dynamic extraction using find and trim to obtain a middle name.

Use the SUBSTITUTE function to replace characters or spaces in text. Pass text, old text, new text, and an optional instance number to target specific occurrences or remove spaces.

Use the substitute function in Excel to replace complete words, addressing case sensitivity, replace spaces and commas, and remove or replace special characters for data cleaning.

learn to count a specific character in Excel using the substitute and length functions, including or excluding o's by manipulating text and subtracting lengths.

Master dynamic use of the substitute function with a live scenario, placing a star on the last space by counting spaces and applying the instance number.

Explore how the replace function cleans data by substituting a character sequence with text, using old text, start number, number of characters, and new text, e.g., replacing Kumar with Singh.

Learn to extract invoice numbers from descriptions using Excel formulas by combining the find, left, and replace functions to dynamically locate the number after the invoice number keyword.

Use substitute, find, length, and right functions to extract the last word from data by placing a star on the last space and selecting the trailing characters.

Master the offset function in Excel to build dynamic ranges by referencing a starting cell and shifting rows or columns, with mandatory parameters reference, row, and column.

Learn to create dynamic ranges in Excel using the offset function, combine it with sum or average, and make height and width dynamic using count a function.

Use the offset function in Excel to build dynamic ranges for name, production, and error. Enable a dynamic chart that updates automatically as you add data, unlike static charts.

Learn to build a dynamic range for vlookup with the offset function, enabling automatic expansion as data grows, and apply it to any function with a data one reference.

learn to build a dynamic pivot table in Excel using the offset function to create a dynamic range, and refresh to include new data.

Learn the basics of the sumproduct function in Excel and apply it to calculate total cost from unit price and quantity, replacing multiple functions with a single formula.

Explore counting with countif and sumproduct for single criteria, using range and criteria across gender, department, and product category data. Apply countifs and sumproduct for broader counting.

Explore counting data with multiple criteria using countifs and sumproduct, comparing criteria ranges and outputs across department, gender, and age, with practical examples.

Explore using sumif and sumproduct in excel to sum sales by region under a single condition, and to compute profit or loss from a data set.

Explore using sumifs and sumproduct to aggregate sales, profit, and loss under multiple conditions such as region and product category, with practical freezing and data validation techniques.

Learn to apply averageif and averageifs to compute average sales and profit by single or multiple conditions, using range, criteria, and average range in Excel.

Master advanced countifs, averageifs, and sumifs with if functions to calculate department and gender-based metrics, using drop-down selections and error handling for robust reports.

Explore advanced uses of the sumproduct function to count present, absent, and leave entries across multi-dimensional roster data, where countifs cannot apply, with row and column references.

Explore basic date functions in Excel to analyze time series data, including day, month, year, and date function, plus today, now, hour, minute, second, and end-of-month calculations.

Explore Excel's network day and network day intl functions to calculate working days between dates, excluding weekends and holidays, and use work day for deadline calculations with holiday adjustments.

Learn to use the date diff function and the date d function to calculate age in years, months, and days from birth date to today, with practical examples.

Explore date functions and the month function to count birthdays by month and compute year-month invoice counts using sumproduct, text, and data validation techniques.

Explore how to use the if function to apply logical tests, returning true or false results for pass/fail, grade, and tax calculations based on total marks or salary.

Learn to apply the and operator to four conditions in Excel and use the if function to output pass or fail. Practice with four subjects' marks above 33.

Explore how to use the or operator in Excel to evaluate multiple conditions, combining logical tests with or function to return true or false and determine pass or fail.

Learn how to use the not function in Excel to reverse a condition within an if statement, turning true to false and enabling pass/fail based on marks more than 200.

Discover how conditional formatting in Excel highlights data based on conditions. Apply it to mark values over 50,000 or over five lakhs using shortcuts like alt h l.

Learn to apply conditional formatting for number formatting in Excel, covering greater than, less than, between, equal to, and date-based rules such as yesterday and last seven days.

Demonstrates applying conditional formatting to text values in Excel, using contains, begins with, ends with, and equal rules to highlight departments such as HR and management.

Learn how to apply and remove conditional formatting in Excel, including top N and top 10% highlights, bottom values, above and below average rules, color scales, and icon sets.

Learn to use Excel conditional formatting to highlight data with data bars, color scales, and icon sets; apply ranges, adjust color scales (including reverse), and display values with three-icon indicators.

Learn to highlight values in Excel by creating a dropdown list for department and applying conditional formatting to dynamically highlight matching rows or cells.

Highlight dates with day, month, and year functions via conditional formatting. Create new rules, freeze columns, and use and conditions to mark the 10th day, March month, and 2023 data.

Learn to highlight max and min in a data range with Excel conditional formatting, using a new rule and colors, green for max and red for min.

Discover how pivot tables in Excel summarize large data sets by rearranging fields into rows, columns, filters, and values to analyze sales, department, and employee data.

Explore pivot table features in Excel to present region and product category data with tabular layout, customize subtotals and grand totals, and adjust headers, repeats, and blank rows.

Discover how to create and customize pivot tables in Excel, including data layout, report filters, and value fields, with tips on refreshing, moving, and clearing.

Learn how to use slicers to filter pivot tables and pivot charts by product category and ship mode, creating interactive regional sales insights.

Create a pivot table in Excel to group data by year and month, optionally by quarter, and display order date, sales, and profit in a tabular report layout.

Explore pivot table techniques to express sales data as percentage of grand total, percentage of Ontario base value, and differences, including running totals.

Create a calculated field in a pivot table to compute tax from profit, using 10% or a conditional tax: if profit is greater than three lakhs then 10% else 5%.