Home · Course Details

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.

  • Duration: 1 day
  • Versions: This course is suitable for users of Excel 2010, 2013, 2106
  • 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.

  • 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

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

Name Date Time Location Cost
Excel Intermediate 24 January 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 7 February 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 21 February 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 7 March 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 21 March 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 4 April 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 23 April 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 7 May 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 23 May 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 11 June 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 24 June 2019 09:00 am - 04:45 pm Sydney $462
Excel Intermediate 22 January 2019 09:00 am - 04:45 pm Melbourne $462
Excel Intermediate 12 February 2019 09:00 am - 04:45 pm Melbourne $462
Excel Intermediate 13 March 2019 09:00 am - 04:45 pm Melbourne $462
Excel Intermediate 8 April 2019 09:00 am - 04:45 pm Melbourne $462
Excel Intermediate 14 May 2019 09:00 am - 04:45 pm Melbourne $462
Excel Intermediate 12 June 2019 09:00 am - 04:45 pm Melbourne $462

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.