Microsoft Excel: Formulae & Functions

Scheduled Dates Locations filtered. Show all UK course locations.

Course Overview

This course will provide a thorough grounding in the use of calculations in Excel. In order to do this effectively, delegates will learn the fundamentals of calculations: creating simple formulae & 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 that deal with multiple answers within formulae. Calculations will be used in single worksheets, across multiple worksheets (3-d) and ultimately across multiple workbooks. The delegate will also gain a greater understanding of how spreadsheets can and are being used in businesses today.

• 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.

Audience

• New users to Excel or self-taught users who would like to formally explore the fundamentals of creating workbooks.
• Anyone who has to deal with financial or statistical data on a regular basis in their job role.
• Anyone who's primary role is to deal with numbers and work with calculations.

The course will also be of benefit to existing users who have had no formal training and wish to develop, or to recap, a more complete understanding of the formulae functionality of Excel.

Skills Gained

By the end of the course the delegate will be able to:

• Use Excel to create their own spreadsheets from scratch
• Create mathematical calculations using simple formulae
• Understand the purpose of functions within calculations
• Use names to assist in referencing cells
• Add reminders to cells through the use of comments

Prerequisites

• Delegates must be current Excel users and be able to navigate around the workbook, enter data and save workbooks
• Delegates need to be familiar with working in a Microsoft Windows environment i.e. mouse skills, working with multiple windows and screen elements such as scroll bars, menu bars and toolbars.
• Have attended the Excel fundamentals course or have equivalent knowledge.

Course Outline

• BODMAS
• Enter formulas in a cell and use the formula bar
• Revise formulas
• Use AutoSum
• Use basic functions (AVERAGE, SUM,
• COUNT, MIN, and MAX
• Use the Paste Function to insert a function
• Autofill
• Copying cells
• Number Formats
• Apply predefined number formats incl.
• Decimal Places
• Create custom number formats
• Date Formats
• Cell Referencing
• Relative, Absolute and partial Absolute cell references
• Worksheet management
• Inserting, moving, naming, hiding,deleting worksheets
• Linking Workbooks
• Link workbooks
• Edit Links
• Working with windows
• Working with & arranging multiplewindows
• Freezing panes
• Splitting windows
• Working with Named Ranges
• What are named ranges
• Create, Define & Delete Named Ranges
• Use a named range in a formula
• Paste & 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 & workbooks
• Saving a template
• Beyond Basic Functions - Date
• TODAY(), NOW(), WEEKNUM(),
• NETWORKDAYS
• Beyond Basic Functions - Logical
• Use logical functions (IF)
• Working with error messages
• Annotating your work with Comments
• Create, edit, and remove a comment
• Printing
• Print Titles
• Page Breaks
• Headers & Footers

Microsoft Excel: Formulae & Functions / MEX-FF