Microsoft Excel-Beginner to Advanced with Data Analysis & AI

Explore the Excel interface basics: columns and rows, the active cell, name box, and formula bar. Understand worksheets and workbooks, the ribbon and toolbar, plus basic shortcuts and functions.

Learn how Excel functions are predefined formulas that simplify calculations, using the sum and average functions, understand syntax and inputs, and explore aggregation, logical, date and time, and database functions.

Learn how to use Excel's basic mathematical functions, including sum, average, max, min, count, and rounding with round, round up, and round down, using ranges and cell references.

Master logical functions in Excel, including booleans, countif, if, and, or, to evaluate conditions like credit period over 30 days and balance for paid or unpaid statuses.

Master date functions in Excel: today and now for current dates and times, days for date intervals, and networkdays and networkdays.intl to count working days with holidays.

Master Excel formatting and number formats by applying colors, borders, alignment, wrap text, fonts, and pictures, using format painter, and creating custom formats for dates, currency, accounting, and percentages.

Explore relative, absolute, and mixed referencing in Excel, showing how formulas change when copied, when to fix rows or columns with dollar signs, and how to compute percentages of totals.

Ranjit shares practical tips to boost Excel proficiency: use keyboard shortcuts, understand function syntax, handle data types, and apply relative and absolute referencing by considering row and column separately.

Master sumif and countif in Excel to sum or count values by criteria, using ranges and sum ranges to compute totals like per-customer invoices or city counts.

Explore how to use sumifs and countifs to sum or count with multiple criteria, including balance, customer name, and credit period over 30 for due receivable analysis, with absolute references.

Master replacing characters by position and substituting text in Excel using replace and substitute, with position counting, instance options, and protecting the rest of the code (e.g., 123 to 456).

Learn to extract characters from text in Excel using left, mid, and right functions, with examples showing two from left, four from middle, and two from right.

Learn how to combine data from multiple columns into one using concatenate, then remove a country code with find and replace to normalize phone numbers in excel.

Explore chart elements in Excel, including chart area, plot area, data points, axes, regions, titles, and data labels, to format and identify data series.

Learn to create a column chart in Excel to compare expenses and show percentage change, including setting horizontal axis categories, vertical values, data labels, axis titles, and a chart title.

Create a bar chart in Excel to visualize total actual expenses for Q1 by category, add data labels and axis titles, adjust design and color, and move the chart.

Learn to create a line chart in Excel showing changes in total expenses from April to June, with data labels and a trend line to highlight the trend.

Learn to present complex data with a combination chart in Excel, using columns for budgeted values and a line for actual values, with clear labels and a concise title.

Learn to create and interpret a scatter plot in Excel by plotting temperature versus accuracy, labeling points with machine names, and identifying relationships to spot best and worst performers.

Explore the funnel chart to visualize a sales pipeline, showing decreasing leads through stages and how to create and format the chart in Excel.

Build a waterfall diagram in Excel to illustrate revenue, cost of goods sold, gross profit, expenses, and net profit as a running total.

Learn to create an Excel stock chart showing open, high, low, and close prices, with layout options to highlight up days with white bars and down days with black bars.

Learn how sparklines embed miniature charts inside cells to visualize trends and patterns in data, such as stock prices, and create them using insert and data range.

Learn to combine vlookup with match to dynamically fetch the column index for SKU across multiple columns, using inventory_list and envy_header with absolute references.

Learn to use index and match for vertical and horizontal lookups in Excel, overcoming Vlookup limits by dynamically finding row and column from an inventory table.

Learn how hlookup searches the top row header and returns a value from a specified row, using absolute references and match to derive the row for April percentage change.

Learn to use xlookup to search a range or array, return a value or an array, handle not found, and use exact or approximate match, including right-to-left lookups.

Explore approximate match in lookups with vlookup and lookup functions, learning how to fetch rates from tax slabs and ranges when exact matches do not exist.

Keep lookup values short with no extra spaces; use match to fetch row or column numbers with exact match, unless data is sorted for approximate match.

Protect a worksheet in Excel by locking cells, encrypting the file with a password, protect workbook structure, and use allow edit ranges to let users edit only the names column.

Learn how to protect the workbook structure in Excel by setting a password in the review tab to prevent inserting, deleting, renaming, moving, or hiding sheets, and unprotect when needed.

Apply data validation in Excel to enforce correct inputs, including 15-digit GST numbers, dates in financial year 2324, invoice amounts between 5000 and 100000, and whole-number and list validations.

Learn to apply Excel filters for text, numbers, and formatting, including built-in and advanced filters. Use criteria ranges to copy filtered data and handle complex criteria.

Learn to use Excel filter and sort functions to fetch a filtered array by department and designation, then sort by employee name from A to Z for clear data analysis.

Apply conditional formatting in Excel to visually highlight key data using rules, icon sets, and data bars, enabling quick data analysis of attendance and salaries.

Consolidate data from multiple worksheets or workbooks into a master sheet to summarize and aggregate by headers and rows across departments and cities, with links to source ranges.

Rely on practical Excel tips: sort data with custom sort, copy using advanced filters, identify duplicates with conditional formatting, and highlight key data with formatting before presenting your report.