
Course Overview
The Excel Advanced course is aimed at those who want to effectively analyse and populate data and produce professional-looking workbooks.
Detailed Course Content
|
Split Text to Multiple Columns
- Fixed width data type
- Delimited data type
Data Cleansing
- Text case: LOWER, UPPER, PROPER
- Extract text: LEFT, MID, RIGHT
- Remove characters: CLEAN, TRIM
- Replace text: REPLACE, SUBSTITUTE
- Format: TEXT, VALUE
- Join text: CONCAT, TEXTJOIN
- TRANSPOSE
Use What-if Analysis Tools
- Goal seek
- Data tables
- Scenarios
- Scenario summary reports
Record, Run & Edit Macros
- Record a macro
- Run a macro
- Edit a macro
Data Consolidation
- Consolidate identical source layout
- Consolidate with links to source data
- Consolidate non-identical source layout
Filter Data
- Filter items
- Use the filter search tool
- Text filters
- Number filters
- Date filters
- Custom filters
Advanced Filters
- Set up filter criteria
- Apply a filter in-place
- Clear a filter
- Extract data
Database Functions
- Use DSUM function
- Use DAVERAGE function
- Use DCOUNT & DCOUNTA functions
- Use DMIN function
- Use DMAX function
Logical Functions
- Use IF functions to check data
- Use IF functions to populate data
- Use AND & OR functions to to perform multiple tests
Lookup Functions
- VLOOKUP for vertical lookups
- HLOOKUP for horizontal lookups
- XLOOKUP for flexible lookups
- LOOKUP for approximate matches
|
Formula Auditing
- Trace precedents/dependents
- Show formulas
- Error checking
Create a PivotTable
- Insert a PivotTable
- Select data to add to PivotTable
- Change PivotTable layout
PivotTable Settings
- Remove grand totals
- Print settings
Sort & Group Data in PivotTables
- Sort data alphanumerically
- Sort data by custom list
- Reorder items manually
- Subtotal data
- Group data
Use Calculations in PivotTables
- Create a calculated item
- Create a calculated field
Filter Data in PivotTables
- Filter data from PivotTable
- Filter data from Fields List
- Insert Slicers and Timelines
Create PivotCharts
- Create a PivotChart
- Modify a PivotChart
Sparklines
- Display mini line chart
- Display mini column chart
- Display win/loss chart
Insert Pictures in Excel
- Insert pictures in headers/footers
- Insert pictures in worksheets
- Insert pictures in cells
Checkboxes
- Insert a checkbox
- Toggle a checkbox
- Use checkbox value in COUNTIF function
- Remove checkbox retaining value
Import Up-to-date Web Data
- Import stock data
- Import currency data
- Import geography data
Printable course information
View Calendar
|
Pre-requisites
- Good knowledge of experience in using Microsoft Excel
- Knowledge of basic functions and linking data
- Alternatively, completion of Excel Intermediate training course
|
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-712349-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();