Microsoft Excel 2016 Master Class: Beginner to Advanced

Students will learn to identify and name every major component of the Microsoft Excel screen. This detailed tour covers everything from the Quick Access Toolbar and Ribbon to the Status Bar and Formula Bar, giving them the precise vocabulary needed to understand and follow along with all future lessons.

Students will learn the precise definition of an Excel worksheet and understand its role as the primary area for data entry and analysis. This lesson will clarify the crucial difference between a worksheet and a workbook, and explain how the grid of cells, columns, and rows functions as the fundamental canvas for all their work in Excel.

Students will learn a powerful and efficient keyboard shortcut for navigating large datasets in Excel. They'll master how to use the Ctrl + Arrow Keys combination to instantly jump to the beginning or end of data ranges, saving a significant amount of time compared to manual scrolling.

Students will learn how to effectively manage multiple worksheets within a single Excel workbook to better organize their data. They'll master the essential skills of adding, renaming, deleting, and reordering worksheet tabs, as well as how to navigate between them.

Students will learn how the Microsoft Excel Ribbon is organized and how to efficiently find the commands they need. This lesson will break down the Ribbon's structure into its core components—Tabs, Groups, and Commands—so they'll understand the logic behind the layout and can navigate it with confidence.

Students will learn the essential procedures for saving and opening their Excel workbooks. This lesson covers how to save a new file for the first time, save subsequent changes, and how to locate and open existing files, ensuring their work is always secure and accessible.

Students will learn the essential techniques for managing the structure of their worksheets by inserting, deleting, hiding, and unhiding columns and rows. They'll master how to add or permanently remove entire sections of their sheet, and also learn the valuable skill of temporarily hiding data from view without deleting it, allowing for a cleaner, more focused workspace.

Students will learn the fundamental process of entering and editing text within individual cells in an Excel worksheet. They'll explore the role of the Formula Bar for data entry, learn the correct procedures for confirming or canceling an entry, and understand how Excel handles text that is wider than the column.

Students will learn the various methods for editing existing data within a cell without having to completely retype it. They'll master the three primary techniques for entering 'edit mode'—using a double-click, the F2 key, and the Formula Bar—giving them the flexibility to quickly correct typos or update information.

Students will learn how to use the essential Undo and Redo commands to easily correct mistakes and reverse actions in Excel. They'll master both the on-screen buttons and the universal keyboard shortcuts, Ctrl + Z for Undo and Ctrl + Y for Redo, giving them the confidence to work without fear of making irreversible errors.

Students will learn about several of Excel's intelligent features designed to make data entry faster and more efficient. This lesson will cover time-saving tools like AutoFill to extend a series of data, AutoComplete to repeat previous entries, and the powerful Flash Fill to automatically detect and complete patterns in their data.

Students will learn the correct procedures for entering numerical data into an Excel worksheet. They'll discover how Excel automatically recognizes and formats numbers, and understand why this distinction from text is crucial for performing accurate calculations and formulas.

Students will learn how to enter dates in various formats that Excel can recognize and use for calculations. They'll discover how Excel stores dates as serial numbers and learn time-saving shortcuts, a foundational understanding needed to perform calculations, sort, and filter data by date.

Students will learn how to insert special characters and symbols that aren't available on their standard keyboard. They'll explore how to use the Insert Symbol dialog box to find and add various characters, such as currency symbols, mathematical operators, and trademark symbols, to their worksheets.

Students will learn the fundamental mouse techniques for selecting cells in an Excel worksheet, a prerequisite for almost any formatting or data manipulation task. They'll practice how to select a single cell, a range of cells, entire rows and columns, and even the whole worksheet using simple click-and-drag methods.

Students will learn how to use keyboard shortcuts to select cells and ranges, offering a faster and more precise alternative to using the mouse. They'll master essential combinations like Shift + Arrow Keys for incremental selections and the powerful Ctrl + Shift + Arrow Keys to instantly highlight entire columns or rows of data.

Students will learn about a lesser-known but useful selection feature in Excel called Extend Selection mode. They'll discover how to use the F8 key to anchor a selection and then easily extend it using the arrow keys or mouse, without having to hold down the Shift key.

Students will learn how to create basic formulas to perform calculations, which is the core function that makes Excel a powerful tool. They'll master the fundamentals of starting a formula with an equals sign (=), using standard arithmetic operators, and referencing other cells to create dynamic calculations that update automatically when data changes.

Students will learn about the Order of Operations, the mathematical rule Excel follows to ensure formulas are calculated correctly and consistently. They'll understand the PEMDAS hierarchy and, most importantly, learn how to use parentheses to control the calculation sequence and guarantee their formulas produce the intended results.

Students will learn how to perform basic calculations with dates by leveraging Excel's powerful date serial number system. They'll practice common operations such as subtracting dates to calculate the duration between two points in time and adding days to a date to project future deadlines or events.

Formulas with Text

Multi-Sheet Formulas

Students will learn the fundamental and essential skill of copying and pasting cells and ranges to duplicate data within their worksheets. They'll master the various methods for this common task, including using the Ribbon, the right-click menu, and the universal keyboard shortcuts Ctrl + C for Copy and Ctrl + V for Paste.

Move Cells or Ranges

Students will learn how to copy and move entire columns and rows to quickly restructure their worksheets. They'll master the difference between copying, which duplicates data, and moving (or cutting), which relocates it, using both keyboard shortcuts and drag-and-drop techniques.

Copying Formulas and Absolute References

Using the Clipboard Task Panel

Students will learn how to move and copy entire worksheets, either within the same workbook or between different Excel files. They'll master using the 'Move or Copy' dialog box to efficiently duplicate sheet layouts and data, a critical skill for creating templates or consolidating information from multiple files.

Fill Handle Basics

Students will learn how to use the Fill Series feature to create complex, customized sequences of numbers and dates with precision. They'll explore how to generate linear, growth, and date series, and how to control the sequence by setting specific step and stop values, offering more power than the basic AutoFill handle.

Custom Fill Series

Students will learn what Named Ranges are and why they are a powerful tool for making their formulas easier to read, understand, and manage. This overview will demonstrate how to assign a memorable name, like 'TaxRate', to a cell or range, and then use that name in calculations instead of a standard cell address like 'B2'.

Students will learn how to manage the scope of their named ranges, allowing them to create names that work across the entire workbook or are specific to a single worksheet. They'll discover how to create worksheet-level names, which enables them to reuse the same name like 'Total' on multiple sheets to refer to different data, a key skill for building organized, multi-sheet models.

Students will learn a time-saving trick to quickly navigate large and complex workbooks using a feature they already know: Named Ranges. They'll discover how to use the Name Box as a dropdown menu to instantly jump to any named cell or range, effectively using these names as bookmarks to navigate between important sections without scrolling.

Students will learn how to use the SUM function, Excel's most popular and fundamental tool for adding together a range of numbers. They'll master both writing the function manually and using the time-saving AutoSum feature to instantly calculate totals for rows and columns of data with a single click.

Students will learn how to use the Quick Analysis Tool, a powerful feature in Excel that provides instant access to a variety of data analysis and visualization options. They'll discover how to use this contextual menu to rapidly apply conditional formatting, create charts, insert totals, and add sparklines with just a few clicks, directly from their selected data.

Students will learn how to use the powerful IF function, one of the most fundamental functions in Excel for making decisions and performing logical tests. They'll master its structure—combining a logical test with a value_if_true and a value_if_false—to create dynamic formulas that return different results based on whether a condition is met.

Students will learn how to make their IF statements more powerful by testing for multiple criteria at the same time. They'll master how to use the AND function to check if all conditions are met and the OR function to check if at least one condition is met, allowing for more complex and realistic decision-making in their formulas.

VLookup 1 - Simple Example

VLookup 2 - Match Within a Range

Vlookup 3 - Intro to Data Validation Lists

Vlookup 4 - Multiple VLookups Using the Validation List

Students will learn how to use a new set of essential Excel functions to quickly perform common statistical calculations and summarize their data. This lesson will introduce them to key functions such as AVERAGE, MIN, MAX, and COUNT, which are fundamental for everyday data analysis.

Proper, Round and Other Functions