Microsoft Excel for Intermediate Level

Facilitator: Mr. Enayet Ullah, ACCA

Number of Sessions: 8

Session Duration: 2 hours

Total Duration: 16 hours

Course Length: 2 Months

Course Fee: 5,000 BDT

Course Contents:

Session 1: Introduction to Intermediate Features

• Clipboard Functions: Paste Special options and their applications (e.g., values, formats, and operations).
• Font Functions: Advanced formatting: Cell styles, custom formatting, and conditional formatting basics.
• Alignment: Center Across Selection vs. Merge & Center, and their best use cases.
• Number: Number formatting for financial, scientific, and custom data types.
• Shortcuts: File Operations: Ctrl + N: New workbook, Ctrl + O: Open workbook, Ctrl + S: Save workbook, F12: Save As, Ctrl + P: Print, Ctrl + W: Close workbook, Ctrl + F4: Close Excel, Alt + F4: Exit Excel

Session 2: Shortcuts, Tips, and Tricks

• Top Shortcuts: Navigation: Arrow Keys: Move cell, Ctrl + Arrow Keys: Jump to edge, Tab / Shift + Tab: Move right/left, Ctrl + G: Go To dialog, Ctrl + Home: Start of sheet, Ctrl + End: End of sheet, Page Up / Page Down: Screen up/down, Alt + Page Up/Down: Screen left/right, Ctrl + Page Up/Down: Switch tabs
• Tips and Tricks: Using Flash Fill (Ctrl + E) to quickly extract patterns in data, Navigating large datasets using Name Manager and Go To (Ctrl + G), Quick Analysis Tool for charts and formatting (Ctrl + Q).

Session 3: Functions and Formulas

• Common Functions: Logical functions: IF, AND, OR, Statistical functions: MIN, MAX, AVERAGE.
• Shortcut Practice: Data Entry: F2: Edit cell, Enter / Shift + Enter: Confirm and move, Ctrl + D/R: Copy down/right, Ctrl + ; / :: Insert date/time, Ctrl + ‘: Copy formula, Ctrl + Shift + “: Copy value
• Tips and Tricks: Nesting functions for complex conditions, Using error-handling functions like IFERROR.

Session 4: Working with Tables and Basic Charts

• Creating and Formatting Tables: Table design and structured references.
• Sorting, Filtering, and Subtotaling in Tables: Multi-level sorting and advanced filtering.
• Introduction to Charts: Column, Line, and Pie charts.
• Customizing Chart Elements: Adding dynamic ranges and formatting for storytelling.
• Shortcuts: Formatting: Ctrl + 1: Format Cells, Ctrl + B/I/U: Bold/Italic/Underline, Alt + H, A, C: Center align, Alt + H, H: Fill color, Ctrl + Shift + &/_: Add/Remove borders

Session 5: Page Layout

• Page Setup: Margins, Orientation, Size, Print Area, Print Preview, Sheet settings, Header/Footer, Page Adjustment.
• Scale to Fit: Adjusting pages for professional printing.
• Shortcuts: Formulas: =: Start formula, Alt + =: AutoSum, Ctrl + `: Show formulas, F4: Toggle references, Ctrl + Shift + Enter: Array formula.
• Tips and Tricks: Best practices for printing large datasets (e.g., repeat row headers on each page).

Session 6: Formulas

• Text Functions: LEN, LEFT, MID, REPLACE, UPPER, PROPER.
• Date Functions: NOW, TODAY, DATE, WEEKDAY, MONTH, YEAR.
• Shortcuts: Selection: Ctrl + A: Select all, Shift + Space: Select row, Ctrl + Space: Select column, Ctrl + Shift + Arrow Keys: Select range, Ctrl + Shift + *: Select region, Ctrl + Shift + O: Select comments
• Tips and Tricks: Combining text and date functions for custom reports, Using TEXT to format numbers dynamically.

Session 7: Lookup

• Advanced Lookups: INDEX and MATCH (Nested Lookups), LARGE and conditional lookups.
• View Features: Page Break and Group settings.
• Workbook Integration: Linking data across multiple sheets and workbooks.
• Shortcuts: Miscellaneous: Ctrl + Z/Y: Undo/Redo, F7: Spell check, Shift + F7: Thesaurus, Alt + F8: Macros, Alt + F11: VBA editor

Session 8: Review and Final Assessment

• Recap of Key Concepts: Revisiting all the covered material with quick exercises.
• Final Assessment: Practical tasks to evaluate skill progress.

Certification:

• Participants will receive a “Certificate of Participation.”
• Successful candidates will earn a “Certificate of Completion” after passing the final assessment.

Trainer’s Profile:

Mr. Enayet Ullah, ACCA

An ACCA member with a degree from Oxford Brookes University and over 9 years of teaching experience. Currently a Financial Analyst at Bashundhara Group, Mr. Enayet’s deep knowledge and dedication make him a trusted mentor to ACCA students.