MIS Training - Advance Excel + Macro + Access + SQL

Autofill advanced tools show how to use fill series, set step values, and apply justify to format text and split data into separate cells.

Explore Flash Fill in Excel and learn how to automatically extract names, phone numbers, and concatenate data using suggestions and keyboard shortcuts to speed up data cleaning.

Master Excel operators and build equation-based calculations, including addition, subtraction, multiplication, division, power, percentage, and concatenation, with focus on order of operations and logical tests.

Learn how to use the concatenate and text functions to join text, extract elements, and prepend initials to a phone number.

Explore the concat and textjoin functions as improved alternatives to concatenate for combining ranges with delimiters. Learn how textjoin handles spaces, commas, and ignoring empty cells to produce clean results.

Learn to use the text function in Excel to perform replace and find operations, including nested functions, to extract and replace starting numbers such as bingo numbers.

Protect the workbook to lock its structure and sheet changes with a password, preventing adding, deleting, renaming, or hiding sheets; unprotect with the password to make edits.

Protect a worksheet by setting a password, choose allowed actions, and use review > protect sheet to enforce restrictions and manage editable ranges.

Use the Excel if function to determine scholarship eligibility and compute the amount: if score is below 70, no scholarship; otherwise (score minus 70) times 1000.

The updated IFS function in Excel replaces nested ifs with multiple logical tests for scores below 40, between 40 and 60, and above 60, evaluated by the first true test.

Learn to apply the max function in Excel with absolute references to prevent range shifts when dragging, and compare it with the min function to identify the lowest value.

Explore how to use and and or with the Excel IF function to evaluate two logical conditions and determine pass or fail based on score thresholds.

Explore how to create defined names for ranges, set scope to sheet or workbook, manage names via the name manager, and use them in formulas and navigation.

Explore how to create and use hyperlinks in Excel to navigate between sheets, link to named ranges, websites, other files, emails, and even create new documents within the workbook.

Learn to extract day, month, and year, convert serial numbers to dates and times, and use today and now for date and time calculations.

Explore how to use the networkdays function to count work days between dates, with optional holidays and customizable weekends via networkdays.intl.

Master the datedif function to compute age from birth date to today, returning years, months, and days with start date, end date, and y, m, d parameters.

Explore pivot table techniques in Excel, focusing on value fields, region-based totals, and transaction analysis, with how to refresh data and adjust settings to build reports.

Explore pivot tables with slicers to filter data, apply ascending or descending sorting, and arrange fields to build focused dashboards.

Master goal seek in Excel to hit a target profit by adjusting sales or costs, selecting the changing cell and goal value, and exploring simple to complex scenarios.

Create a loan table in Excel using data table and what-if analysis to calculate emi and total interest, adjust loan terms, and customize headings and formats.

Learn to create and customize charts in Excel, from selecting data and choosing a column chart to configuring horizontal and vertical axis titles, the legend, data labels, and formatting options.

Learn how to customize chart options by selecting data sources, editing titles, and adjusting fields, including applying joins and filtering to tailor sales reports.

Explore how to create 2D and 3D maps in Excel Power Map, visualize sales data with field maps, customize styles, and export animated maps as videos with specified resolutions.

Discover how to apply filters in Excel to extract candidates by qualification and location, or by text patterns, including advanced filters, top or bottom percent, and above or below average.

Sort data efficiently by name alphabetically with A to Z, then apply ascending or descending order, and use custom sort with a custom list to prioritize values.

Create dynamic dependent drop-down lists in Excel using data validation, named ranges, and the indirect function to populate department and corresponding employee names.

Explore how to use wildcards in Excel formulas to filter data, focusing on names starting with a letter and matching specific character lengths using * and ? in criteria.

Demonstrate how to nest if with vlookup to handle no matches and zeros, display blanks, and reuse an existing formula by copying and applying conditional outputs.

Master the offset function in Excel to build dynamic reports. Learn to set a reference cell, adjust rows and columns, and use height and width with sum, count, and average.

Demonstrate how to record a macro in Excel, assign shortcuts, and save the macro in a workbook, then review the VBA code to automate formatting tasks.

Record a macro to automate opening the expense report template, assign a shortcut, and learn several ways to run macros to simplify end-user tasks.

Explore different ways to run macros in Excel by assigning them to shapes and adding macro options to the ribbon, enabling quick access across sheets and workbooks.