Library

Course: Microsoft Excel - Excel from Beginner to Advanced

Microsoft Excel - Excel from Beginner to Advanced

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

Microsoft Excel All-In-One Training

This Microsoft Excel course includes all three Levels from Beginner to Advanced

  • Microsoft Excel Level 1 - Beginner
  • Microsoft Excel Level 2 - Intermediate
  • Microsoft Excel Level 3 - Advanced

As your instructor I will use my 16+ years of Excel training and certifications including: Microsoft Office Specialist, Expert and Master Instructor: 2000, XP (2002), 2003, 2007, 2010, 2013 and 2016, Microsoft Certified Trainer (MCT), Microsoft Certified Professional (MCP) to guide you step by step through Beginner, Intermediate and Advanced Levels of Excel.

At completion of this course you will master Excel skills that will alleviate the discomfort of uncertainty and reduce time working through your daily tasks, as well have all the training you need to help pass both: Excel 2016 Core (77-727) and Excel Expert (77-728) Exams and become certified, like me. Below are just a few of the topics that you will master:

  • Creating effective worksheets
  • Writing and editing formulas and calculations
  • Use sorting and filtering options
  • Build a database right the first time and avoid common design flaws
  • Creating informative and captivating Charts
  • Eliminate repetitive tasks instantly using Macros
  • Create many scenarios with varying groups of data
  • Manage large ranges of data in a List or Table
  • Increase productivity with functions: IF, PMT, VLOOKUP, FIND, SEARCH, MATCH, INDEX and much more
Basic knowledge
  • This course was created with Excel 2016. However, Excel 2007, 2010, or 2013 will work with most, or all features
  • You will need Microsoft Excel installed on your computer to follow along and complete the exercise files
What you will learn
  • Master Microsoft Excel from Beginner to Advanced
  • Build a solid understanding on the Basics of Microsoft Excel
  • Writing Formulas and performing Calculations
  • Increase productivity with functions: IF, PMT, VLOOKUP, FIND, SEARCH, MATCH, INDEX and much more
  • Create charts with Trend Lines, and Dual Axis
  • Use Excel as a Database with Sort and Filter options
  • How to avoid the most common Database Design flaws
  • Taught by Certified Microsoft Office Excel 2016 Specialist and Expert with over 16 years of Excel training
Curriculum
Number of Lectures: 157
Total Duration: 15:33:53
Excel Level 1 – Beginner
  • Level 1 - Exercise Files, Instructor's Notes, Quiz and Answers  
    1. Exercise Files - the exercise files are provided for you to practice what you've learned after watching the training videos. The exercise files are numbered, and are the exact same as the instructor uses in the video
    2. Instructor Notes - are quick notes about the main points covered in the training videos, and are typically used to reference a point quickly without having to watch the entire training video over again
    3. Quiz & Answers - there are two separate .pdf files: Quiz and Answers. The Quiz has one question for each video to test yourself on what was presented, and of course the answers to compare
Basics
  • Excel 2016 Introduction - First Look  

    Excel 2016 introduction for beginners who want to get started with the first look at Excel 2016, by Microsoft Certified Trainer, Excel 2016 Specialist and Expert.

  • Themes, Save Location, Back Stage & Fonts  

    Learn how to set default options for: Office Theme, Save Location, Backstage , Font, Body Font, Theme Fonts and Excel 2016 Startup Screen.

  • Quick Access Toolbar  

    Excel 2016 training video on how to customize the Quick Access Toolbar, by adding and removing commands from the toolbar that you can then access from it in a single click! Excel 2016 training video.

  • Ribbon  

    Excel 2016 training video on how to customize the Ribbon including: adding Tabs, Groups and Commands, as well as hiding, deleting or resetting it. Watch my Excel 2016 training on the Ribbon!

  • Navigating Shortcuts, Tips, Tricks & Selecting Cells  

    Excel 2016 tutorial on how to navigate the cells in your spreadsheet with some great tips, tricks and shortcuts. Also learn how to select cells either separately, as a group, or non-linearly.

  • Data Entry & Basic Worksheet  

    Excel 2016 tutorial on the basics of entering data into a cell, on a spreadsheet! And then how to create your own first mini database, or table of data!

  • Save, Save As & Backup Copy  

    Excel 2016 tutorial on how to save your workbook, and the difference between "Save" and "Save As"; where you can use the "Save As" feature to save a copy of your workbook. However, the first time you save any Excel workbook it will force you to "Save As" and I explain why in the Excel tutorial.

  • Convert To 2016  

    Excel 2016 tutorial on how to convert older versions of Excel to Excel 2016.

  • Compatibility Checker  

    Excel 2016 Tutorial on how to check the compatibility of the current version of Excel when converting it to an earlier version of Excel. For example, some features in Excel 2016 may not appear, or function in earlier version of Excel, and the Compatibility feature will check to see what current Excel 2016 features won't convert.

  • Tell Me What You Want To Do - The New Help Feature  

    Excel 2016 tutorial on the new Excel 2016 feature, "Tell Me What You Want To Do", or the newest version of accessing Help in Excel 2016.

Functions & Formulas
  • Basic Formulas  

    Excel 2016 tutorial on how to create basic formulas in Excel. Also, some simple tricks, tips and shortcuts to adding additional formulas into other cells like copy and pasting, or using the AutoFill feature. There's more than one way to enter a formula, and depending upon your work you may find it necessary to learn all in this basic formulas Excel 2016 tutorial.

  • SUM & AVERAGE  

    Excel 2016 tutorial on how to sum, or add up a range of cells by using the SUM function, or average a range of cells by using the AVERAGE function. As well as other tips, tricks and shortcuts like: how to show and hide functions, calculation options, error check and more in the Excel 2016 tutorial on SUM and AVERAGE functions!

  • MIN & MAX  

    Excel 2016 tutorial on how to find the minimum or lowest (smallest) value in a range of cells using the MIN function, or the maximum, or highest (largest) value in a range using the MAX function. Please let me know what you think after watching my MIN and MAX functions Excel training video!

  • COUNT & COUNTA  

    Excel 2016 tutorial on how to count the number of cells in a range that contain numbers using the COUNT function, and how to count the number of cells in a range that are not empty using the COUNTA function in this Excel training video.

  • Move, Copy, Cut, & Paste Data into Cells  

    Excel 2016 tutorial on how to work with your data from one cell to another in your workbook, on a worksheet by moving, copying, cutting and pasting your data. I'll show you the many ways that can increase your efficiency when it comes time to move, copy, cut and paste your data in this free Excel 2016 training video.

  • Absolute & Relative References  

    Excel 2016 tutorial on how to create a cell reference that remains the same when the formula is copied, or autofilled to other cells, known as an Absolute Reference, as opposed to a reference that always changes when the formula is copied to another cell known as a Relative Reference in this Excel training video.

  • Mixed References  

    Excel 2016 tutorial on cell references that contain both an absolute and relative reference known as a mixed reference. Remember, an absolute reference is when a formula remains the same when it has been copied to other cells, and relative is where the formula changes when copied to another cell; and when a formula contains both it is known as a mixed reference.

  • AutoFill  

    Excel 2016 tutorial on how to quickly copy the contents of a cell or range of cells to adjacent cells using the AutoFill feature, in this free Excel training video.

  • FlashFill  

    Excel 2016 tutorial on FlashFill, which is part of the AutoFill feature. To use the FlashFill, you need to be next to a column of data you want to change, or actually even several columns away, as long as it's within the same database range; then when FlashFill recognizes a pattern after you type in a few, it will fill in a preview of what it thinks is a pattern, and then you can hit Enter to accept, or continue giving the FlashFill more examples, in this free Excel training video.

  • Converting Text into Columns  

    Excel 2016 tutorial on how to break up phrases, or groups of words or data into cells of their own by using the Text to Column feature in the free Excel training video.

Editing Worksheets
  • Editing Cell Data  

    Excel 2016 how to edit data within a cell that includes tips, tricks and shortcuts in the Excel training video on editing the contents of a cell.

  • Insert & Delete Cells, Columns and Rows  

    Excel 2016 tutorial on how to insert and delete cells, columns, rows in this Excel training video.

  • Find & Replace Data  

    Excel 2016 tutorial on how to find data like text, or numbers and also formats within your spreadsheet and workbook, and also to replace those instances in a single click in this Excel training video on finding and replacing data.

  • GoTo  

    Excel 2016 tutorial on how to use the GoTo feature in Excel. This GoTo feature is used to go to a referenced cell, or named range quickly, and will remember the last cell you were, when after advancing to another cell in this Excel training video.

  • Transpose or Flip a Range of Data  

    Excel 2016 tutorial on how to Transpose data, or switching column labels into rows labels, and row labels in to column labels in this Excel Transpose data training video.

  • Change Height, Width, & Hide Rows & Columns  

    Excel 2016 tutorial on how to adjust the size, height, width of your columns or rows, as well as how to hide and unhide them in this Excel training video.

  • Check Worksheet or Workbook Spelling with Spell Check  
  • Smart Lookup & Thesaurus  
Formatting
  • Formatting Text  

    Excel 2016 tutorial on how to apply formatting like bold, italics and text alignment to a cell containing text as you'll see in this Excel 2016 training video on formatting.

  • Basic Number Formats  

    Excel 2016 tutorial on how to apply different number formats like: currency, including various currencies around the world and their symbols and variation of date and time. Watch this Excel 2016 training video on basic number formats!

  • Basic Custom Number Format  

    Excel 2016 tutorial on how to create a basic custom number format. Learn how to create a custom number format like Social Security field that adds text to the number, but without actually being part of the number as you'll see in this Excel 2016 training video on custom number formats.

  • Adding Borders & Colors to Cells  

    Excel 2016 tutorial on how to add and remove borders and colors or shading to your cell or range of cells in this Excel 2016 training video on borders and colors!

  • Formatting Shortcuts  

    Excel 2016 tutorial on how to use formatting shortcuts like the: Format Painter that will copy all the formats of a selected cell (or range of cells) that you can then apply to another in a single click. Also, the secret behind the F4 key and how not only to apply the most recent format, but a combination of many formats as you'll see in this Excel training video on formatting shortcuts, tips and tricks!

  • Adding Background  

    Excel 2016 tutorial on how to add a background image to your worksheet in this Excel 2016 training video.

  • Align, Merge & Wrap Cell Data  

    Excel 2016 tutorial on how to align the contents of a cell, left, right, or centered, merge two or more cells together as well as how to unmerge, and wrapping the data within a cell so the cells grows vertically to accommodate the text that doesn't display width wise in the cell as you'll see in this Excel 2016 training video.

  • Cell Styles  

    Excel 2016 tutorial on Cell Styles which is a collection of formats you can apply to a cell, or range in a single click like: number, alignment, font, border, fill and protection as you see in this Excel 2016 training video on Cell Styles.

  • AutoFormat  

    Excel 2016 tutorial on how apply a predefined selection of colors, fonts and number formats by using the AutoFormat feature in a single click as you'll see in this Excel 2016 training video.

  • Applying Themes  

    Excel 2016 tutorial on Themes which is a collection of complementary colors, fonts and effects you can apply to your entire work book in a single click as you'll see in this Excel 2016 training video on Themes.

  • Basic Conditional Formatting  

    Excel 2016 tutorial on how to apply a format to a cell when a criteria has been met, or conditional formatting. You'll see the top 5 most popular conditional formats used including: highlight, top and bottom values, data bars, color scales and icon sets in this Excel 2016 training video.

  • Conditional Formatting Manager  

    Excel 2016 tutorial on how to use the Conditional Formatting Manager to edit, or delete your conditional formats of a selected range or worksheet as you'll learn in this Excel 2016 training video.

Print
  • Print Page Breaks, Grildines & Headlines  

    Excel 2016 tutorial on how to display or hide gridlines and headlines in the spreadsheet as well as from printing. Also, I'll cover the page break line that appears after you come out of the Print Preview as you'll see in this Excel 2016 training video.

  • Page Orientation  

    Excel 2016 tutorial on how to use Portrait or Landscape as the Page Orientation to view more data vertically, or horizontally respectively when you print out your spreadsheet as you'll learn in this Excel 2016 training video.

  • Print Rows at the Top of Each Page - Repeat Column Headers  

    Excel 2016 tutorial on how to print and have repeated at the top of each printable page your column headers, or on the left your row headers. Training done by Microsoft Certified Trainer and Excel 2016 Specialist and Expert.

  • Headers & Footers  

    Excel 2016 tutorial on how to have data, or images repeated at the top, or bottom of every printed page known as headers and footers; like title, name, logo, date, or page number. Watch this Excel 2016 training video on a couple of ways of how to insert a header or footer into your spreadsheets.

  • Editing Page Margins & Center on Page  

    Excel 2016 tutorial on how to edit your margins prior to printing your spreadsheet, as well as how to center on page your data either horizontally or vertically.

  • Page Breaks  

    Excel 2016 tutorial on how to control the amount of data printed on a page by using page breaks. You'll learn how to view, insert and remove them in the Page Break Preview workbook view.

  • Setting Print Range & Scaling  

    Excel 2016 tutorial on how to set a cell, or range of cells as a print area that will be the only part of your spreadsheet that prints, and how to scale down you font size to allow more to fit onto your printed page prior to printing in this Excel 2016 training video.

Modifying Worksheet Appearance
  • Name, Move, Copy, Color, Link Worksheets in Workbook  

    Excel 2016 tutorial on how to name and add color to the worksheets tab, as well as how to, move, or copy worksheets within workbook, or into another. Also, learn how to data on one worksheet into another, or another workbook in this Excel 2016 training video.

  • Hide Worksheets & Workbooks  

    Excel 2016 tutorial on how to hide and unhide spreadsheets and workbooks.

  • Split Worksheet  

    Excel 2016 tutorial on how to divide the worksheet window into different panes that each scroll separately by using the Split feature.

  • Freeze Panes  

    Excel 2016 training video on how to freeze panes which will keep rows and columns visible while the rest of the spreadsheet scrolls (and is based upon current selection).

  • New Windows - Multiple Workbook Views  

    Excel 2016 tutorial on the New Windows feature that allows you to open up the same workbook so you can work in different places at the same time.

  • Customize Formula Options  

    Excel 2016 tutorial on how to customize formula options including how to disable background error checking.

  • Speak Cells  

    Excel 2016 tutorial on how to turn on the Speak Cells feature that has Microsoft Excel speak back through your speakers the contents of your cells.

Excel Level 2 – Intermediate
  • Level 2 - Exercise Files, Instructor's Notes, Quiz & Answers  
    1. Exercise Files - the exercise files are provided for you to practice what you've learned after watching the training videos. The exercise files are numbered, and are the exact same as the instructor uses in the video
    2. Instructor Notes - are quick notes about the main points covered in the training videos, and are typically used to reference a point quickly without having to watch the entire training video over again
    3. Quiz & Answers - there are two separate .pdf files: Quiz and Answers. The Quiz has one question for each video to test yourself on what was presented, and of course the answers to compare
Advanced Formulas & Functions
  • Named Ranges  

    Excel 2016 tutorial on how to assign your own name to a cell, or range of cells. This can be very helpful when using a familiar name of a cell, or range of cells instead of Excel's default names, and also these names are links that when selected will take you right to that named cell, or range of cells; additionally these names you assign can also be used in formulas too!

  • Version Control  

    Excel 2016 tutorial on the Version Control feature that can be used to protect your workbook if you closed out of it without saving it; and by default it's turned on, and by default will automatically save your workbook every 10 minutes, and you can change that time.

  • Linking Data From One Cell, or Range into Another  

    Excel 2016 tutorial on how to link data from one cell(s) into another, as in adding up a bunch of cells from different worksheets into a cell; and when any changes are made in those other cells, that cell is automatically updated.

  • Convert Text to Upper, Lower, or Proper Case using Formulas  

    Excel 2016 tutorial on how to convert your text to upper, lower or even proper case. Proper case being able to convert the first letter to upper case or being capitalized. Training done by Microsoft Certified Trainer and Excel 2016 Specialist and Expert.

  • CONCATENATE  

    Excel 2016 tutorial on how to use the CONCATENATE function to combine two or more columns of data into one.

  • LEFT, SEARCH & FIND  

    Excel 2016 tutorial on LEFT function that returns the number of characters at the start, or "left" side of the cell, and the SEARCH and FIND functions that are used to find the text like a space delimiter within a cell, and when these two are combined Excel will count from "left" to right the minimum number specified in the "search", up unto and including the delimiter, as you'll see in this Excel 2016 training video on LEFT, SEARCH and FIND functions.

  • RIGHT, FIND & LEN  

    Excel 2016 tutorial on RIGHT function that returns the number of characters beginning at the end, or "right" side of the cell, and the FIND function is used the find the text like a space delimiter with a cell, and the LEN function is used to count each characters in the cell, and when these three are combined, Excel will return the exact number of characters from the right most part of the cell, as you'll see in this Excel 2016 training video on RIGHT, FIND and LEN functions.

    RIGHT, FIND & LEN Functions.

  • MID  

    Excel 2016 tutorial on the MID function which is used to return the characters from the middle of a text string, given a starting position and length.

    MID.

  • AND & OR  

    Excel 2016 tutorial on the AND function where the logical tests has to meet all conditions, and the OR function where it only needs to meet one condition, or criteria.

  • IF  

    Excel 2016 tutorial on how to use the IF function, where based upon a logical test, like if once cell is greater or equal another, then enter another function or statement if true, and if false then another.

  • Combining & Nesting Functions AND & IF  

    Excel 2016 tutorial how to combine and nest functions including the AND and IF functions.

  • IFERROR  

    Excel 2016 tutorial on the IFERROR function which is used to replace Excel's default error expressions with one of your choice that is more readily understandable for other users.

  • PMT or Payment  

    Excel 2016 tutorial on the PMT or Payment function that is used to calculate the monthly payment amount on a loan like a car or house using the three variables in the syntax including: interest rate, number of payments and the principle amount.

  • Display & Print Formulas  

    Excel 2016 tutorial on how to show, or display and print formulas or functions instead of their resultant values.

  • SUMIFS, COUNTIFS & AVERAGEIFS  

    Excel 2016 tutorial on how to place conditions, or criteria on what you want to sum or add up, count up, or average out by using the SUMIF, COUNTIF and AVERAGEIF as well as the plural SUMIFs, COUNTIFs and AVERAGEIFs functions respectively.

  • CONVERT  

    Excel 2016 tutorial on the CONVERT function that will convert one unit of measurement into another like liters to gallons.

  • MATCH & INDEX  

    Excel 2016 tutorial on the MATCH function which will return a numerical representation of a value's place in a single row or column, or vector; and is often used to return an item's row number for the use in another lookup function like the INDEX function. The INDEX function returns the value in a row and/or column of a cell range.

  • LOOKUP  

    Excel 2016 tutorial on the LOOKUP function that will look in one row, or column range, or vector range, for a value and a return value from the same position in one row or column.

  • VLOOKUP  

    Excel 2016 tutorial on the Vertical Look Up or VLOOKUP function that allows you to look up a value, in a table, and compares the look up value to the values listed in the left most column of the table, and then returns the value from the row you specify as you'll see in this Excel 2016 training video on the VLOOKUP function.

  • HLOOKUP  

    Excel 2016 tutorial on the Horizontal Look Up or HLOOKUP function that allows you to look up a value, in a table, and compares the look up value to the values listed in the top most row of the table, and then returns the value from the column you specify as you'll see in this Excel 2016 training video on the HLOOKUP function.

Tables
  • Single & Multicell Array Formulas  

    Excel 2016 tutorial how to work with single and multi-cell arrays, and they're used to do multiple calculations on cells in a group, known as an array, simultaneously.

  • Create Tables  

    Excel 2016 tutorial on how to have Excel treat a part of your worksheet as a separate datasheet by converting that range of cells into a table.

  • Modifying Tables  

    Excel 2016 tutorial on how to modify tables including: add, remove rows or columns, removing duplicate records, adding a total row and more!

  • Format Table  

    Excel 2016 tutorial on how to format your table.

  • Custom Table Styles  

    Excel 2016 tutorial on how to create your own table style which can be composed of the table elements including: whole table, first column stripe, first row stripe, last column, header row, total row and more - applied to a table in a single click!

  • Table Sorting & Filtering  

    Excel 2016 tutorial on how to sort and filter your records in an Excel table.

  • DSUM, DAVERAGE & DCOUNT  

    Excel 2016 tutorial on how to add, average or count up a range of cells in a column (field) of records in a list or database, based upon the criteria you specify in a mini or sub database using the DSUM, DAVERAGE and DCOUNT functions in this Excel 2016 training video.

  • Date & Time Functions  

    Excel 2016 tutorial on how to calculate the difference between two dates, like the number of days, as well as inserting the exact time.

  • Sort Records In Database  

    Excel 2016 training video on how to sort your records by one or more fields in your database including by: text, numbers, colors, custom sorts and more!

  • AutoFilter  

    Excel 2016 tutorial on how to apply the AutoFilter feature to your database, allowing you to filter your records by column label, or header.

  • Advanced Filter & Macros  

    Excel 2016 training video on how to use the Advanced Filter feature on a list range, or database in conjunction with a criteria range. And to help the process go fast, I've also included a Macro!

  • Subtotals  

    Excel 2016 training video on how to use the Subtotals feature which is used to quickly calculate rows of related data by inserting subtotals and totals.

Charts
  • Creating Charts  

    Excel 2016 training video on how the basics of how to create charts and the chart elements including: Axes, Axis Titles, Chart Title, Legend, as well as formatting with styles, chart types including bar, columns and pie, and the chart's legend.

  • Modifying Charts  

    Excel 2016 training video on how to modify your charts including: resizing your charts, changing your chart's type, styles, colors, in a series, data labels, format data labels, callout labels, leader lines, move chart as object, or as a new sheet, change data range and more!

  • Trendlines  

    Excel 2016 training video on how to add a Trendline (trend line) to your chart, which is a line on your chart predicting future trends, based upon the averages of past data.

  • Chart & Axis Titles  

    Excel 2016 training video on adding and working with Chart & Axis Titles, which are titles for your horizontal X and vertical Y axis.

  • Format Chart  

    Excel 2016 training video on how to format your chart including: layouts, legend, a data series, a single data point, axis options including number formats, and format code.

  • Dual Axis Chart  

    Excel 2016 training video on Dual Axis Charts which are used to show more than one measurement on a single chart.

  • Chart Templates  

    Excel 2016 training video on how to save a favorite chart as a template, which can be used when creating new carts, making some modifications, without overwriting the original.

  • Chart Sharing & Embedding  

    Excel 2016 training video on how to insert your chart into other applications like Word & PowerPoint with a back link, so that any changes made to your chart in Excel will update those changes in Word and PowerPoint.

PivotTables & PivotCharts
  • Create PivotTables  

    Excel 2016 training video on how to create a basic PivotTable, which is an interactive worksheet table that is used to analyze, and summarize large amounts of data.

  • Customize PivotTable  

    Excel 2016 training video on how to customize your PivotTables including: grouping, custom calculated field, subtotals and more!

  • PivotTable Slicers  

    Excel 2016 training video PivotTable Slicer which is a quicker way to filter your data within a PivotTable.

  • Create PivotCharts  

    Excel 2016 training video on how to create a PivotChart [Pivot Chart] which is used is a way to analyze your data, using an interactive chart.

Graphics
  • Adding & Modifying Pictures  

    Excel 2016 training video on how to add pictures to your worksheets, as well as how to: rotate, resize, reset, apply styles and more!

  • Drawing & Modifying Shapes  

    Excel 2016 training video on how to draw and modify basic shapes including: circles, squares, lines, ovals, rectangles, and more!

  • SmartArt Graphics  

    Excel 2016 training video on how to work with SmartArt Graphics, which are used to help visually communicate information like with: lists, processes, cycles, hierarchy, relationships, matrixes, pyramids or pictures.

  • Layering & Grouping Objects  

    Excel 2016 training video on how to stack, or layer your objects including: pictures, images, shapes and graphics as well as grouping them together into one.

Customize
  • Insert Cell Comments  

    Excel 2016 training video on how to insert a comment into a cell, edit, delete, format and move them as well.

  • Using Hyperlinks  

    Excel 2016 training video on how to insert and work with hyperlinks in your workbook including links to: websites, email, places within file, and other external files.

  • Create Watermarks  

    Excel 2016 training video how to create a watermark on your worksheets, a faded image or washed out mark that identifies the intent like: top secret, do not print, draft, or other identifiable purposes even including products.

  • Customize Themes  

    Excel 2016 training video how to customize your themes including colors and fonts. Remember themes are a collection of: fonts, colors and effects that can be applied throughout your worksheet in a single click.

  • Conditional Formatting Rules  

    Excel 2016 training video on the conditional formatting rules including formatting cells: based upon their values, only cells that contain specified values, top or bottom ranked values, above or below average and only unique or duplicate values.

  • Free Excel Custom Templates  

    Excel 2016 training video on how to access and download free Excel custom templates like: purchase orders, invoices, budgets, inventories, calendars and more!

Excel Level 3 – Advanced
  • Level 3 - Exercise Files, Instructor's Notes, Quiz & Answers  
    1. Exercise Files - the exercise files are provided for you to practice what you've learned after watching the training videos. The exercise files are numbered, and are the exact same as the instructor uses in the video
    2. Instructor Notes - are quick notes about the main points covered in the training videos, and are typically used to reference a point quickly without having to watch the entire training video over again
    3. Quiz & Answers - there are two separate .pdf files: Quiz and Answers. The Quiz has one question for each video to test yourself on what was presented, and of course the answers to compare
Workbook
  • Workbook Properties  

    Excel 2016 training video on Workbook Properties, which is the place where you can enter in information about the workbook including: who created the workbook, editor, author, comments, company, website and more!

  • Custom Views  

    Excel 2016 training video on how to create custom views of your worksheet, or for example, be able to show or hide parts of your worksheet and set that as a custom view.

  • Record & Copy Macros  

    Excel 2016 training video on how to record and copy a Macro from one workbook to another. Macros are a series of recorded actions that can be played back, or ran in a single click; thereby, helping you save time by not having to repeat the same steps over again yourself!

  • Edit Macros  

    Excel 2016 training video on how to edit a Macro.

  • Macros: Absolute & Relative Referencing  

    Excel 2016 training video on the differences between Absolute and Relative referencing (references) in conjunction with recording a Macro.

  • Conditional Formatting Using Formulas  

    Excel 2016 training video on how to use formulas, or functions with Conditional Formatting to apply formatting, based upon a condition you specify, to a cell, or range of cells.

  • Quick Analysis Tool  

    Excel 2016 training video on how to quickly analyze a range of selected cells by using the Quick Analysis Tool, which includes shortcuts to: Formatting, Charts, Totals, Tables and Sparklines.

Collaborating
  • Data Validation  

    Microsoft Excel 2016 training video on Data Validation, which is data that has to be met within a certain condition, or criteria you set, or it cannot be entered into a cell. In other words, you can control a user's input to a degree that will result in more accurate data entry by locking, or blocking certain ranges or types of data into a cell. To validate the entered data.

  • Password Protect Worksheets  

    Microsoft Windows 7 training video on how to password protect your worksheet from anyone editing, or making changes to it. Lock your cells and then password protect, as well as how to unlocking your cells prior to password protecting your worksheet for those cells you'd like to remain editable by others.

  • Password Protect Selected Cells  

    Excel 2016 training video on how to password protect cells, preventing other users from editing selected cells without a password.

  • Document Inspector  
  • Protect Workbook Structure  

    Excel 2016 training video on protecting the workbook structure, which will prevent other users from adding, deleting, moving, renaming, coloring, hiding worksheets in Excel by protecting the workbook with a password.

  • Password to Open & or Modify  

    Excel 2016 training video on how to require a password to open, and/or modify your Excel workbook.

  • Always Create Backup  

    Excel 2016 training video on how to have Excel always create a backup of your workbook automatically.

  • Share Workbook  

    Excel 2016 training video on how to share your workbook with others, and be able to have more than one person work on it at the same time across your network.

  • OneDrive  

    Excel 2016 training video on Microsoft's OneDrive, and how to upload, share and set permissions to your Excel workbook using Microsoft's OneDrive.

  • Track Changes  

    Excel 2016 training video on how to keep track of any changes made to your workbook, and then be able to accept or reject those changes quickly.

  • Protect & Share Workbook  

    Excel 2016 training video on how to password protect and share your workbook with others, and keeping them from turning off the Track Changes feature; and to always keep track of any changes anyone makes to your workbook.

  • Merge Workbooks  

    Excel 2016 training video on how to merge one workbook into another to compare differences between the two and also be able to accept, or reject any changes quickly.

  • Digital Signatures or Digital ID  

    Excel 2016 training video on Digital Signatures, or Digital ID, which is used to confirm the identity of the person who sent you the workbook.

  • Mark As Final  

    Excel 2016 training video on the Mark as Final feature, and how to let other users know that the workbook is a final draft and prevent any inadvertent changes by making it read-only; however, users can make changes if they go out of their way to remove the Mark as Final feature.

Audit Worksheets
  • Trace Dependents, Precedents & Errors  

    Excel 2016 training video on Trace Dependents, Precedents and Errors. Dependents: Excel traces an arrow from a cell, like one containing a formula, to those cells it's "dependent" upon. Precedents: shows arrows that indicate which cells affect the value of the currently selected cell . Error: shows arrows indicating which cells affect the error value of the currently selected cell.

  • Troubleshoot Invalid Data  

    Excel 2016 training video on the Troubleshoot Invalid Data feature, which circles invalid data, or data that doesn't meet the criteria set from the Data Validation feature being applied to a cell, or range of cells.

  • Watch Window & Evaluate Formulas  

    Excel 2016 training video on Watch Windows and Evaluate Formulas. The Watch Windows allows you to watch formulas in any cell, from anywhere in your worksheet in one small window. The Evaluate Formulas allows you to step by step go through each part of a formula or function.

  • Outline & Group Data  

    Excel 2016 training video on how to Outline and Group a range of cells where you can expand the range for data details, or collapse it for the subtotals and totals.

Analyzing Data
  • Sparklines  

    Excel 2016 training video on Sparklines, which are small line charts you can insert into a cell for a selected row.

  • 3D Map  

    Excel 2016 training video on the New Excel 2016 3D Map! Which allows you to visually plot your data on a 3D map by geographical regions as well as over time. Even create visual tours!

  • Data Table  

    Excel 2016 training video on the Data Table, which starts off with you creating a table of varying values, that you then can use in conjunction with a formula, that will in turn display the results of each changing value in an adjacent range. For example, you create a table of 10 different interest rates, then using the PMT function you find out the monthly payment of the first the interest rate; however, with the Data Table feature you can instantly see the results of all 10 of the interest rates calculated into each monthly payment.

  • Scenario Manager  

    Excel 2016 tutorial on the Scenario Manager that allows you to create different groups of values, or scenarios and switch between them.

  • Forecast Sheet  

    Excel 2016 training video on Forecast Sheet, and how to take historical date and, or time based data to create a graphical projection of future trends.

  • Goal Seek  

    Excel 2016 training video on Goal Seek, which is used to find the right input for the value you want out of a formula or function.

  • Solver  

    Excel 2016 training video on the Solver Add-in, and how you can set the value(s) you want a function to return.

  • Random Samples  

    Excel 2016 training video on the Sampling, and how to pick out a random number from a range of numbers.

  • RAND  

    Excel 2016 training video on the RAND function in conjunction with the Advanced Filter feature, and how to return a random number from a range of cells.

  • Descriptive Statistics  

    Excel 2016 training video on Descriptive Statistics, and how to display a sampling of common statistics for a range of data including: MIN, MAX, SUM, COUNT and more functions!

  • Power View  

    Excel 2016 training video on Power View, and how to create attractive visualizations and reports of your worksheets with special emphasis on variables that are useful to you in a separate worksheet. Elements that can be added to a Power View worksheet include: charts, maps, KPIs or Key Performance Indicators and Hierarchies.

Excel Level 3 – Advanced
  • Consolidating Data  

    Excel 2016 training video on Consolidating Data, and how to summarize data from separate ranges, consolidating results in a single output range.

  • Link Workbooks  

    Excel 2016 training video on Link Workbooks, and how to link data, or cells from one workbook into another that will automatically update with to changes made.

  • Form Data  

    Excel 2016 training video on Form Data, and how to use it to help data entry into worksheets with many columns in a single window, and not have to scroll horizontally to enter data at the end of long rows.

  • Export Data  

    Excel 2016 training video on Export Data, and how to export your data into a text file, either to then reimport into another database that won't import directly from Excel, or because you'd like to view and, or edit it in Microsoft's Word.

  • Import Text File  

    Excel 2016 training video on on Import Text File, and how to import those data files exported into a text file from another program, or even from Excel.

  • Power Pivot  

    Excel 2016 training video on Power Pivot, and how to use is as another way to import data into Excel. It's a more robust way to manage external data through the PowerPivot, as you can filter data, and rename columns & tables.

  • Publish to Web  

    Excel 2016 training video on Publish to Web, and how to publish your Excel worksheet as a web page to be viewed on the web.

  • XML Extensible Markup Language  

    Excel 2016 training video on XML or Extensible Markup Language, and how to use it to convert Excel data into the XML file format that is a friendly language which most programs can import from; as well as how to import an XML file into Excel.

  • Advanced Custom Number Formats  

    Excel 2016 training video on Advanced Custom Number Format, and how to add: symbols, text and color formats to your numbers.

  • Camera  

    Excel 2016 training video on Camera, and how to copy a range of cells into a picture, which is linked back to the selected range. So, anytime the cell's data change, the picture will update with those changes.

Reviews (0)