Workbook basics

• Creating workbooks
• Inserting worksheets
• Renaming sheets
• Moving sheets
• Copying sheets
• Colouring sheet tabs
• Moving sheets to another workbook

0.5 hour

Sheet basics

• Selecting Cells
• Using shortcuts to select
• Selecting non adjacent cells
• Selecting columns and rows
• Inserting and deleting columns, rows, and cells
• Selecting a record set
• Selecting the entire sheet
• Resizing columns and rows

1 hour

Copy and paste

• Copy values
• Paste values
• Cut values
• Paste Special
• Shortcut keys to copy, cut and paste
• Using mouse to copy, cut and paste

0.5 hour

Data Entry cheats

• Filling data
• Copying calculations
• Working with dates
• Entering current date and time
• Custom lists

1 hour

Excel Help

• Using the Office Assistant
• Getting Help Without the Office Assistant
• Tailoring help
• Searching

0.5 hours

Preparing for print

• Page setup
• Repeat heading on every page
• Set print area
• Change margins
• Background (2007)
• Headers and footers
• Hiding gridlines

1 hour

Formatting Module 1

• Formatting cells
• List formatting
• Automatic formatting

1 hour

Formatting Module 2

Prerequisites: Formatting Module 1

• Conditional formatting
• Custom formatting
• Styles

1.5 hours

Protecting Worksheets

• Worksheet protection
• Workbook protection
• Locking cells
• Password protecting a file

1 hour

Auditing

• Auditing toolbar
• Watching values

0.5 hours

Naming Ranges

• Naming cells
• Multiple page names
• Using names
• Changing name reference

• Deleting names

1 hour

Projection Tools

• Goal Seek
• Scenario
• Solver

1 hour

Calculation fundamentals

• BODMAS
• Calculations
• Basic Functions

2 hours

Linking Calculations

• 3D formulae
• Linking cells
• Changing the document link to
• Removing links
• Options when sending a file with links

0.5 hours

Date and Time Functions

• Entering current Date (today)
• Entering current time (time)
• Entering current date and time (now)
• Calculating dates (date)

0.5 hour

Text functions

• Changing the case of text
• Concatenation
• Extracting value from cell (left, right, mid)
• Trim
• Replace

1.5 hour

Logical Function Module 1

Prerequisites: Calculation fundamentals

• If Statement
• Nested If

1 hours

Logical Function Module 2

Prerequisites: Logical Function Module 1

• And function
• Or function
• IsError/IsErr
• IsNA

1.5 hours

Lookup functions

Prerequisites: Naming ranges and calculations fundamentals

• Vlookup
• Hlookup
• Lookup
• Creating the lookup list

2 hours

Lists Module 1

• What is a list
• Sorting
• Filtering
• Data entry form
• List feature
• Formatting lists
• Freeze and Split

1 hour

Lists Module 2

Prerequisites: Lists Module 1

• Advanced filtering
• Subtotals
• Grouping and ungrouping
• Data validation
• Circling invalid data

1.5 hours

Lists Module 3 – Calculations

Prerequisites: Calculation fundamentals and Lists module 1

• Dfunctions
• Countif
• Sumif

1 hour

Pivot Tables Module 1

• Creating
• Adding fields
• Moving fields
• Subtotals
• Filtering
• Grouping
• Pivot Charts
• Updating

1.5 hours

Pivot Tables Module 2

• Creating calculated columns
• Show pages
• Pivot table formatting
• Table options
• Creating a running total

1 hour

Importing

• Importing CSV
• Importing from ODBC database
• Importing from word
• Text to columns
• MS Query

1.5 hours

External data

• Database query
• Web query
• Updating data

1 hour

Pivot Tables Module 1

• Creating
• Adding fields
• Moving fields
• Subtotals
• Filtering
• Pivot Charts
• Updating

1.5 hours

Pivot Tables Module 2

• Creating calculated columns
• Show pages
• Pivot table formatting

Charts Module 1

• Creating a chart
• Changing colour
• Chart options
• Adding titles

1 hour

Charts Module 2

• Setting a default chart type
• Saving Chart settings
• Adding Trend lines
• Error bars
• Adding a secondary axis
• Multiple chart types in 1 chart

1 hour