Call us or whatsapp us 08103382376  or Request a call back !

Excel Fundamentals

3 Day Microsoft Excel Course  – Organise format and calculate data

What is Excel ?

Microsoft Excel is a software program produced by Microsoft that allows users to organize, format and calculate data with formulas using a spreadsheet system.

Come enjoy the motivating atmosphere, learning with your peers while you discover the possibilities of Microsoft Excel.

Audience: Data Analysts, Business Analysts, Developers, Data Managers, Business Intelligence Analysts, IT Administrators, Data Architects

Recommended Prerequisites: None

Course Schedule: 3 Days, 24 Academic Hours

About the course

 

Course Syllabus:

This 3 day course will give delegates a thorough study on using Microsoft Excel.

Day 1

Getting Started with Microsoft Excel

  • Starting Out
  • About Workbooks
  • Exploring your Workbook
  • Getting Help with Excel

Getting to grips with the basics

  • The Excel Interface
  • Using Backstage View
  • Understanding the Ribbon
  • Using & customising the Quick Access toolbar
  • The Home Tab
  • The Insert Tab
  • The Page Layout Tab
  • The Formulas Tab
  • The Data Tab
  • The Review Tab
  • Using shortcuts

Organising Your Workbooks effectively

  • Create, save & open a new Workbook
  • Entering numbers & text in a Workbook
  • Adding, deleting & naming sheets

Entering Data into Spreadsheets

  • Inserting Cells, Columns & Rows
  • Deleting Cells, Columns & Rows
  • AutoFill & using SmartTags

Presenting Your Data

  • Font formatting
  • Borders & Shading
  • Number formatting (currency etc)
  • Alignment options
  • Clearing formats
  • Applying cell styles
  • Formatting shortcut

Defining & Creating Calculations

  • Writing a basic formula
  • Understanding the order of BODMA
  • The Sum function & AutoSum
  • Creating statistical functions (Average, Max and Min)
  • An introduction to the Function Wizard

Advanced File Tasks

  • Saving and File Management
  • Converting and Encrypting Files
  • File Properties and Digital Signatures
  • File Accessibility and Compatibility

Working with Functions and Formulas

  • Formulas with Absolute References
  • Autosum Functions (SUM & AVERAGE)
  • IF and Nested Functions
  • Working with Named Ranges
  • Array Functions (SUMIF & AVERAGEIF)
  • Formula Auditing
  • Text Functions

Managing Tables in Microsoft Excel

  • Creating Excel Tables
  • Working with Records and Filters
  • Advanced Filters

Data Tools in Excel

  • Text to Columns
  • Flash Fill
  • Remove Duplicates
  • Data Validation
  • Consolidate

How to do more with Charts in Excel

  • Trendlines
  • Error Bars
  • Working with Axis Options
  • Advanced Chart Formatting

Day 2

Sparklines

  • Creating Sparklines
  • Editing Sparklines

Grouping & Outlining and Subtotals

  • Outlining and Grouping Data
  • Using the Subtotals Tool

What-If Analysis

  • Exploring Scenarios
  • Goal Seek and Data Tables
  • Using Solver

PivotTables

  • Getting Started with PivotTables
  • Displaying Data in a PivotTable
  • Formatting a PivotTable
  • Using the Classic PivotTable Layout
  • Slicers

Charting Pivoted Data

  • Getting Started with Pivot Charts
  • Using the PivotChart Tools Tabs
  • Formatting a PivotChart

Advanced Excel Tasks

  • Excel and Hyperlinks
  • Using Custom AutoFill Lists
  • Sharing Workbooks

Creating HLOOKUP and VLOOKUP Functions

  • Using the VLOOKUP Function
  • Using the LOOKUP Function

Introduction to Macros in Excel

  • Recording and Playing Macros
  • Copying and Deleting Macros
  • Visual Basic and Macros

Advanced Conditional Formatting

  • Editing Standard Formatting Rules
  • Using Formulas in Conditional Formatting

Using Form Controls and Templates

  • Using Form Controls
  • Creating Templates

Advanced Lookup Functions

  • Index Function
  • Using the Match Function
  • Using Index and Match Together
  • Using Index and Match with Array Formula
  • Using the Indirect Function

Advanced Functions in Excel

  • Using Error Functions
  • Using Logical Functions
  • Using Array Formulas
  • Using Rounding Functions

Advanced Pivot Table Tools

  • Importing Pivot Table Data
  • Calculated Fields in Pivot Tables
  • Showing Pivot Data as a Percentage
  • Creating Custom Pivot Tables Styles

Introduction to User Defined Functions Using VBAs

  • Creating a User Defined Function
  • Creating an Interactive Procedure
  • Create and Share a Function Add-in

Day 3

Recording and Running Macros in Excel

  • Setting Excel Macro Options
  • Record and Run Simple Macros
  • Assign Macros to the Quick Access Toolba

Introduction to User Defined Functions Using VBAs

  • Creating a User Defined Function
  • Creating an Interactive Procedure
  • Create and Share a Function Add-in

Recording and Running Macros in ExcelVBAs

  • Setting Excel Macro Options
  • Record and Run Simple Macros
  • Assign Macros to the Quick Access Toolbar

Introduction to the Excel VBA Editor

  • Starting the Visual Basic Editor
  • Using the Project Explorer
  • Using the Properties Window
  • Creating, Importing and Exporting Modules
  • Explore Recorded Macros

Creating Procedures and Functions

  • Creating Sub and Function Procedures
  • Declare and Use Variables
  • Displaying Message to the User
  • Getting Input from the User

Working with the Excel Object Model

  • Understanding Objects, Properties and Methods
  • Referencing Cells and Ranges
  • Gathering Cell and Range Information
  • Using the Offset Method
  • Using the Resize Method
  • Using With Blocks
  • Referencing Workbooks and Worksheets

Controlling Program Flow

  • Using the IF Statement
  • Using the Select Case Statement
  • Using For Next Loops
  • Using Do Loops
  • Using For Next Loops

Advanced Procedures

  • Calling Procedures
  • Passing Values to Procedures

Advanced Variables

  • Understanding Variable Scope
  • Understanding Object Variables
  • Creating Array Variables
  • Creating Dynamic Array Variables

Interacting with Office Applications

  • Setting References to other Applications
  • Setting References using Late Binding
  • Create a PowerPoint Presentation
  • Create a Letter in Word

Debugging and Error Traps

  • Review of Error Types
  • Debugging tools
  • Setting Error Traps

Creating Custom User Forms

  • Creating a Custom Form
  • Adding Form Controls
  • Creating Event Procedures

Workbook and Worksheet Events

  • Create Workbook Events
  • Create WorkSheet Events

Your Trainer

Our international certified trainers are highly experienced Microsoft Excel practitioners who have real-world expertise.

Key Information

I – All course material including a copy of Excel for Beginners will be provided to all candidates.

II – Laptops will be provided for all candidates with pre-installed Microsoft Excel.

III – Our training venue is based in Lagos (Specific venue will be emailed on confirmation of place on the course).

For more information on our training, please enquire using the form above or call 08103382376

Your Name (required)

Company Name

Phone Number

Your Email (required)

Courses

Your Message

Excel Fundamentals Course

₦200,000per course

error: Content is protected !!