The two day workshop would cover the topics as noted below.
Day 1 -
Excel Overview
• A overview of the screen, navigation and basic spreadsheet concepts
• Understanding workbooks, worksheets, rows, columns, cells
• Various selection techniques
Range Names
• Concept and Purpose
• Naming individual or range cells
• Deleting and amending named ranges
• Using named cells/ranges in formulae
Conditional Functions & Formats and Date Calculations
• If Statements, Nested If
• And, Or
• Combining If, And, Or
• Sum if , Count if
• Conditional Formatting
• Different date/time formats
• How dates are stored
• Calculating working days
• Using the DAY(), MONTH(), YEAR() functions
• Time calculations
Basic List Management
• Sorting Data
• Adding Subtotals
• AutoFilter
• Freeze Panes
• Group and Outline
• Data Form
Lookup & Information Functions
• Vertical Lookup (Vlookup)
• Horizontal Lookup (Hlookup)
• Match and Index
• IsText, IsValue, IsDate , IsNull , IsErr, Is Error, IsNa
Excel Charts / Graphs
• Using the Chart Wizard
• Editing and Formatting charts
• Saving custom chart types
• Setting a default chart type
Hyperlink
What If Analysis
• Scenarios
• Goal Seek
• Data Tables
Assignment No.1
Day 2 -
Pivot Tables
• Using the Pivot Table Wizard
• Changing the pivot table layout
• Formatting
• Grouping items
• Inserting calculated fields
• Pivot Table Options
• Display and hide data in fields
• Lay out reports directly on worksheet
• Pivot Chart
Introduction to Macros
• Purpose of Macros
• Recording macros
• Where to save macros
• Absolute and relative record
• Running macros:
• Custom buttons, menu items, keyboard shortcuts
Assignment No.2