This Prospen Africa Microsoft Excel for Administrative and Accounting Professionals masterclass programme deals with the rich functionality of Microsoft Excel to deal with the challenges of working with different accounting data sets that an accounting department must deal with. The programme deals with the core fundamental of Excel toolsets that assist administrative and accounting staff in dealing with real accounting case studies.
Provide delegates with a professional understanding of how Excel’s deals with data |text and values|
Examine Microsoft’s methodology on ETL |Extract Transpose Load) data so it can be utilised in calculations.
Basic and advanced Data Cleaning data sets. Microsoft best practice is that users must move towards Power Query which allows for incredible transformations.
Examining the powerful formulas in Excel to conduct Lookup and Reference that allows for enhanced Table Development
Working with Tables and PivotTables to sort and filter information.
Administrative Officials:
Administrative Assistants/Officers: Those responsible for managing data, generating reports, and organizing information within an administrative context.
Office Managers: Professionals overseeing office operations, task delegation, and workflow management.
Executive Assistants: Individuals supporting executives in organizing information, scheduling, and data analysis.
Accounting Officials:
Accountants: Professionals dealing with financial data, analysis, and reporting.
Financial Analysts: Those involved in financial planning, forecasting, and data interpretation.
Bookkeepers: Individuals managing financial records, transactions, and reconciliations.
Our diverse instructional approaches ensure effective learning:
– Lectures & Presentations: Engage with expert-driven, stimulating content.
– Course Material: Access well-crafted supporting resources.
– Group Work: Collaborate on discussions and case studies for practical insights.
– Workshops & Role-Play: Participate in immersive, scenario-based activities.
– Practical Application: Focus on applying theoretical knowledge in real situations.
– Post-Training Support: Receive extensive support after training for skill implementation.
Understanding the properties of a Data Table and how data is manipulated to summarise data into meaningful Tables.
Understanding Excel’s value fields that allow for calculations
Accounting and numerical values
Date Values
Time Values
Other Values
Importing Data from external sources
Data Cleaning Techniques of Data Sets |from small to very large |5,000 rows plus|
“Utilising the standard tool in Excels to deal with small data sets:
– Data Tools (including Text to Column)
– Text functions Sort and Filter”
Utilising the Power Query to deal with massive data sets |plus 10,000 to millions of rows|
Converting PDF Documents to Tables with numerical values
Converting Lists into Excel’s TABLE object that gives us advanced Sorting and Filtering capability using Slicers
Introduction to Excel’s PivotTables that allows for the construction of multi-dimension Tables
Application of Excel’s Formula Library
“Lookup and Reference functions
– VLOOKUP, Hlookup, Xlookup, Index+Match
– Filter, Sort, Unique
– WrapCols, WrapRows, ToCol, ToRow, Transpose”
“Date and Time
– Date, Yearfrac, Year, Month, Day, Today,
– Networkday, Weekday, Weeknum
– Hour, Minute, Seconds”
Exploring Excel’s advanced formatting environment including conditional formatting
The entire day will be utilised in advanced case studies.
Automating reconciliations
Age Analysis of transactions
Forecasting payments and receipts
Development of advanced Tables with multiple dimensions
Presenting information to management