Course Code:
exceladvbsk
Duration:
15 hours
Prerequisites:
- Knowledge of Windows and the basics of using Microsoft Excel.
Overview:
Format of the Course
- Interactive lecture and discussion.
- Lots of exercises and practice.
- Hands-on implementation in a live-lab environment.
Course Customization Options
- customised course
Course Outline:
Adjustment of the working environment
- Hotkeys, facilities
- Tool - go to
Organization of information
- Assign and manage the names of cells and ranges
- Protect worksheet and workbook
- Secure and encrypt files
- Collaboration and track changes, comments
- Inspection sheet
- Create your own templates (charts, worksheets, workbooks)
Data analysis
- Logic
- Basic Features
- Advanced Features
- Creation of complex / multiple formulas
- Scenarios
- Search result
- Solver
- Charts
- Graphics support (shadows, charts, AutoShapes)
Database management (list)
- Data consolidation
- Grouping and outlining data
- Sorting data (over 4 columns)
- Advanced data filtering
- Database Functions
- Subtotal (partial)
- All About Tables and Pivot Charts
Cooperation with other applications
- Get External Data (CSV, TXT)
- OLE (static and link)
- Web Queries
- Publication sheets on site (static and dynamic)
- Publication PivotTables
Automation of work
- Conditional Formatting
- Create your own formats
- Check the validation
Automated Data Processing with Power Query
Advanced Analysis with Power Pivot
Macros - automation tasks in sheets
- Registration macros
- Understanding and modifying the code macros (VBA items)
- Connecting the interface macros or workbook
- Create forms on screen or printed