The 3-in-1 Excel, VBA & SQL Master Course

Let's check some examples of using Excel. Throughout the course, we'll get back to these in detail, of course.

We're gonna work a lot with worksheets, so it's time to discuss what they are.

The ribbon is where the Excel commands are grouped. Let's check it and let's also talk about undoing and redoing the changes we've made.

We'll discuss the Quick Access Toolbar, how to save our work and some other useful features.

We'll discuss the Save As functionality in detail, but we'll also check out what options the File tab offers in general.

We'll change column widths and insert new rows. But, ultimately, we'll discuss the very basics of organizing our data.

We'll talk about the SUM function and arguments in general. We'll write our very first formula/function in Excel!

This time we'll pass more arguments to the SUM function. We'll also use the Insert Function command for the first time.

We'll learn the different ways of referring to cells and ranges inside a function.

We'll sum numbers with an arithmetic operator. We'll also discuss the difference between formulas and functions.

Let's talk about subtraction! We'll familiarize ourselves with another arithmetic operator.

Yet another arithmetic operation will be discussed: multiplication. But we'll also talk about operation precedence and what Protected View is.

We'll use parentheses to force Excel to calculate our formulas differently. We'll also take a look at the Evaluate Formula command for the first time.

We've arrived at the fourth arithmetic operation: division. We'll also quickly discuss decimal places.

We've been using dynamic cell references in the course so far, but now it's time to discuss them in more detail.

Let's check how Excel adjusts the row numbers/indexes when working with dynamic cell references.

Let's format the font and background of a cell. We'll also discuss the Format Painter command.

We'll check the alignment options.

Let's discuss cell borders.

We'll format a range of cells this time, not just a single cell.

We'll familiarize ourselves with the Formulas Tab and the AutoSum command.

Let's discuss the Calculation command group of the Formulas Tab.

We'll discuss the Formula Auditing command group of the Formulas Tab. Most importantly, we'll start discussing how to handle errors in our formulas.

We'll discuss circular references, which we must avoid!

Let's check how the Watch Window works.

Let's learn how to use the AVERAGE function.

Let's learn how to use the MIN function.

Let's learn how to use the MAX function.

Let's learn how to use the COUNT function.

What happens if the format of our cell is General (the default option) but we start increasing the decimals of the underlying value? Let's find out!

Up to how many digits can Excel provide precise calculations? Let's find out!

Our topic is the scientific notation and very big numbers.

We'll continue our discussion about the scientific notation. Let's see how we can represent very small numbers in scientific notation.

Excel is great when working with dates. Let's take a look at how Excel handles dates.

How can Excel actually understand dates? Let's find out!

Epoch is an important topic not just for Excel, but for computer science in general. Let's discuss the epoch in regards to Excel.

A quick example for using the percentage format. We'll also briefly talk about absolute references.

We might want to show a number as a currency, so let's see how we can do that. We'll also talk about changing the thousand and decimal separators.

Let's check our options for showing negative numbers as currencies, and we'll also talk about the accounting format.

Although it's unusual to represent numbers as fractions in Excel, this might be the format you need, so, of course, we'll briefly discuss the topic.

We work a lot with data in Excel that is simple text. But working with text is not always simple or straightforward.

So far, we've used the letters of the English alphabet only. However, Excel can handle all kinds of letters/characters.

In certain cases, it could be useful to format numbers as text, but we have to be careful about this!

Let's add filters to the headers of our table, and let's quickly filter some names!

Let's continue filtering the names. This time we'll use the small search bar as well.

Let's take a look at some examples of using the Custom AutoFilter box/dialogue.

We've filtered for text previously, now it's time to filter for numbers.

Let's finish the discussion of filtering numbers.

Let's check how we can filter based on cell color, and we'll also discuss why it is not a good idea to have gaps in your table.

Excel offers a lot of options for filtering dates. Let's take a look.

We'll learn how to sort data. We'll discuss why we must avoid gaps in our tables when we are sorting data. I'll also ruin my data on purpose, so you'll know what NEVER to do in Excel.

Sometimes it's not enough to sort our data based on a single column. So, we must know how to apply multi-level sorting.

When working with data, we prefer to have a nice layout. Autofitting helps us to adjust the size of our rows and columns, so that they fit our data perfectly.

Cells might contain a lot of text. In that case, the Wrap Text command could be very useful. We'll also discuss the Merge & Center command.

When you're working with large amounts of data, the Freeze Panes command can help you to maintain a good overview of your data, even when you have to scroll a lot.

If you're working with a lot of data, you might want to rearrange it. We'll discuss how we can do this easily.

If you're using the same range for various calculations, it might be advantageous to give a name to that range, so that you can refer to the range by a name. We'll discuss how to do that.

Let's discuss the scope of named ranges.

So far, from the point of view of Excel, the tables we've been working with are just ranges of cells. Let's see what Excel considers an actual table.

Let's continue the discussion about inserted tables. We'll also discuss when it might not be a good idea to use named ranges.

The INDEX function can be deceivingly simple, however, there's a lot to discuss about it. This is just the beginning!

We'll use the INDEX function with a named range argument. We'll also return a whole column with the INDEX function, however, this works only in Excel 365.

We'll take a closer look at the 2nd implementation of the INDEX function.

We'll combine the SUM function with the INDEX function to sum the values of a specific column. This is the first time we use nested functions!

We'll start discussing the famous VLOOKUP function, but we'll also take a quick look at the Search & Replace functionality of Excel.

I'll just say a few words about the search functionality, because I was a bit brief about it in the previous video. Later, we'll discuss it in more detail.

Let me show you step-by-step how VLOOKUP works.

Our formula looks good, but we don't get the expected result. Let's investigate the problem!

So far, we've forced VLOOKUP to search for exact matches. It's time to discuss approximate matches as well.

Let's discuss approximate matches further. Can we make sure that there will always be an approximate match?

I'll show you why it is a bad idea to get approximate matches when searching for text. I'll also briefly explain what "fuzzy" search means.

Let's make the error message more descriptive with the IFERROR function when VLOOKUP cannot find a match. We'll also talk about how to make long formulas more readable.

Let's check what happens to the cell references inside nested functions (spoiler alert: there will not be any surprises here).

A brief discussion about the NAME error.

A brief discussion about the VALUE error.

A brief discussion about the REF error.

A brief discussion about the NUM error.

A brief discussion about the DIV/0 error.

A brief discussion about the NULL error.

It can happen that the data you receive from someone is a bit problematic. For example, you might receive text values that have spaces at the beginning or end. The TRIM function is a perfect choice for such a scenario.

How can we be sure that the TRIM function removes the spaces from the end? Answer: by using the LEN function!

We'll learn how we can check if 2 cells contain the same value. We'll also talk about the special TRUE and FALSE values.

Having blank rows in a table can make our work impossible. Let's check how we can quickly remove annoying blank rows!

You might want to hide some of columns/rows of your table, or, even more importantly, you always want to make sure that no data is hidden from you when you work with a table. Let's check how we can hide/unhide data with Excel!

Sooner or later, you'll probably ask people to provide some information to you. With dropdown lists, you can restrict the possibilities for providing data, so you can make sure that you'll end up with consistent data.

Let's dig a bit deeper into the topic of dropdown lists.

We'll finish the topic of dropdown lists in this video.

It's very important to understand conditional logic. So, let's start discussing it while learning a new function!

Let's check what comparison operators are available to us!

Previously, we've used the IF function to decide which salespeople are eligible for bonuses. Now, we'll use the IFS function to assign a tier or title to them based on their performance.

Let's use the IFERROR function to get rid of the NA errors!

Let's get rid of the IFERROR function and use an alternative way of assigning the title "Intern" to some of our salespeople.

We need a way to check if more than one conditions are TRUE. We'll use the AND function to achieve that.

Conditional formulas can become very long very quickly, so let's adjust the Formula Bar so that we can read our formulas more easily.

What if we want to check if at least one condition (of a set of conditions) is TRUE? Answer: use the OR function!

Let's combine the IF and OR functions!

With the COUNTIF function, we can count the number of cells where a given condition is TRUE. For example, we can quickly calculate how many salespeople are eligible for a bonus.

Let's practice the COUNTIF function a bit by using different comparison operators.

Let's use the COUNTIFS function to count the number of cells where more than one given conditions are TRUE.

We already know how to sum cells with the SUM function, however, now we'll learn about the SUMIF function so that we can sum only those cells that meet a given condition.

Let's use the SUMIF function again, but this time we'll provide a Sum_range argument to it.

We can use the SUMIFS functions to sum only those cells that evaluate to TRUE for multiple conditions. We'll sum the profit made by those salespeople who represent the West Sales Company in the EMEA region.

Conditional formatting is great for adding visual clues to our data based on some conditional logic. I'll provide an overview of the abundant conditional formatting options available to us in Excel.

Let's apply our first conditional formatting rule!

Let's apply an additional conditional formatting rule to our range, but this time we should create our own custom rule instead of using a built-in one.

Let's apply a conditional formatting rule with icon sets and make sure that the icon distribution looks nice.

Let's check what happens when the order of the applied conditional formatting rules matters.

It's very important to check if your data contains duplicates. We can easily check for duplicates by using conditional formatting.

The time has come for discussing cell references. In this video, I'll introduce a scenario/task that'll help us understand how dynamic cell references work.

Let's calculate some net salaries so that we'll really understand how dynamic cell references work.

We've already seen absolute cell references throughout the course, but we haven't discussed what they are exactly. We'll press F4 and the magic will happen!

Let's discuss mixed cell references: we'll lock only the column references this time!

Let's discuss mixed cell references further: we'll lock the row references this time!

Let's refer to a cell on another worksheet with an absolute cell reference!

If a worksheet name contains at least one space character, we'll have to adjust the reference to it.

Although it's usually easier to work with a single workbook, it might happen that we have to make references to other workbooks. Let's see how!

So far, we've referenced workbooks in our formulas that have already been opened. Do you think our formulas will break if I close the referenced workbooks? Let's find out!