Microsoft Excel Online Courses (Introduction, Intermediate, Excel)

Microsoft Excel: Introduction – Intermediate – Advanced

INTRODUCTION

Course Description: This basic Microsoft Excel course is ideal for beginners who want to learn how to produce spreadsheets, work with data and perform basic calculations

Performance Based Objectives  

• Upon successful completion of this course, participants will be able to:
• Create, modify and format a spreadsheet
• Use basic formulas and functions
• Calculate and manage multiple worksheets
• Create a chart
• Customise the print settings and print

Course prerequisites: Participants will need to be familiar with Microsoft Windows and basic Windows techniques such as opening, closing and saving files.

Course Outline

Overview
• Navigation shortcuts
• Quick range selection techniques
• Getting data into your spreadsheet
• Data entry ranges
Formatting the worksheet
• Adjust column widths
• Indenting
• Centre aligning
• Merging and centring
• Text Wrap• Formulas
• Navigation shortcuts
• Quick range selection techniques
• Getting data into your spreadsheet
• Data entry ranges
Multiple worksheets
• Copy and paste
• Copy an entire sheet
• Calculating across worksheets
• Copying formulas
• Renaming sheets
• Inserting/deleting a sheet
• Grouping sheets• Functions
• The AVERAGE function
• The MAX Function
• The MIN Function
Formatting text and numbers
• Font formatting
• Number formatting
• Borders
• Cell shading• Format painter
• Adding cell comments
Working with columns and rows
• Inserting columns/rows
• Deleting columns/rows
• Hiding columns/rows
• Freeze panes
• AutoFill sequences
Sorting and filtering
• Quick list sorting
• Basic list filtering• Charts
• Selecting data to chart
• Creating a chart
• Changing the chart type
• Switching columns/rows
• Adding titles
• Reposition the legend
• Using chart layouts
• Printing a chart
• Pie chart specifics
Printing and page setup
• Print preview
• Margins, orientation, scaling
• Page layout view
• Page break preview
• Headers and footers
• Printing titles on all pages
• Setting a print area
• Print
• Save as PDF
Working with OneDrive Files
• Saving to OneDrive
• Opening a file in Excel Online

Book your place today: Ylenia Morselli – [email protected] – 086 0144853

 

INTERMEDIATE

Course Description: This course has been developed for people wanting to utilise Excel to perform calculations using a variety of common worksheet functions, filter, sort and summarise database lists, format and modify charts, and conditionally format cells.

Performance Based Objectives  

• Understand and use a range of common worksheet functions
• Understand and utilise Absolute Referencing
• Organise a database list by advanced filtering and subtotalling
• Utilise Conditional Formatting to change cell appearance

Course prerequisites: Participants will need to have attended or be familiar with the topics covered in the Microsoft Excel Introduction course.

Course Outline

Functions and Formulas
• Absolute cell referencing
• Function syntax
• Common statistical functions
• The COUNTIF function
• The SUMIF function
• The IF Function• Working with date and time
• Date functions – TODAY and NOW
• EDATE and NETWORKDAYS
• Work with time values
• AutoFill date sequences
Conditional formatting
• Format cell value
• Format using a formula
• Editing conditional formatting rules
• Adding sparklines• Advanced sorting and filtering
• Sorting custom lists
• Sorting by colour
• Complex filtering criteria
• Searching for criteria
• SUBTOTAL function
• Subtotalling a list
• Creating a simple PivotTable
• Advanced charts
• Creating a chart sheet
• Switch columns/rows
• Create a dual axis chart
• Changing a series chart type
• Moving a chart
• 3D charts
• 3D chart rotation
• Create a simple, static dashboard

Book your place today: Ylenia Morselli – [email protected] – 086 0144853

ADVANCED

Course Description: This course exposes several advanced features of Excel for What-if analysis, database analysis and how to build calculations using advanced functions as well as how to automate processes.

Performance Based Objectives  

• Build complex calculations with advanced functions
• Link, export and consolidate data
• Limit data entry and build complex models with Lookup functions
• Analyse data with simple Tables, PivotTables and PivotCharts.
• Create and modify Macros

Course prerequisites: Participants will need to have attended or be familiar with all the topics covered in the Microsoft Excel Intermediate course.

Course Outline

Protecting your data
• Workbook protection
• Protecting cells
• Protecting structureFunctions
• Rounding and math
• AND and OR
• Nested IF
• IFS function
• VLOOKUP and HLOOKUP
• Transposing data
• Concatenating
• INDEX and MATCH
• Using help for functionsRange names
• Defining range names
• Using names in formulas
• Navigating with names
• Using named constants
• Documenting range namesData Validation
• Limiting cell values
• Creating drop-down cell lists
• Validation error messages

• What-if analysis tools
• Using goal seek

Getting and transforming data
• Get Data
• Transform in Query Editor
• Clean up data in Query Editor
Tables
• Create a table
• Add a table formula
• Add records to a table
• Add totals row
• Work with an outline – group and hideWorkbook linking
• Workbook linking
• Dealing with broken links
Data consolidation
• Consolidation by cell position
• Consolidation by headingsExporting data
• Saving as a different file type
• Exporting to Word
• Pasting data with a link
• Copying a graph with a linkPivotTable reports
• Create a PivotTable
• Rearranging data
• Applying filters
• Adding fields
• Modify field settings
• Refreshing data
• Formatting a PivotTable
PivotCharts
• Creating a PivotChart
• Filtering a PivotChartMacros
• Recording a Macro
• Editing a Macro
• Relative/Absolute Recording
• Stepping through a Macro
• Macro buttons
• Customising the toolbar
• Writing a line of VBA code
• Deleting a MacroCircular references
• Circular error messages
• Locating circular references
• When circular references are necessary 

 

 

 

 

 

 

Book your place today: Ylenia Morselli – [email protected] – 086 0144853

For training dates and details please see our Course Schedule

Login

Lost your password?