Excel Advanced

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.

Obsah školení

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