Microsoft Excel Masterclass (2016/2019) - Basic to Advanced

Create your first Excel file by opening a blank workbook, entering item name, quantity, and price for an inventory list, and saving it on the desktop.

Master formatting basics in Excel, including fonts, colors, borders, alignment, and merge and center; learn to insert, delete, hide, and rename sheets, and format numbers as currency.

Master building and evaluating Excel formulas with cell references rather than hardcoded values. Observe order of operations and how cell changes update results, plus absolute and relative references.

Master how to use relative, absolute, and mixed references in Excel formulas, drag and copy formulas across cells, and apply F4 to freeze columns or rows for accurate calculations.

Explore how Excel's built-in mathematical functions streamline calculations, from sum and average to max, min, mean, median, mode, and round, with automatic range updates.

Learn to sum data with Excel's sum and subtotal functions, handle noncontiguous ranges, avoid double counting, and drag formulas to extend totals across rows and columns.

Explore essential text functions in Excel, including concatenate, length, left, right, mid, and search, to transform and extract text with nesting and counting of text values.

Master logical functions in Excel—with if, and, and or—using true/false tests on flavor, price, and size to decide buy or don't buy.

Explore Excel's conditional mathematical functions, including sumif and countif, to sum incentives and count employees by criteria like experience under 10 years or ids over 105.

Learn how to apply data filters in Excel to display only relevant records, using multi-level criteria, date ranges, and numeric conditions, while preserving your original data.

Convert your data into an Excel data table to enable auto filters, dynamic expansion, and structured references, while naming the table for quick navigation.

Create and customize bar and column charts in Excel from raw data, using insert options, designs, and chart elements like axis titles and data labels for clear, comparable visuals.

Learn to create line charts in Excel to compare monthly revenue for tables and chairs, format axes, add data labels and markers, adjust series order, and design clear trend visuals.

Learn to create and customize pie and doughnut charts in Excel, add percentages to data labels, adjust angles and explosion, and compare chart types for dashboards.

Learn how to print in Excel with print preview, page layout, margins, headers and footers, and print area controls to fit content on one page.

Save Excel workbooks in multiple formats, including CSV and text, and retrieve data back into Excel using Get Data, Power Query, and load options.

Master Excel date and time functions to store and manipulate dates and times, using today, day, month, year, now, date, and networkdays for dynamic calculations.

Master excel database functions dsum, daverage, and dcount to query data by criteria. Learn how to set the database, field, and criteria to calculate totals, averages, and counts.

Learn to create combo charts in Excel by combining bar and line graphs, add data labels, and use a secondary axis to compare targets with actual sales.

Explore conditional formatting in Excel to highlight top twenty-five percent, top ten, bottom fifty, and values over ten thousand or under five hundred, with editable rules for dashboards.

Master how data bars use conditional formatting to visualize values in a range, showing relative size for positive and negative numbers and enabling color customization.

Learn to use icon sets with conditional formatting to visualize performance, applying green, yellow, and red indicators based on rules and moving icons to the right of the data.

Learn how to apply color scales in conditional formatting to create heat maps that reveal trends and max/min values, with practices to avoid totals and maintain a professional color palette.

Learn to apply conditional formatting with logical functions, using data by vendor, city, units sold, and total sales; manage rule order and stop-if-true to highlight results.

Master Excel pivot tables to analyze, sort, filter, and present data for dashboard creation, using flexible row and column layouts, real-time updates, and clear reports.

apply conditional formatting to pivot tables using data bars, control grand total impact, and manage rules to tailor formatting across categories and subcategories.

Learn to filter pivot table data with city filters and slicers, connect slicers to multiple pivot tables, and manage filter clearing and pivot table naming.

Learn to retrieve specific pivot table data with get pivot data, using slicers and report connections, and keep revenue and items sold constant via a separate reference table.

Explore creating and formatting pivot charts from pivot tables, add slicers to filter by location and category, and customize charts with ready-made designs, chart titles, and dynamic updates.