Home ·
This course provides a solid grounding in the more commonly used formulas and Functions in Excel and in the best practice methods for analysing data. You will learn techniques to help you find information, write formulas and analyse data in tables, lists and charts and how to get the most out of the powerful Pivot Tables and Pivot Chart data analysis tools.
Not sure which level to attend? Click here to take our quick online quiz.
Not sure which level to attend? Click here to take our quick online quiz.
Snapshot
- Duration: 1 day
- Versions: This course is suitable for users of Excel 2010, 2013, 2016, 2019
- Target Audience: This course is suitable for experienced Excel users who need to develop skills with complex formulas and data analysis tools.
- Prerequisites: Successful completion of MCI Solutions’ Excel Introduction courses or equivalent knowledge and skills are required to participate in this course.
Learning Outcomes
- Use absolute and relative references in formulas
- Create range names
- Use statistical, logical, math and date Functions
- Use subtotals to summarise lists
- Create and modify charts
- Create and modify Pivot Tables and Pivot Charts
Course Outline
Conditional formatting
- Applying and clearing conditional formatting
- Top ten items
- Working with Data Bars, Colour Scales and Icon Sets
- Creating and editing Sparklines
Formula referencing
- Absolute versus relative referencing
- Creating absolute and mixed references
Labels and names
- Creating names for cell ranges
- Using names to select ranges
- Using names in formulas
Statistical Functions
- Using COUNT, COUNTA and COUNTIF
- Using MODE, MEDIAN, STDEV
Logical Functions
- Using IF to display text and to calculate values
- Nesting IF Functions
- Using IFERROR, AND, OR and NOT
Math Functions
- Using INT, ROUND and PRODUCT
- Using SUMIF, SUMIFS and SUMPRODUCT
Summarising and subtotaling
- Creating and using subtotals
Creating charts
- Creating a new chart
- Working with an embedded chart
- Resizing a chart
- Printing charts
- Creating a chart sheet
- Changing the chart type and chart layout
- Changing the chart style
- Deleting a chart
Pivot Tables
- Creating a PivotTable
- Filtering a PivotTable report
- Clearing a report filter
- Formatting a PivotTable report
- Creating slicers
PivotTable techniques
- Using compound fields
- Counting in a PivotTable report
- Formatting PivotTable report values
- Working with PivotTable totals
- Grouping in PivotTable reports
- Creating calculated fields
- PivotTable options
PivotCharts
- Creating a PivotChart
- Changing the PivotChart type
- Using the PivotChart filter field buttons
Exercise Files| Name | Date | Time | Location | Cost (inc GST) | |
| Excel Intermediate | 6 February 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 25 February 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 12 March 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 18 March 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Intermediate | 24 March 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 2 April 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 21 April 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 6 May 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 20 May 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 10 June 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 17 June 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Intermediate | 23 June 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 7 July 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 21 July 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 6 August 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 19 August 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 3 September 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 16 September 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Intermediate | 22 September 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 7 October 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 20 October 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 5 November 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 24 November 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 3 December 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 | |
| Excel Intermediate | 9 December 2020 | 09:00 am - 04:45 pm | Melbourne | $462.00 | |
| Excel Intermediate | 16 December 2020 | 09:00 am - 04:45 pm | Sydney | $462.00 |
Related Content
Not quite the content you’re looking for?
Need a course you can’t find content for on our website? We have quite possibly the largest catalogue of course content in Australia! Let us know what you’re looking for and we will customise something for you.