Foundation Level Course Outline
Using Excel 2010
• The Excel Cell Referencing System
• Entering Numbers and Text
• Summing a Column of Numbers
• Entering a Date
• Worksheets and Workbooks
• Saving a Workbook
• Creating a New Workbook
• Opening a Workbook
• Switching between Workbooks
• Help
Selection Techniques
• Selecting a Cell, cell range, row & column.
Manipulating Rows and Columns
• Inserting Rows & Columns
• Deleting Rows& Columns
• Column Widths
• Row Heights
Manipulating Cells and Cell Content
• Copying a Cell or Range Contents
• Deleting Cell Contents
• Moving the Contents of a Cell or Range.
• Editing Cell Content
• Undo and Redo
• AutoFill
• Sorting
• Searching and Replacing
Worksheets
• Renaming a Worksheet
• Inserting a New Worksheet
• Deleting a Worksheet
• Copying and moving a Worksheet
Font Formatting
• Font Type
• Font Size
• Bold, Italic, Underline
• Cell Border Formatting
• Background Colour
• Font Colour
Alignment Formatting
• Aligning cell contents
• Centring a Title Over a Cell Range
• Cell Orientation
• Text Wrapping
• Format Painter
Number Formatting
• Decimal Point Display
• Comma Style Formatting
• Currency Symbol
• Date Styles
• Percentages
Freezing Row and Column Titles
• Freezing Row and Column Titles
Formulas
• Creating Formulas
• Copying Formulas
• Operators
• Using Operators In Formulas
• Formula Error Messages
• Relative Cell Referencing
• Absolute Cell Referencing
Functions
• Sum Function
• Average Function
• Max Function
• Min Function
• Count Function
• The Counta Function
• The Countblank Function
• Using the If Function
Charts
• Inserting different chart types.
• Resizing and deleting a Chart
• Chart Title or Labels
• Chart Background Colour
• Column, Bar, Line or Pie Slice Colours
• Copying and Moving Charts
Customising Excel
• Modifying Excel Options
• Minimising the Ribbon
• Autocorrect Options
Intermediate Level
Course Outline
Setup & Printing Issues
• Worksheet Margins
• Worksheet Orientation
• Worksheet Page Size
• Headers and Footers
• Header and Footer Fields
• Scaling Your Worksheet to Fit a Page(s)
• Printing Titles On Every Page
• Printing the Row and Column Headings
• Spell Checking
• Previewing a Worksheet
• Viewing Workbooks Side By Side
• Printing Options
Functions and Formulas
• Getting Help With Functions.
• Nested Functions.
• Consolidating Data using a 3-D Reference Sum Function.
• Mixed References within Formulas.
Time & Date Functions
• TODAY.
• NOW.
• DAY.
• MONTH.
• YEAR.
Mathematical Functions
• ROUND.
• ROUNDDOWN.
• ROUNDUP.
Logical Functions
• IF.
• AND.
• OR.
Mathematical Functions
• SUMIF.
Statistical Functions
• COUNT.
• COUNTA.
• COUNTIF.
• COUNTBLANK.
• RANK.
• TEXT FUNCTIONS
• LEFT.
• RIGHT.
• MID.
• TRIM.
• CONCATENATE.
Financial Functions
• FV.
• PV.
• NPV.
• RATE.
• PMT.
Lookup Functions
• VLOOKUP.
• HLOOKUP.
Database Functions
• DSUM.
• DMIN.
• DMAX.
• DCOUNT.
• DAVERAGE
Named Ranges
• Naming Cell Ranges.
• Removing a Named Range.
• Named Cell Ranges and Functions.
Cell Formatting
• Applying Styles to a Range.
• Conditional Formatting.
• Custom Number Formats.
Manipulating Worksheets
• Copying or Moving Worksheets
• Splitting a Window.
• Hiding Rows, Columns & Worksheets.
Templates
• Creating Excel Templates.
• Editing Excel Templates
Paste Special Options
• Using Paste Special to Add, Subtract, Multiply & Divide.
• Using Paste Special ‘Values’.
• Using Paste Special Transpose Option.
Advanced Level Course
Outline
Pivot Tables
• Using a Pivot Table.
• Filtering and Sorting Data
• Grouping Data
Input Tables
• One-Input Data Tables.
• Two-Input Data Tables.
Charts
• Combined Line and Column Chart.
• Adding a Secondary Axis.
• Changing the Chart Type For a Data Series.
• Adding a Data Series to a Chart.
• Removing a Data Series from a Chart.
• Modifying the Chart Title, Legend & Data Labels.
• Chart Axis Scales.
• Inserting Images Into Chart Columns.
• Formatting the Chart Area
Hyperlinks
• Inserting a Hyperlink.
• Editing a Hyperlink
• Removing a Hyperlink
Linking & Embedding
• Linking Data within a Worksheet.
• Linking Cells between Worksheets within a Workbook.
• Linking Data between Workbooks.
• Linking Data from Excel to a Word Document.
• Linking an Excel Chart to a Word Document.
• Updating, Locking and Breaking Links.
Importing Text Files
• Importing a Delimited Text File.
Sorting and Filtering Data
• Sorting Data by Multiple Columns
• Custom Sorts
• Customized List
• AutoFilter.
• Top 10 AutoFilter.
• Advanced Filter Criteria.
• Sub-Totalling.
• Removing Subtotals.
• Expanding and Collapsing Outline Detail.
Tracking and Reviewing Changes
• Enabling or Disabling 'Track Changes'.
• Sharing, Comparing and Merging Worksheets.
Scenarios
• Scenario Manager.
• Scenario Summary Reports.
Validating
• Whole Number.
• Decimal Number.
• List.
• Date.
• Time.
• Text Length.
• Validation Input Message and Error Alert.
• Removing Data Validation.
Auditing
• Tracing Precedent Cells.
• Tracing Dependent Cells.
• Identifying Cells With Missing Dependents.
• Showing All Formulas In a Worksheet.
• Comments..
Macros
• Using Macros
• Assigning a Macro to a Button on the Quick Access Toolbar.
• Deleting Macros.
Passwords & Security Issues
• Password Protection.
• Password Protecting Cells and Worksheets.
• Hiding Formulas.
• Un-Hiding Formulas.