Course Outline:
 

Course Contents (Beginner):

Excel Basics

  • An overview of the screen, navigation and basic spreadsheet concepts
  • Understand Excel terminology workbooks, worksheets, rows, columns, cells
  • Various selection techniques
  • Understanding the various mouse cursors
  • Working with Ribbon Tabs, Groups and Dialog Boxes
  • Standard and Contextual ribbons
  • Use the task pane
  • Understand and use the File Tab
  • Saving, File types, File compatibility (earlier versions)
  • Useful keyboard commands
  • Quick Access toolbar & Mini Toolbar
  • Get Help!

Entering Data

  • Various ways to Enter, Edit and Delete information (Text, Numbers, Dates)
  • Use Auto List feature
  • Date and Time are storage in Excel
  • Enter Dates and Times
  • Date/time formatting
  • Methods to Move and Copy data
  • Use the Fill Handle to copy data and create simple lists
  • Create series of information
  • Insert, Delete and Hiding Rows & Columns
  • Insert, Delete and Move Cells
  • Insert, Delete, Move and Copy Sheets
  • Various navigation techniques

Creating & Editing Formulae & Functions

  • Define Formulae
  • Create & Edit Formulae
  • BoDMAS : Mathematical Order
  • Copy & Move Formulae
  • Use Basic Functions - Sum, Average, Max, Min, Count, Counta
  • Use Autosum tool
  • Introduction to the Function Library
  • Reference a cell in other sheets
  • Understand Relative Referencing
  • Apply Absolute (Fixed) Referencing

Formatting and Proofing

  • Formatting of Cells with Number formats, Font formats, Alignment, Borders, Fill colours and Patterns
  • Quick formats and themes
  • Sheet tab formatting and Renaming
  • Copy and Clear Formats
  • Merging cells
  • Spell Check, Find & Replace and AutoCorrect

Printing and Page Setup

  • Page Orientation
  • Add Headers and Footers
  • Print a selection
  • Set a print area
  • Clear a print area
  • Shrink to Fit
  • Adjust page breaks within Page Break Preview
  • Repeat columns and rows
  • Print Gridlines
  • Save print settings

Multiple File & Large Workbook Tools

  • Split screen tool
  • Freeze panes
  • Arrange workbook windows
  • Zooming tools
  • GoTo tool
  • Name box navigation

Course Contents (Intermediate):

Recap

  • Absolute references
  • Basic function creation methods
  • Formulae
  • Data entry and formatting
  • Date, Text, Number principles

Range Names

  • Concept and Purpose
  • Naming individual or range cells
  • Deleting and amending named ranges
  • Using named cells/ranges in formulae
  • Create Named Ranges and Cells
  • Manage Names (Setting Scope, Deleting, Renaming)
  • Navigate Named Ranges
  • Use names to Calculate
  • Use ranges in Functions
  • Paste Names
  • Filter Names

Formula Auditing Tools

  • Revealing Formulae
  • Tracing Precedents/Dependents
  • Goto Special
  • 3D Calculations
  • Linking sheets in the same file
  • Linking different Excel files
  • Using Edit, Links
  • Viewing different files at once
  • Saving a workspace
  • Viewing different sheets at once
  • Window Split
  • Data consolidation

Conditional Functions & Formats and Date Calculations

  • Work with Styles
  • Use If Statements, Nested If
  • Use And, Or, Not
  • Nesting If, And, Or, Not
  • Use the Sumif , Countif, Averageif
  • Use the Sumifs, Countifs, Averageifs
  • Use the Iserror, Iferror functions
  • Apply Conditional Formatting
  • Date Calculations
  • Calculate working days
  • Use the DAY(),MONTH(),YEAR() functions
  • Create and use Time calculations

Formulae Auditing

  • Use the Formula Auditing Tools
  • Reveal Formulae (in a cell)
  • Trace Precedents/Dependents
  • Setting a Watch
  • Goto Special

Worksheet Management & Linking

  • Link sheets in the same workbook
  • Link cells in different Excel files
  • Manage and edit Links
  • View different files at same time
  • Saving a workspace
  • View different sheets at same time
  • Data consolidation (within same file)
  • Data consolidation (across workbooks)

Data List Management

  • Create an Excel data list
  • Use the Excel List Tools
  • Use Autofilter
  • Sort the Data
  • Use the Advanced Filter
  • Add Subtotals to a list
  • Apply Data Validation to keep data clean
  • Group and Outline your data
  • Use the Data Form

Excel Charts / Graphs

  • Create a Chart with keyboard
  • Use the Chart Wizard
  • Edit and Format a chart
  • Change the Chart source Data
  • Apply Trendlines
  • Use Sparklines
  • Change Charts from objects to sheets
  • Add labels and axes to chart
  • Show specific Data Points
  • Save custom chart types
  • Change the default chart type
  • Using the Chart Wizard
  • Editing and Formatting charts
  • Saving custom chart types
  • Setting a default chart type

Course Contents (Advanced):

Lookup & Information Functions

  • Build the Vertical Lookup (Vlookup) function
  • Build the Horizontal Lookup (Hlookup) function
  • Build the Match and Index functions
  • Build the IsText, IsValue, IsDate , IsNull , IsErr, IsNa functions
  • Build and use Database Functions

Pivot Tables

  • Create A PivotTable
  • Change the PivotTable layout
  • Manipulate PivotTable fields
  • Format the PivotTable and apply Styles
  • Use Banding (Formatting)
  • Group & Ungroup items
  • Filter data in the PivotTable
  • Insert calculated fields
  • Change calculation options
  • Change PivotTable Options
  • Display and hide data in fields
  • Lay out reports on worksheet
  • Create a PivotChart from report
  • Manipulate PivotChart fields
  • Use Slicers

What If Analysis

  • Create and manipulate Scenarios
  • Create and change Custom Views
  • Build scenario Reports
  • Use the Goal Seek tool
  • Use Solver & Advanced Solver Features
  • Build Data Tables

Protecting & Sharing Files

  • Share a file
  • Track changes
  • Accept or reject changes
  • View Change history
  • Apply Data validation rules
  • Insert, edit and delete comments

Introduction to Macros

  • Review the purpose of Macros
  • Record a macro
  • Save a macro
  • Edit a Macro
  • Absolute and relative records
  • Running macros:
  • Apply macro to Custom buttons on the Ribbon or Quick Access toolbar, keyboard shortcuts

Customising Excel

  • Customise the Quick Access Toolbar and Ribbons
  • Set Workbook options
  • Change workbook defaults

Templates

  • View Template Types
  • Use Normal Template
  • Open Sample Templates
  • Create Custom Templates
  • Use Custom and online Templates
  • Open And Edit Templates
  • Setting Template Properties

Objects In Excel

  • Insert, Format And Delete Objects
  • Insert A Drawing Object
  • Use the design ribbon
  • Use SmartArt
  • Apply Formatting to SmartArt
  • Use QuickStyles
  • Explore 2D And 3D Formatting
  • Insert and use WordArt
  • Insert and Format Shapes
  • More on Themes and styles
  • Manually Format an object
  • Customise or create A Theme
Home    l    Company    l    Registration    l    Contact   l    Terms   l    Privacy Policy
Copyright © 2011, Photo Art Studio. All Rights Reserved
Course Information:
Duration: 14 Hours Instruction: In-Class / Online  
Platform: Windows & Macintosh Concepts: Beginner / Intermediate / Advanced  
Fee: $450 (+HST) Prerequisites: None.