This course prepares to bring skills in using MS Excel from basic to advanced levels. The course provides students with both an overview of the advanced features and functions of the program and knowledge of their practical applications. Practical exercises accompany every topic covered during the course. VBA basics will also be added, which will be explained and practically shown.
Duration
48h
Agenda
- Revise the basics of Excel
- Data entry and editing, selecting, searching for cells and groups of cells
- Copy, paste, fill cells and ranges
- Graphic formatting
- Cell styles (standard and custom)
- Data types and formatting by data type
- Comments: insertion, appearance, editing
- Inserting and working with simple objects (graphics, frames)
- Customize the ribbon
- Formulas
- Naming cells and ranges
- Relative, absolute and mixed addressing
- Addressing from other sheets, using named ranges
- Logical functions Or, And
- Date and time functions
- Text functions
- Conditional function IF
- Conditional autosums
- Functions VLOOKUP, VLOOKUP
- Transpose function
- Quick analysis
- Naming cells
- Name field
- Name from selection
- Dynamic names
- Fast navigation
- Readability of formulas
- Conditional formatting
- Automatic formatting depending on the value
- Rule management
- Modifying standard rules
- Table object
- Create and format tables
- Dynamic named range
- Convert to range
- Data validation
- Checking the correctness of the entered data
- Drop-down lists
- Input message, types of warnings
- Data sorting and filters
- Sort by value
- Sort by multiple columns at once (custom sort)
- AutoFilter, filtering by data type
- Sorting and filtering with colors and icons
- Custom Lists
- Filling the day of the week with months
- Create your own custom lists
- Sort by custom list
- Data Tools
- Data Consolidation
- Remove duplicates
- Outline and grouping
- Partial Totals
- Outline tool
- Check and correct formulas
- A circular reference and setting the number of iterations of the calculation
- Working with charts
- Basic chart types and specifying data sources
- Format charts from gallery and manually
- Create templates
- New charts in Excel 2016
- PivotTables and PivotCharts
- Create pivot tables
- Calculations
- Sorting and filtering
- Create PivotCharts
- Formatting
- Workbook management
- Create templates
- Cross-workbook links (fixing file links)
- File properties, document inspection compatibility
- File sharing
- Change management
- Protection of ranges of workbooks and sheets
- Data Import
- Simple import from database
- Import data from a text file
- What if analysis
- Search result
- Scenario manager
- Advanced formulas
- Addressing from other worksheets, using named ranges
- Advanced date and time functions
- Advanced text features
- Functions VLOOKUP, VLOOKUP
- Functions Index, Matching Position, Intermediate Adr, Ranges Intersection
- Array formulas
- Naming Cells, Advanced Named Cell Work
- Naming cells and ranges
- Name visibility ranges
- Referencing names from other worksheets
- Dynamic names (no table object)
- Name manager
- Intermediate addr
- Conditional formatting
- Dynamic formatting depending on the settings in the sheets
- Formula dependent
- Table object
- Structured references (headers, data, summary row)
- Slicers
- Advanced Filters
- Building multiple complex filtering conditions
- Data Tools
- Data Consolidation
- Text as columns
- Check and correct formulas
- Dependency tracking
- Show formulas
- Detector
- Accuracy of calculations
- Manual recalculation of the sheet and workbooks
- Working with charts
- Sparklines
- New charts in Excel 2016
- PivotTables and PivotCharts
- Creation of pivot tables (dimensions and grouping, basic summaries, multiple summaries horizontal / vertical)
- Calculations (show values as)
- Calculation fields and items
- Sorting and filtering (by labels, by values)
- Create PivotCharts
- Slicers (including timeline, joining multiple tables / charts to the same slicers)
- Formatting (styles, selecting table elements)
- Conditional formatting (formatting visibility)
- TakeTableData function
- Sort by custom lists
- Generating pivot tables based on a template
- Data model (relationships).
- File sharing
- Change management (logging changes, enabling changes, committing changes)
- Protection of ranges of workbooks and sheets
- Marking as final
- Data Import
- Simple import from database
- Import data from a text file
- Data import from XM file
Audience and prerequisites
This course is intended for professionals and office workers who want to learn advanced-level Excel skills.
Before attending this course, students must have:
- Basic computer knowledge, such as keyboard and mouse skills.
- Basic file-management skills. The student should know how to navigate to folders and files on a computer running Windows.
- Basic knowledge of the Excel interface, workbook manipulation and formula creation.
Certificates
Course participants receive completion certificates signed by ALX.
