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.

Aims

This short course focuses on the more advanced features beyond Microsoft Excel Course: Level 2. Advanced analysis tools, especially pivot tables, solver, outlining and summarising are covered, as are some key automation features such as macros.

Outcomes

Upon completion of this short course, you should be able to:

  1. Use a range of lookup and reference functions.
  2. Modify options.
  3. Create and use defined names in a workbook.
  4. Protect data in worksheets and workbooks.
  5. Create summaries in your spreadsheets using subtotals.
  6. Use data linking to create more efficient workbooks.
  7. Use the Data Consolidation feature to combine data from several workbooks into one.
  8. Understand and create simple PivotTables.
  9. Construct and operate PivotTables using some of the more advanced techniques.
  10. Create and edit a PivotChart.
  11. Use goal seeking to determine the values required to reach a desired result.
  12. Group cells and use outlines to manipulate the worksheet.
  13. Create recorded macros.
  14. Use the macro recorder to create a variety of macros.

Content

This short course covers the following topics:

Lookup functions

  • Understanding Data Lookup Functions.
  • Using CHOOSE.
  • Using VLOOKUP.
  • Using VLOOKUP For Exact Matches.
  • Using HLOOKUP.
  • Using INDEX.
  • Using MATCH.
  • Understanding Reference Functions.
  • Using ROW And ROWS.
  • Using COLUMN And COLUMNS.
  • Using ADDRESS.
  • Using INDIRECT.
  • Using OFFSET.

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

Defined names

  • Understanding Defined Names.
  • Defining Names From Worksheet Labels.
  • Using Names In Typed Formulas.
  • Applying Names To Existing Formulas.
  • Creating Names Using The Name Box.
  • Using Names To Select Ranges.
  • Pasting Defined Names Into Formulas.
  • Defining Names For Constant Values.
  • Creating Names From A Selection.
  • Scoping Names To A Worksheet.
  • Using The Name Manager.
  • Documenting Defined Names.

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.

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 linking

  • Understanding Data Linking.
  • Linking Between Worksheets.
  • Linking Between Workbooks.
  • Updating Links Between Workbooks.

Data consolidation

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

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.

Goal seeking

  • Understanding Goal Seeking.
  • Using Goal Seek.

Grouping and outlining

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

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.

Recorder workshop

  • Preparing Data For An Application.
  • Recording A Summation Macro.
  • Recording Consolidations.
  • Recording Divisional Macros.
  • Testing Macros.
  • Creating Objects To Run Macros.
  • Assigning A Macro To An Object.

Delivery Style

This short course is delivered as presenter-taught computer-based training in a Microsoft Windows environment.

Intended Audience

This short course is designed for users who are already familiar with basic Excel features and operations and who are now ready to explore more of the advanced analysis and automation tools. This short course assumes a good general understanding software and experience in the construction and modification of workbooks. The student should be able to copy, create charts, and create basic formulas.

You should be familiar with the basic functions of a computer operating system such as navigating the environment using a mouse and keyboard, starting applications, copying and pasting objects, formatting text, creating folders, opening files, saving files, familiarity with the accessing the Internet and use of common web browsers. If you do not have these skills, we recommend attending Basic Computer Skills Course and Effective Internet Use before attempting this course.

Features

  • Free repeat class - Conditions apply
  • Free USB flash drive
  • Expert trainer
  • Dedicated computer for every student
  • Small class size
  • Student notes – yours to keep
  • Certificate of completion

What others say.

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

Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
Enrol Now
There are places available
If there isn't a class to suit you, please join the waiting list.