MS Excel Workshop

September 27, 20210MS Excel Workshop

  • Overview
  • Course Outline
  • Workshop Instructions:
  • Testimonials

Overview

Course Outline

Excel Ice Breaking Session!
  • Ice breaking session to bring all participants on the same learning pace
  •  Objective Setting for the course and participant expectations
  • Be Ready for quick questions and to learn daily use shortcuts
  • Quick transfer of valuable knowledge to be used during the course
Formatting your Spreadsheet
  • Freezing row and column titles
  • Using auto format
  • Custom number formats
  • Using Paste Special
  • Pasting different types of information
  •  Formatting and editing a series
  • Utilizing fill handle trick
Summarizing & Consolidating Data
  • Working with Data Tables
  •  Grouping & Creating Sub Totals
  •  Formatting the Subtotal Rows
  •  Adding and Copying with Subtotals
  •  Consolidating Data from multiple sheet
Filter & Sorting
  • Extracting Unique Values with Filtering
  •  Advance Filter on more than 1 Criteria
  •  Sorting Data on different cell formats
  •  Sorting data with Multiple Level Sorts
  •  Data extraction by Sorting & Filter
Formulas & Powerful Functions
  • Relative & Absolute Cell Reference
  •  Text Functions (Left, Upper, Concatenate , truncate and others)
  •  Date Functions (Today, Now, sorting on dates, extracting Date & Time)
  •  Using Array Formulas
  •  The IF & NESTED IF function
  •  Conditional & Logical Functions ( If, And, Or, and Not )
  •  The ROUND, PMT function
  •  Using Count IF , SUMIF , Count, CountA
  •  Using Vlookup Functions
  •  Advanced VLOOKUP Function
  •  Using HLOOKUP Functions Using MATCH and INDEX
  •  Using the Offset Function
Security in Excel
  • Preventing Sheet from Deletion
  •  Cell / Row / Column Level Security
  •  Preventing your data from Copying
  •  Preventing data selection
Dynamic Table
  • Converting data into table
  •  Automatic calculation in table
  •  Converting table to normal range
  •  Using table in Charts & Vlookup
Charts, Graphs & Techniques
  •  Recommended Charts
  •  Trend lines
  •  Sparkline’s charts
  •  Saving Custom Chart as Template
  •  Formatting and Editing a series
List Management
  •  Formatting data on Conditions
  •  Highlight Duplicate & Unique Values
  •  Formatting data using Data Bars & Icons
  •  Clearing & Managing rules
  •  Indirect & Flash Fill
  •  Remove Duplicates
Conditional Formatting – CF
  • Analyzing Data with CF
  •  Hierarchy of CF rules
  •  Create, Edit & Delete a CF rule
  •  Use databars as a visual guide
  •  Find duplicate values
  •  Make a rule dependent on another cell
  •  Highlight a row if it’s within a range of dates
  •  Highlight columns based on value of a cell
Data Analysis Using Pivots
  • Creating Pivot table & Analyzing data
  • Dynamic Pivot Table & Calculation
  • Calculating variances with pivot
  • Grouping data in pivot tables
  • Selecting elements of a pivot table
  • Making use of multiple data fields
  • Using grand totals and subtotals
  • Changing a pivot table’s calculations
Macros
  • Recording Macros for Automating tasks
  • Creating Button to run a MacroDeleting Macros

Workshop Instructions:

  • August 29-30, 2022 – 10:00 am – 03:00 pm (GST)
  • Regular Fee: USD 510 Per Participant (Exclusive of VAT) | Online Session
  • For registration (s) send us your Name, Designation, Organization, and Mobile Number to [email protected]
  • For More Information please contact: Qazi Waqas Ahmed Mobile: +971 56 309 0819; Email: [email protected]

Testimonials

Share