
Course Overview
The Excel Advanced course is aimed at those who want to effectively analyse their data and create professional reports in Excel.
Detailed Course Content
|
Sorting
- Sort alphabetically
- Sort numerically
- Sort by date
- Sort multiple columns
- Sort by colour
- Sort by custom list
Filtering
- Filter items
- Use the filter search tool
- Text filters
- Number filters
- Date filters
- Custom filters
Advanced Filtering
- Set up filter criteria
- Apply a filter in-place
- Clear a filter
- Extract data
Database Functions
- Use DSUM function
- Use DAVERAGE function
- Use DCOUNT function
- Use DMIN function
- Use DMAX function
Goal Seek
- Use Goal Seek to set desired outcome
- Clear Goal Seek results
Data Tables
- Use a one-way data table
- Use a two-way data table
Scenarios
- Set up a scenario
- Display a scenario
- Produce a scenario summary report
Macros
- Record a macro for a scenario summary
- Run the macro
Consolidation
- Consolidate identical source layout
- Consolidate with links to source data
- Consolidate non-identical source data
|
Subtotals
- Subtotal a single column
- Create a nested subtotal
- Remove subtotals
Group data
- Manually group rows and columns
- Automatically group data
- Clear grouping
Create a Pie Chart
- Insert a 2D pie chart
- Select / Change pie chart data
- Add percentages to pie chart
- Change pie slice colour
Create a Column Chart
- Insert a quick column chart
- Add a trendline
Customise Charts
- Change chart type
- Select chart style and colours
- Add chart elements
- Move a chart
- Resize a chart
Create a Pivot Table
- Insert a pivot table
- Select data to add to pivot table
- Change pivot table layout
- Pivot table options
- Update pivot table source data
Analyse data in Pivot Tables
- Filter data
- Subtotal data
- Group data
Calculations in Pivot Tables
- Create a calculated item
- Create a calculated field
Printable course information
View Calendar
|
Pre-requisites
- Good experience in using Excel
- Knowledge of basic functions and linking data
- Alternatively, completion of Excel Effective Workbooks 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);
})();