Mastering Microsoft Excel: From Basics to Advanced Technique

Learn how to work with rows, columns, and cells, insert or delete elements, and hide or unhide data using keyboard shortcuts and cell addresses.

Edit data in cells in Excel by inserting, deleting, hiding, and unhiding rows and columns; move or copy values using cut and paste.

Learn to customize the quick access toolbar and ribbon in Excel by adding and removing commands through the customize options, file tab, and ribbon settings.

Master worksheet views, including normal, page layout view, and page break view, while using freeze panes to lock rows and columns, margins, orientation, and headers and footers.

Learn to manage an Excel workbook by creating, renaming, deleting, and adding sheets, navigating within and across sheets, and using hide, unhide, protect, and unprotect features to secure data.

Master how to manipulate data in a worksheet by merging cells, applying alignment and orientation, wrapping text, using format painter, and employing relative and absolute references in formulas.

Learn to format data types in Excel by applying long date, currency, and text formats to selected ranges using the numbers tab, and explore importing data into workbooks.

Mastering Excel lists lets you sort data by single-level, multi-level, or custom levels and format the range as a table with headers, filters, and a dynamic range.

Create, format and modify tables in Excel, ensure headers, and use automatic filtering, dynamic range expansion, table styles, and calculated columns for efficient data analysis.

Explore database functions in Excel, including Dsum, Dcount, and D average, and apply them to a dataset to sum salaries by criteria, count records, and compute average salaries.

Master the structure of an Excel function and apply formulas—sum, average, max, min, count, if, and ifs—and text functions like left, right, mid, len, concat, and textjoin to transform data.

Mastering Microsoft Excel teaches inbuilt logical and date and time functions, including switch, sumif, averageif, countif, sumifs, and other date and time tools through practical problem solving.

Identify Excel formula errors such as division by zero, name errors, reference errors, and circular references, and troubleshoot with trace precedents, dependents, watch window, and evaluate formula.

Explore formatting and validation in Excel to ensure accurate, consistent data. Learn applying font styles, colors, alignment, and data types like text, number, currency, and date, plus format painter.

Discover how to validate entered data, apply conditional formatting to highlight salaries above 70,000, below 50,000, or between 65,000 and 70,000, and insert sparklines to visualize salary and age trends.

Create and customize column, bar, histogram, and pie charts in Excel to visualize data, adjust titles, axis labels, data labels, gridlines, legends, styles, and data filters.

Explore how to create and modify advanced Excel charts: waterfall, combo, and funnel charts, using chart elements, styles, and colors to analyze financial data and trends.

Master pivot tables in Excel by creating and configuring pivot tables, adding slicers, grouping data, and formatting results to analyze sales by salesperson, product, region, and month.

Create and manipulate pivot charts and pivot tables to summarize sales data, apply chart styles, data labels, and color palettes, and filter for region and product insights.

Master lookup and vlookup in Excel, using vector and array forms, with lookup value, lookup vector, and result vector, plus exact and approximate matches.

Master hlookup for horizontal lookups in Excel, using table arrays and row index numbers; combine with if to evaluate four quality parameters and decide batch pass/fail and release.

Learn how to use excel's index and match functions, in array and reference forms, for powerful lookups, including two-way, exact, case-sensitive, and closest matches.

Discover how macros automate Excel tasks with VBA, including recording, editing, formatting, and applying calculations, and copy macros between workbooks.

Explore VBA basics, including what VBA is and how to create a module and a sub procedure in the Visual Basic Editor for automating Office tasks.

Explore conditional statements and loops in VBA, including if, do while, for each, and for next, with practical examples like grading by age, first negative number, and total sales.