Excel 365: Working with Formulas and Functions

In this E-Learning course, learners will be shown a range of functions for performing calculations in Excel, such as simple mathematical and statistical functions, as well as more complex reference and array functions. These functions can be used in formulas. The names of the functions usually indicate what type of result they produce, for example SUM or WEEKNUM.

The course will begin by introduction to the structure of formulas and functions, followed by an introduction to frequently used functions in Excel.

Course Topics

Basics of using formulas and functions

Performing simple calculations

Getting to know how to structure and enter formulas
Subtracting Values
Multiplying Values
Dividing values

Calculating with and without cell references

Entering a function
Calculation with relative cell references
Copying functions with relative references
Setting absolute cell references
Using names in formulas
Using the Function Wizard

Finding and correcting errors

Checking errors in calculations
Finding circular references
Switching off error checking

Getting to know the most important functions

Mathematical functions

Calculating a sum total
Rounding values up and down with ROUND
Calculating subtotals
Converting a Roman numeral into an Arabic numeral

Date functions

Inserting the current date with TODAY
Extracting DAY, MONTH or YEAR
Combining day, month and year with DATE
Calculating the number of days between dates with DAYS
Determining the WEEKNUM

Logical functions

Validating a condition using IF
Combining multiple conditions with OR
Combining multiple conditions with AND
Determining precisely one true condition with XOR

Statistical functions

Counting cells with numbers with COUNT
Calculating an average
Adding specific values with SUMIF
Counting specific cells with COUNTIF

Text functions

Removing spaces with TRIM
Joining text with TEXTJOIN

Lookup and reference functions

Searching for values in a table with VLOOKUP
Searching for values in a table with HLOOKUP
Displaying a message if a value is missing with IFNA
Searching for values flexibly with XLOOKUP

Dynamic array functions

Dynamically removing duplicates with UNIQUE
Dynamic filtering with FILTER