Course description
This course is assigned for skilled Microsoft Excel users, who want to use special tools, techniques, methods and functions. You should already know Microsoft Excel on “Microsoft Excel – practical use” level.
Course structure
Conditional Formatting
- Based on a cell value
- Based on a formula
- Quick styles in conditional formatting
Formulas
- Entering advanced formulas
- Naming cells and ranges
- Impact of copying and moving absolute references
Functions
- IF function
- VLOOKUP function
- SUMIF, COUNTIF, SUBTOTAL functions
- Entering and adjusting nested functions
- Selected functions from category Text, Date and Time
- Conversion of numbers saved as text to number format
- Treating error messages
Links
- Linking sheets and workbooks
- Linking with Word and PowerPoint applications
- Consolidation of data from more spreadsheets
Charts
- Chart types
- Combined chart types
- Creating charts in a single cell
Databases in Microsoft Excel
- Preparation of data
- Multiple sorting
- Advanced filtering options
- Customized own list
- Data validation
- Extracting unique values
- Grouping data
PivotTables
- Creating and modifying PivotTabels and PivotCharts
- Filtering and sorting in PivotTabels
- Tools Slicer and Timeline
- Displaying options
- Grouping categories
- Option Show values as
- Repeating labels in PivotTables
Document security
- Sheet and workbook protection
- Hiding formulas
- To save a file with a password
Microsoft Excel settings
- Creating customized ribbon
- Quick access toolbar
