Back

Microsoft Excel Basic To Intermediate Training

This Excel Mastery course will take you from basic spreadsheet design to advanced data analysis techniques, empowering you to optimize your workflow, automate tasks, and make better decisions using Excel.

Duration: 2 Days (8 Hours Per Day)

Who Should Attend:

  • Business Analysts
  • Accountants and Financial Analysts
  • Project Managers
  • Administrators and Operations Managers
  • Data Analysts

Individuals who want to deepen their understanding of Excel’s advanced functions and tools.

Course Outline:

DESIGNING BETTER SPREAD SHEETS

  • The Golden rules of Spreadsheet design
  • Improving readability With Cell Styles
  • Controlling data Input and Adding Navigation Buttons.
  • Use forms to input data quickly
  • Create a Summary Sheet
  • Name Range, Absolute and Partial Referencing with General Calculative Function

´Making Decision with Logical Functions

  • Logical Functions (AND, OR, IF)
  • IF, Nested IFs & IFS Functions
  • Conditional IF & Multiple Criteria IF
  • Error Handling with IFERROR &IFNA

´Work With Lookup Formulas

  • V look up: Exact & Approximate Match
  • Horizontal Look-up
  • Flexible Look-up: Index and Match
  • Modern Look Up: Lookup and Match
  • Look up with duplicates

´Sort and Filter Like a Pro

  • Sort on Multiple Columns
  • Sort with Custom List
  • Sort and Sort by Function
  • Filter Function
  • Extract and Sort Unique Values

´Compare Data

  • Custom Formatting
  • Conditional Formatting.
  • Find Duplicates With Conditional Formatting
  • Find duplicates with Formulas
  • Advanced Conditional Formatting.

´ WORKING WITH POWER QUERY

  • Power query Overview
  • Importing Data using get and Transform Data
  • Power Query Editor
  • Combining Files
  • Transforming and Loading Files to Excel

´Analyze Data with Pivot Tables

  • Pivot table explained
  • Create a Pivot table from Scratch
  • Pivot the Fields
  • Apply number formatting totals
  • Show Values as Summarize by.
  • Group Pivot table Fields
  • Format error values and empty cells
  • Choose a report Layout
  • Apply and Modify Pivot table Styles

´Pivot Charts and Add Interaction to Pivot Tables

  • Create Pivot Charts
  • Format Pivot Charts
  • Using Map Charts
  • Insert and Format Slicers
  • Connect Slicers to Pivot Chart
  • Update Pivot Table Data

´DATA VALIDATION

  • Dynamic dropdown list
  • Input Messages & Error Alerts
  • Data Protection and Layout

´WHAT IF ANALYSIS TOOLS

  • Goal seek and PMT Function
  • Using Scenario Manager
  • Data Tables with one and 2 Variables