|
|
 |
|
 |
|
Microsoft Excel > Advanced
Lesson 1 – Filtered lists
Setting up a filtered list
View only the data you need
with automatic filtering
Find records in a database that meet your criteria
Extract records that meet your criteria
Analyse data with database functions
Sort data by specific criteria
Lesson 2 – Financial functions
Working with Excels financial functions
Using the PMT function to calculate loan repayments
Using the PV to calculate the present value of an investment
Using the FV to calculate the future value of an investment
Lesson 3 – Conditional logic
Using conditional Logic - IF functions
Evaluating a condition to True or False
Evaluating more than one condition
Working with nested Functions
Lesson 4 – Lookup tables
Creating a lookup table
Using lookup tables
Working with Vlookup and Hlookup
Limitation of Lookup tables
Using the Match function
Lesson 5 – Advanced charting and graphing
Advanced Graphing techniques
Creating custom charts
Classic combination Charts
Plotting data using secondary axis
Custom scatter graphs
Line and column combination graphs
Lesson 6 – Data input tables
Creating a data table
Using one input data tables
Using two input data tables
Formulas to use with input tables
Changing variables in input tables
Lesson 7 – Pivot tables
Creating and building a pivot table
Working with pivot tables
Changing and updating pivot tables
Getting external data into a workbook
Creating a dynamic summary with a pivot table
Lesson 8 – Sharing data with other applications
Converting a worksheet into a Microsoft Access table
Create links to more than one worksheet
Update data when the source information changes
Breaking links with other files
Creating Hyperlinks
Lesson 9 – Auditing your spreadsheet
Working with the auditing tools
Tracing dependants
Tracing precedents
Protecting your data
Protect a sheet
Protect a workbook
Share your work with colleagues
Lesson 10 – Advanced Excel Functions
Comparing alternatives for better decisions
Using goal seek
Creating multiple scenarios to compare alternatives
Test different scenarios using What If
Save different groups of values using
Scenario Manager
Lesson 11 – Customising user options
Analysing and sharing your data
Validating your data
Restricting data entry to ensure accuracy
Tracking changes
Creating custom views
Lesson 12 – Creating and using Macros
Creating macros to automate repetitive tasks
Add comments to your macro to make them easier to understand
Edit your macros
Create a macro button to make the macro easier to run
|
|
|
