Centre for Continuing Education

Microsoft Excel Course: Level 3

Microsoft Excel. Unlock insights and tell the story in your data.

Learn Microsoft Excel the smart way with Microsoft Excel courses at CCE, the University of Sydney.

This short course focuses on the more advanced features beyond Microsoft Excel Course: Level 2. Advanced analysis tools including data linking, data consolidation and outlining and summarising are covered, as are some key automation features such as macros. Learn how to import and export data, create Data Tables and Scenarios for What If analysis, techniques to validate data, and create form controls such as list and combo boxes to make data entry easier.

Aims

This course aims to provide experienced users with proficient skills in developing more complex formulas, list analysis using a variety of tools and creating simple macros as well as features and tips to assist efficiency.

Outcomes

Upon successful completion of this course, participants should be able to:

  • use a range of lookup and reference functions
  • modify options
  • create and use defined names in a workbook
  • protect data in worksheets and workbooks
  • create summaries in their spreadsheets using subtotals
  • use data linking to create more efficient workbooks
  • use the Data Consolidation feature to combine data from several workbooks into one
  • understand and create simple PivotTables
  • construct and operate PivotTables using some of the more advanced techniques
  • create and edit a PivotChart
  • use goal seeking to determine the values required to reach a desired result
  • group cells and use outlines to manipulate the worksheet
  • create recorded macros
  • use the macro recorder to create a variety of macros.

Content

This short course covers the following topics:

Setting Excel Options

  • Understanding Options
  • Personalising
  • Setting the Default Font
  • Setting Formula Options
  • Understanding Save Options
  • Setting Save Options
  • Setting the Default File Location
  • Setting Advanced Options

Protecting Data

  • Understanding Data Protection
  • Providing Total Access to Cells
  • Protecting a Worksheet
  • Working with a Protected Worksheet
  • Disabling Worksheet Protection
  • Providing Restricted Access to Cells
  • Password Protecting a Workbook
  • Opening a Password Protected Workbook
  • Removing a Password from a Workbook

Importing and Exporting

  • Understanding Data Importing
  • Importing from an Earlier Version
  • Understanding Text File Formats
  • Importing Tab Delimited Text
  • Importing Comma Delimited Text
  • Importing Space Delimited Text
  • Importing Access Data
  • Working with Connected Data
  • Unlinking Connections
  • Exporting to Microsoft Word
  • Exporting Data as Text
  • Inserting a Picture
  • Modifying an Inserted Picture

Data linking

  • Understanding Data Linking
  • Linking between Worksheets
  • Linking between Workbooks
  • Updating Links between Workbooks

Grouping and Outlining

  • Understanding Grouping And Outlining
  • Creating An Automatic Outline
  • Working With An Outline
  • Creating A Manual Group
  • Grouping By Columns

Summarising and Subtotalling

  • Creating Subtotals
  • Using a Subtotalled Worksheet
  • Creating Nested Subtotals
  • Copying Subtotals
  • Using Subtotals with AutoFilter
  • Creating Relative Names for Subtotals
  • Using Relative Names for Subtotals

Data consolidation

  • Understanding Data Consolidation
  • Consolidating With Identical Layouts
  • Creating A Linked Consolidation
  • Consolidating From Different Layouts
  • Consolidating Data Using The SUM Function

Data Tables

  • Understanding Data Tables and What-If Models
  • Using a simple What-If Model
  • Creating a One-Variable Table
  • Using One-Variable Data Tables
  • Creating a Two-Variable Data Table

Scenarios

  • Understanding Scenarios
  • Creating a Default Scenario
  • Creating Scenarios
  • Using Names in Scenarios
  • Displaying Scenarios
  • Creating a Scenario Summary Report
  • Merging Scenarios

PivotTables

  • Understanding PivotTables
  • Recommended Pivot Tables
  • Creating your own PivotTable
  • Defining The PivotTable Structure
  • Filtering a PivotTable
  • Clearing a Report Filter
  • Switching PivotTable Fields
  • Formatting a PivotTable
  • Understanding Slicers
  • Creating Slicers
  • Inserting a Timeline Filter

PivotTable Features

  • Using Compound Fields
  • Counting in a PivotTable
  • Formatting PivotTable Values
  • Working with PivotTable Grand Totals
  • Working with PivotTable Subtotals
  • Finding the Percentage of Total
  • Finding the Difference From
  • Grouping in a PivotTable
  • Creating Running Totals
  • Creating Calculated Fields
  • Providing Custom Names
  • Creating Calculated Items
  • PivotTable Options
  • Sorting in a PivotTable

PivotCharts

  • Inserting a PivotChart
  • Defining The PivotChart Structure
  • Changing The PivotChart Type
  • Using The PivotChart Filter Field Buttons
  • Moving PivotCharts to Chart Sheets

Advanced Filters

  • Understanding Advanced Filtering
  • Using an Advanced Filter
  • Extracting Records with Advanced Filter
  • Using Formulas in Criteria
  • Understanding Database Functions
  • Using Database Functions
  • Using DSUM
  • Using The DMIN Function
  • Using The DMAX Function
  • Using The DCOUNT Function

Validating Data

  • Understanding Data Validation
  • Creating a Number Range Validation
  • Testing a Validation
  • Creating an Input Message
  • Creating an Error Message
  • Creating A Drop Down List
  • Using Formulas as Validation Criteria
  • Circling Invalid Data
  • Removing Invalid Circles
  • Copying Validation Settings

Controls

  • Understanding types of Controls
  • Understanding how Controls Work
  • Preparing a Worksheet for Controls
  • Adding a Combo Box Control
  • Changing Control Properties
  • Using The Cell Link to Display The Selection
  • Adding a List Box Control
  • Adding a Scroll Bar Control
  • Adding a Spin Button Control
  • Adding Option Button Controls
  • Adding a Group Box Control
  • Adding a Check Box Control
  • Protecting a Worksheet with Controls

Sharing Workbooks

  • Sharing Workbooks via the Network
  • Sharing Workbooks via OneDrive
  • Saving to OneDrive
  • Sharing Workbooks
  • Opening Shared Workbooks
  • Enabling Tracked Changes
  • Accepting or rejecting Changes
  • Disabling Tracked Changes
  • Adding Worksheet Comments
  • Navigating Worksheet Comments
  • Editing Worksheet Comments
  • Deleting Comments

Recorded macros

  • Understanding Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro with Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to The Toolbar
  • Running a Macro from The Toolbar
  • Assigning a Macro to The Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro

Intended Audience

This course assumes an intermediate level understanding of the software, and a good general understanding software and experience in the construction and modification of workbooks. It is designed for those who are ready to explore more of the advanced analysis and automation tools.

Delivery Style

Delivered as presenter-taught computer-based training in a Microsoft Windows environment. You will have dedicated access to a computer so you can follow the instructor and work through in-class activities. Please note that teaching instructions and materials are specifically tailored to Windows PC users.

Additional Information

Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.

Features

  • $50 repeat class - Conditions apply
  • Expert trainer
  • Dedicated computer for every student
  • Small class size
  • Student notes – yours to keep
  • Statement of completion

What others say.

  • Extremely knowledgeable tutor who created an excellent learning environment, clearly explained what we covered and assisted a few students in the break time too. Excellent take home resources. Thank you!

  • Really enjoyed the course, content fabulous, plenty of opportunities to practice and understand the procedures involved.

  • The pace was fast and the tutor was good at making sure everyone was on the same page before moving on. The lesson was punctuated by good humour and the tutor reiterated summaries that helped drill in key components. Fantastic class, highly recommended!

  • The course was designed perfectly to allow participants to engage fully with the course content and instructions provided by the tutor. The tutor was very helpful in explaining all concepts and directions so all could follow along and maximise the value of the course. I would highly recommend this course for those wishing to advance their Excel knowledge to a proficient and professional standing.

  • Hugely experienced tutor. Complex concepts explained in an easy to assimilate manner with very good practical exercises. Thank you.

  • I found the course moved at a great pace for me, especially since I had been to Level 2 Excel the week prior. The sessions are great for areas of focus and the course materials are excellent for personal review.

  • I really enjoyed the course. It was well structured and the tutor was very knowledgeable with a clear way of explaining complex concepts.

  • I wish I took this course before I started my job in investment banking! The course gave a great overview on the Excel advanced features which are applicable to the working environment. The workbook is detailed and Excel files are provided to do exercises within our own time. The class is small and the tutor spent time to answer questions with each one of us throughout the day.

  • Exceeded my expectations! The tutor was an exceptional trainer. I would 100% recommend it. Everything ran smoothly and I learnt so much.

  • The teacher was very knowledgeable and patient with the class. It was set at a great pace – not too slow where boredom sets in and not too quick that we couldn’t keep up. The classroom and computers were great. Every topic that was taught was new to me and I walked away with an understanding of not just what to do but why it’s done, which is a valuable way to ensure it’s remembered easier. I have highly recommended this course to my peers.

  • The course was intensive but well planned. As a result you never felt rushed in the learning process. The lecturer was engaging and an excellent teacher. I would recommend the course to anyone with a strong requirement for Excel in their day to day life.

  • Excellent course – felt I learned so much, and entered at the right level too (attended the Intermediate course too – the two courses go nicely together). Satisfied with the content, structure of the course, and the pedagogic approach. The tutor was outstanding – very helpful, gave tons of examples which we were able to relate to and plenty of repetitions. Have already recommended this to a lot of my colleagues!

  • The course was perfectly presented with a very competent presenter — and just as important, the course was incredible value for money. I have recommended it to my employer to consider this well presented and targeted training.

  • I really liked the format of the notes and exercises,very nice for self-study and suited to work through in small chunks.

  • Very enjoyable course and loved the training materials and the chance to re-do the exercises in my own time to increase understanding.

  • Well, I’m new to Excel, all I know I have pick up from work colleagues and I thoroughly enjoyed the course. Once I get into it I will benefit greatly with my work load. Pivot Table and Macro, loved learning about them. I’m only sorry it was for 1 day – there is so much more to learn...

  • I found the tutor’s knowledge and approach to teaching to be outstanding. The class strengths and weaknesses were taken into account as the tutor tailored the day to suit our needs.

Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...
Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...
Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...
Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...
Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...
Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...
Microsoft Excel Course: Level 3

<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>This short course focuses on the more advanced features beyond <a href="/course/mse2">Microsoft Excel Course: Level 2</a>. Advanced analysis

...

What others say.

  • The tutor was highly engaging, had a clear communication style and a true teacher who catered for the different needs of the class. I also found the content of the course was very beneficial.
  • The tutor was very informative and covered a massive amount in a short time. Excellent!
  • Excellent course - felt I learned so much, and entered at the right level too (attended the Intermediate course too - the two courses go nicely together). Satisfied with the content, structure of the course, and the pedagogic approach. The tutor was outstanding - very helpful, gave tons of examples which we were able to relate to and plenty of repetitions. Have already recommended this to a lot of my colleagues!