Course overview

Duration: 1 day (6.5 hours)

Our Excel Mastering Formulas and Functions course looks at how to confidently use formulas and functions within Excel.

It looks at how to correctly construct formulas and explains how to build common formulas such as working with percentages.  It investigates a range of built in Excel functions and shows you how to use the formula auditing tools to help when things go wrong. 

This course is aimed at existing users of Excel who want to further their skills. Participants should be able to confidently create and amend worksheets.

Objectives 

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

• Construct Formulas
• Use a range of common Functions
• Work with absolute and relative references in formulas
• Create percentage calculations
• Use named ranges in formulas
• Use the XLOOKUP command
• Create formulas with date and use date functions
• Use formulas in conditional formatting
• Use a range of Count functions
• Use the formula auditing tools

Content

Formulas vs Functions

• Constructing formulas
• Using functions
• Common Excel functions
• Using the function wizard vs the formula bar
• Formulas in tables

Absolute vs relative references in formulas

• Using absolute references in formulas
• Using partial absolute referencing

Range Naming

• Naming ranges
• Using range names in formulas
• Single and multi-cell ranges

Working with percentages

• Creating formulas with percentages
• Percentage breakdowns
• Percentage increases
• Formatting as a percentage

Using Lookups

• Using XLOOKUP to insert information from other spreadsheets

Calculating with dates

• Calculating with dates
• Using date functions

Using formulas in conditional formatting

• Using conditional formulas
• Using functions in conditional formatting

Count functions

• COUNT
• COUNTA
• COUNTBLANK

Formula auditing

• Using the auditing facility
• Tracing how formulas are made up
• Tracing precedents and dependants
• Evaluating formulas
• Error checking
• Showing formulas on a sheet

Public Training Programme

Explore our current selection of public courses, available as convenient and cost-effective virtual sessions.

Engage with expert instructors and a diverse peer group in an interactive, online learning environment.

Ideal for those valuing flexibility and affordability, our public courses are ideal where you only have small numbers requiring training and offer training from the comfort of your home or office.

View our full catalogue of Leadership, Personal Development, Systems, Office Applications and Project Management courses

Contact us

Do you have questions, suggestions, or feedback? We value your input and are here to assist you. Please don't hesitate to get in touch with us via email or phone:

• Email: [email protected]
• Phone: +44 (0)20 8152 6551

Our dedicated team is ready to respond to your enquiries and provide the support you need. Feel free to reach out, and we'll do our best to assist you.

Thank you for visiting our website, and we look forward to hearing from you!