Microsoft Excel in the Business Context (All Versions)

After this video, you will know how to add, subtract, multiply and divide numbers. Also, you will learn how to apply and copy-paste cell formats.

Understanding the difference between absolute and relative references, you will gain a massive increase in your productivity. Leveraging this functionality, you will be able to quickly copy and paste information across the worksheet, whilst keeping rows, columns, or entire cells fixed.

Apply basic operations, formulas, absolute and relative references to solve this exercise. Use the file "References" for this exercise.

Let's first focus on the basics - which text functions actually exist? In the last step of this video, we will then determine the number of people whose last name starts with "A" or ends with "t" out of a dataset containing information about 500 people - for this, we need to build on our existing knowledge of formulas and combine it with the concept of text functions.

Starting with an introduction from scratch, I want to show you how to leverage the IF function to automatically calculate trade discounts.

Only available to Office365 users

IFS is a simplification of the standard if statement with multiple logical tests. However, both approaches lead you to the same solution, it really comes down to individual preference.

Building on our previously acquired knowledge, let's now focus on advanced IF functions. These serve as the basis for creating complex models in the future based on different criteria.

SUMIFS is basically like SUMIF... but with up to 127 range/ criteria pairs.

Using the IFERROR function will enable you to take your modelling skills to the next level. Whenever a calculation renders an error (say, dividing by 0), you will have problems incorporating this result in future calculations. With the IFERROR functionality, you can circumvent this problem.

Further practice your understanding of IF functions with this video. In addition to that, you will also learn how to remove duplicates from a data set to arrive at a clean set of unique values.

Let's move on to one of the most frequently used formulas: VLOOKUP. Understanding the boundaries within which this formula works is absolutely key. After understanding its use cases, we will slowly extend its applications.

Rather than looking for an exact match with the FALSE argument, sometimes we have to settle for the closest approximation of a certain value. The VLOOKUP in combination with the TRUE argument can also prove beneficial in examples such as determining trade rebates.

The VLOOKUP comes in handy when creating a so-called mapping for two data sets sharing a unique identifier. One key thing we need to bear in mind for this, however, is that the rule of having the lookup value in the leftmost cell also applies in this scenario, and that we may have to tweak our data first before we can apply the VLOOKUP.

Often overlooked, but just as powerful: Whilst VLOOKUP allows you to search from left to right, HLOOKUP can be used to search from top to bottom.

Only available to Office 365 users

It's finally released!! Microsoft has made the Xlookup available for all users of O365. In this video, I will teach you how this new formula outperforms all of the previous lookups we have discussed.

A major drawback of the VLOOKUP is that we need to have the lookup value in the leftmost column our our table array. With a combination of the INDEX and MATCH functions, we no longer have to worry about this, and can freely navigate within our table.

Closing the loop on this topic, I want to show you how you can use INDEX and MATCH to dynamically plan your next road trip.

Using filters and the sorting functionality is key to gaining valuable insights into your data quickly.

By formatting your data as an actual table, you gain access to new functionalities, such as automatically copying and pasting your formulas. Another key advantage of having your data formatted as a table is the fact that any newly added data will automatically be considered within the range of your specified table.