This course is aimed at those who are using Excel to maintain lists of data and carry out some form of analysis on them. It is designed for people who need to know how to fully understand and manipulate lists to maximum effect. This will be done by covering data manipulation and filters, the more advanced mathematical and statistical analysis of data and ultimately the use of Pivot Tables, Macros and Charts within Excel.

• For delegates who use Microsoft Excel to store and analyse data
• To create and maintain a list of data that makes lists work for you
• Use Filters to quickly select sets of data within lists
• Manipulate data into useful elements e.g. separate data from a single cell
• See the magic of Pivot Tables and the speed of which they analyse and present statistical information.

Delegates are required to have a good working knowledge of Excel with the ability to easily create professional looking workbooks from scratch.

List management
• Data forms
• AutoFilter
• Sorting data
• Creating sub-totals

Pivot Tables
• Creating a Pivot Table with the Wizard
• Adding and removing items from rows and columns
• Adding filters with pages
• Summarising numbers in the data area
• Pivot the row and column headings
• Refreshing a Pivot Table
• Reducing disk space
• Selecting different functions
• Format the data and autoformat
• Show data options
• Display grand totals
• Change the sort order of the ‘Rows’, ‘Columns’ or data
• Grouping
• Multiple row and column items
• Drill down into the data behind a number
• Use hide and show detail

Macros (recorded)
• Record macros
• Personal macro workbooks
• Run macros
• Deleting macros
• Assign a macro to a command button or a menu item
• View VBA code
• Edit a recorded macro

Creating charts
• Chart wizard
• Chart types
• Customising a chart
• Formatting

Advanced charts
• Error bars and trend lines
• Embedding
• Dual Y-axis

Beyond basic functions (optional and time dependent)
• Basic If functions
• Example text function (Left, Right, Mid and Concatenate – &)
• HLookup and VLookup

This Formulae and Functions Microsoft Excel course will provide a thorough grounding in the use of calculations in Excel. To do this effectively, you will learn the fundamentals of calculations: creating simple formulae and the use of Excel’s in-built function formulae. This will lead onto the use of statistical and date functions and ultimately the use of logical “IF” statements. Calculations will be used in single worksheets and across multiple worksheets and workbooks. The delegate will also gain a greater understanding of how spreadsheets can and are being used in in a variety of industries.

Delegates will:
• Focus on the calculation (formulae & function) capabilities of Excel
• Speed up your productivity and become familiar with the Excel function library
• Using named ranges to make it easier to refer to cell references
• Follow calculations others have created and subsequently track and resolve errors
• Introducing conditional statements in formulae.

Foundation formulas (recap)
• BIDMAS (order of operations)
• Enter formulas in a cell and use the formula bar
• Revise formulas
• Use AutoSum
• Use basic functions (AVERAGE, SUM, COUNT, MIN, and MAX)
• Use paste to insert a function

Autofill
• Copying cells
• Create a custom list
• Copying formats

Number formats
• Apply predefined number formats incl. decimal places
• Create custom number formats
• Date formats
• Relative, absolute and partial cell references

Linking formulae and worksheets
• Inserting, moving, naming, hiding, deleting worksheets
• Link worksheets and workbooks
• Using 3-D references
• Edit links

Working with windows
• Working with and arranging multiple windows
• Freezing panes
• Splitting windows

Working with named ranges
• What are named ranges
• Create, define and delete named ranges
• Use a named range in a formula
• Paste and apply named ranges

Auditing a worksheet
• Trace precedents (find cells referred to in a specific formula)
• Trace dependents (find formulas that refer to a specific cell)

Creating a template
• Protecting cells
• Protecting sheets and workbooks
• Saving a template

Beyond basic functions – date
• TODAY(), NOW()

Beyond basic functions – logical
• Use logical functions (IF)
• Using nested IF statements
• Applying AND / OR

Annotating your work with comments
• Create, edit, and remove a comment
• Display and print comments

Printing
• Print titles
• Page breaks
• Headers and footers

This Microsoft Excel VBA course is aimed at those who need to develop integrated spreadsheet solutions using Microsoft Excel. Typically, these may be senior I.T. staff or “super users” who need to be able to produce in-house “turnkey” systems.

Prior attendance on either the Advanced Excel or Formula and Functions programmes, or a thorough knowledge of Microsoft Excel, is recommended. The course is 100% hands-on and, although no previous programming experience is required, programming in the VBA environment forms much of the course so an understanding of basic programming techniques would also be desirable.

Excel Macro’s
• Recording visual basic “macros”
• The visual basic environment
• Understanding the visual basic language
• Assigning code to buttons, menu’s and toolbars

Writing visual basic code
• Procedures sub and function statements
• Private procedures
• Named arguments

Variables
• What is a variable?
• Dim, public, private, static and constant statements
• Variable scope
• Naming conflicts

User defined functions
• Creating a function
• Function arguments
• Return values

Visual basic objects
• Properties
• Methods
• Object and common collections
• Object containers
• The object browser
• Performing multiple actions on an object

Control structures
• If…then…else
• Select case
• For…next
• Do…loop

User interface objects
• Dialog boxes
• Placing controls on a worksheet
• The forms toolbar

The Excel menu system under VBA control
• Testing and debugging applications
• Starting, halting and tracing execution
• Resolving compile and run-time errors
• Working with breakpoints
• Using the debug window

Error handling and trapping
• On error statement
• Excel built in error values