Advanced Excel Course Syllabus

Module 1: Microsoft Excel Fundamentals

• Launching Excel and understanding the startup screen
• Introduction to the Excel interface and customizing the Quick Access Toolbar
• Navigating and understanding the structure of an Excel workbook
• Saving, opening, and managing Excel documents
• Common Excel shortcut keys

Module 2: Entering and Editing Text and Formulas

• Entering text for spreadsheet titles
• Working with numeric and date values
• Creating and editing basic formulas
• Understanding relative versus absolute cell references

Module 3: Working with Basic Excel Functions

• Using SUM(), MIN(), MAX(), AVERAGE(), and COUNT() functions
• Addressing common calculation errors
• Utilizing AutoSum, AutoFill, and AutoSum shortcut key

Module 4: Modifying an Excel Worksheet

• Moving and copying data, rows, and columns
• Changing cell dimensions and managing rows/columns visibility
• Renaming, deleting, and organizing worksheets

Module 5: Formatting Data in an Excel Worksheet

• Applying font formatting, background colours, and cell borders
• Formatting data as currency, percentages, and creating styles
• Utilizing Conditional Formatting for dynamic data presentation

Module 6: Inserting Images and Shapes into an Excel Worksheet

• Inserting and formatting images and shapes
• Working with Excel SmartArt

Module 7: Creating Basic Charts in Excel

• Creating and modifying column and pie charts
• Formatting and moving charts between worksheets

Module 8: Printing an Excel Worksheet

• Previewing and adjusting print settings
• Managing margins, scaling, orientation, and adding headers/footers
• Printing specific ranges of cells

Module 9: Working with Excel Templates

• Using and creating custom Excel templates

Module 10: Managing Excel Lists

• Understanding list structure and sorting data
• Filtering data using AutoFilter and creating subtotals
• Formatting lists as tables and removing duplicates

Module 11: Excel List Functions

• Using DSUM(), DAVERAGE(), DCOUNT(), and SUBTOTAL() functions

Module 12: Data Validation in Excel

• Creating data validation lists and custom error messages
• Using dynamic formulas with data validation

Module 13: Importing and Exporting Data

• Importing data from text files and Microsoft Access
• Exporting data to text files

Module 14: PivotTables in Excel

• Creating and modifying PivotTables
• Grouping and formatting PivotTable data
• Using PivotCharts and slicers

Module 15: Handling Large Data Sets

• Using Freeze Panes and grouping data
• Linking worksheets and consolidating data

Module 16: Conditional Functions in Excel

• Using IF(), COUNTIF(), SUMIF(), and IFERROR() functions
• Creating and managing named ranges

Module 17: Lookup Functions in Excel

• Using VLOOKUP(), HLOOKUP(), INDEX(), and MATCH() functions
• Combining INDEX() and MATCH() for dynamic lookups

Module 18: Text-Based Functions in Excel

• Utilizing LEFT(), RIGHT(), MID(), LEN(), SEARCH(), and CONCATENATE() functions

Module 19: Auditing and Error Checking

• Tracing precedents and dependents in formulas
• Using the Watch Window and showing formulas

Module 20: Protecting Worksheets and Workbooks

• Protecting specific cells, workbook structure, and adding passwords

Module 21: Automating Tasks with Macros

• Understanding and creating macros
• Activating the Developer Tab and recording macros
• Assigning macros to buttons for automation