|
1. Lookup Functions
1. Understanding Data Lookup Functions
2. Using CHOOSE
3. Using VLOOKUP
4. Using VLOOKUP For Exact Matches
5. Using HLOOKUP
6. Using INDEX
7. Using MATCH
8. Understanding Reference Functions
9. Using ROW And ROWS
10. Using COLUMN And COLUMNS
11. Using ADDRESS
12. Using INDIRECT
13. Using OFFSET
2. Setting Excel Options
1. Understanding Excel Options
2. Personalising Excel
3. Setting The Default Font
4. Setting Formula Options
5. Understanding Save Options
6. Setting Save Options
7. Setting The Default File Location
8. Setting Advanced Options
3. Chart Object Formatting
1. Understanding Chart Object Formatting
2. Selecting Chart Elements
3. Using Shape Styles To Format Objects
4. Changing Column Colour
5. Changing Pie Slice Colour
6. Changing Bar Colours
7. Changing Chart Line Colours
8. Using Shape Effects
9. Filling The Chart Area And The Plot Area
10. Filling The Background
11. The Format Dialog Box
12. Using The Format Dialog Box
13. Using Themes
4. Labels And Names
1. Understanding Labels And Names
2. Creating Names Using Text Labels
3. Using Names In New Formulas
4. Applying Names To Existing Formulas
5. Creating Names Using The Names Box
6. Using Names To Select Ranges
7. Pasting Names Into Formulas
8. Creating Names For Constants
9. Creating Names From A Selection
10. Scoping Names To The Worksheet
11. Using The Name Manager
12. Documenting Range Names |
5. Protecting Data
1. Understanding Data Protection
2. Providing Total Access To Cells
3. Protecting A Worksheet
4. Working With A Protected Worksheet
5. Disabling Worksheet Protection
6. Providing Restricted Access To Cells
7. Password Protecting A Workbook
8. Opening A Password Protected Workbook
9. Removing A Password Workbook
6. Summarising And Subtotalling
1. Creating Subtotals
2. Using A Subtotalled Worksheet
3. Creating Nested Subtotals
4. Copying Subtotals
5. Using Subtotals With AutoFilter
6. Installing The Conditional Sum Wizard
7. Using The Conditional Sum Wizard
8. Creating Relative Names For Subtotals
9. Using Relative Names For Subtotals
7. Data Linking
1. Understanding Data Linking
2. Linking Between Worksheets
3. Linking Between Workbooks
4. Updating Links Between Workbooks
8. Data Consolidation
1. Understanding Data Consolidation
2. Consolidating With Identical Layouts
3. Creating An Outlined Consolidation
4. Consolidating With Different Layouts
9. PivotTables
1. Understanding Pivot Tables
2. Creating A PivotTable Shell
3. Dropping Fields Into A PivotTable
4. Filtering A PivotTable
5. Clearing A Report Filter
6. Switching PivotTable Labels
7. Formatting A PivotTable
10. Data Linking
1. Using Compound Fields
2. Counting In A PivotTable
3. Formatting PivotTable Values
4. Working With PivotTable Grand Totals
5. Working With PivotTable SubTotals
6. Finding The Percentage Of Total
7. Finding The Difference From
8. Grouping In PivotTables
9. Creating Running Totals
10. Creating Calculated Fields
11. Providing Custom Names
12. Creating Calculated Items
|
13. PivotTable Options
14. Sorting In A PivotTable
11. PivotCharts
1. Creating A PivotChart Shell
2. Dragging Fields For The PivotChart
3. Changing The PivotChart Type
4. Using The PivotChart Filter Pane
5. Moving PivotCharts To Chart Sheets
12. Goal Seeking
1. Goal Seek Components
2. Using Goal Seek
13. Grouping And Outlining
1. Understanding Grouping And Outlining
2. Creating An Automatic Outline
3. Working With An Outline
4. Creating A Manual Group
5. Grouping By Columns
14. Solver
1. Understanding How Solver Works
2. Setting Solver Parameters
3. Adding Solver Constraints
4. Performing The Solver Operation
5. Running Solver Reports
6. Refining Solver Answers
15.
Recorded Macros
1. Understanding Excel Macros
2. Setting Macro Security
3. Saving A Document As Macro Enabled
4. Recording A Simple Macro
5. Running A Recorded Macro
6. Relative Cell References
7. Running A Macro With Relative References
8. Viewing A Macro
9. Editing A Macro
10. Assigning A Macro To The Toolbar
11. Running A Macro From The Toolbar
12. Assigning A Keyboard Shortcut To A Macro
13. Deleting A Macro
14. Copying A Macro
15. Tips For Developing Macros
16. Recorder Workshop
1. Preparing Data For An Application
2. Recording A Summation Macro
3. Recording Consolidations
4. Recording Divisional Macros
5. Testing Macros
6. Creating Objects To Run Macros
7. Assigning A Macro To An Object
Concluding Remarks
|
