Microsoft Excel Mastery: Beginner to Advanced

Learn how to create basic formulas in Excel using equals, plus, minus, asterisk, and forward slash to perform addition, subtraction, multiplication, and division with automatic recalculation for membership totals.

Master average, max, and mean calculations in Excel using the insert function and auto fill, while preserving formatting and efficiently copying results down the table.

Master absolute cell referencing in Excel by locking a cell with dollar signs, using the F4 shortcut, and understanding when to use relative, mixed, and absolute references.

Execute cross-sheet calculations by linking values from the 2015 sales sheet and 2016 total to build a dynamic, updating sales summary.

Apply borders to improve data readability and emphasize totals by using all borders, top and bottom borders, or outside borders; customize color and style in the more borders dialog.

Learn to apply and customize Excel number formats, including general, currency, accounting, percentage, and date formats, use custom formats, and manage clear formats for accurate calculations.

Apply built-in styles from the home tab to ensure consistent headings and totals across worksheets, and use themes to align colors and fonts across Excel, PowerPoint, and Word.

Master single and multi-level sorting in Excel, sorting by account manager, then by total sales and date, with header recognition and ascending or descending options.

Sort data by state, locate Rajastan and Maharastra records with find and match case, and insert page breaks to place each group on its own page for printing.

Learn to set up print titles by repeating rows and the first column, adjust print area, and choose page order and grid lines options for clear, accurate prints.

Master 3D formulas to sum staff expenses across multiple worksheets efficiently, using 3D references, and copy across sheets, while noting identical structure and potential issues when moving sheets.

Consolidate by position across multiple workbooks using the data tools consolidate feature to sum ranges in each book without links.

Learn to combine full names and emails in Excel using concat and concatenate, plus the ampersand, a space in quotes, and copying the formula down.

Learn to split a combined location column into building code and phone extension using left, mid, and right Excel functions, with a find helper to handle varying word lengths.

Learn date calculations in Excel using today, now, and yearfrac to display current date and time, measure years between dates, and forecast next review dates.

Learn to create, manage, and use named ranges in Excel, using create from selection, apply names, and base names to replace references in formulas and document them for others.

Master Excel count functions to tally shipped orders, total orders, and missing order priorities using named ranges, counting numeric values, non-empty cells, and blanks.

Learn to summarize sales data in Excel using the sumifs function with one or multiple criteria, including handling absolute and relative references to drag formulas across a dashboard.

Learn how to work with tables in Excel, compare ranges vs tables, select data with table arrows, remove duplicates, and use sorting, filtering, and the total row.