The Excel Masterclass- The Complete Microsoft Excel Course

Learn to work with large data in Excel by differentiating headers, formatting for readability, and using efficient selection methods to manage thousands of employee records.

Customize the Excel ribbon by adjusting the data tab to reveal tools like flash fill and remove duplicates, and learn how to reset the selected ribbon.

Learn quick sorting in Excel by sorting the first name column using the data tab, choosing ascending or descending order to organize employee data efficiently.

Learn two-level sorting in Excel using the custom sort: sort by last name, then by first name, with ascending order to group and break ties.

Sort by month joined using a custom list to place January through December, then sort horizontally by rows to arrange employee data (months joined, year joined, batch, department, usernames).

Format data as a table to turn datasets into readable shaded tables with headers and filters, and use the total row to summarize values with sum, count, min, and max.

Remove duplicates in Excel using the Remove Duplicates tool from the Data tab or Table Design tab, and select the relevant column or all columns to keep only unique rows.

Learn to use daverage and dcounts in Excel to compute average sales and counts with criteria, using the insert function and dynamic fields to analyze by seller or product.

Learn how to use the subtotal function in Excel to compute sum, average, or count, compare it with a normal sum, and filter to show totals by category.

Explore data validation in Excel to ensure accurate data entry, using list-based validation, drop-down menus, and invalid data checks to enforce correct sales records.

Learn how to create Excel data validation with customizable error alerts and input messages, including stop, warning, and information notices, plus dropdown prompts and helpful hints.

Learn to apply dynamic data validation by referencing a range for dropdown lists, updating source data easily, and using circle invalid data and custom error messages to guide correct entry.

Explore creating pivot tables in Excel, placing reports on a new worksheet, configuring fields such as filters, columns, rows, and values, and analyzing sales by product, month, and salesperson.

Modify pivot tables to analyze sales by salesperson and product, adjusting value field settings to use average and show values as percentage of grand total or previous month.

Group data in pivot tables by selecting months, applying group commands to form quarters, rename to Q1–Q4, and toggle collapsible totals like the sum of sales.

Format pivot tables to improve readability by applying currency and number formats, adjusting layout with compact form and subtotals or grand totals, and using banded rows and color schemes.

Explore how pivot tables summarize data and drill down to reveal detailed entries behind totals by double-clicking, creating breakdown sheets and refreshing after edits.

Explore pivot charts built from pivot tables to visualize sales data by salesperson and product, using analyze tools, pivot table options, and chart design for dynamic filtering.

Learn to filter pivot table reports in Excel by using the filter pane, moving month, product, and salesperson to the filters, enabling multi-level, multi-item selections.

Learn to use slicers with pivot tables in Excel to create interactive dashboards, filter data by salesperson or quarter, and perform multi-select with Ctrl or the multi-select option.

Explore Power Pivot basics as you link customer info and orders across two sheets, establish relationships, and build a multi-table pivot table to summarize data.

Activate the Power Pivot tab by going to file, options, add-ins, manage, excel add-ins, then go, select Microsoft Power Pivot for Excel, and click okay; the tab appears.

Add customer info and orders to a data model to enable pivot table analysis across sheets. Name the tables and link fields for cross-sheet analysis in Power Pivot.

Create relationships in Power Pivot by linking the customer info and orders tables on customer ID, forming a one-to-many relationship for pivot table analysis.

Create pivot tables from a two-table data model using Power Pivot, establishing relationships between customer info and orders, and explore fields, regions, freight, and shipping options.

Learn how to use the Excel if function to determine bonus eligibility based on a sales target, and master named ranges and absolute referencing with name manager.

Explore how the and function tests multiple weekly sales against a 200 threshold. Learn to evaluate all arguments and return true only if every week meets the target.

Explore how to use the minimum function to identify the lowest weekly sales, then nest it inside the if function to determine who qualifies for the bonus.

Count IT department employees with countif, then confirm the result. Use countifs for IT department members joined in 2015, with ranges and criteria.

Explore how to use the iferror function in Excel to gracefully handle division errors, replacing #div/0 with meaningful results like not applicable, within practical examples.

Master vlookup in Excel using a lookup value, table array, and column index to retrieve matches with false for an employee ID to fetch first names and dates of birth.

Learn Vlookup fundamentals: sort data ascending, place the lookup value in the leftmost column, and define the table array, column index, and exact-match false; handle errors with iferror.

Learn how to use hlookup to retrieve data horizontally and compare it with vlookup. Practice selecting the lookup value, table array, and correct row index with absolute references.

Master index and match for lookups beyond vlookup. Learn how index returns a value from a given row and column, and how match finds a value’s position with exact matching.

See index and match in action in a practical dashboard to retrieve total sales by item, using coffee and Kwame as examples. Learn how match locates the row.

Use data validation with lists to power an index and match driven Excel dashboard, selecting a salesman and a product to instantly retrieve corresponding sales values.

Explore how index and match outperform vlookup by locating the lookup value anywhere in the data and returning fields like date of birth, department, and email from the employee data.

Learn how xlookup replaces vlookup and hlookup, returning first name, gender, and email from an employee ID without moving columns, and explore free office web access for office 365 features.

Explore text based excel functions, including len to measure length and left, right, and mid to extract characters. Learn to concatenate and use flash fill for automatic data completion.

Learn how to use the future value function in Excel for monthly contributions over five years at 18 percent, using what-if analysis, goal seek, and data tables.

Use the fv function to model future value and build data tables for monthly contributions. Explore varying years and amounts with what-if analysis and goal seek to see outcomes.

Import a folder's file names into Excel using the files function and index, define a name for the list, and auto-fill the rest with Ctrl+E.

Explore how ChatGPT enhances Excel by generating and explaining formulas, performing autosum on C5:F5, and using concatenate, the and function, and FlashFill to combine names.