Course NT-Excel (II) Microsoft Excel, part 2
The course provides knowledge and skills in working with Microsoft Excel tools for data preparation and analysis. The course was created on the basis of many years of experience in conducting trainings for data analysis experts, their most typical tasks and methods of analysis. The course also considers interesting innovations in Excel for analytics, in particular, working with multi-page tables, automating calculations using built-in functions, "deep diving" into pivot tables and their multiple settings, and also provides an understanding of working with the Data Object Model.
After completing this course, students will be able to:
- form reports of different complexity;
- choose the data you need according to one or more criteria;
- perform calculations;
- automatically collect one of several tables;
- build various graphs and charts;
- use built-in capabilities of the Excel program to automate similar operations.
Audience Profile
The course is intended for specialists who have studied one of the previous versions of Microsoft Excel, use the application at work and wish to gain advanced knowledge of Excel.
Before attending this course, students must have:
- knowledge in the scope of the first part of the course "Microsoft Excel"; or equivalent knowledge;
- skills for creating Excel workbooks, working with tables, saving files and performing basic data operations, formatting, copying and moving, cleaning and deleting;
- skills of using absolute and mixed reference formulas;
- skills in using Excel to create simple formulas, also using the "Insert function" window; (Insert Function);
- skills in using functions TODAY, ROUND, IF, SUMIF, COUNTIF, VLOOKUP (VPR), HLOOKUP (HPR), etc.;
- skills for working with the "Table" object, formatting and using the summary line, using the table field when writing formulas;
- skills for creating summary tables based on the data of one sheet;
- skills for constructing linear graphs and diagrams based on Excel table data.
1. Use of names
- Creating Named Cell Ranges and Constants
- Working with the Name Manager
- Using Names in Formulas and Workbook Navigation
2. Using hyperlinks
3. Some tips for working effectively in Excel
- Removing duplicates
- Visual presentation of data using conditional formatting (including formula-based formatting) and rule management
- Data visualization using sparklines
- Data splitting into columns (with delimiters, fixed width)
- Instant value populating
- Data sorting:
- Sorting by a single criterion
- Multi-level sorting
- Sorting by formatting
- Sorting by columns
- Sorting based on custom autocomplete lists
- Data filtering:
- AutoFilter
- Advanced Filter
- Filtering with a Separator
- Copying Filtered Data
- Grouping Rows and Columns. Creating a Sheet Structure
- Summarizing Subtotals
- Consolidating Data
4. Solving business problems using various functions
- Formulas that reference cells in other sheets and other workbooks
- Working with relationships
- Using some mathematical, statistical, and logical functions; date and time functions; text functions; reference functions and auto-substitution in formulas: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, VLOOKUP, HLOOKUP, INDEX, MATCH, ROW, COLUMN, IFS, AND, OR, NOT, IFERROR, SUM DATE, EOMONTH, EDATE, DATEDIF, NETWORKDAYS, WORKDAY, WEEKDAY, TIME, HOUR, MINUTE, SECOND, etc.
- Solving complex problems
5. Working with Smart tables
- Creating and formatting tables
- Built-in templates for table design
- Sorting and filtering tables. Using separators
- Using results. Result dependency on filters
- Using formulas in tables
- Converting a table to a range
6. Analyzing Data with PivotTables and PivotCharts
- Creating a PivotTable
- Customizing the PivotTable Layout and Style
- PivotTable Value Field Options
- PivotTable Data Pivot Operations
- Analyzing, Sorting, and Grouping PivotTable Data
- Filtering Data: Filters, Dividers, and Time Scale
- Displaying Report Filter Pages
- Customizing Subtotals and Grand Totals in a PivotTable
- Creating, Customizing, and Format PivotCharts
- Refreshing PivotTable and Chart Data
7. Information security
- Validating values entered by the user:
- Setting data entry restrictions
- Maintaining lists (drop-down list)
- Finding invalid values
- Protect cells, ranges, and hide formulas
- Protect Excel workbook sheets and structures
- Password-protect a workbook
Sign up for the closest date
Copyright © 2026 Networking Technologies