[vc_row][vc_column][vc_column_text]OVERVIEW
Students will learn how to make use of the more advanced functions in Excel and get an introduction to Visual Basic for Applications (VBA) programming language. They shall be able to use advanced functions and formulas, know how to format content, create beautiful charts and apply these skills to situations such as creating reports, business documents with formulas (invoices, quotations, receipts), etc.
DURATION
5 Days full day workshop (09:00-16:00hrs) or 10 days evening classes (17:30-19:30hrs)
COST
K3,500 per person
COURSE OUTLINE
Lesson 1: Introduction and Essential Background
- Working with several workbooks and sheets
- Formatting of cells and data
- Enhancing a Worksheet’s Appearance
- Encrypting Files and locking cells with passwords
- Printing workbooks, setting margins and sizing of content
- Importing and exporting data in CSV and other formats
Lesson 2: Formulas and Functions
- Autosum Functions (SUM & AVERAGE)
- IF and Nested Functions
- Working with Named Ranges
- Array Functions (SUMIF & AVERAGEIF)
- Formula Auditing
- Formulas with Absolute References
- Text Functions
- Using the VLOOKUP Function
- Using the LOOKUP Function
- Applying formulas and function to real world scenarios
Lesson 3: Working with Charts
- Creating beautiful kinds of charts from source data
- Using Recommended and Standard Charts
- Chart Elements
- Chart Tools – Design & Format
- Trendlines
- Error Bars
- Working with Axis Options
- Advanced Chart Formatting
- Creating charts for reports
Lesson 4: Pivot Tables
- Getting Started with Pivot Tables
- Displaying Data in a Pivot Table
- Formatting a PivotTable
- Using the Classic Pivot Table Layout
- Slicers
Lesson 5: Introduction to Macros in Excel
- Recording and Playing Macros
- Copying and Deleting Macros
- Visual Basic and Macros
Lesson 6: Introduction to Visual Basic for Applications (VBA)
a) Working with the the Visual Basic Editor
- Creating Procedures and Functions (Sub and Function)
- Declare and Use Variables
- Displaying Message to the User
- Getting Input from the User
- Working with the Excel Object Model
b) Understanding Objects, Properties and Methods
- Referencing Cells and Ranges
- Gathering Cell and Range Information
- Methods (Offset and Resize)
- Using With Blocks
- Referencing Workbooks and Worksheets
c) Controlling Program Flow
- Using the IF Statement
- Using the Select Case Statement
- Using Loops (For Next, Do)
d) Using Form Controls and Templates
- Advanced Lookup Functions (Index, Match)
- Advanced Functions (Error, Logical, Rounding, Indirect, Array formulas)
- User Defined Functions Using VBA
[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_btn title=”Time Table” color=”inverse” i_icon_fontawesome=”fa fa-arrow-right” add_icon=”true” link=”url:https%3A%2F%2Fkenoinstitute.org%2Ftimetable%2F|||” css=”.vc_custom_1505254097002{padding-right: 5px !important;}”][vc_btn title=”Register” color=”danger” i_icon_fontawesome=”fa fa-arrow-right” add_icon=”true” link=”url:https%3A%2F%2Fkenoinstitute.org%2Fregister%2F|||” css=”.vc_custom_1505254119246{padding-right: 5px !important;}”][vc_btn title=”More Courses” color=”success” i_icon_fontawesome=”fa fa-arrow-right” add_icon=”true” link=”url:https%3A%2F%2Fkenoinstitute.org%2Fcourses%2F|||” css=”.vc_custom_1505254136854{padding-right: 5px !important;}”][/vc_column][/vc_row]
