Microsoft Excel: from ZERO to ADVANCED

Learn formulas and functions in Excel by mastering the equal sign syntax, cell references, and array sums, plus exploring the insert function and 500+ functions.

Apply formulas in Excel to perform common mathematical operations using addition, subtraction, multiplication, division, and exponentiation, and follow the order of operations.

Learn text manipulation in Excel with formulas like trim, proper, lower, upper, length, find, left, right, exact, replace, text to column, and concatenate to transform strings.

Explore manipulating dates and times in Excel with functions such as day, month, year, network days, weekday, eomonth, hour, minute, second, now, and today.

Learn to perform conditional analysis in Excel using logical functions (and, or, if), apply conditional formatting and formulas across ranges, lock cells with absolute references, and compute region-based travel allowances.

Learn vlookup, hlookup, and other lookup concepts to fetch scores in Excel, then explore index and match, plus data validation for dynamic lists.

Explore common financial analysis formulas in Excel chapter 15, including future value, present value, NPV, IRR, rate, and straight-line depreciation, through practical examples.

Master Excel statistical analysis formulas, including count, countif, countifs, max, min, average, averageif, averageifs, median, large, small, and standard deviation. Learn to apply these functions to real data.

Master array formulas in Excel to perform multi-item calculations, activate them with ctrl-shift-enter, and use conditional arrays to find minimum scores by gender.

Identify and fix common Microsoft Excel formula errors, including hash, value, name, reference, division by zero, and #N/A, using IF, IFERROR, VLOOKUP, and conditional formatting.

Learn to create and customize Excel charts from simple data, using axes, titles, and data labels. Adjust chart elements like legend, grid lines, trend lines, styles in the design tab.

Explore advanced charting techniques in Excel, including speedometer gauges, thermometer charts, milestone charts, gunshot charts, symbol-enhanced bars, target vs actual charts, and scatter plots with lookup.

Learn to create and customize sparkline graphics in Excel from a selected data range, choosing colors, high/low points, markers, win/loss, and axis visibility.

Master custom number formatting in Excel to manage positives, negatives, zeros, and text; hide values, format dates, display millions or billions, and build dashboards with shapes and visuals.

Learn how to implement Excel dashboards by transforming raw data into information with story, goal, and visual form, using pivot charts and slicers for actionable insights.

Learn to import data into Excel, identify and remove duplicates, and clean data using text-to-column and common text formulas, while handling blanks and errors.

Apply data validation in Excel to restrict inputs with lists, numbers, dates, and text length. Learn to set up input and error messages to guide users and protect source data.

Learn to create worksheet outlines in Excel by grouping columns or rows, using auto outline, ungrouping and clearing outlines, and preparing graphs from outlined data with subtotals and subtitles.

Link worksheets to sum first term, second term, and final exam scores across subjects, creating a total that updates with changes. Consolidate across sheets to build a unified score table.

learn to create and analyze data with pivot tables in Excel, using fields like region, salesperson, item, and sales to summarize totals, counts, and percentages.

Explore what-if analysis in Excel, using scenario manager on the data tab, changing cells, and scenario summaries to compare best, hybrid, and work-from-home budgets.

Explore what-if analysis in Excel by using goal seek and solver to maximize profit, find break-even points, and analyze data tables with fixed costs, unit costs, and production constraints.

Learn to analyze data in Excel using the Analysis ToolPak. Enable the add-in via file options and add-ins, then explore regression, descriptive statistics, and correlation to derive insights.

Learn four ways to protect your work in Microsoft Excel: password-protect sheets, encrypt the file, protect workbook structure, and adjust permissions from the home and review tabs.

Discover how Power Pivot links multiple tables to build a data model, enabling measures and KPIs beyond standard pivots, with practical steps to enable the add-in.

Import Excel data into a Power Pivot data model from multiple sources, using first row headers and diagram view to establish one-to-many relationships among academic, personal, and fee structure.

Learn to add formulas and measures to Power Pivot within a data model, linking fee structure, student details, and academic data to create pivot tables in Excel.

Discover how Power Query loads, cleans, and transforms annual data, combines multiple year files, creates travel days, and builds pivot tables for scalable reporting.

Enable the developer tab, open the Visual Basic for Applications editor, and learn to record macros, use modules, and understand the sub end sub syntax for Excel automation.

Create a custom VBA function named quarter to extract a date’s quarter, using a module, the month function, and simple conditional logic from a selected date.

Explore creating user forms in Excel with VBA, using the developer tab to insert a form, configure properties and colors, and add text boxes, checkboxes, and radio buttons.

Build and use a VBA-driven user form to capture student scores and save records to an Excel worksheet, exploring form controls, data validation, and clearing records.

Learn how Excel events in VBA trigger code on workbook and worksheet actions, such as creating a new sheet with a message box prompt and tracking cell changes.

Learn how to create and reuse custom Excel add-ins by saving code as an xlam file, loading it via the Developer tab, and calling a quota function from any workbook.