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.

Enrol Now
Enquire About This Course

  • 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.

  • 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

Exercise Files

Challenge Exercise Files

Summary of common shortcut keys

Full list of shortcut keys

 Excel file of all keyboard shortcuts

 Post Course Quiz

Name Date Time Location Cost
Excel Intermediate 3 December 2020 09:00 am - 04:45 pm Sydney $420.00
Enrol

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.

Contact Us