This course allows you to widen and explore your knowledge of built-in Excel functionality. Moreover, this course gives you explanations and practical exercises to create your own Excel function, call them and use Macros for fun and productive tool not only to fasten up your daily Excel tasks, but also to help with better understanding of fundaments of Visual Basic.
If you have been already working with Excel and want to explore possibilities on Excel expert level, then this course is for you!
Course fits in 2-6 people in one group to have both best group work experience and personal training benefits.
1 Name of the training
Microsoft Excel deep learning and VBA-based macro training in a small group of 2-6 people
2 Curriculum group
• 0482 - Computer use
3 Study volume
50 academic hours - classroom learning + 60 academic hours – independent study
The volume of training includes time for solving tasks and tests.
4 Description of the learning environment
Train in the space provided by the facilitator. If necessary and by agreement, at the customer's place.
5 Study form
Auditory, contact, online participation is also possible if necessary
6 Languages of study
Estonian, Russian, English
7 Teaching staff
Veronika Jallai
8 Price
€2,500 + VAT
9 Target group
Employed or unemployed adults
Computer users who have experience working with a spreadsheet program and want to learn new capabilities and applications to make working with a spreadsheet program more efficient
10 Conditions for starting studies
Must have at least Microsoft Excel training or work experience with Excel.
A conversation (telephone) during which the presence of advanced knowledge in computer use is found out. Conducted by the trainer before registering for the training.
Specifically:
Basic and basic skills in table calculation (filling in, saving, opening a table, simple design, simpler operations and calculations (operations with cells, calculation with areas - sum), ability to use the mouse and keyboard.
If necessary, they will be directed to the necessary training, after completion of which it will be possible to participate in the training.
11 Course content
Working in the spreadsheet program MS Excel:
• Table structure logic and table preparation for calculations and analyses.
• Options for eliminating design errors with Excel tools.
Working with data:
• Different data types (%, neg number, date, time, currency, text, number) and their effect; for calculation.
• Formatting of entered data and its effect on calculations.
• Removing formats.
• Placing data between different worksheets and files, structure logic.
Calculations and functions in Excel:
• Order of verbs and use of parentheses.
• Named areas in data tables and simplifying work through them.
• Basic functions
• Rounding functions
• Text functions for correcting and editing data
• Data splitting and merging functions
• Date functions
• Database functions for summarizing
• Conditional functions
• Search functions in tables
• Combining different functions with each other (e.g. combining several IF functions, eliminating error messages, etc.).
• Absolute and relative address in formulas.
• Calculation between multiple tables and different worksheets.
• Using a formula row, tracing functions.
Using large data tables:
• Use of tables with large data volumes (freeze, split, search data) (Freeze, Split, Side by Side).
• Data sorting, sorting settings (sorting by dates. creating sorting conditions).
• Data filtering, using an extended filter, displaying unique data (Filter, Advanced Filter).
• Subtotals by group (Subtotal).
• Goal Seek (finding the correct starting number in the table).
• Creating, reading and changing a pivot table (PivotTable).
• Functions in a crosstab. Presentation of data in % and no. values in the final report.
• Preparation of daily reports (by months, quarters, years).
• Calculation with fields (adding calculations to a crosstab), charts.
Designing tables:
• Conditional formatting, adding conditional formatting through the function (Conditional Formatting).
Charts:
• Creating a diagram from the data in the table.
• Creating a chart from several tables.
• Adding and removing data from the chart.
• Diagram design, work with scale.
Saving and printing:
• Recording formats and recording.
• Page settings for printing (page number, header, footer, borders, print area).
• Tuning the printout of tables, graphs.
Protecting tables and working together:
• Table protection options (file password protection, cell password protection, partial protection).
• Sharing a workbook on a computer network and using a common workbook.
• Change tracking and data recovery capabilities.
• Adding input restrictions to tables (enable text, enable number, check space, etc.).
Import data:
• Import data into Excel from external data sources. CSV, Internet, etc.
Macros and VBA:
• Configuring Excel security settings to run and create macros
• Macro recording and execution capabilities
• Basics of Visual Basic
• functions and procedures
• overview of Excel objects and variables
• Writing a macro (for requesting data from users, filling and organizing tables...)
• Creating a calculation function used in Excel tables
• Input and output functions (MsgBox and InputBox)
• Reading data from the worksheet and writing to the worksheet
• Using forms to enter parameters
• Conditional statements (IF; CASE)
• Different cycles (FOR-NEXT, DO-WHILE, DO-UNTIL, LOOP)
• Using Excel's built-in functions for data analysis
• Running a procedure or function from within another procedure
• Running the procedure step by step, searching for errors
• Creating and using forms
• Various work-related practical tasks
• Learning opportunities, careers and Excel level diplomas
12 Learning outcomes
• As a result of the training, the trainee acquires the basic knowledge of digital literacy, which is based on the content creation competency area of the international DigComp framework.
• As a result of the training, the trainee can skillfully use the MS Office spreadsheet capabilities.
• Those who have completed the training are more confident in using spreadsheet software and have acquired correct and work-simplifying techniques;
• acquires systematic and comprehensive skills for effective use of MS Excel capabilities in daily business.
• Knows work-accelerating "tricks" that save time and practical skills to independently prepare voluminous documents in a professional manner.
• Is able to prepare various reports from tables, illustrate data using diagrams and format them correctly.
• Can use Excel analysis tools.
• Those who have completed the training have comprehensive skills for working with a spreadsheet program. Create various calculating tables. Perform analyzes as needed using the functions and analysis capabilities of the spreadsheet program
• Can work with MS Excel macros, create, modify and use them
• Can create new functions using VBA
• Knows how it is possible to automate daily and specific activities in Excel
13 List of study materials
If necessary, additional study materials recommended by the trainer based on the learner's needs.
14 Requirements for Termination
Completion of practical tasks is a condition for graduation.
15 Evaluation
Practical work can be counted if the task is completed by at least 50%
16 Method of assessment
Formative during the entire training and summarizing at the end of the course based on the learning outcomes.
The trainer gives written or oral (including video/audio speech) feedback to the learner about the performance of practical tasks
Assessment is non-differentiating.
17 Qualifications for the lecturer necessary to conduct the training
At least a bachelor's degree or equivalent.
At least three advanced computer training experience.
Professional Macro and VBA experience.
18 Other information
The times and days of the training are agreed between the learner and the trainer.
Graduates are issued a certificate or certificate in accordance with the Adult Education Act.
