Microsoft Excel - Excel From Beginner to Advanced

Excel with this A-Z Microsoft Excel Course. Microsoft Excel 2010, 2013, 2016, 2019, and Office 365

What you'll learn

  • Master Microsoft Excel from Beginner to Advanced
  • Learn the most common Excel functions used in the Office
  • Maintain large sets of Excel data in a list or table
  • Wow your boss by unlocking dynamic formulas with IF, VLOOKUP, INDEX, MATCH functions and many more
  • Build a solid understanding on the Basics of Microsoft Excel
  • Harness the full power of Microsoft Excel by automating your day to day tasks through Macros and VBA
  • Create dynamic reports by mastering most popular tools, PivotTables
  • Access to a Professional Trainer with  10+ years of Excel Training

Requirements

  • This course was created with Excel 2013. But all features will work on in Excel 2010, 2013, 2016, and most will work in 2007.

Description

Microsoft Excel all in One Package

This Microsoft Excel course 4 different courses.

  • Microsoft Excel 101 – An Introduction to Excel
  • Microsoft Excel 102 – Intermediate Level Excel
  • Microsoft Excel 103 – Advanced Level Excel
  • Master Microsoft Excel  Macros and VBA in 6 simple projects

 

Material recorded with Excel 2013 but works in 2010, 2013, 2016, 2019 (Office 365)

Enroll now to go through a deep dive of the most popular spreadsheet tool on the market, Microsoft Excel. As your instructor I will use my 15+ years of Excel Training to guide you step by step through the beginner to advanced level and beyond.

As you participate in each of the 4 courses you will master Excel tools that will clear away the pain of stumbling through your daily tasks. You will start with basics, building a solid foundation that will give you further knowledge as you progress into intermediate and advanced level topics.

At completion of this course you will have mastered the most popular Excel tools and come out with confidence to complete any Excel tasks with efficiency and grace. Below are just a few of the topics that you will master :

  • Creating effective spreadsheets
  • Managing large sets of data
  • Mastering the use of some of Excel’s most popular and highly sought after functions (SUM, VLOOKUP, IF, AVERAGE, INDEX/MATCH, and many more…)
  • Create dynamic report with Excel Pivot Tables
  • Unlock the power and versatility of Microsoft Excel’s Add-In, PowerPivot
  • Audit Excel Worksheet formulas to ensure clean formulas
  • Automate your day to day Excel tasks by mastering the power of Macros and VBA

Who this course is for

  • This Microsoft Excel courses will take you through 4 levels of Microsoft Excel topics (Beginner, Intermediate, Advanced, and Macros/VBA)
  • If your looking to take your Excel skills from beginner to advanced level and beyond, then this course is for you.

Course Content

  • Microsoft Excel 101 Course Introduction
 Ask Questions
 Course Exercise Files

Source File

  • Microsoft Excel Fundamentals
 Launching Excel
 Microsoft Excel Startup Screen
 Introduction to the Excel Interface
 Customizing the Excel Quick Access Toolbar
 More on Excel Interface
 Understanding the Structure of an Excel Workbook
 Saving an Excel Document
 Common Excel Shortcut Keys
  • Entering and Editing Text and Formulas
 Entering Text to Create Spreadsheet Titles
 Working with Numeric Data in Excel
 Entering Date Values in Excel
 Working with Cell References
 Creating Basic Formulas in Excel
 Relative vs Absolute Cell References in Formulas
 Understanding the order of Operation
  • Working with the Basic Excel Functions
 The structure of an Excel Functions
 Working with the SUM() function
 Working with the MIN() and MAX() function
 Working with the Average() function
 Working with the Count() function
 Adjacent Cells Error in Excel Calculations
 Using the AutoSum Command
 Excel's AutoSum Shortcut Key
 Using the Autofill Command to Copy Formulas
  • Modifying an Excel Worksheet
 Moving and Copying Data in an Excel Worksheet
 Inserting and Deleting Rows and Columns
 Changing the Width and Height and Cells
 Hiding and Unhiding Excel Rows and Columns
 Renaming an Excel Worksheet
 Deleting an Excel Work Sheet
 Moving and Copying an Excel Worksheet
  • Formatting Data in an Excel Worksheet
 Working with Font Formatting Commands
 Changing the Background Color of a Cell
 Adding Border to Cells
 Formatting Data as Currency Values
 Formatting Percentages
 Using Excel's Format Painter
 Creating Styles to Format Data
 Merging and Centering Cells
 Using Conditional Formatting
  • Inserting Images and Shapes into an Excel Worksheet
 Inserting Images
 Inserting Excel Shapes
 Formatting Excel Shapes
 Working with Excel SmartArt
  • Creating Basic Charts in Excel
 Creating an Excel Column Chart
 Working with the Excel Chart Ribbon
 Adding and Modifying Data on an Excel Chart
 Formatting an Excel Chart
 Moving a Chart to Another Worksheet
 Working with Excel Pie Charts
  • Printing an Excel Worksheet
 Viewing Your Document in Print Preview
 Changing the Margins, Scaling, and Orientation
 Working with Page Layout View
 Adding Header and Footer Content
 Printing a Specific Range of Cells
  • Working With Excel Templates
 Intro to Excel Templates
 Opening an Existing Template
 Creating a Custom Template
  • Congratulations! You've Built a Solid Microsoft Excel Foundation
 Congratulations!
  • Microsoft Excel 102 Course Introduction
 Excel 102 Course Exercise Files - Download
  • Working With an Excel List
 Understanding Excel List Structure
 Sorting a List Using Single Level Sort
 Sorting a List Using Multi Level Sort
 Using Custom Sorts in an Excel List
 Filter an Excel List using the Auto-Filter Tool
 Creating Subtotals in a List
 Format a List as a Table
 Using Conditional Formatting to Find Duplicates
 Removing Duplicates
  • Excel List Functions
 Introduction to Excel's Function: DSUM()
 Excel DSUM() Function Single Criteria Continued
 Excel DSUM() Function with OR Criteria
 Excel DSUM() Function with AND Criteria
 Excel Function: DAVERAGE()
 Excel Function: DCOUNT()
 Excel Function: DSUBTOTAL()
  • Excel Data Validation
 Understanding the Need for Excel Data Validation
 Creating an Excel Data Validation List
 Excel Decimal Data Validation
 Adding a Custom Data Validation Error
 Dynmic Formulas by Using Data Excel Data Validation Techniques
  • Imprting and Exporting Data
 Importing Data into Microsoft Excel
 Importing Data From Text Files
 Importing Data From Microsoft Access
 Importing Data From Text Files
 Importing Data From a Database
 Microsoft Excel Legacy Export Options for New Excel Versions
 Exporting Data to Text Files
  • Excel Pivot Tables
 Understanding Excel Pivot Tables
 Creating an Excel Pivot Table
 Modifying Excel Pivot Table Calculations
 Grouping Pivot Table Data
 Formatting Pivot table Data
 Drilling Down into Pivot Table Data
 Creating Pivot Charts
 Filtering Pivot Table Data
 Filtering with the Slicer Tool
  • Working With Excel's Power Pivot Tools
 Why Power Pivot?
 Activating the Excel Power Pivot AddIn
 Creating Data Model with Power Pivot
 Creating Pivot Tables based on Data Models
 Power Pivot Calculated Fields
 Power Pivot KPIs
  • Working with Large Sets of Excel Data
 Using the Freeze Panes Tools
 Grouping Data (Columns and/or Rows)
 Print options for large Sets of Data
 Linking Worksheets (3D Formulas)
 Consolidating Data from Multiple Worksheets
  • Congratulations! You're an Intermediate Excel User
 Congratulations!
  • Microsoft Excel 103 Course introduction
 Excel 103 Course Exercise Files - Download
  • Working with Excel's Conditional Functions
 Working with Excel Name Ranges
 Advantages and Disadvantages of Excel Name Ranges
 Editing an Excel Name Range
 Using Excel's IF() Function
 Excel's IF() Function with a Name Range
 Nesting Functions with Excel
 Nesting Excel's AND() Function with IF() Function
 Using Excel's COUNTIF() Function
 Using Excel's SUMIF() Function
 Using Excel's IFERROR() Function
  • Working with Excel's Conditional Functions
 Microsoft Excel VLOOKUP() Function
 Microsoft Excel HLOOKUP() Function
 Microsoft Excel INDEX() Function
 Microsoft Excel MATCH() Function
 Microsoft Excel INDEX() and MATCH() Function Combined
 Microsoft Excel INDEX() and MATCH() Function Combined Continued
 Creating a Dynamic HLOOKUP() with the MATCH() Function
  • Working with Excel's Text Based Functions
 Using Excel's Left(), Right(), and MID() Functions
 Using Excel's LEN() Function
 Using Excel's SEARCH() Function
 Using Excel's CONCATENATE() Function
  • Auditing an Excel Worksheet
 Tracing Precedents in Excel Formulas
 Tracing Dependents in Excel Formulas
 Working with the Watch Window
 Showing Formulas
  • Protecting Excel Worksheets and Workbooks
 Protecting Specific Cells in a Work Sheet
 Protecting the Structure of a Workbook
 Adding a Workbook Password
  • Mastering Excel's "What If?" Tools
 Working with Excel's Goal Seek Tool
 Working with Excel's Goal Solver Tool
 Building Effective Data Tables in Excel
 Creating Scenarios in Excel
  • Automating Repetitive Tasks in Excel with Macros
 Understanding Excel Macros
 Activating the Developer Tab in Excel
 Creating a Macro with Macro Recorder
 Editing a Macro with VBA
 Creating Buttons to Run Macro
  • Congratulations!
 Congratulations on Completing Microsoft Excel 103
  • Miceosoft Excel Macros and VBA Course Introduction
 Welcome to the Course
 Understanding the Why and How Behind Excel Macros
  • Project 1: Using Excel's Macro Recorder Tool
 Introduction: Inserting and Formatting Text
 Start Recording!
 Running a Macro
 Running a Macro with a Button
 Oops! I Need to Edit My Macro
 Practical Uses of Excel Macros
  • Excel VBA Concepts
 Excel VBA Concepts
 Excel Visual Basic Editor (VBE)
 Excel VBA Modules
 Creating an Excel VBA Procedure
 Adding Code to a VBA Procedure
 Understanding Excel VBA Variables
 Building Logic with an IF Statement
 Working with Excel VBA Loops to Repeat Excel Block of Code
  • Project 2: Moving Beyond the Basics and into VBA
 Introduction: Interacting with The User
 Breaking Down The VBA Code
 Prompting the User for Information
 Continue Excel VBA Input Box
 Building Logic Into Your Macros
 Alerting the User of Errors
 Other Practical Uses of Message and Input Boxes
  • Project 3: Preparing and Cleaning Up Data with a Little VBA
 Introduction: Cleaning Up and Formatting Data
 Preparing to the Use of Macro Recorder
 Inserting the Headers
 Formatting the Headers
 Putting It All Together with a Little VBA
 Running the Loop
  • Project 4: Using VBA to Automate Excel Formulas
 Introduction: Automate Excel Formulas
 Automate the SUM() Function Through Code
 Continue Automate SUM Function
 Test the SUM Function
 Loop the SUM() Functions Over Multiple Worksheets
  • Project 5: Bringing It All Together and a Weekly Report
 Introduction: Creating the Final Report
 Creating the Final Report loop
 Copying and Pasting Data with VBA
 Running the Final Report Procedure
  • Project 6: Working with Excel VBA User Forms
 Introduction: Working with Excel VBA User Forms
 Creating an Excel User Form
 Adding Controls to The Form
 Adding VBA Code to The Initialize Event
 Adding VBA Code to The ComboBox Change Event
 Adding VBA Code to The Add Worksheet Button
 Change Worksheet Name
 Adding VBA Code to the Create Report Button
 Showing the Form
  • Importing Data From Text Files
 Opening a Text File For Import
 Opening a Text File For Import
 Get Data From Text File
 Importing Multiple Text Files with the GetOpenFileName Method
 Creating a Loop to Read Each File
 Adding a New Sheet for Imported Data
 Clear the Clipboard
 Working with The Screen Updating Property
 Creating Reusable Code with a VBA Function
  • Source Files

Source File

Cours