Microsoft Excel Course Outline

Microsoft Excel Beginners, Intermediate & Advanced Courses

There is a training workbook with exercises, exercise files and tests for each half day course. Over all Beginners to Advanced courses there is the equivalent of 5 full days of classroom style training but you can enrol into each of the short Excel courses one at a time if you want.

Enrol Quick for Short Courses and Learn Fast with Tutor Support.

 

Microsoft Excel Beginners Courses

Microsoft Excel Beginners (Course 301) – Section 1 

Introduction to Microsoft Excel: Get to know the Excel screen and what the various parts of the screen are called, learn how to turn parts of the screen on and off so that you can customise your screen and know how to deal with situations when you use other peoples computers and their Excel screen looks different. Understanding how Zoom works, the active cell and how to preview your work so you can see what it looks like before printing it. Understanding the difference between page break preview and print preview, working with files and understanding Shortcut menus.

  • the excel screen (Length – 2:39)
  • more about the excel screen (Length – 5:08)
  • print preview & zoom + Active cell (Length – 4:49)
  • uses for Excel & changing zoom (Length – 3:48)
  • changing what you view (Length – 3:02)
  • Page Break and Normal Views (Length – 2:24)
  • working with multiple files (Length – 2:37)
  • Right click and shortcut menu (Length – 3:16)

Microsoft Excel Beginners (Course 301) – Section 2 

Working with exercise files, understanding the contents of cells and realise where the information is that even though it may appear in a different place or not appear at all. Getting around the program using the mouse and keyboard, moving between sheets and files (workbooks), Inserting and working with sheets.

  • open the exercise files (Length – 3:09)
  • the different type of content in cells (Length – 3:16)
  • moving around the spreadsheet (Length – 5:02)
  • moving around using the keyboard (Length – 4:59)
  • moving around workbooks (Length – 3:27)
  • working with sheets (Length – 4:30)
  • inserting sheets (Length – 2:52)

Microsoft Excel Beginners (Course 302) – Section 1 

Selecting information: Understanding the mouse cursors used within Excel, how to select cells, columns and rows and multiple ranges of information, how to select cells using the keyboard. Copy and Paste: Drag and drop, cut, copy and paste in a worksheet and from one worksheet to another and even between different software programs.

  • mouse pointers and active cell (Length – 2:25)
  • selecting cells, rows and columns (Length – 3:12)
  • select all and multiple ranges (Length – 3:46)
  • multiple columns and selecting text (Length – 2:40)
  • selecting text using the keyboard (Length – 3:29)
  • drag and drop (Length – 2:51)
  • cut and paste (Length – 2:57)
  • copy and paste (Length – 3:31)
  • copy and paste between sheets (Length – 4:48)
  • copy and paste between programs (Length – 3:39)

Microsoft Excel Beginners (Course 302) – Section 2 

Editing cells: Typing and entering information, editing existing information and entering the changes, using the status bar information, escaping out of cells. Changing column width and row height for single and multiple columns as well as making all the columns the same width and using AutoFit. Database: Creating a simple database, understanding simple database structure (ideal knowledge for mail merge with Microsoft Word), Inserting and deleting columns, using Undo and redo options.

  • entering information (Length – 5:48)
  • editing information (Length – 3:37)
  • status bar in Excel (Length – 2:25)
  • editing & ESC out of cells (Length – 3:10)
  • column width and row height (Length – 2:22)
  • width of several rows or columns (Length – 2:39)
  • AutoFit (Length – 3:16)
  • creating a simple database (Length – 4:51)
  • more about databases (Length – 3:33)
  • insert and delete columns and rows (Length – 4:17)
  • undo and Redo options (Length – 1:54)

Microsoft Excel Beginners (Course 302) – Section 3 

Autofill: Automatically fill numbers, dates, number patterns, days, months and years and much more. The fast way to insert sequential numbers and words. Understanding date formatting (this forms the basis for future calculations based on date format),

  • Using AutoFill (Length – 3:10)
  • Autofill a series (Length – 2:29)
  • AutoFill dates (Length – 3:23)
  • AutoFill Shortcut menu (Length – 2:55)
  • AutoFill Days, Months, Years (Length – 3:13)
  • Date formatting (Length – 1:49)
  • increasing numbers using fill handle (Length – 3:17)
  • AutoFill exercises (Length – 2:22)

Microsoft Excel Beginners (Course 303) – Section 1 

Formulas: Entering formulas using the keyboard, arrow keys and the mouse, understand the importance of relative cells references, common formula errors, using multiplication and division and how to combine two formulas in one.

  • Entering formulas using keyboard (Length – 2:57)
  • Entering formulas using arrow keys (Length – 2:16)
  • Entering formulas using the mouse (Length – 3:07)
  • formula errors (Length – 3:07)
  • Why use cell references (Length – 2:53)
  • Multiply and divide (Length – 4:13)
  • Multiple formulas (Length – 4:08)

Microsoft Excel Beginners (Course 303) – Section 2 

AutoCalculate: Get quick additions and basic functions quickly without having to enter formulas. Functions: Introductions to functions and function syntax, Autosum, Autosum for several and selected ranges and an introductions to other basic functions like Maximum, Minimum, Average and more…

  • AutoCalculate (Length – 3:21)
  • AutoCalculate Options (Length – 3:34)
  • Intro to AutoSum Function (Length – 2:27)
  • AutoSum for several ranges (Length – 2:03)
  • AutoSum for selected ranges (Length – 1:44)
  • Other Basic Functions (Length – 2:35)

Microsoft Excel Beginners (Course 303) – Section 3 

Functions: Sum function in more detail, using the formula bar and editing functions, using the F2 key, using Autofill to save a lot of time copying (or filling) functions using relative cell references.

  • Typing the Sum Function (Length – 1:52)
  • Sum Function with keyboard and mouse (Length – 1:50)
  • Sum function area (Length – 3:45)
  • Edit Formulas using formula bar (Length – 2:41)
  • Edit functions within the cell (F2 key) (Length – 3:03)
  • AutoFill to copy formulas (relative) (Length – 3:50)
  • Autofill Functions (summary) (Length – 3:46)

Microsoft Excel Beginners (Course 303) – Section 4 

Formatting: This effects the way your spreadsheet looks, everything from lines, colours, shading, and even the format of text and numbers. Topics include: Currency formatting, date and text formatting, merging cells and centering, Alignment formatting and text control within single and  merged cells, fill colour, font colour and Borders.

  • Types of Formatting (Length – 3:35)
  • Currency Formatting – menu bar (Length – 3:19)
  • Currency Formatting – toolbar (Length – 1:31)
  • Date Formatting – menu bar (Length – 2:42)
  • Text Formatting + Merge and Centre (Length – 2:55)
  • Alignment Formatting (Length – 3:44)
  • More about Alignment Formatting (Length – 2:49)
  • Alignment Formatting – Text Control (Length – 2:59)
  • Fill Colour and Font Colour (Length – 3:10)
  • Borders (Length – 4:02)

Microsoft Excel Beginners (Course 303) – Section 5

Advanced formatting: Autoformats that allow you to quickly make your spreadsheet look very professional, repeat last action, and format painter make it faster for you to duplicate commands and formatting, using format painter with entire spreadsheets to apply multiple formatting in just a couple clicks.

  • AutoFormat (Length – 3:18)
  • AutoFormat Options (Length – 3:24)
  • Repeat Last Action – F4 key (Length – 2:24)
  • Format Painter (Length – 2:45)
  • Format Painter – multiple cells (Length – 2:44)
  • Format Painter – Entire Sheets (Length – 3:01)

 

Excel Beginners   Enrol   Packages

Microsoft Excel Intermediate Courses

Microsoft Excel Intermediate (Course 304) – Section 1

Views: There are several ways you can view your spreadsheet on the screen and each has its own purpose. The most important thing to note is that none of these views affect the way your spreadsheet prints. Topics include: Zoom (set and custom), Print Preview and page break preview versus Normal view. Page Setup: Including page orientation, Scaling and margins, and changing these settings.

  • Print Preview (Length 3:09)
  • Zoom and Print Preview (Length – 2:26)
  • Normal and Page Break Preview (Length – 2:38)
  • Introduction to Page Setup (Length – 3:50)
  • Page Setup – Orientation (Length – 2:33)
  • Page Setup – Scaling (Length 3:14)
  • Scaling using Page Break Preview (Length – 2:34)
  • Page Formatting and Margins (Length – 3:32)
  • Margins in Print Preview (Length – 2:56)

Microsoft Excel Intermediate (Course 304) – Section 2

Print areas: Setting and clearing defined print areas, understanding the difference between default and set page breaks, Inserting and moving page breaks, Changing the print order of pages, centering the spreadsheet when printing and showing/hiding gridlines and headers.

  • Set and Clear the Print Area (Length – 3:49)
  • Inserting a page break (Length – 2:49)
  • default and set page break (Length – 1:55)
  • set page breaks (Length 2:51)
  • page print order (Length – 3:12)
  • Centering and showing guidelines (Length – 3:12)

Microsoft Excel Intermediate (Course 304) – Section 3 

Introduction to headers and footers, using preset and custom headers and footers, including the use of file information and common fields. Freezing panes when viewing so that certain information headers remain on the screen while you scroll through lists, and Repeating rows so that when the information is printed rows or column headers are repeated. Printing options.

  • Preset Headers and Footers (Length – 2:53)
  • Intro to Custom Headers and Footers (Length – 2:49)
  • Text Header (Length – 2:09)
  • File Information fields in footer (Length – 2:43)
  • Freezing Panes (Length – 2:39)
  • Repeating Rows at top of page (Length – 2:45)
  • Printing multiple sheets (Length – 2:35)

Microsoft Excel Intermediate (Course 305) – Section 1

Charting: Creating a simple chart and learning that charts are dynamic and based up on the information stored somewhere else within the spreadsheet, Inserting labels for heading, the x & y axis, values and more. Learning how to chart mutliple rows and columns

  • Opening exercise files (Length – 1:22)
  • Creating a basic chart (Length – 3:15)
  • Charts are dynamic (Length – 1:52)
  • Charts with labels (Length – 2:43)
  • Charts with more labels (Length – 2:45)
  • Charting multiple rows and columns (Length – 2:58)

Microsoft Excel Intermediate (Course 305) – Section 2

Charting: Organising your sheet tabs, especially for new chart sheets, changing the data range within the chart wizard, charting several separate data ranges, charting options and different types of charts (pie charts)

  • Organising your sheet tabs (Length – 1:49)
  • Changing the data range in wizard (Length – 2:11)
  • Charting multiple data ranges (Length – 3:39)
  • Charting Options for labels (Length – 3:10)
  • Using a Pie chart (Length – 2:53)

Microsoft Excel Intermediate (Course 305) – Section 3 

Chart formatting: Understanding chart objects for the purpose of formatting, understanding the buttons in the chart toolbar, and using right-click to format. Formatting a data series, and parts of a pie chart, having 2 different data formats within the one chart and how to get charts into Microsoft Word documents.

  • Chart objects (Length 2:57)
  • Charting toolbar (Length – 3:09)
  • Right-click and format (Lengh – 2:19)
  • Format the data series (Length – 3:17)
  • Format pie charts (Length – 2:22)
  • Two or more chart formats (Length – 1:45)
  • Getting charts into Word (Length – 4:20)

Microsoft Excel Intermediate (Course 306) – Section 1 

Number and percentage formatting: Understanding the various ways of formatting, including the understanding form percentages to better use them in formulas and functions. Understand that cells can be formatted (even without data in them) so that when you enter data, it can look completely different to what you expected to see. Sometimes cells can be formatted in such a way that even though you enter numbers, a date may be shown. Learn about why in this section.

  • Cell number formatting (Length – 2:18)
  • More on number formatting (Length – 1:41)
  • Percentage formatting (Length – 2:28)
  • More about percentage formatting (Length – 2:27)
  • Using percentage in formulas (Length – 1:47)

Microsoft Excel Intermediate (Course 306) – Section 2 

Relative and Absolute Cell references: Understand how relative cell referencing works to help you copy formulas and functions quickly. Also understand where they don’t work and why. Absolute cell referencing is a powerful way to reduce the amount of calculations you have to create and can save several columns or rows of typing. Learn the different types of and how to use absolute cell references in this section.

  • Relative Cell references (Length – 2:36)
  • Where Relative Cell references don’t work (Length – 2:39)
  • Absolute Cell references (Length – 2:49)
  • Absolute Reference options (Length – 2:00)
  • Absolute Row Reference (Length – 1:39)
  • Formulas using Absolute Row Reference (Length – 2:28)

Microsoft Excel Intermediate (Course 306) – Section 3 

Learn and understand some fundamental mathematical functions that will help you perform most formulas and functions. These are fundamental skills that will help make more complicated functions easier to understand. Understand how to combine two formulas into one and as a result write much better formulas and get to know the order of mathematical calculations. Using Functions like Maximum, Minimum and average

  • Formulas – increasing by percentage (length – 2:36)
  • Formulas – mathematics rules (Length – 2:51)
  • Formulas – adding multiple ranges (Length – 2:55)
  • Formulas – adding multiple ranges using keyboard (Length – 1:23)
  • Formulas – Average and Maximum (Length – 2:02)

Microsoft Excel Intermediate (Course 306) – Section 4 

Getting to know the “Insert Functions dialog box”, how to search for a particular function and understand the concept of “arguments” (or syntax) in functions and function categories such as financial, Date and time, maths and trig, etc. Understanding and constructing an IF Statement.

  • Functions – Insert Functions dialog box (Length – 3:43)
  • Functions – Function Arguments dialog box (Length – 3:42)
  • Functions – Function Arguments more info (Length – 2:28)
  • Functions – The IF Function (2:54)
  • Functions – Constructing an IF Function (Length – 3:54)

Microsoft Excel Intermediate (Course 306) – Section 5 

Comments: Understand the uses for comments. Insert comments that help to explain the purpose of a cell, choose the size and location of the comments and control the way they appear on the spreadsheet. Edit comments and change the formatting within a comment, change the name of the person who made the comment, and make sure that the comment appears all the time or only when you move the mouse to it. Show/Hide Columns/Rows: Learn how to hide columns or rows that contain confidential information. This way you can print the spreadsheet out without the confidential information, but use the confidential information to perform the necessary calculations.

  • Understand and insert Notes (Length – 2:15)
  • Editing Comments (Length – 1:25)
  • Show or Hide Comments (Length – 1:24)
  • Formatting Comments (Length – 1:03)
  • Hide & Unhide columns and rows (Length – 1:39)

 

Excel Intermediate   Enrol   Packages

Microsoft Excel Advanced Courses

Microsoft Excel Advanced (Course 307) – Section 1

  • Microsoft Excel Program Options
  • Introduction to Excel Databases 
  • How to Create an Excel Database
  • Using Forms 
  • Navigate around your form
  • Find Information Using a Form 
  • Find Information Using the Find Command 
  • Searching an Entire Workbook for Text 
  • Search an Entire Workbook for a Cell Reference 
  • Finding Text in Comments

Microsoft Excel Advanced (Course 307) – Section 2

  • Labels and Information 
  • Names used in Formulas 
  • Named Ranges are Absolute Values 
  • Find a Range of Named Cells 
  • Named Ranges can be used in Combination 
  • The Naming Manager & Naming Constants
  • Applying Named Ranges to Formulas 
  • Apply Name Ranges from a Selection

Microsoft Excel Advanced (Course 307) – Section 3

  • Open Data Files in Excel 
  • Using the Text Import Wizard
  • Sorting and Filtering 
  • Removing a Column or Row 
  • Filtering
  • Advanced Filter using Wildcards (search text within cells) 
  • Advanced Filter for Multiple Search Criteria (OR) 
  • Narrow the Results using Advanced Filter (AND)
  • Data Validation, Drop Down Lists and Named Ranges 
  • Formatting Cells with Drop Down Lists

Microsoft Excel Advanced (Course 308) – Section 1

  • Using AutoFilter 
  • Cleaning up data using AutoFilter – part 1
  • Cleaning up data using AutoFilter – part 2
  • Perform an Advanced Filter 
  • Advanced Filter using Wildcards (search text within cells) 
  • Advanced Filter for multiple search criteria 
  • Narrowing search results using Advanced Filter

Microsoft Excel Advanced (Course 308) – Section 2

  • Inserting a Pivot Table 
  • Filtering data in a Pivot table 
  • Sort dates by month in a pivot table 
  • Changing the value field settings in a pivot table 
  • Creating pivot table charts
  • The CHOOSE function
  • Get month values and Choose
  • Automatically produce ratings
  • Choose the calculation you want to occur

Microsoft Excel Advanced (Course 308) – Section 3

  • Get months values using the Choose function
  • Show grades for student results data using CHOOSE function
  • Case Study: Calculating PAYG & Super from Net Payments to staff
  • Insert a VLOOKUP which tells us the Isle and price of a product 
  • Perform vlookup using Named Ranges 
  • HLOOKUP 
  • Paste Special for Formulas, values, comments and formatting 
  • MATCH AND INDEX 
  • Find the match 
  • Incorporate cell information in text 
  • Use Index to find the value in a matched row 
  • NESTED MATCH AND INDEX FUNCTIONS
  • Performed a nested MATCH & INDEX function 
  • Combine MATCH with IFERROR functions 
  • PROTECTION 
  • Open a protected workbook 
  • Understanding cell protection 
  • Finding Cells which contain calculations

Microsoft Excel Advanced (Course 309) – Section 1

  • Consolidating data from several sheets into one
  • Consolidate data using named ranges
  • Duplicate formatting between sheets using Format Painter
  • Grouping and ungrouping Rows in a worksheet
  • Manually grouping rows in Excel
  • Case Study: Using financial calculations and goal seek, discover how much we can afford to borrow to buy a house

Microsoft Excel Advanced (Course 309) – Section 2

  • Turning on the Solver Tool in Excel 
  • Using Solver and Constraints to achieve our objective
  • Case Study: Use the solver to help a business startup find the best way to reach their budgeted target income given different products and services available for sale.

Microsoft Excel Advanced (Course 309) – Section 3

  • Inserting a Form Component (Combo box) – Developer Tab
  • Case Study: How to save thousands off your mortgage

 

Excel Advanced   Enrol   Packages

 

Training Choices

If you are looking for an Excel course by itself, we have a number of options available. See the enrolment page to see the options.

If you wish to complete an MYOB Bookkeeping course as well as an Excel Course you will notice that the MYOB Bookkeeping PLUS training package includes everything you need.