Back to all courses
The Excel Functions Guide
Intermediate

Deepen and develop your Excel Functions skills

Learn how to efficiently manage databases, clear text in cells, and retrieve values from other tables. The course also walks you through how to combine functions, which is required to perform more advanced tasks in Excel. With this knowledge, you will be able to automate time-consuming parts of your work.

  • Go through the course at your own pace
  • Practical exercises and quizzes
  • Personal diploma
  • Save lessons as favorites

Learnesy - Online courses in Excel and Dataanalys

Choose what suits you best.

50,000+ people have increased their skills with our online courses.

Contact us today.

For teams

Offer
  • Personal Account Manager
  • Personal account for all users
  • Choose the courses that suits your needs
  • Multi-user discounts
  • Optional Kickoff at startup
  • Statistics via Data Insight
  • Technical support
  • Unique login domain

One course

2 495 SEK
  • Access to all material 24/7 for 12 months
  • Complete the course when it suits you
  • Repeat freely during access
  • Course maintained and updated continuously
  • Personal and digitally shareable diploma by course

Course kit

3 995 SEK
  • Choose the 3 courses that best suit your needs
  • Contact us for help with the selection of courses
  • Personal and digitally shareable diploma by course
  • Access to all material 24/7 for 12 months
  • Courses that are maintained and updated continuously
  • Choose freely from all Learnesy’s courses
  • Did you choose the wrong course? No problem, we can change it afterwards

All courses

5 995 SEK
  • 24/7 access to all of Learnesys courses for 12 months
  • Take full courses or see only the lessons you need
  • Save your favorite lessons with the favorite feature
  • Get ongoing updates in the courses
  • Personal and digitally shareable diploma by course

We at Learnesy are passionate about competence development.

If you have questions about our courses, do not hesitate to contact us!

What you will learn

A library of 100+ functions

After completing the entire course, it can be used as a library of functions. Save your favorite functions and return to them for a refresh whenever you need it.

The most useful functions

Functions that show you how to summarize, count, how to manage absolute and relative references, financial functions, statistical functions, and more.

VLOOKUP and reference functions

VLOOKUP, perhaps Excel’s most famous function, is not always easily understood. Learn this useful function along with 10 other practical functions used to search for and collect values.

Handle dates and text

Something that even experienced Excel users know is complicated are dates and texts in tables or databases. Use functions such as WEEKDAY, WORKINGDAYS, MID, and many other smart tips and tricks to get your data in order and manage it correctly.

See the full course contents
  1. Course Content

    Expand All
    Chapter Content
    0% Complete 0/11 Steps
    COUNT – Counts the number of cells containing numbers
    Count the nonblank cells with COUNTA
    Count the number of blank cells with COUNTBLANK
    Count cells based on a criteria with COUNTIF
    Count cells based on multiple criteria with COUNTIFS
    Count logical values
    Count text occurrences
    Use SUM to sum up the values in a range of cells
    Sum cells based on one criteria with SUMIF
    Sum cells based on multiple criteria with SUMIFS
    SUMPRODUCT sums the product of a range of cells
    Chapter Content
    0% Complete 0/3 Steps
    The IF-function
    Combine the IF-function with AND / OR
    Combine multiple IF-functions
    Chapter Content
    0% Complete 0/4 Steps
    Copy exact formulas
    How to mix relative and absolute references
    Sum values from multiple sheets with 3D references
    Insert hyperlinks
    Chapter Content
    0% Complete 0/9 Steps
    Learn the tricks for Date and Time
    Get the number of days between two dates with DATEDIF
    Day of the year
    Days until anniversity
    Last day of the month
    Quarter
    Weekdays
    Number of NETWORKDAYS between two dates
    NETWORKDAY returns a date after a number of weekdays
    Chapter Content
    0% Complete 0/11 Steps
    How to compare text in two different cells
    JOIN and LEN
    Remove unwanted characters
    Capital or small letters with UPPER, LOWER and PROPER
    Extract characters with LEFT, RIGHT and MID
    FIND and SEARCH characters in cells
    SUBSTITUTE and REPLACE characters in cells
    Count the number of words in cells
    Number of instances a text or a number occurs in a cell
    Separate strings of text from a cell
    Use the Text to columns-guide
    Chapter Content
    0% Complete 0/9 Steps
    Search and return a value with VLOOKUP
    Search and return a value with HLOOKUP
    How to use VLOOKUP if you don’t have an exact reference
    Use MATCH, INDEX, CHOOSE to find values in a list
    Use INDEX/MATCH to return a value left of a reference
    Get the cell reference for the cell with max value
    Get a value using coordinates with OFFSET
    Use INDEX/MATCH to lookup values in a two-dimensional range
    Get values with an INDIRECT reference
    Chapter Content
    0% Complete 0/11 Steps
    Get the average of values with AVERAGE/AVERAGEIF
    Use FORECAST to predict the next number in a range
    Find the middle/most common value with MEDIAN, MODE
    Identify the n:th largest/smallest number with LARGE/SMALL
    Use STDEV to calculate standard deviation
    RAND returns a random number between 0-1
    RANDBETWEEN returns a random number between two numbers
    Rank the numbers in a range with RANK, RANK.AVG
    PERCENTILE returns a number corresponding to a percentile
    QUARTILE returns the number corresponding to a quartile
    Neutralise negative numbers in a list
    Chapter Content
    0% Complete 0/4 Steps
    ROUND numbers to integers or decimals
    Use ROUNDUP/ROUNDDOWN to round numbers
    Round to the closest EVEN or ODD number
    Delete decimals with INT/TRUNC
    Chapter Content
    0% Complete 0/6 Steps
    How to find and correct circular references
    How to deal with #NAME?, #VALUE! and ####
    How to deal with #DIV/0! and #REF!
    Trace dependent/Precedents cell references
    Show and Evaluate formula, Error checking
    Correct errors with ISERROR
    Chapter Content
    0% Complete 0/10 Steps
    Introduction to array formulas
    Count the number of errors in a range
    Count unique values in a range
    Count cells with or and and criteria
    Find the most frequent word in a range
    Sum every nth row in a range
    Sum the largest numbers in a range
    Sum a range with error values
    Sum cells with or and and criteria
    Two-column lookup in an array formula
    Chapter Content
    0% Complete 0/6 Steps
    Introduction to depreciation
    SLN (Straight Line)
    SYD (Sum-of-Years’ Digits)
    DB (Fixed Declining Balance Method)
    DDB (Double Declining Balance Method)
    VDB (Variable Declining Balance)
    Chapter Content
    0% Complete 0/13 Steps
    Introduction to Dynamic Array Functions
    SORT
    SORTBY
    UNIQUE
    FILTER
    RANDARRAY
    SEQUENCE
    XMATCH
    BONUS: Show the nth largest or smallest values
    BONUS: Filter every nth row
    BONUS: Use FILTER to remove columns
    BONUS: Randomly sort a list
    BONUS: Get common values

More about the course

  • Approximately 90 lessons divided into 12 chapters.
  • Total video time: 1 hour 40 minutes.
  • Estimated time to take the course including quizzes: 4 hours.
  • Average length per lesson: 1 minute.
  • Personal shareable certificate after completing the course.

The Excel Functions Guide is a course aimed towards those who have either gone through Excel Essentials or have previous knowledge in Excel and want to expand your expertise within the program’s many functions. In addition to the more basic functions, this course also teaches you dynamic array functions and how to handle error messages.