Working Effectively in MS Excel 2400-SP-EXCEL-EP

The goal of this course is to introduce advanced MS Excel tools for working with data and effective methods for using them at an advanced level.

Course topics:

✓ Advanced search and address functions
X.LOOKUP function (in two-dimensional lookup), INDEX function, MATCH function, X.MATCH function. Dynamic ranges, OFFSET function, ADDRESS function, naming ranges.

✓ Advanced pivot tables
Grouping and filtering variables, statistics and advanced calculation options, fields and calculated fields, pivot charts, creating pivot tables from external data sources, slicers, and timelines.

✓ Advanced conditional calculation functions
Advanced logical tests, the IF function, SWITCH function, conditional count functions COUNTIF, COUNTIFS, conditional calculation functions SUMIF, SUM. CONDITION, AVERAGE.IF, AVERAGE.CONDITION, MAX.CONDITION, MIN.CONDITION, database functions, and constructing criteria in database functions.

✓ Dynamic arrays
New features in Excel 365, dynamic arrays, spilling ranges, the FILTER, SORT, UNIQUE, and SEQUENCE functions. Combining dynamic arrays with conditional functions and lookup functions.

✓ Conditional Formatting
Quick conditional formatting, cell highlighting rules, first and last rules, data bars, color scales, icon sets, creating custom rules, creating advanced rules using functions.

✓ Data Validation
Analyzing built-in criteria and creating custom ones, creating error-proof forms (e.g., values outside a list, duplicate values, leaving observations blank). Advanced data validation using formulas. Dynamic data validation lists using the OFFSET, UNIQUE, SORT, or FILTER functions.

✓ Form controls
Controls such as: label, button, list box, combo box, spin button, scroll bar, check box, radio button, group box. Creating business dashboards using controls, including a deposit calculator, an offer search tool based on the FILTER function, and a dynamic chart based on data entered into controls.

✓ Using Excel in Business
Advanced pivot tables. Advanced, custom charts. Creating a dashboard using pivot tables, charts, and slicers. Creating a dashboard with graphical elements and custom charts. Automating tasks using dynamic arrays. Working with big data, analyzing the performance of Excel functions.

✓ Business dashboards
Real-world business examples for data of various types and structures. Creating analytical reports.