Detailed Course Outline
1 Names
- 1.1 Basic knowledge of names
- 1.2 Set names
- 1.3 Insert names in formulas
- 1.4 Useful techniques when using names
- 1.5 Manage names
- 1.6 Exercise
2 Structure tables
- 2.1 Create outlines
- 2.2 Working with structured tables
- 2.3 Remove outlines
- 2.4 Working with partial results
- 2.5 Exercises
3 Link formatting to conditions
- 3.1 Individual rules for conditional formatting
- 3.2 Displaying value distribution with graphical elements
- 3.3 Formatting cells depending on conditions
- 3.4 Display and edit rules
- 3.5 Working with multiple rules
- 3.6 Exercise
4 Monitor formulas and cell contents
- 4.1 Check formulas for errors
- 4.2 Monitor cell contents
- 4.3 Exercise
5 Special filters
- 5.1 Basic knowledge of special filters
- 5.2 Search criteria for the special filter
- 5.3 Using special filters
- 5.4 Exercise
6 Excel databases
- 6.1 Basic knowledge of Excel databases
- 6.2 Remove duplicates
- 6.3 Using database functions
- 6.4 Exercise
7 Combine and consolidate data
- 7.1 Combining data
- 7.2 Basic knowledge of table consolidation
- 7.3 Consolidate data with formulas
- 7.4 Consolidate data by position
- 7.5 Consolidate data by heading
- 7.6 Edit consolidation results
- 7.7 Exercise
8 Create Pivot Tables
- 8.1 Basic knowledge of pivot tables
- 8.2 Create recommended pivot table from Excel data
- 8.3 Creating an individual pivot table from Excel data
- 8.4 Changing calculations in the value range
- 8.5 Using predefined report layouts and formats
- 8.6 Filtering data of a pivot table
- 8.7 Filtering Pivot Table Data Using Timelines
- 8.8 Create pivot table from external data source
- 8.9 Exercise
9 Customize Pivot Tables
- 9.1 Changing the layout of a pivot table
- 9.2 Grouping data of a pivot table individually
- 9.3 Using special display options
- 9.4 Using calculated fields and elements
- 9.5 Additional partial results
- 9.6 PIVOT DATA ASSIGNMENT function
- 9.7 Updating data in a pivot table
- 9.8 Create PivotCharts
- 9.9 Changing properties of pivot tables
- 9.10 Changing the default layout of pivot tables
- 9.11 Exercises
10 Trend analyses, data tables, target value search
- 10.1 Data analysis with Excel
- 10.2 Basic knowledge trend analyses
- 10.3 Trend analyses
- 10.4 Determine/display trend on a forecast sheet
- 10.5 Trend analysis with the TREND function
- 10.6 Basic knowledge of data tables
- 10.7 Data table with one variable
- 10.8 Data table with two variables
- 10.9 Target value search
- 10.10 Exercise
11 Solver
- 11.1 Activate Add-In Solver
- 11.2 Basic knowledge solver
- 11.3 Calculating a specific target value
- 11.4 Defining constraints for the solver
- 11.5 Calculating the minimum or maximum target value
- 11.6 Using solver models
- 11.7 Solver reports
- 11.8 Exercise
12 scenarios
- 12.1 Basic knowledge of scenarios
- 12.2 Create and protect scenarios
- 12.3 Working with scenarios
- 12.4 Exercise
13 Matrizen
- 13.1 Basic knowledge of matrices
- 13.2 Creating matrix formulas
- 13.3 Edit matrix formulas
- 13.4 Matrix constants
- 13.5 Exercise
14 Different diagram types
- 14.1 Bar and column charts
- 14.2 Line and area diagrams
- 14.3 Circular and ring diagrams
- 14.4 Hierarchy diagrams
- 14.5 Statistics diagrams
- 14.6 Dot (XY) and bubble charts
- 14.7 Waterfall and rate diagrams
- 14.8 Funnel diagrams
- 14.9 Surface and network diagrams
- 14.10 Composite diagrams
- 14.11 Map diagrams
- 14.12 Exercise
15 Working professionally with diagrams
- 15.1 Formatting the label of the value axis
- 15.2 Changing the division of the value axis
- 15.3 Format category axis
- 15.4 Show secondary axis
- 15.5 Show trend lines
- 15.6 Display error indicators for data series
- 15.7 Diagram templates
- 15.8 Exercise
16 Design tables with illustrations
- 16.1 Basic knowledge of illustrations
- 16.2 Inserting illustrations
- 16.3 Editing illustrations
- 16.4 Formatting graphics and shapes
- 16.5 SmartArt graphics
- 16.6 Exercise
17 Importing or exporting data
- 17.1 Import Access data
- 17.2 Import text files
- 17.3 Importing other foreign formats
- 17.4 Exporting Excel data
- 17.5 Exercise
18 Creating Queries with Power Query
- 18.1 Basic knowledge Power Query
- 18.2 Creating Queries with Power Query
- 18.3 Update or delete queries
- 18.4 Edit queries
- 18.5 Editing Query Criteria in the Power Query Editor
- 18.6 Creating/adding queries from multiple database tables
- 18.7 Exercise
19 Working in a team
- 19.1 Basic knowledge of teamwork
- 19.2 Making workbooks available and sharing them online
- 19.3 Editing workbooks online in a team