Excel Formulae & Functions

Category:

Introduction

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.

Product Documents

Or call us now on 01284 763040

Description

Course Content

Foundation Formulas (recap)
♦ 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
♦ Create a Custom List
♦ Copying Formats

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 Formulae
♦ Link worksheets
♦ Using 3-D references

Linking Workbooks
♦ Link workbooks
♦ Edit Links

Working with windows
♦ Working with & arranging multiple windows
♦ 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()

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 & Footers

Course Duration: One Day