Spreadsheet Principles

• What is a spreadsheet?

Screen Layout

• Navigation with Excel, Menus, Toolbars or Ribbons(V2007), Spreadsheets etc

Entering Data

• Entering Text, Numbers, Dates
• Entering Auto Lists
• Deleting
• Editing
• Selecting data
• Moving and Copying
• Viewing the Clipboard
• Inserting new columns and new rows

• Using the Scrollbars and Mouse
• Using the Keyboard
• Go To

Creating Formulae

• Concept
• Creating Formulae
• Editing
• Bodmas : Mathematical Order
• Copying Formulae

• Concept
• Using the AutoSum Icon
• Common Functions Sum, Min, Max, Average, Count

Cell References

• Concept
• Relative references
• Absolute references

Formatting

• Formatting Cells
• Number formats
• Changing font type and size
• Text alignment and orientation
• Wrapping text
• Applying borders and shading
• Autoformat
• Conditional formatting
• Format Painter
• Clearing Formats
• Formatting Columns and Rows

• Page Orientation
• Adding Headers and Footers
• Printing a selection
• Setting a print area
• Shrink to Fit
• Adjusting page breaks within Page Break Preview
• Repeating columns and rows
• Printing Gridlines
• Setting a print area

Naming Cell Ranges

• Concept and Purpose
• Naming individual cells
• Naming ranges of cells
• Deleting and amending named ranges
• Using named cells/ranges in formulae
• Named ranges as a navigation aid

• Benefits and purpose
• If Statements
• Nested If
• And, Or, Not
  Combining If, And, Or, Not
  Sumif
• Countif
• Conditional Formatting

Working with Dates and Times

• Entering Dates and Times
• Different date/time formats
• How dates are stored
• Calculating working days
• Using the DAY(),MONTH(),YEAR() functions
• Time calculations

Worksheet Management and Linking

• Inserting and deleting sheets
• Changing the default number of workbook sheets
• 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

• Sorting Data
• Adding Subtotals
• Autofilter
• Freeze Panes
• Group and Outline
• Data Form

Charting your data

• Using the Chart Wizard
• Editing and Formatting charts
• Saving custom chart types
• Setting a default chart type

Protecting and Sharing Excel Files

• Sharing a file
• Tracking changes
• Accepting or rejecting changes
• Applying Data validation rules
• Inserting comments
• Protecting cells, sheets, files
• Password protecting a file

Checking for Errors

• Spell-check
• Find and replace
• Autocorrect
• Formula Auditing Tools
• Revealing Formulae
• Tracing Precedents/Dependents
• Goto Special

Lookup Functions

• Vertical Lookup (Vlookup)
• Horizontal Lookup (Hlookup)
• Match and Index

Informational Functions

• IsText
• IsValue
• IsDate
• IsNull
• IsErr, Is Error, IsNa

Summarising Data with Pivot Tables

• Using the Pivot Table Wizard
• Changing the pivot table layout
• Formatting
• Grouping items
• Inserting calculated fields
• Pivot Table Options
• Display and hide data in fields
• Lay out reports directly on worksheet
• Pivot Chart

• Scenarios
• Custom Views
• Reports
• Goal Seek
• Solver

Protecting and Sharing Excel Files

• Sharing a file
• Tracking changes
• Accepting or rejecting changes
• Applying Data validation rules
• Inserting comments

Customisation Options

• Showing / Hiding Toolbars
• Customizing Toolbars
• Customizing Menus
• Adaptive Menus
• Default Options
• Addins

Introduction to Macros

• Purpose of Macros
• Recording macros
• Where to save macros
• Absolute and relative record
• Running macros:
• Custom buttons, menu items, keyboard shortcuts

What is a Macro?

Concept and discussion

The Visual Basic Interface

• The Project Window
• Code Window
• Properties Window
• Navigating between Excel and the VB Editor

Recorded Macros

• Record New Macro
• Assigning a keyboard short cut
• Saving with the file

Methods of Executing

• Menu
• Visual Basic Toolbar
• Keyboard Shortcut
• Custom Toolbar Button
• Custom Menu Item
• Button
• Alt [F8]
• Forms Toolbar

Editing the Code

• Using the Visual Basic Editor
• Structure of a sub routine
• Describe the colour scheme of Key Words
• Navigation in a Module
• Editing in a module
• Use of Function Keys
• Reassign keyboard shortcut
• Run subroutines together

Checking For Errors

• Debugging
• Step Mode
• Immediate Window
• Object Browser
• Toggle Breakpoint
• Help system

Types of Recording

• Relative
• Absolute

Portability

• Personal Macro Workbook
• This Work Book
• New Workbook
• Addins

Creating New Functions

• Uses
• Function Keyword
• Use of Arguments
• Variables
• Constants

• IF
• Nested IF
• AND OR NOT
• SELECT CASE
• Inbuilt Functions
• Distribution
• Watch Pane
• Step through Functions using the immediate Window

• Theory of Object
• Properties and Methods
• Active Objects
• Object Model Hierarchy
• Inheritance

Spreadsheet Form Controls

• Combo box
• Spinner control
• Scroll bar
• Option button
• Check Box control

Interactive Macros

• Msgbox
• Conditional Message Box
• Inputbox

Creating Interactive Forms

• Form Controls
• User forms
• Using the Properties Window
• Combo box
• Radio Buttons
• Group box
• Check box
• Built in Dialog Boxes
• Creating Custom Menus
• Creating Custom Toolbars

• What are Event Procedures?
• On Open
• Before Close
• On Activate
• On Deactivate
• On Key
• On Time

• Protecting/locking code for view/edit
• Digital Signatures

Customizing the VB Editor

• Dockable Windows
• Format Editor