• Home
  • Microsoft Office Excel 2010 Mastery
Microsoft Excel Microsoft Office Excel 2010 Mastery

Microsoft Office Excel 2010 Mastery

99.00 99.00

This Package Contains Courses - See More
  • Course Delivery: On Demand
  • Language: English
  • Difficulty: Intermediate

Chat Live | Contact Us | Toll Free: (888) 360-8764



This course builds on the skills and concepts taught in Excel 2010: Intermediate. Students will work with advanced formulas, as well as lookup functions such as VLOOKUP, MATCH, and INDEX. In addition, students will learn about data validation and database functions such as DSUM. They will learn how to work with PivotTables and PivotCharts, how to import and export data, and how to query external databases. Finally, students will learn about the analytical features of Excel (such as Goal Seek and Solver), running and recording macros, SmartArt graphics, and conditional formatting with graphics.


Topics Covered

Course Introduction - Duration: 2m

Unit 01 - Logical & Statistical Functions - Duration: 37m

Topic A: Logical Functions

  • The IF Function
  • Demo - A-1: Using the IF Function
  • Demo - A-2: Using a Formula to Apply Conditional Formatting
  • OR, AND, and NOT Functions
  • Demo - A-3: Using OR, AND, and NOT as Nested Functions
  • Nested IF Functions
  • Demo - A-4: Using Nested IF Functions
  • The IFERROR Function
  • Demo - A-5: Using the IFERROR Function

Topic B: Math and Statistical Functions

  • The SUMIF Function
  • Demo - B-1: Using SUMIF
  • The COUNTIF Function
  • Demo - B-2: Using COUNTIF
  • The AVERAGEIF Function
  • Demo - B-3: Using AVERAGEIF
  • Demo - B-4: Using SUMIFS, COUNTIFS, and AVERAGEIFS
  • The ROUND Function
  • Evaluate Formula Dialog Box
  • Demo - B-5: Using ROUND
  • Unit 01 Review

Unit 02 - Financial & Date Functions - Duration: 32m

Topic A: Financial Functions The PMT Function

  • Demo - A-1: Using the PMT Function

Topic B: Date and Time Functions

  • Date Functions
  • Demo - B-1: Using Date Functions
  • Calculating Time
  • Demo - B-2: Using Time FunctionsTopic B

Topic C: Array Formulas Array Formulas

  • Creating an Array Formula
  • Demo - C-1: Using an Array Formula
  • Applying Arrays to Functions
  • Demo - C-2: Applying Arrays to Functions
  • Modify an Array Formula
  • Demo - C-3: Modifying the Array Formula

Topic D: Displaying and Printing Formulas

  • Displaying Formulas in Cells
  • Hide Formulas from Users
  • Show Hidden Formulas
  • Demo - D-1: Showing, Printing, and Hiding Formulas
  • Automatic Recalculation
  • Edit Iteration Calculation Options
  • Demo - D-2: Setting Calculation Options
  • Unit 02 Review

Unit 03 - Lookups and Data Tables - Duration: 28m

Topic A: Using Lookup Functions

  • The HLOOKUP Function
  • The VLOOKUP Function
  • Demo - A-1: Examining VLOOKUP
  • VLOOKUP for Exact Matches
  • Demo - A-2: Using VLOOKUP to Find an Exact Match
  • VLOOKUP for Approximate Matches
  • Demo - A-3: Using VLOOKUP to Find an Approximate Match
  • HLOOKUP for Exact Matches
  • Demo - A-4: Using HLOOKUP to Find Exact Matches
  • HLOOKUP for Approximate Matches
  • Demo - A-5: Using HLOOKUP to Find Approximate Matches

Topic B: Using MATCH and INDEX

  • The MATCH Function
  • Demo - B-1: Using the MATCH Function
  • The INDEX Function
  • Demo - B-2: Using the INDEX Function

Topic C: Creating Data Tables

  • One-Variable Data Tables
  • Demo - C-1: Creating a One-variable Data Table
  • Two-Variable Data Tables
  • Demo - C-2: Creating a Two-variable Data Table
  • Unit 03 Review

Unit 04 - Advanced Data Management - Duration: 16m

Topic A: Validating Cell Entries

  • Data Validation
  • Demo - A-1: Observing Data Validation
  • Setting Data Validation Rules
  • Demo - A-2: Setting Up Data Validation
  • Using Date Criteria
  • Demo - A-3: Setting Date and List Validations

Topic B: Exploring Database Functions

  • Structure of Database Functions
  • Demo - B-1: Examining the Structure of Database Functions
  • Demo - B-2: Using the DSUM Function
  • Unit 04 Review

Unit 05 - Exporting and Importing - Duration: 25m

Topic A: Exporting and Importing Text Files

  • Using the Save As Command
  • Demo - A-1: Exporting Excel Data to a Text File Importing Data
  • Demo - A-2: Importing Data from a Text File into a Workbook. The Text Import Wizard Converting Text to Columns
  • Demo - A-3: Converting Text to Columns Removing Duplicates
  • Demo - A-4: Removing Duplicate Records

Topic B: Exporting and Importing XML Data

  • The XML Maps Dialog Box
  • The XML Source Pane Importing XML Data Exporting Data to an XML File Deleting XML Maps

Topic C: Querying External Databases

  • Using Microsoft Query
  • Demo - C-1: Getting External Data from Microsoft Query Web Query Retrieving Data from a Web Page
  • Demo - C-2: Using a Web Query to Get Data from the Web
  • Unit 05 Review

Unit 06 - Analytical Tool - Duration: 28m

Topic A: Goal Seek and Solver

  • Using the Goal Seek Utility
  • Demo - A-1: Using Goal Seek to Solve for a Single Variable Activating Add-Ins The Add-Ins Dialog Box The Solver Parameters Dialog Box
  • Demo - A-3: Using Solver to Solve for Multiple Variables

Topic B: The Analysis ToolPak

  • Analysis ToolPak Using the Sampling Analysis Tool
  • Demo - B-1: Using the Sampling Analysis Tool

Topic C: Scenarios

  • Creating a Scenario
  • Demo - C-1: Creating Scenarios Add a Scenario Manager Button Merging Scenarios A Sample Scenario Summary
  • Unit 06 Review

Unit 07 - Macros and Custom Functions - Duration: 33m

Topic A: Running and Recording a Macro

  • Running Macros
  • Demo - A-1: Running a Macro Recording Macros
  • Demo - A-2: Recording a Macro Assigning Macros to Buttons
  • Demo - A-3: Assigning a Macro to a Button Add Macro Buttons to the Ribbon Insert a Button Change Button Properties
  • Demo - A-4: Inserting a Macro Button Create Auto_Open Macro
  • Demo - A-5: Creating an Auto_Open Macro

Topic B: Working with VBA Code

  • VBA Code Observing VBA Code
  • Demo - B-1: Observing a VBA Code Module Example of Editing VBA Code
  • Demo - B-2: Editing VBA Code

Topic C: Creating Functions

  • Function Procedures Creating a Custom Function
  • Demo - C-1: Creating a Custom Function
  • Unit 07 Review

Got questions? Contact us below or call 877-881-2235

Why Choose 360training.com?

  • Fast and easy courses completion
  • Get an education faster than at traditional colleges!
  • 100% online - No classroom attendance required.
  • Unlimited 24x7 online customer support
  • Over 500,000+ certified nationwide.