Complete Microsoft Advanced Excel Certification

Learn Microsoft Excel layout basics, including the title bar and ribbon, create and open workbooks, manage worksheets, insert or delete rows and columns, and protect sheets.

Enter data and format in Excel, mastering fonts, font size, bold, italics, underline, borders, background color, alignment, merge and center, and wrap text.

Explore Excel's paste special options, including copying formulas, values only, and number formatting, preserving column width, and creating pictures or linked pictures from data.

Explore Excel's number formatting options, including general, number, currency, date, time, percentage, fraction, and scientific formats, with controls for decimals, thousand separators, and negative number displays.

Learn relative, absolute, and mixed references in Excel, and how to lock a cell or a row/column to copy formulas across rows or columns, including currency conversion.

Discover how to hide and unhide columns and rows in Excel using mouse options and keyboard shortcuts such as control zero, control nine, control shift zero, and control shift nine.

Learn advanced fill using autofill to generate sequences for numbers, months, and days, and create custom lists that persist across Excel files in this system.

Learn to use text to column in Excel to split a single name and surname column into separate fields via delimiter or fixed width, set destination, and skip columns.

Master flash fill in excel to extract digits, surnames. Use ctrl+e or flash fill from data tools group to get first four digits, last four digits, and emails.

Learn to customize Excel views with normal view, page break preview, and page layout, and create custom views to hide columns, display headers and footers, grid lines, and adjust zoom.

Learn to compare sheets from the same workbook by opening a window and using view side by side with synchronous scrolling. Toggle between sheets and arrange windows vertically or horizontally.

Learn to create and edit hyperlinks in Excel to existing files, web pages, document places (sheet and cell), new documents, and email actions, and to remove or change them.

Master basic Excel functions by using sum, average, count, max, and min; learn to insert formulas via home or formulas tab and use autosum for quick calculations.

Explore spell check and thesaurus in Excel via the review tab, using F7 and Shift+F7 to correct spellings and replace words, and view workbook statistics for data, tables, and formulas.

Master page layout in Excel by applying themes, adjusting margins and orientation, setting print area and print titles, and centering the page for clean, professional prints.

Master conditional formatting in Excel to highlight values by rule, including greater than, between, text contains, and date rules, plus data bars, color scales, icon sets, and custom formulas.

Learn to convert your data into a table, name and resize it, and use the design tab, styles, filters, and slicers to analyze with pivot tables.

Master advanced filter in Excel to copy filtered results to another location, set list and criteria ranges, and extract unique records, using criteria like region, hire date, and salary.

Learn to apply Excel's subtotal feature by sorting data, selecting the region and department columns, and adding region and department totals using sum and other functions.

Learn core text functions in Excel, such as length, find, left, right, mid, join/concatenate, lower, upper, proper, exact, trim, substitute, replace, and is text or is number, with practical examples.

Master date handling in Excel with functions like today, now, edate, eomonth, and networkdays to compute dates, working days, and holiday adjustments.

Explore the if function in excel, build logical tests with operators, and nest with and/or to handle multiple conditions, including pass/fail and yes/no outputs.

Master statistical functions in excel, including sumifs, countifs, and averageifs, using multiple criteria to analyze region, department, and dates with practical examples.

Master vlookup and hlookup in excel, including exact and approximate matches, vertical and horizontal lookups, and practical tips with named ranges, trimming spaces, and joining fields for advanced lookups.

Master the vlookup and match function combination to retrieve part details, description, buyer name, car model, and quantity for a given part number with exact matches.

Learn to use index with match to overcome vlookup limitations and perform reverse lookups by row and column. Build exact-match lookups with data validation to fetch employee details and salaries.

Master array formulas in Excel, including entering with Ctrl+Shift+Enter, to produce single or multiple results. See examples for transpose, sum with arrays, and vlookup with arrays.

Learn to handle Excel errors with the if error function, using VLOOKUP with exact match and returning outputs like zero or invalid input when data is missing.

Define and use named ranges in Excel to simplify workbook navigation and speed up worksheet creation, using name box, name manager, define name, and create from selection.

Apply data validation in Excel to control numbers, decimals, text length, dates and times; create dropdown lists, customize input messages and error messages, and enforce uniqueness with custom rules.

Learn to summarize data across multiple sheets using the consolidate feature, replacing 3D references with robust consolidation that uses labels, left column, and links to source data.

Install and use the solver add-in in Excel, set objective cell, and add constraints to optimize up to 200 decision variables, illustrated with subject marks to reach a percentage.

Explore how to create and customize Excel charts, including column, line, pie, donut, bar, area, and scatter charts, with embedded and new sheet options, labels, legends, and trends.

Learn to create dynamic pivot tables from worksheets or external data, transform numeric data into concise region and salary summaries, and enhance analysis with slicers, timelines, calculated fields, and charting.

Explore what-if analysis in Excel, using Goal Seek to determine required inputs. Use Data Table to model dynamic outcomes and Scenario Manager to compare budgeted cases.

Explore import and export in Excel, from exporting to pdf or csv to importing from csv, text, or web sources, with table loading and refresh options.

Create interactive dashboards in Excel by building data models, pivot charts, and slicers; learn to connect multiple charts to a single slicer and format visuals.

Learn Visual Basic for Applications (VBA) and macros to automate repetitive tasks in Excel and PowerPoint, create custom functions, and leverage the developer tab and Visual Basic Editor.

learn to record macros in Excel from the developer tab, format headings with fonts and colors, and save as a macro-enabled workbook.