Excel Formulas and Functions

Training

Online

£ 45 VAT inc.

Description

  • Type

    Training

  • Level

    Intermediate

  • Methodology

    Online

  • Class hours

    3h

  • Duration

    1 Week

  • Start date

    Different dates available

Formulas and functions are at the core Microsoft Excel and learning to use them effectively is an essential step in becoming proficient in Excel. This course provides clear and easy-to-follow instructions on how to build formulas for analysis, projection and data cleansing.
• The course starts by discussing all of the different components that can be used to create a formula. You will be given clear demonstrations of the use of relative, mixed and absolute references, the use of dollar signs and how to name cell ranges to add clarity to your formulas.
• Naturally, Excel functions are discussed in depth and many examples of using functions to analyze your data, beginning with every day functions like SUM, AVERAGE, MIN and MAX.
• We then examine the use of text functions to clean up data prior to creating reports; functions like TRIM, UPPER, LOWER, PROPER, LEFT, RIGHT, MID and CONCATENATE.
• Next, we turn our attention to conditional functions like IF, IFERROR, AND and OR. These functions allow you to make the value displayed in a cell dependent on the results of logical tests.
• We then see how conditional functions can be combined with mathematical functions; using COUNTIF and SUMIF to produce calculations which only include those cells which match certain criteria.
• And, naturally, no discussion of Excel functions would be complete without lookup functions. We discuss the use of VLOOKUP and, also, how the INDEX and MATCH functions can be used to lookup up data whose format makes the use of VLOOKUP impossible.
• When you have completed this course, you will feel more confident about creating your own Excel formulas and taking advantage of Excel's wide variety of powerful functions.

Facilities

Location

Start date

Online

Start date

Different dates availableEnrolment now open

About this course


Understand the key elements which are used to create Excel formulas and feel confident about building formulas of their own.


Excel users both inexperienced and experienced who don't feel confident about using Excel formulas.

Excel 2016, 2013, 2010 or 2007 running under Windows. Mac users will also benefit from this course, but there are a few differences between versions.

Certificate of completion

A free trial version of this course is available.


Sign up for the free trail version of this course. If it meets with your expectations, then enroll for the full course.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Subjects

  • Excel
  • Excel for Accounting
  • Office IT
  • Excel Formula
  • MS Excel
  • Microsoft Office
  • VBA (Microsoft Access / Office)
  • Excel Intermediate training
  • Office 365 training
  • Excel training

Teachers and trainers (1)

Microsoft Specialist

Microsoft Specialist

Experienced vba developer and trainer

Course programme

1. Excel Formula Essentials

1. Anatomy of an Excel Formula

2. Understanding Relative References

3. Absolute References

4. Mixed References

5. Operator Precedence

6. Using Named Ranges

2. Text functions

1. Flash Fill revision

2. Using the TRIM Function

3. Changing the Case of Text

4. The LEFT and RIGHT Functions

5. The MID Function

6. The CONCATENATE Function

3. Conditional functions

1. The IF Function

2. The IFERROR Function

3. Nested IF Statements

4. The OR Function

5. The AND Function

4. Conditional Number Crunching Functions

1. The COUNTIF Function

2. The SUMIF Function

5. Lookup Functions

1. VLOOKUP Exact Match

2. VLOOKUP Approximate Match

3. INDEX And MATCH

6. Project - Creating an invoice template

1. Overview of the Final Project

2. Creating Basic Formulas

3. Creating a Drop-down List

4. Using VLOOKUP and Concatenation

5. Using the CHAR function to Insert Line Breaks

6. Using IF to Suppress Blank Address Lines

7. Protecting the Worksheet

8. Saving the Model as a Template

Excel Formulas and Functions

£ 45 VAT inc.