Advanced Formulas, Functions and Commands
- Errors in a worksheet
- Function categories
- Nested functions
- Working with a lookup function
- Setting up cell protection
- Removing document protection
- Protecting a workbook
- The INDEX function
- Custom number formats
- Conditional formatting
- Hyperlinks
Working with Data Lists
- General information on the structure of a data list
- Complex sorting via a dialog box
Working with Data Validation
- Setting a data validation rule
- Checking existing data retrospectively
- Extending data validation
Goal Seek
Consolidating
The Scenario Manager
- The problem
- Working with estimated data
- Opening the Scenario Manager
- Creating a report for scenarios
- Viewing, editing and deleting a scenario
Data Analysis Using Data Tables
- Data table with one variable
- Data table with two variables
The PivotTable
- What is a PivotTable?
- A data list is required
- PivotTable Tools
- Creating the PivotTable report
- Modifying the PivotTable
- Swapping rows and columns
- Filtering and sorting
- Grouping data
- Displaying extreme values
- Changing the data source
- Inserting a slicer and timeline
- Power Pivot and Power View
Inserting/Creating a Table in a Range
- Inserting a table in the default format
- Changing the table format
- Inserting a table using a table style
- Filtering with slicers
- Deleting a table
Outlining Excel Data
- Showing and hiding cell ranges
- Removing the outline
- Defining levels and ranges yourself
Charts
- Break-even analysis
- Sparklines
- Combo chart
- Extending charts with data series
- Scaling
- Changing units
- Data labels
- Creating a PivotChart
- New chart types
Inserting Illustrations (Graphics, Clip Art, etc.)
- Inserting clip art (online graphics)
- Editing inserted graphic objects
- Picture tools
- Adding graphics and objects to a chart
- Getting add-ins from the Office Store
Macros – Automating Workflows
- Recording a macro
- Running a macro
- Opening a workbook with macros
Creating a Custom Function
- Procedures
- Components of a custom function
- The custom Bruttobetrag (gross amount) function
- Calling the custom function
Data Import and Export
- Exchanging data via the clipboard
- The Paste icon
- Cell references to other worksheets
- External references
- OLE and DDE
Templates
- The advantages of a template
- Setting up and saving a template
- Using the template for a new workbook
- Editing the template
Forms
- Validation and cell protection
- Controls
- Formatting
- Printing
